unity and MySql

摘要:数据库的图片以及内容如图:

技术分享

技术分享

1.数据库的封装

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

 

public class SqlAccess
{
    public static MySqlConnection dbConnection;
    //如果只是在本地的话,写localhost就可以。
    // static string host = "localhost"; 
    //如果是局域网,那么写上本机的局域网IP
    static string host = "localhost";
    static string id = "root";//可以通过这个用户访问数据库
    static string pwd = "013287";//创建链接时候的密码
    static string database = "man";//数据库的名字

    public SqlAccess()
    {
        OpenSql();
    }

    public static void OpenSql()
    {

        try
        {
            string connectionString = string.Format("Server = {0};port={4};Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd, "3306");//链接数据库的字段
            dbConnection = new MySqlConnection(connectionString);
            dbConnection.Open();
        }
        catch (Exception e)
        {
            throw new Exception("服务器连接失败,请重新检查是否打开MySql服务。" + e.Message.ToString());

        }

    }

    public DataSet CreateTable(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];

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

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

        }

        query += ")";

        return ExecuteQuery(query);
    }

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

    //插入一条数据,包括所有,不适用自动累加ID。
    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 ExecuteQuery(query);

    }

    //插入部分ID
    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 ExecuteQuery(query);

    }

    public 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[0] + "‘ ";

        }

        return ExecuteQuery(query);

    }

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

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

    public void Close()
    {

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

    }

    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;
    }
}

2.数据库与unity的链接

这个脚本需要挂载在一个激活的物体上

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

public class NewBehaviourScript : MonoBehaviour {

    string Error = null;
    void Start()
    {
        try
        {

            SqlAccess sql = new SqlAccess(); //获取对封装好的数据库的引用

            //   sql.CreateTableAutoID("people", new string[] { "idcard", "age", "name" }, new string[] {  "idcard", "age", "name"});
            //sql.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"});
            // sql.InsertInto("people", new string[] { "idcard", "age", "name" }, new string[] { "6789", "22", "qiang" });
            //  sql.InsertInto("people", new string[] { "idcard", "age", "name"}, new string[] { "5", "age2", "name2" });

            DataSet ds = sql.SelectWhere("people", new string[] { "idcard", "age", "name" }, new string[] { "idcard" }, new string[] { "=" }, new string[] { "520" }); //把返回的数据集付给ds,此时的修改已经在封装好的sqlAccess中改回数据库了,此时通过ds进行UI显示或输出等
            if (ds != null)
            {

                DataTable table = ds.Tables[0];

                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        Debug.Log(row[column]);
                    }
                }
            }
            sql.Close();
        }
        catch (Exception e)
        {
            Error = e.Message;
        }

    }

    // Update is called once per frame
    void OnGUI()
    {

        if (Error != null)
        {
            GUILayout.Label(Error);
        }

    }
}

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。