Unity连接MySQL数据库

最新推荐文章于 2024-07-06 08:00:00 发布

蔚蓝色青春

最新推荐文章于 2024-07-06 08:00:00 发布

阅读量1.6w

点赞数 7

文章标签： Unity c# mysql socket visual studio

本文链接：https://blog.csdn.net/qq_28352753/article/details/51020426

版权

前两天研究Socket和C#连接数据库其实都是为了将这些和unity结合使用做的基础学习。

所以最后都会归结到Unity上面。其实学会了C#关于数据库的操作，Unity肯定也就会了。

首先准备工作，新建一个unity项目就不用讲了。在unity项目工程下建立一个Plugins文件夹，里面存放的都是一些需要用到的动态链接库文件。主要有五个文件。

如图：

然后在C#封装了一个SQLAccess类。这个类主要做的就是和数据库连接和组拼了增删改查的SQL语句。

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace Assets
{
    public class SqlAccess
    {
        public static MySqlConnection mySqlConnection;//连接类对象
        private static string database = "test";
        private static string host = "127.0.0.1";
        private static string id = "root";
        private static string pwd = "123";

        public SqlAccess()
        {
            OpenSql();
        }
        /// <summary>
        /// 打开数据库
        /// </summary>
        public static void OpenSql()
        {
            try
            {
                //string.Format是将指定的 String类型的数据中的每个格式项替换为相应对象的值的文本等效项。
                string sqlString = string.Format("Database={0};Data Source={1};User Id={2};Password={3};", database, host, id, pwd, "3306");
                mySqlConnection = new MySqlConnection(sqlString);
                mySqlConnection.Open();
            }
            catch (Exception)
            {
                throw new Exception("服务器连接失败.....");
            }
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="name">表名</param>
        /// <param name="colName">属性列</param>
        /// <param name="colType">属性类型</param>
        /// <returns></returns>
        public DataSet CreateTable(string name, string[] colName, string[] colType)
        {
            if (colName.Length != colType.Length)
            {
                throw new Exception("输入不正确：" + "columns.Length != colType.Length");
            }
            string query = "CREATE TABLE  " + name + "(" + colName[0] + " " + colType[0];
            for (int i = 1; i < colName.Length; i++)
            {
                query += "," + colName[i] + " " + colType[i];
            }
            query += ")";
            return QuerySet(query);
        }
        /// <summary>
        /// 创建具有id自增的表
        /// </summary>
        /// <param name="name">表名</param>
        /// <param name="col">属性列</param>
        /// <param name="colType">属性列类型</param>
        /// <returns></returns>
        public DataSet CreateTableAutoID(string name, string[] col, string[] colType)
        {
            if (col.Length != colType.Length)
            {

                throw new Exception("columns.Length != colType.Length");

            }

            string query = "CREATE TABLE  " + name + " (" + col[0] + " " + colType[0] + " NOT NULL AUTO_INCREMENT";

            for (int i = 1; i < col.Length; ++i)
            {

                query += ", " + col[i] + " " + colType[i];

            }

            query += ", PRIMARY KEY (" + col[0] + ")" + ")";

            //    Debug.Log(query);

            return QuerySet(query);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="items">需要查询的列</param>
        /// <param name="whereColName">查询的条件列</param>
        /// <param name="operation">条件操作符</param>
        /// <param name="value">条件的值</param>
        /// <returns></returns>
        public DataSet Select(string tableName, string[] items, string[] whereColName, string[] operation, string[] value)
        {
            if (whereColName.Length != operation.Length || operation.Length != value.Length)
            {
                throw new Exception("输入不正确：" + "col.Length != operation.Length != values.Length");
            }
            string query = "SELECT " + items[0];
            for (int i = 1; i < items.Length; i++)
            {
                query += "," + items[i];
            }
            query += "  FROM  " + tableName + "  WHERE " + " " + whereColName[0] + operation[0] + " '" + value[0] + "'";
            for (int i = 1; i < whereColName.Length; i++)
            {
                query += " AND " + whereColName[i] + operation[i] + "' " + value[i] + "'";
            }
            return QuerySet(query);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="cols">条件：删除列</param>
        /// <param name="colsvalues">删除该列属性值所在得行</param>
        /// <returns></returns>
        public DataSet Delete(string tableName, string[] cols, string[] colsvalues)
        {
            string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsvalues[0];

            for (int i = 1; i < colsvalues.Length; ++i)
            {

                query += " or " + cols[i] + " = " + colsvalues[i];
            }
          //  Debug.Log(query);
            return QuerySet(query);
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="cols">更新列</param>
        /// <param name="colsvalues">更新的值</param>
        /// <param name="selectkey">条件：列</param>
        /// <param name="selectvalue">条件：值</param>
        /// <returns></returns>
        public DataSet UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
        {

            string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + colsvalues[0];

            for (int i = 1; i < colsvalues.Length; ++i)
            {

                query += ", " + cols[i] + " =" + colsvalues[i];
            }

            query += " WHERE " + selectkey + " = " + selectvalue + " ";

            return QuerySet(query);
        }
        
        /// <summary>
       /// 插入一条数据，包括所有，不适用自动累加ID。
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="values">插入值</param>
        /// <returns></returns>
        public DataSet InsertInto(string tableName, string[] values)
        {

            string query = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";

            for (int i = 1; i < values.Length; ++i)
            {

                query += ", " + "'" + values[i] + "'";

            }

            query += ")";

        // Debug.Log(query);
            return QuerySet(query);
        }

        
        /// <summary>
        /// 插入部分
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="col">属性列</param>
        /// <param name="values">属性值</param>
        /// <returns></returns>
        public DataSet InsertInto(string tableName, string[] col, string[] values)
        {

            if (col.Length != values.Length)
            {

                throw new Exception("columns.Length != colType.Length");

            }

            string query = "INSERT INTO " + tableName + " (" + col[0];
            for (int i = 1; i < col.Length; ++i)
            {

                query += ", " + col[i];

            }

            query += ") VALUES (" + "'" + values[0] + "'";
            for (int i = 1; i < values.Length; ++i)
            {

                query += ", " + "'" + values[i] + "'";

            }

            query += ")";

         //   Debug.Log(query);
            return QuerySet(query);

        }
        /// <summary>
        /// 
        /// 执行Sql语句
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public static DataSet QuerySet(string sqlString)
        {
            if (mySqlConnection.State == ConnectionState.Open)
            {
                DataSet ds = new DataSet();
                try
                {

                    MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sqlString,mySqlConnection);
                    mySqlDataAdapter.Fill(ds);
                }
                catch (Exception e)
                {
                    throw new Exception("SQL:" + sqlString + "/n" + e.Message.ToString());
                }
                finally
                {

                }
                return ds;
            }
            return null;
        }

        public void Close()
        {

            if (mySqlConnection != null)
            {
                mySqlConnection.Close();
                mySqlConnection.Dispose();
                mySqlConnection = null;
            }

        }
    }
}

若需要在局域网下访问数据库，则讲host改为本机的IP地址。

然后在Unity新建一个脚本，放在场景中任何物体都可以，用来操作数据库

using Assets;
using UnityEngine;
using System.Collections;
using System.Data;
using  UnityEngine.UI;

public class Test : MonoBehaviour
{
    public Text text;
	
	void Start () {
        SqlAccess sql = new SqlAccess();
       // sql.CreateTableAutoID("jl", new string[] { "id", "name", "qq", "email", "blog" }, new string[] { "int", "text", "text", "text", "text" });
        //sql.InsertInto("jl", new string[] { "name", "qq", "email", "blog" }, new string[] { "jianglei", "289187120", "jianglei@mail.com", "jianglei.com" });
        //  sql.InsertInto("jl", new string[] { "name", "qq", "email", "blog" }, new string[] { "lizhih", "34546546", "lizhih@mail.com", "lizhih.com" });
	   // sql.Delete("jl", new string[] {"id"}, new string[] {"2"});
       DataSet ds=sql.Select("jl", new string[] { "name", "qq" }, new string[] { "id" }, new string[] { "=" }, new string[] { "1" });
	    if (ds!=null)
	    {
	        DataTable table = ds.Tables[0];
	        foreach (DataRow dataRow in table.Rows)
	        {
	            foreach (DataColumn dataColumn in table.Columns)
	            {
	                Debug.Log(dataRow[dataColumn]);
	                text.text += "  "+dataRow[dataColumn].ToString();
	            }
	        }
	    }
        sql.Close();
	}
	
	
	void Update () {
	
	}
}

最后点击运行，观察控制台输出和Navicat的数据库就可以看到运行结果了。