首页 > 代码库 > Unity3D 连接MySQL数据库笔记4-Unity3d代码
Unity3D 连接MySQL数据库笔记4-Unity3d代码
新建Unity3D项目
新建一个 Plugins文件夹
把所需要的DLL放到 Plugins中
新建C#脚本 命名为ToMysql.cs
不多说了 ，直接上代码，很简单 有注释 ，大家一看就明白了
using UnityEngine;using System;using System.Collections;using System.Data;using MySql.Data.MySqlClient;public class ToMysql : MonoBehaviour { public static MySqlConnection dbConnection; /*mysql数据库连接对象*/ static string host = ""; static string id = ""; static string pwd = ""; static string database = ""; static string result = ""; private string strCommand ; public static DataSet MyObj; public string connectionString; public Vector2 pPos; public Vector2 pSize; public DataTable pdatatable; public DataSet pDataset; public Vector2 conPos; public Vector2 conSize; void Awake() { host = "127.0.0.1"; /*mysql数据库服务器地址*/ id = "root"; /*mysql用户名*/ pwd = "123456"; /*密码*/ database = "test";/*数据库名*/ result = ""; /*mysql版本号信息暂存*/ strCommand = "Select uname from tuser"; /*一些UI按钮的坐标配置信息11*/ pPos = new Vector2( 50,50 ); pSize = new Vector2(100,35); /*一些UI按钮的坐标配置信息22*/ conPos = new Vector2( 300,60 ); conSize = new Vector2( 150, 50 ); /*初始给一个新增员工信息的时候的默认文本框内默认值*/ pkey = "100291"; name = "姓名"; sex = "男"; age = "25"; adress = "家庭详细住址"; money = "2800"; } void OnGUI() { /*第一次连接到MySQL数据库*/ ConnectFirstServer(); /*显示连接到的MySQL数据库的版本号信息*/ GUILayout.Label(result); /*主要大列表显示员工信息的修改删除function*/ ShowP_GUIData(); /*添加员工信息*/ AddPerson(); /*点了修改编辑信息后*/ ShowEdit_PersonData(); } /*关闭Unity程序*/ public static void OnApplicationQuit() { closeSqlConnection(); } /*第一次连接到MySQL数据库*/ public void ConnectFirstServer() { if( dbConnection == null ) { GUI.Button( new Rect(conPos.x ,conPos.y,conSize.x,conSize.y),"数据库Server地址"); host = GUI.TextField(new Rect(conPos.x ,conPos.y+50,conSize.x,conSize.y),host ); GUI.Button( new Rect(conPos.x ,conPos.y+100,conSize.x,conSize.y),"mysql用户名"); id = GUI.TextField(new Rect(conPos.x ,conPos.y+150,conSize.x,conSize.y),id ); GUI.Button( new Rect(conPos.x ,conPos.y+200,conSize.x,conSize.y),"密码"); pwd = GUI.TextField(new Rect(conPos.x ,conPos.y+250,conSize.x,conSize.y),pwd ); GUI.Button( new Rect(conPos.x ,conPos.y+300,conSize.x,conSize.y),"数据库名"); database = GUI.TextField(new Rect(conPos.x ,conPos.y+350,conSize.x,conSize.y),database ); connectionString = string.Format("Server = {0};Database = {1}; User = {2}; Password = {3};",host,database,id,pwd); if(GUI.Button(new Rect(conPos.x+180 ,conPos.y+200,conSize.x,conSize.y),"登录数据库")) { openSqlConnection(connectionString); MyObj = GetDataSet(strCommand); /*读取数据函数Debug 管理员账号密码*/ ReaderData(); /*测试方法Debug 管理员账号密码*/ ShowDataSet(); /*重新读取p_info表里所有员工信息*/ SelectPdataSet(); } } } // Connect to database private static void openSqlConnection(string connectionString) { dbConnection = new MySqlConnection(connectionString); try{ dbConnection.Open(); result = dbConnection.ServerVersion; /*获得MySql的版本*/ } catch(MySqlException e) { Debug.Log( e.Message ); dbConnection = null; host = "服务器连接错"; id = "用户名或密码错误"; pwd = "用户名或密码错误"; } Debug.Log(result); } /*关闭Mysql连接*/ private static void closeSqlConnection() { dbConnection.Close(); dbConnection = null; } /*执行某些mysql语句*/ public static void doQuery(string sqlQuery) { IDbCommand dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = sqlQuery; IDataReader reader = dbCommand.ExecuteReader(); reader.Close(); reader = null; dbCommand.Dispose(); dbCommand = null; } #region Get DataSet /*得到一个 dataSet对象*/ public DataSet GetDataSet(string sqlString) { DataSet ds = new DataSet(); try { MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection); da.Fill(ds); } catch (Exception ee) { Debug.Log("SQL:" + sqlString + "\n" + ee.Message.ToString()); dbConnection = null; } return ds; } #endregion /*Debug显示管理员账号密码*/ void ReaderData() { MySqlCommand mySqlCommand = new MySqlCommand("Select * from tuser;", dbConnection); MySqlDataReader reader = mySqlCommand.ExecuteReader(); try { while (reader.Read()) { if (reader.HasRows) { print("uname:" + reader.GetString(0) + "--upwd：" + reader.GetString(1) ); } } } catch (Exception e) { Debug.Log("查询失败了！"+ e.Message ); dbConnection = null; } finally { reader.Close(); } } /*Debug显示管理员账号密码*/ void ShowDataSet() { string commandString = "Select * from tuser"; /* 创建DataSet命令对象和DataSet*/ if(dbConnection.State != ConnectionState.Open) { dbConnection = new MySqlConnection(connectionString); try { dbConnection.Open(); result = dbConnection.ServerVersion; /*获得MySql的版本*/ } catch(MySqlException e) { Debug.Log( e.Message ); dbConnection = null; } } using( MySqlDataAdapter DataAdapter = new MySqlDataAdapter(commandString, connectionString)) { DataSet Dataset = new DataSet(); /* 填充DataSet对象*/ DataAdapter.Fill(Dataset, "tuser"); /*从DataSet获取一个表*/ DataTable datatable = Dataset.Tables[0]; for (int i = 0; i < datatable.Rows.Count; i++) { Debug.Log( datatable.Rows[i]["uname"].ToString() ); Debug.Log( datatable.Rows[i]["upwd"].ToString() ); } } } /*显示员工信息function*/ void SelectPdataSet() { string commandString = "Select * from p_info"; /* 创建DataSet命令对象和DataSet*/ if(dbConnection.State != ConnectionState.Open) { dbConnection = new MySqlConnection(connectionString); try{ dbConnection.Open(); result = dbConnection.ServerVersion; /*获得MySql的版本*/ } catch(MySqlException e) { Debug.Log( e.Message ); } } using( MySqlDataAdapter DataAdapter = new MySqlDataAdapter(commandString, connectionString)) { if(pDataset !=null ) { pDataset = null; } pDataset = new DataSet(); /* 填充DataSet对象*/ DataAdapter.Fill(pDataset, "p_info"); /*从DataSet获取一个表*/ if(pdatatable != null) { pdatatable = null; } pdatatable = pDataset.Tables[0]; } } /*主要列表显示员工信息的修改删除function*/ void ShowP_GUIData() { if( pdatatable != null ) { if(pdatatable == null) return; try{ for (int i = 0; i < pdatatable.Rows.Count; i++) { GUI.Button(new Rect(pPos.x ,15 ,pSize.x,pSize.y ), "姓名" ); GUI.Button(new Rect(pPos.x+100 ,15 ,pSize.x,pSize.y ), "性别" ); GUI.Button(new Rect(pPos.x+200 ,15 ,pSize.x,pSize.y ), "年龄" ); GUI.Button(new Rect(pPos.x+300 ,15 ,pSize.x +200,pSize.y ), "家庭详细住址" ); GUI.Button(new Rect(pPos.x+600,15 ,pSize.x,pSize.y ), "月工资" ); GUI.Button(new Rect(pPos.x ,pPos.y +i*35 ,pSize.x,pSize.y ), pdatatable.Rows[i]["pname"].ToString() ); GUI.Button(new Rect(pPos.x+100 ,pPos.y +i*35 ,pSize.x,pSize.y ), pdatatable.Rows[i]["psex"].ToString() ); GUI.Button(new Rect(pPos.x+200 ,pPos.y +i*35 ,pSize.x,pSize.y ), pdatatable.Rows[i]["page"].ToString() ); GUI.Button(new Rect(pPos.x+300 ,pPos.y +i*35 ,pSize.x +200,pSize.y ), pdatatable.Rows[i]["padress"].ToString() ); GUI.Button(new Rect(pPos.x+600,pPos.y +i*35 ,pSize.x,pSize.y ), pdatatable.Rows[i]["pmoney"].ToString() ); if( GUI.Button(new Rect(pPos.x+710,pPos.y +i*35 ,pSize.x,pSize.y ), "修改" )) { canEdit_person = true; Debug.Log( pdatatable.Rows[i]["pkey"].ToString() ); Edit_Person( pdatatable.Rows[i]["pkey"].ToString() ); pdatatable = null; } if( GUI.Button(new Rect(pPos.x+810,pPos.y +i*35 ,pSize.x,pSize.y ), "删除" ) ) { DeletePerson( pdatatable.Rows[i]["pkey"].ToString() ); } } if( pdatatable.Rows.Count>=1) { if( GUI.Button(new Rect(pPos.x+750,15 ,pSize.x,pSize.y ), "增加员工信息" )) { canAddPerson = true; pdatatable = null; } } } catch { Debug.Log( "Some Error"); } } } public string pkey; public string name; public string sex; public string age; public string adress; public string money; public bool canAddPerson; public int selGridInt = 0; public string[] selsex = new string[] {"男", "女"}; public string sqlstr; /*添加员工信息的function*/ void AddPerson() { if(canAddPerson ) { GUI.Button( new Rect(150 ,conPos.y+100,conSize.x,conSize.y),"员工唯一编号" ); pkey = GUI.TextField(new Rect(conPos.x ,conPos.y+100,conSize.x,conSize.y),pkey ); GUI.Button( new Rect(150 ,conPos.y+160,conSize.x,conSize.y),"姓名" ); name = GUI.TextField(new Rect(conPos.x ,conPos.y+160,conSize.x,conSize.y),name ); GUI.Button( new Rect(150 ,conPos.y+220,conSize.x,conSize.y),"性别" ); selGridInt = GUI.SelectionGrid(new Rect(conPos.x ,conPos.y+220,100,30), selGridInt, selsex, 2); GUI.Button( new Rect(150 ,conPos.y+270,conSize.x,conSize.y),"年龄" ); age = GUI.TextField(new Rect(conPos.x ,conPos.y+270,conSize.x,conSize.y),age ); GUI.Button( new Rect(150 ,conPos.y+330,conSize.x,conSize.y),"家庭详细住址" ); adress = GUI.TextField(new Rect(conPos.x ,conPos.y+330,conSize.x,conSize.y),adress ); GUI.Button( new Rect(150 ,conPos.y+380,conSize.x,conSize.y),"员工月工资" ); money = GUI.TextField(new Rect(conPos.x ,conPos.y+380,conSize.x,conSize.y),money ); if( GUI.Button(new Rect(conPos.x+280 ,conPos.y+220,conSize.x,conSize.y),"添加此员工信息") ) { if( pkey.Trim()==""|| name.Trim()==""|| sex.Trim()==""||age.Trim()==""|| adress.Trim()==""||money.Trim()=="") { pkey = "有错误请重新输入"; name = "姓名"; sex = "性别必须为男或女"; age = "年龄必须为数字"; adress = "家庭详细住址"; money = "月工资必须为数字"; return; } if( selGridInt == 0 ) { sex = "男"; } if( selGridInt == 1 ) { sex = "女"; } if( isNumberic(age)==false && isNumberic(money)==false ) { pkey = "有错误请重新输入"; name = "姓名"; sex = "性别必须为男或女"; age = "年龄必须为数字"; adress = "家庭详细住址"; money = "月工资必须为数字或超过了限制"; return; } #region /*插入数据*/ try{ openSqlConnection(connectionString); if( dbConnection.State != ConnectionState.Open) { dbConnection.Open(); } sqlstr = string.Format("INSERT INTO p_info(`pkey`, `pname`, `psex`, `page`, `padress`, `pmoney`) VALUES (‘{0}‘,‘{1}‘,‘{2}‘,‘{3}‘,‘{4}‘,‘{5}‘)", pkey,name,sex,age,adress,money); MySqlCommand cmd = new MySqlCommand(sqlstr, dbConnection); cmd.ExecuteNonQuery(); dbConnection.Close(); Debug.Log("InsertData"); canAddPerson = false; SelectPdataSet(); } catch (MySqlException e){ pkey = "发生错误插入失败"; name = "姓名"; sex = "性别必须为男或女"; age = "年龄必须为数字"; adress = "家庭详细住址"; money = "月工资必须为数字或超过了限制"; Debug.Log("信息录入失败" + e.Message); } #endregion } if( GUI.Button(new Rect(conPos.x+280 ,conPos.y+320,conSize.x,conSize.y),"返回") ) { canAddPerson = false; SelectPdataSet(); } } } /*检查输入的是否是数字*/ public bool isNumberic(string message) { /*判断是否为整数字符串*/ /*是的话则将其转换为数字并将其设为out类型的输出值、返回true, 否则为false*/ int result = -1; /*result 定义*/ try { /*当数字字符串的为是少于4时，以下三种都可以转换，任选一种*/ /*如果位数超过4的话，请选用Convert.ToInt32() 和int.Parse()*/ result = int.Parse(message); return true; } catch { return false; Debug.Log("number false"); } } /*删除选中员工信息的方法*/ void DeletePerson(string Spkey) { #region /*删除数据*/ try{ openSqlConnection(connectionString); if( dbConnection.State != ConnectionState.Open) { dbConnection.Open(); } sqlstr = string.Format("Delete from p_info where pkey=‘{0}‘", Spkey); MySqlCommand cmd = new MySqlCommand(sqlstr, dbConnection); cmd.ExecuteNonQuery(); dbConnection.Close(); Debug.Log("DeleteData"); SelectPdataSet(); } catch (MySqlException e){ Debug.Log("删除失败" + e.Message); } #endregion } public bool canEdit_person; /*传入选中要修改员工的主键pkey,并读取出对应数据给多个字段*/ void Edit_Person( string mpkey ) { if(canEdit_person) { #region 检索当前选中的数据 string commandString5 = string.Format("Select * from p_info where pkey=‘{0}‘", mpkey); Debug.Log( commandString5); /* 创建DataSet命令对象和DataSet*/ if(dbConnection.State != ConnectionState.Open) { dbConnection = new MySqlConnection(connectionString); try { dbConnection.Open(); result = dbConnection.ServerVersion; /*获得MySql的版本*/ } catch(MySqlException e) { Debug.Log( e.Message ); } } using( MySqlDataAdapter DataAdapter = new MySqlDataAdapter(commandString5, connectionString)) { DataSet spDataset = new DataSet(); /* 填充DataSet对象*/ DataAdapter.Fill(spDataset, "p_info"); /*从DataSet获取一个表*/ DataTable spdatatable = spDataset.Tables[0]; pkey= spdatatable.Rows[0]["pkey"].ToString() ; name = spdatatable.Rows[0]["pname"].ToString() ; age =spdatatable.Rows[0]["page"].ToString() ; adress= spdatatable.Rows[0]["padress"].ToString() ; money=spdatatable.Rows[0]["pmoney"].ToString() ; } #endregion } } /*点击修改资料按钮后，详细显示当前选中的员工的信息，并提供文本框进行修改*/ void ShowEdit_PersonData() { if(canEdit_person) { /* GUI.Button( new Rect(150 ,conPos.y+100,conSize.x,conSize.y),"员工唯一编号" ); pkey = GUI.TextField(new Rect(conPos.x ,conPos.y+100,conSize.x,conSize.y),pkey ); */ GUI.Button( new Rect(150 ,conPos.y+160,conSize.x,conSize.y),"姓名" ); name = GUI.TextField(new Rect(conPos.x ,conPos.y+160,conSize.x,conSize.y),name ); GUI.Button( new Rect(150 ,conPos.y+220,conSize.x,conSize.y),"性别" ); selGridInt = GUI.SelectionGrid(new Rect(conPos.x ,conPos.y+220,100,30), selGridInt, selsex, 2); GUI.Button( new Rect(150 ,conPos.y+270,conSize.x,conSize.y),"年龄" ); age = GUI.TextField(new Rect(conPos.x ,conPos.y+270,conSize.x,conSize.y),age ); GUI.Button( new Rect(150 ,conPos.y+330,conSize.x,conSize.y),"家庭详细住址" ); adress = GUI.TextField(new Rect(conPos.x ,conPos.y+330,conSize.x,conSize.y),adress ); GUI.Button( new Rect(150 ,conPos.y+380,conSize.x,conSize.y),"员工月工资" ); money = GUI.TextField(new Rect(conPos.x ,conPos.y+380,conSize.x,conSize.y),money ); Debug.Log("EditPersonData"); if( GUI.Button(new Rect(conPos.x+280 ,conPos.y+220,conSize.x,conSize.y),"返回主页") ) { canEdit_person = false; SelectPdataSet(); } if( GUI.Button(new Rect(conPos.x+280 ,conPos.y+320,conSize.x,conSize.y),"修改员工信息") ) { if( pkey.Trim()==""|| name.Trim()==""|| sex.Trim()==""||age.Trim()==""|| adress.Trim()==""||money.Trim()=="") { pkey = "有错误请重新输入"; name = "姓名"; sex = "性别必须为男或女"; age = "年龄必须为数字"; adress = "家庭详细住址"; money = "月工资必须为数字"; return; } if( selGridInt == 0 ) { sex = "男"; } if( selGridInt == 1 ) { sex = "女"; } if( isNumberic(age)==false && isNumberic(money)==false ) { pkey = "有错误请重新输入"; name = "姓名"; sex = "性别必须为男或女"; age = "年龄必须为数字"; adress = "家庭详细住址"; money = "月工资必须为数字或超过了限制"; return; } #region /*更新数据*/ try{ openSqlConnection(connectionString); if( dbConnection.State != ConnectionState.Open) { dbConnection.Open(); } sqlstr = string.Format("UPDATE p_info SET pname=‘{0}‘, psex=‘{1}‘, page=‘{2}‘, padress=‘{3}‘, pmoney=‘{4}‘WHERE pkey = ‘{5}‘", name,sex,age,adress,money ,pkey); MySqlCommand cmd = new MySqlCommand(sqlstr, dbConnection); cmd.ExecuteNonQuery(); dbConnection.Close(); Debug.Log("UpdateData"); canEdit_person = false; SelectPdataSet(); } catch (MySqlException e){ pkey = "发生错误更新失败"; name = "姓名"; sex = "性别必须为男或女"; age = "年龄必须为数字"; adress = "家庭详细住址"; money = "月工资必须为数字或超过了限制"; Debug.Log("信息录入失败" + e.Message); } #endregion } } } }
新建一个 GameObject,将这个脚本拖放到 GameObject对象上
点击运行，输入
服务器地址：127.0.0.1
用户名：root
密码：123456
需要访问的数据库db名：test
点击 登录数据库
如果登录成功，会看到我们刚才插入的信息
点击增加员工信息
嘿嘿，点击添加此员工信息
成功！嘿嘿！
点击删除《深华东》 试试
删除成功！
点击修改 张三的信息
修改性别为女，年龄为18，工资为800，点击修改员工信息
修改成功！
有的朋友反应，发布无法连接，我这里测试是没有问题，
具体请看 ，发布后连接数据库如下图
欢迎与我交流Unity3D技术，QQ2360450496
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。