Unity3D 连接MySQL数据库笔记4-Unity3d代码

来源：互联网发布：听歌学英文软件编辑：程序博客网时间：2024/06/05 04:36
新建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
点击登录数据库