Unity链接MySQL数据库读取及保存（工具类）

 工具类如下：

using System;
using MySql.Data.MySqlClient;
using System.Data;
using UnityEngine;

class ConnectionSQLProgram
{
    //远程连接
    //  string connectionString= "User ID = ; Password =.; Host =; Port =3306 ; Database = ;Charset = ";
    //本地连接
    //public static string connectionString = "Database = ;Charset = ;User ID = ; Password = ; Host = ; Port = 3306";
    //链接
    //string connectionString = string.Format("Server = {0}; Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd);
    //开启
    //ConnectionSQLProgram.OpenSqlConnection(connectionString);
    //开启数据库一次后，后续再次开启数据库进行操作直接调用：ConnectionSQLProgram.OpenSqlAtAdministratorModel();
    //关闭
    //ConnectionSQLProgram.CloseConnection();
    //每次对数据库进行操作都要有开启及关闭
    static MySqlConnection dbConnection;
    static string ConnectionString = "";
    //打开数据库链接
    public static void OpenSqlConnection(string connectionString)
    {
        try
        {
            ConnectionString = connectionString;
            dbConnection = new MySqlConnection(connectionString);
            dbConnection.Open();
            if(GS_Loading.Instance!=null)
                GS_Loading.Instance.isSqlTrue = true;
        }
        catch (Exception e)
        {
            if (GS_Loading.Instance != null)
                GS_Loading.Instance.isSqlTrue = false;
            //throw new Exception("服务器连接失败，请重新检查是否打开MySql服务。" + e.Message.ToString());

        }
        if (GS_Loading.Instance != null)
            GS_Loading.Instance.WarningShow();
    }
    public static void OpenSqlAtAdministratorModel()
    {
        try
        {
            dbConnection = new MySqlConnection(ConnectionString);
            dbConnection.Open();
        }
        catch (Exception e)
        {
            throw new Exception("服务器连接失败，请重新检查是否打开MySql服务。" + e.Message.ToString());

        }
    }
    //关闭数据库连接
    public static void CloseConnection()
    {
        if (dbConnection != null)
        {
            if(dbConnection.State == ConnectionState.Open)
            {
                
                dbConnection.Close();
                dbConnection.Dispose();
                dbConnection = null;
            }
            
        }
    }

    //保存数据
    public static DataSet GetDataSet(string sqlString)
    {
        DataSet ds = new DataSet();
        try
        {
            //用于检索和保存数据
            //Fill(填充)能改变DataSet中的数据以便于数据源中数据匹配
            //Update(更新)能改变数据源中的数据以便于DataSet中的数据匹配
            MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
            da.Fill(ds);
        }
        catch (Exception ee)
        {
            throw new Exception("SQL:" + sqlString + "\n" + ee.Message.ToString());
        }
        return ds;
    }

    //增 insert
    //string timeS = System.DateTime.Now.Year + "-" + System.DateTime.Now.Month + "-" + System.DateTime.Now.Day + " " + System.DateTime.Now.Hour + ":" + System.DateTime.Now.Minute + ":" + System.DateTime.Now.Second;
    //DataSet ds = connectionSQL.InsertInto("XXX", new string[] { "2", "l123", "121326", "0", "0", timeS, "" });

    public static 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 ExecuteQuery(query);
    }

    //插入部分ID
    //InsertInto("表名", new string[] { "ip", "TaskID", "SelectRoleID", "UserID" }, new string[] { Ip, taskId , "", userID  });
    public static 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 ExecuteQuery(query);

    }

    //删 delete
    //Delete("GS_USER",new string[] {"ID"} ,new string[] {"2"});
    public static 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 ExecuteQuery(query);
    }
    //删 两个值满足一起删除
    public static DataSet DeleteAdd(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 += " and " + cols[i] + " = " + "'" + colsvalues[i] + "'";
        }
        Debug.Log(query);
        return ExecuteQuery(query);
    }
    //改 update
    //UpdateInto("表名", new string[] {"UserName","PassWord","Sex","IsAdmin"},new string[] { "s123","wzry","1","1" }, "ID","1");
    public static 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+"'" + " ";
        Debug.Log(query);
        return ExecuteQuery(query);
    }
    //没用过 如果有错 试着部分地方将""更改为 "'"参考DeleteAdd方法
    public static DataSet UpdateInto(string tableName, string[] cols, string[] colsvalues, string[] selectkey, string[] operation, 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[0] + " = " + selectvalue[0] + " ";

        for (int i = 1; i < selectkey.Length; ++i)
        {
            query += " AND " + selectkey[i] + "'" + operation[i] + "'" + selectvalue[i];
        }
        Debug.Log(query);
        return ExecuteQuery(query);
    }
    //查询表所有内容 SeldetAll("表名");
    public static DataSet SeldetAll(string tableName)
    {
        string query = "select * from " + tableName;
        return ExecuteQuery(query);
    }
    //SelectWhere("表名", new string[] {"UserName","PassWord","Sex","IsAdmin"},new string[] { "ID" }, new string[] {"="}, new string[] { "1" });
    //查 select
    public static DataSet SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
    {
        if (col.Length != operation.Length || operation.Length != values.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 " + col[0] + operation[0] +  values[0];
        for (int i = 1; i < col.Length; ++i)
        {
            query += " AND " + col[i] + operation[i] +  values[i];
        }
        Debug.Log(query);
        return ExecuteQuery(query);

    }

    public static DataSet ExecuteQuery(string sqlString)
    {
        if (dbConnection.State == ConnectionState.Open)
        {
            DataSet ds = new DataSet();
            try
            {

                MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
                da.Fill(ds);

            }
            catch (Exception ee)
            {
                throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());
            }
            finally
            {
            }
            return ds;
        }
        return null;
    }
}

将读取到的数据库信息保存类代码：注意数据库中属性类型，保存时虽然转换成字符串进行保存但是数据类型还是要注意的。

读出时也要注意数据类型。如果类型不一致会报错。

using System.Collections;
using System.Collections.Generic;
using System.Data;
using UnityEngine;

public class GS_USER
{
    struct USER
    {
        public int ID;
        public string UserName;
        public string PassWord;
        public int Sex;
        public int IsAdmin;
        public string LoginTime;
        public string CreatTime;
    }
    USER user;
    List<USER> GS_USERList = new List<USER>();
    private static GS_USER uniqueInstance;
    private GS_USER()
    {
    }
    public static GS_USER GetInstance()
    {
        // 如果类的实例不存在则创建，否则直接返回
        if (uniqueInstance == null)
        {
            uniqueInstance = new GS_USER();
        }
        return uniqueInstance;
    }
    #region 读取MySQL
    public void SetPropertyInformation()
    {
        GS_USERList.Clear();
        DataSet ds = ConnectionSQLProgram.SeldetAll("GS_USER");
        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                if (table.Rows[i][0].ToString() != "")
                {
                    user.ID = (int)(table.Rows[i][0]);

                }
                else
                {
                    user.ID = -1;
                }
                if (table.Rows[i][1].ToString() != "")
                {
                    user.UserName = (string)table.Rows[i][1];
                }
                else
                {
                    user.UserName = "";
                }
                if (table.Rows[i][2] != null)
                {
                    user.PassWord = (string)(table.Rows[i][2]);
                }
                else
                {
                    user.PassWord = "";
                }

                if (table.Rows[i][3].ToString() != "")
                {
                    user.Sex = (int)(table.Rows[i][3]);
                }
                else
                {
                    user.Sex = -1;
                }
                if (table.Rows[i][4].ToString() != "")
                {
                    user.IsAdmin = (int)(table.Rows[i][4]);
                }
                else
                {
                    user.IsAdmin = 0;
                }
                if (table.Rows[i][5].ToString() != "")
                {
                    user.LoginTime = table.Rows[i][5].ToString();
                }
                else
                {
                    user.LoginTime = "";
                }
                if (table.Rows[i][6].ToString() != "")
                {
                    user.CreatTime = table.Rows[i][6].ToString();
                }
                else
                {
                    user.CreatTime = "";
                }

                GS_USERList.Add(user);
            }
        }

    }
    #endregion
}

 

调用： 

private GS_USER GS_USER;       

string host = "0.0.0.1";    

string id = "xxx";  //***不要变*** 访问数据库表的用户名

string pwd = "xxx";  //密码 访问数据库表的密码

string database = "表名"; //要访问的数据库表名   

connectionString = string.Format("Server = {0}; Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd);

ConnectionSQLProgram.OpenSqlConnection(connectionString);

GS_USER = GS_USER.GetInstance();

GS_USER.SetPropertyInformation();

ConnectionSQLProgram.CloseConnection();