Unity3D研究院之Unity中连接本地或局域网MySQL数据库（五十九）

net start mysql 
net stop mysql

using UnityEngine;  
using System;  
using System.Data;  
using System.Collections;   
using MySql.Data.MySqlClient;
using MySql.Data;
using System.IO;
public class SqlAccess 
{

    public static MySqlConnection dbConnection;
	//如果只是在本地的话，写localhost就可以。
   // static string host = "localhost";  
	//如果是局域网，那么写上本机的局域网IP
	static string host = "192.168.1.106";  
    static string id = "root";
    static string pwd = "1234";
    static string database = "xuanyusong"; 

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

}

using UnityEngine;  
using System;  
using System.Data;  
using System.Collections;   
using MySql.Data.MySqlClient;
using MySql.Data;
using System.IO;
public class NewBehaviourScript : MonoBehaviour {

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

		SqlAccess sql = new  SqlAccess();

		 sql.CreateTableAutoID("momo",new string[]{"id","name","qq","email","blog"}, new string[]{"int","text","text","text","text"});
		//sql.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"});
		sql.InsertInto("momo",new string[]{"name","qq","email","blog"},new string[]{"xuanyusong","289187120","xuanyusong@gmail.com","xuanyusong.com"});
		sql.InsertInto("momo",new string[]{"name","qq","email","blog"},new string[]{"ruoruo","34546546","ruoruo@gmail.com","xuanyusong.com"});

		DataSet ds  = sql.SelectWhere("momo",new string[]{"name","qq"},new string []{"id"},new string []{"="},new string []{"1"});
		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.UpdateInto("momo",new string[]{"name","qq"},new string[]{"'ruoruo'","'11111111'"}, "email", "'xuanyusong@gmail.com'"  );

		 sql.Delete("momo",new string[]{"id","email"}, new string[]{"1","'000@gmail.com'"}  );
		 sql.Close();
		}catch(Exception e)
		{
			Error = e.Message;
		}

	}

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

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

	}
}