Unity3D访问SQL server数据库
导入unity3D连接数据库相关库（dll）
unity3D必须向创建的工程中添加相关类库，才能连接上SQL server数据库。
相关类库在unity3D的安装位置可以找到。
我的路径为H:\Unity\Editor\Data\Mono\lib\mono\2.0
不同版本的unity不能通用图上System.Data.dll,因为 .Net框架有差别，低版本的dll放在高版本unity工程中，在使用脚本引用后访问特定类或方法会报错。
Assets/Scripts/SQLConnect.cs(140,9): error CS0012: The type
System.Xml.Serialization.IXmlSerializable' is defined in an assembly that is not referenced. Consider adding a reference to assemblySystem.Xml, Version=2.0.5.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e’
低版本unity使用的System.Data.dll导入高版本unity中，在使用DataSet类时会报上面的错误。
我用4.6的System.Data.dll导入5.3.3版不会报错，导入5.5.3版会报上面的错误。5.0.4的导入2017.4.2中也不会报错。
三层架构
三层架构(3-tier architecture) 通常意义上的三层架构就是将整个业务应用划分为:界面层(User Interface layer)、业务逻辑层(Business Logic Layer)、数据访问层(Data access layer)。区分层次的目的即为了"高内聚低耦合"的思想。在软件体系架构设计中，分层式结构是最常见，也是最重要的一种结构。微软推荐的分层式结构一般分为三层，从下至上分别为:数据访问层、业务逻辑层(又或称为领域层)、表示层。
数据访问层
1:数据访问层:主要是对非原始数据(数据库或者文本文件等存放数据的形式)的操作层，而不是指原始数据，也就是说，是对数据库的操作，而不是数据，具体为业务逻辑层或表示层提供数据服务。
我一般先建立存储表中的数据的类。
using UnityEngine;
using System.Collections;
public class DC_bianya
{
private string DC_NO;
private string DC_Name;
private string DC_Inform;
private bool DC_Sstate;
//编号
public string NO
{
get { return DC_NO; }
set { DC_NO = value; }
}
//名称
public string Name
{
get { return DC_Name; }
set { DC_Name = value; }
}
//信息
public string Inform
{
get { return DC_Inform; }
set { DC_Inform = value; }
}
//状态
public bool Sstate
{
get { return DC_Sstate; }
set { DC_Sstate = value; }
}
}
建立数据连接类
using UnityEngine;
using System.Collections;
using System.Data;
using System.Data.SqlClient ;
using System;
using System.Text;
public class ServerDBManager
{
private string constr = "";
// private string lastErrorInfo = "";
private SqlConnection sqlcon;
private SqlTransaction sqltrans;
private string m_ServerIP = ""; //SQL数据库IP，一般用IP
private string m_DBName= ""; //对应数据库名
private string m_UserName = ""; //SQL登录名，混合模式
private string m_UserPwd = ""; //SQL密码
//关闭数据库连接
public bool CloseConnection()
{
if(this.sqlcon.State == ConnectionState.Open )
{
this.sqlcon.Close ();
}
return true;
}
//打开数据库连接
public bool OpenConnection(string ipstr,string dbname,string username,string userpwd)
{
try
{
this.m_ServerIP = ipstr;
this.m_DBName = dbname ;
this.m_UserName = username;
this.m_UserPwd = userpwd;
this.sqlcon = new SqlConnection ();
this.constr = "Data Source="+this.m_ServerIP +";Initial Catalog ="+this.m_DBName +";User ID="+this.m_UserName +";Password="+this.m_UserPwd;
this.sqlcon.ConnectionString = this.constr ;
this.sqlcon.Open();
}
catch(Exception e)
{
e.ToString ();
return false;
}
return true;
}
//获取记录
public DataSet GetRecorder(string sqlstr)
{
DataSet set = new DataSet();
try
{
SqlDataAdapter adapter = new SqlDataAdapter(sqlstr,sqlcon );
adapter.Fill(set);
adapter.Dispose();
}
catch (Exception e)
{
e.ToString();
return null;
}
return set;
}
//更新数据
public int UpdateRecorder(string sqlupdatestr)
{
int n;
try
{
SqlCommand command = new SqlCommand(sqlupdatestr ,sqlcon );
n = command.ExecuteNonQuery();
}
catch (Exception e)
{
e.ToString();
return -1;
}
return n;
}
//插入数据
public int Insertecorder(string sqlInsertStr)
{
int n;
try
{
SqlCommand command = new SqlCommand (sqlInsertStr ,sqlcon );
n = command.ExecuteNonQuery();
}
catch (Exception e)
{
e.ToString();
return -1;
}
return n;
}
//删除操作
public int DeleteRecorder(string sqldelete)
{
int n;
try
{
SqlCommand command = new SqlCommand(sqldelete, sqlcon);
n = command.ExecuteNonQuery();
}
catch (Exception e)
{
e.ToString();
return -1;
}
return n;
}
//CAD图纸进行二进制存储，
public bool InsertCADTest(ModelTestSet msa)
{
bool flag=false;
SqlTransaction localTrans;
localTrans = sqlcon.BeginTransaction();
StringBuilder cadbuild = new StringBuilder();
cadbuild.Append("insert into TestSet(");
cadbuild.Append("[TestID],");
cadbuild.Append("[TestType],");
cadbuild.Append("[TestName],");
cadbuild.Append("[TestURL],");
cadbuild.Append("[TestCAD],");
cadbuild.Append("[TestMark])");
cadbuild.Append(" values(");
tring str = "@TestID,@TestType,@TestName,@TestURL,@TestCAD,@TestMark)";
cadbuild.Append(str);
string cmdtest = cadbuild.ToString();
SqlCommand commmand = new SqlCommand(cmdtest, sqlcon);
commmand.Transaction = localTrans;
commmand.Parameters.AddWithValue("@TestID", msa.TestID);
commmand.Parameters.AddWithValue("@TestType", msa.TestType);
commmand.Parameters.AddWithValue("@TestName", msa.TestName);
commmand.Parameters.AddWithValue("@TestURL", msa.TestURL);
commmand.Parameters.AddWithValue("@TestCAD", msa.TestCAD);
commmand.Parameters.AddWithValue("@TestMark", msa.TestMark);
if (commmand.ExecuteNonQuery() > 0)
{
flag = true;
}
else
{
localTrans.Rollback();
flag = false;
}
localTrans.Commit();
return flag;
}
}
业务逻辑层
业务逻辑层(Business Logic Layer)无疑是系统架构中体现核心价值的部分。它的关注点主要集中在业务规则的制定、业务流程的实现等与业务需求有关的系统设计，也即是说它是与系统所应对的领域(Domain)逻辑有关，很多时候，也将业务逻辑层称为领域层。
业务逻辑类
using UnityEngine;
using System.Collections;
using System;
using System.Data;
using System.Data.SqlClient ;
using UnityEngine.UI;
public class BusinessManager
{
private string m_ServerIp = "";
private string m_DBName = "";
private string m_UserName = "";
private string m_UserPwd = "";
//private string m_ErrorStr = "";
//验证用户名和密码
public bool UserLogin(string username,string pwd,ref int info)
{
bool flag = false;
ServerDBManager db = new ServerDBManager();
if(!db.OpenConnection (m_ServerIp,m_DBName ,m_UserName,m_UserPwd))
{
info = 0;
return flag;
}
string sqlStr = "select userpwd from DCUser where userlevel=0 and userName='" + username + "'";
DataSet set = null;
try
{
set = db.GetRecorder(sqlStr);
if(set.Tables[0].Rows.Count !=1)
{
info = 1; //没有找到用户
flag = false ;
}
else
{
if(set.Tables[0].Rows[0]["userpwd"].ToString ()!=pwd )
{
info = 2; //密码错误
flag = false;
}
else
{
info =3; //密码通过
flag = true;
}
}
}
catch (Exception e)
{
info = 4; //异常
flag = false;
e.ToString ();
}
finally
{
db.CloseConnection ();
}
return flag;
}
//获取表中数据
public bool Getbianya(int Num)
{
DataSet dt = null;
//DC_bianya bianya = new DC_bianya();
ServerDBManager sd = new ServerDBManager ();
if(!sd.OpenConnection (m_ServerIp,m_DBName ,m_UserName,m_UserPwd))
{
//bianya = null;
return false ;
}
string sqlStr = "select Sstate from BianYaQi where Sstate=1 and NO="+Num;
try
{
dt = sd.GetRecorder (sqlStr );
}
catch (Exception e)
{
e.ToString ();
}
finally
{
sd.CloseConnection ();
}
return (dt.Tables[0].Rows.Count ==1);
}
//数据记录
public int GetRecordID(string user)
{
int flag = -1;
DataSet ds = null;
ServerDBManager sdb = new ServerDBManager();
if (!sdb.OpenConnection(m_ServerIp, m_DBName, m_UserName, m_UserPwd))
{
return flag;
}
string sqlStr = "insert into Record(UserID,RecDate) values('" + user + "',getdate())";
try
{
if (sdb.Insertecorder(sqlStr) == 1)
{
string sqls = "select top 1 RecordID from Record where UserID='" + user + "' order by RecDate desc";
ds = sdb.GetRecorder(sqls);
flag = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
}
}
catch (Exception e)
{
e.ToString();
}
finally
{
sdb.CloseConnection();
}
return flag;
}
//插入记录
public bool AddFault(int id,string fau,string fkey)
{
bool flag = false;
ServerDBManager sdb = new ServerDBManager();
if (!sdb.OpenConnection(m_ServerIp, m_DBName, m_UserName, m_UserPwd))
{
return flag;
}
string sqlStr = "insert into Fault(RecordID,FaultInfo,FaultJudge,Fkey) values(" + id + ",'" + fau + "','是','" + fkey + "')";
try
{
if (sdb.Insertecorder(sqlStr) == 1)
{
flag = true;
}
}
catch (Exception e)
{
e.ToString();
}
finally
{
sdb.CloseConnection();
}
return flag;
}
//人员登录列表
public List<HCLogin> GetHCLogin(string filtStr)
{
DataSet dt = null;
List<HCLogin> hclog = new List<HCLogin>();
ServerDBManage sdb = new ServerDBManage();
if (!sdb.OpenConnection(S_ServerIP, S_DBName, S_UserName, S_UserPwd))
{
return hclog;
}
string sqlStr = "select userID,userName,userPwd,userLevel,userTeam,userFlag,CountFlag from HClogin where 1=1" + filtStr;
try
{
dt = sdb.GetRecorder(sqlStr);
}
catch (Exception e)
{
e.ToString();
}
finally
{
sdb.CloseConnection();
}
if (dt != null && dt.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
{
HCLogin hcl = new HCLogin();
hcl.HCuserid = dt.Tables[0].Rows[i][0].ToString().Trim();
hcl.HCusername = dt.Tables[0].Rows[i][1].ToString().Trim();
hcl.HCuserpwd = dt.Tables[0].Rows[i][2].ToString().Trim();
hcl.HCuserlevel = dt.Tables[0].Rows[i][3].ToString().Trim();
hcl.HCuserteam = dt.Tables[0].Rows[i][4].ToString().Trim();
hcl.HCuserflag = dt.Tables[0].Rows[i][5].ToString().Trim();
hcl.HCCountFlag =Convert.ToInt32(dt.Tables[0].Rows[i][6]);
hclog.Add(hcl);
}
}
return hclog;
}
public HCLogin GetUserName(string ID)
{
DataSet dt = null;
HCLogin hc = new HCLogin();
ServerDBManage sdb = new ServerDBManage();
if (!sdb.OpenConnection(S_ServerIP, S_DBName, S_UserName, S_UserPwd))
{
return hc;
}
string sqlStr = "select userName,userTeam,userFlag,CountFlag,userLevel from HClogin where userID = "+ID;
try
{
dt = sdb.GetRecorder(sqlStr);
}
catch (Exception e)
{
e.ToString();
}
finally
{
sdb.CloseConnection();
}
if (dt != null&&dt.Tables[0].Rows.Count>0)
{
hc.HCusername = dt.Tables[0].Rows[0][0].ToString().Trim();
hc.HCuserteam = dt.Tables[0].Rows[0][1].ToString().Trim();
hc.HCuserflag =dt.Tables[0].Rows[0][2].ToString().Trim();
hc.HCCountFlag =Convert.ToInt32 (dt.Tables[0].Rows[0][3]);
hc.HCuserlevel = dt.Tables[0].Rows[0][4].ToString().Trim();
}
return hc;
}
#region 公有属性
/// <summary>
/// 数据库服务器
/// </summary>
public string ServerIp
{
get {return m_ServerIp;}
set { m_ServerIp = value; }
}
/// <summary>
/// 数据库名称
/// </summary>
public string DBName
{
get { return m_DBName; }
set { m_DBName = value; }
}
/// <summary>
/// 用户名
/// </summary>
public string UserName
{
get { return m_UserName; }
set { m_UserName = value; }
}
/// <summary>
/// 密码
/// </summary>
public string UserPwd
{
get { return m_UserPwd; }
set { m_UserPwd = value; }
}
#endregion
}
连接数据库参数可以从外部文件中读取，也可以加密。
using System;
using System.IO;
using System.Runtime.InteropServices;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Specialized;
using System.Security.Cryptography;
using System.Threading.Tasks;
public class IniFiles
{
public string FileName;
[DllImport("kernel32")]
private static extern bool WritePrivateProfileString(string section, string key, string val, string filePath);
[DllImport("kernel32")]
private static extern int GetPrivateProfileString(string section, string key, string def, byte[] retVal, int size, string filePath);
//类的构造函数，传递INI文件名
public IniFiles(string AFileName)
{
// 判断文件是否存在
FileInfo fileInfo = new FileInfo(AFileName);
//Todo:搞清枚举的用法
if ((!fileInfo.Exists))
{ //|| (FileAttributes.Directory in fileInfo.Attributes))
//文件不存在，建立文件
System.IO.StreamWriter sw = new System.IO.StreamWriter(AFileName, false, System.Text.Encoding.Default);
try
{
sw.Write("#配置档案");
sw.Close();
}
catch
{
throw (new ApplicationException("Ini文件不存在"));
}
}
//必须是完全路径，不能是相对路径
FileName = fileInfo.FullName;
}
//写INI文件
public void WriteString(string Section, string Ident, string Value)
{
string inputStr = Encode(Value);//Value;//Encode()加密
if (!WritePrivateProfileString(Section, Ident, inputStr, FileName))
{
throw (new ApplicationException("写Ini文件出错"));
}
//if (!WritePrivateProfileString(Section, Ident, Value, FileName))
//{
// throw (new ApplicationException("写Ini文件出错"));
//}
}
//读取INI文件指定
public string ReadString(string Section, string Ident, string Default)
{
Byte[] Buffer = new Byte[65535];
int bufLen = GetPrivateProfileString(Section, Ident, Default, Buffer, Buffer.GetUpperBound(0), FileName);
//必须设定0（系统默认的代码页）的编码方式，否则无法支持中文
string s = Encoding.GetEncoding(0).GetString(Buffer);
s = s.Substring(0, bufLen);
s = Decode(s);
return s.Trim();
}
//读整数
public int ReadInteger(string Section, string Ident, int Default)
{
string intStr = ReadString(Section, Ident, Convert.ToString(Default));
try
{
return Convert.ToInt32(intStr);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return Default;
}
}
//写整数
public void WriteInteger(string Section, string Ident, int Value)
{
WriteString(Section, Ident, Value.ToString());
}
//读布尔
public bool ReadBool(string Section, string Ident, bool Default)
{
try
{
return Convert.ToBoolean(ReadString(Section, Ident, Convert.ToString(Default)));
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return Default;
}
}
//写Bool
public void WriteBool(string Section, string Ident, bool Value)
{
WriteString(Section, Ident, Convert.ToString(Value));
}
//从Ini文件中，将指定的Section名称中的所有Ident添加到列表中
public void ReadSection(string Section, StringCollection Idents)
{
Byte[] Buffer = new Byte[16384];
//Idents.Clear();
int bufLen = GetPrivateProfileString(Section, null, null, Buffer, Buffer.GetUpperBound(0),
FileName);
//对Section进行解析
GetStringsFromBuffer(Buffer, bufLen, Idents);
}
private void GetStringsFromBuffer(Byte[] Buffer, int bufLen, StringCollection Strings)
{
Strings.Clear();
if (bufLen != 0)
{
int start = 0;
for (int i = 0; i < bufLen; i++)
{
if ((Buffer[i] == 0) && ((i - start) > 0))
{
String s = Encoding.GetEncoding(0).GetString(Buffer, start, i - start);
Strings.Add(s);
start = i + 1;
}
}
}
}
//从Ini文件中，读取所有的Sections的名称
public void ReadSections(StringCollection SectionList)
{
//Note:必须得用Bytes来实现，StringBuilder只能取到第一个Section
byte[] Buffer = new byte[65535];
int bufLen = 0;
bufLen = GetPrivateProfileString(null, null, null, Buffer,
Buffer.GetUpperBound(0), FileName);
GetStringsFromBuffer(Buffer, bufLen, SectionList);
}
//读取指定的Section的所有Value到列表中
public void ReadSectionValues(string Section, NameValueCollection Values)
{
StringCollection KeyList = new StringCollection();
ReadSection(Section, KeyList);
Values.Clear();
foreach (string key in KeyList)
{
Values.Add(key, ReadString(Section, key, ""));
}
}
/**/
读取指定的Section的所有Value到列表中，
//public void ReadSectionValues(string Section, NameValueCollection Values,char splitString)
//{ string sectionValue;
// string[] sectionValueSplit;
// StringCollection KeyList = new StringCollection();
// ReadSection(Section, KeyList);
// Values.Clear();
// foreach (string key in KeyList)
// {
// sectionValue=ReadString(Section, key, "");
// sectionValueSplit=sectionValue.Split(splitString);
// Values.Add(key, sectionValueSplit[0].ToString(),sectionValueSplit[1].ToString());
// }
//}
//清除某个Section
public void EraseSection(string Section)
{
//
if (!WritePrivateProfileString(Section, null, null, FileName))
{
throw (new ApplicationException("无法清除Ini文件中的Section"));
}
}
//删除某个Section下的键
public void DeleteKey(string Section, string Ident)
{
WritePrivateProfileString(Section, Ident, null, FileName);
}
//Note:对于Win9X，来说需要实现UpdateFile方法将缓冲中的数据写入文件
//在Win NT, 2000和XP上，都是直接写文件，没有缓冲，所以，无须实现UpdateFile
//执行完对Ini文件的修改之后，应该调用本方法更新缓冲区。
public void UpdateFile()
{
WritePrivateProfileString(null, null, null, FileName);
}
//检查某个Section下的某个键值是否存在
public bool ValueExists(string Section, string Ident)
{
//
StringCollection Idents = new StringCollection();
ReadSection(Section, Idents);
return Idents.IndexOf(Ident) > -1;
}
//确保资源的释放
~IniFiles()
{
UpdateFile();
}
//加密方法
public static string Encode(string data)
{
string KEY_64 = "VavicApp";
string IV_64 = "VavicApp";
byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(KEY_64);
byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(IV_64);
DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
int i = cryptoProvider.KeySize;
MemoryStream ms = new MemoryStream();
CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateEncryptor(byKey, byIV), CryptoStreamMode.Write);
StreamWriter sw = new StreamWriter(cst);
sw.Write(data);
sw.Flush();
cst.FlushFinalBlock();
sw.Flush();
return Convert.ToBase64String(ms.GetBuffer(), 0, (int)ms.Length);
}
//解密方法
public static string Decode(string data)
{
string KEY_64 = "VavicApp";
string IV_64 = "VavicApp";
byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(KEY_64);
byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(IV_64);
byte[] byEnc;
try
{
byEnc = Convert.FromBase64String(data);
}
catch
{
return null;
}
DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
MemoryStream ms = new MemoryStream(byEnc);
CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateDecryptor(byKey, byIV), CryptoStreamMode.Read);
StreamReader sr = new StreamReader(cst);
return sr.ReadToEnd();
}
}
表示层
位于最外层(最上层)，最接近用户。用于显示数据和接收用户输入的数据，为用户提供一种交互式操作的界面。
定义全局类
using UnityEngine;
using System.Collections;
public static class GlobalMemo
{
public static string m_SysUser="";
public static string m_ServerIp = "";
public static string m_DBName = "";
public static string m_DBUser = "";
public static string m_DBPwd = "";
public static string m_SystemArea = "3601";
}
登陆类
using UnityEngine;
using System.Collections;
using UnityEngine.UI;
public class LoginBtnManager : MonoBehaviour
{
public InputField user;
public InputField pwd;
public GameObject warn;
public Text warnt;
//登陆方法
public void Login()
{
if(!GetDBConfig())
{
return ;
}
BusinessManager bmgr = new BusinessManager();
bmgr.ServerIp = GlobalMemo.m_ServerIp;
bmgr.DBName = GlobalMemo.m_DBName;
bmgr.UserName = GlobalMemo.m_DBUser;
bmgr.UserPwd = GlobalMemo.m_DBPwd;
int reflag = 0;
string username = user.text;
string password = pwd.text;
//调用逻辑层方法
bmgr.UserLogin (username,password,ref reflag);
switch(reflag)
{
case 0:
warnt.text = "数据库未连接";
warn.SetActive(true);
warnt.IsActive();
break;
case 1:
warnt.text = "用户名不存在";
warn.SetActive(true);
warnt.IsActive();
break;
case 2:
warnt.text = "密码错误";
warn.SetActive(true);
warnt.IsActive();
break;
case 3:
PlayerPrefs.SetString ("User",username);
Application.LoadLevel ("dianchang");
break;
}
}
public void Cancel()
{
Application.Quit();
}
/// <summary>
/// 获得数据库配置
/// </summary>
/// <returns></returns>
private bool GetDBConfig()
{
bool flag = false;
string s = Application.dataPath;
//IniFiles _IniFiles = new IniFiles(System.AppDomain.CurrentDomain.BaseDirectory + "config.ini");
IniFiles _IniFiles = new IniFiles(s + "/config.ini");
GlobalMemo.m_ServerIp = _IniFiles.ReadString("DBCof", "SQLAddress", "");
GlobalMemo.m_DBName = _IniFiles.ReadString("DBCof", "SQLDB", "");
GlobalMemo.m_DBUser = _IniFiles.ReadString("DBCof", "SQLUser", "");
GlobalMemo.m_DBPwd = _IniFiles.ReadString("DBCof", "SQLPwd", "");
if (GlobalMemo.m_ServerIp == "" || GlobalMemo.m_DBName == "" || GlobalMemo.m_DBUser == "" ||GlobalMemo.m_DBPwd == "")
{
flag = false;
}
else
{
flag = true;
}
return flag;
}
}
config.ini外部数据DES算法
版权声明：本文为zhu2370830原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。