MySql增删查改

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using UnityEngine;
public class SqlConnectJson
{
    public string Server;
    public string port;
    public string Database;
    public string UserID;
    public string Password;
    public string ConnectionTimeOut;
}
public class SQLData
{
    private static SQLData instance;
    private SQLData()
    {
    }
    public static SQLData MySqlInstance
    {
        get
        {
            if (null == instance)
            {
                instance = new SQLData();
            }
            return instance;
        }
    }
    //连接部分
    private string connString = "";
    public string ConnString { set { connString = value; } get { return connString; } }
    private MySqlConnection connection = null;
    //获取连接数据库对象
    private MySqlConnection Connection
    {
        get
        {
            try
            {
                if (connection == null)
                {
                    Debug.Log(connString);
                    connection = new MySqlConnection(connString);
                    connection.Open();
                }
                else if (connection.State == ConnectionState.Closed)
                {
                    connection = new MySqlConnection(connString);
                    connection.Open();
                }
                else if (connection.State == ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
            catch (Exception e)
            {
                throw new Exception("Database connection failed. Please Check MySql Service!" + "\n" + e.Message.ToString());
            }
        }
    }
    public bool InsetInfo(string tableName, string[] col, string[] values)
    {
        if (col.Length != values.Length)
        {
            throw new Exception("coluns.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 += ")";
        return executeSQL(query);
    }
    public bool InsetInfo(string tableName, string col, string values)
    {
        string query = "insert into " + tableName + " (" + col + ") values (" + values + ")";

        return executeSQL(query);
    }
    //更新方法
    public bool UpdateInfo(string tableName, string col, string value, string whereName, string whereValue)
    {
        string query = "update " + tableName + " set " + col + " = " + "'" + value + "' where " + whereName + " = '" + whereValue + "'";
        Debug.Log(query);
        return executeSQL(query);
    }
    public bool UpdateInfo(string tableName, string[] col, string[] value, string whereName, string whereValue)
    {
        if (col.Length != value.Length)
        {
            throw new Exception("coluns.Length != colType.Length");
        }
        string query = "update " + tableName + " set " + col[0] + " = " + "'" + value[0] + "'";
        for (int i = 1; i < col.Length; i++)
        {
            query += "," + col[i] + " = " + "'" + value[i] + "'";
        }
        query += "where " + whereName + " = " + "'" + whereValue + "'";
        return executeSQL(query);
    }
  

    /// <summary>
    ///精细查找方法
    /// </summary>
    /// <param name="Target">目标名</param>
    /// <param name="TableName">查询的表名</param>
    /// <param name="Basis">依据名</param>
    /// <param name="Who">依据</param>
    public List<string> LocalSelectInfo(string Target, string TableName, string Basis, string BasInfo)
    {

        MySqlDataAdapter SqlData = null;
        List<string> mList = new List<string>();
        string Que = string.Format("SELECT {0} FROM {1} WHERE {2}='{3}'", Target, TableName, Basis, BasInfo);
        // Debug.Log(Que);
        SqlData = new MySqlDataAdapter(Que, Connection);
        string Data = "";
        //实例化数据集，并写入查询到的数据
        DataSet ds = new DataSet();
        SqlData.Fill(ds);

        //按行和列打印出数据
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                Data += ds.Tables[0].Rows[i][j].ToString().Trim() + "@";
                if (j == ds.Tables[0].Columns.Count - 1)
                {
                    string[] mData;
                    mData = Data.Split('@');
                    string Tmp = "";
                    for (int z = 0; z < mData.Length; z++)
                    {
                        if (mData[z] != "")
                        {
                            if (z == mData.Length - 2)
                            {
                                Tmp += mData[z];
                                mList.Add(mData[z]);
                            }
                            else
                            {
                                Tmp += mData[z] + "@";
                                mList.Add(mData[z]);
                            }
                        }
                    }
                    Data = "";
                }
            }
        }
        return mList;
    }

    /// <summary>
    ///精细查找方法
    /// </summary>
    /// <param name="Target">目标名</param>
    /// <param name="TableName">查询的表名</param>
    public List<string> LocalSelectInfo(string Target, string TableName)
    {

        MySqlDataAdapter SqlData = null;
        List<string> mList = new List<string>();
        string Que = string.Format("SELECT {0} FROM {1}", Target, TableName);
        // Debug.Log(Que);
        SqlData = new MySqlDataAdapter(Que, Connection);
        string Data = "";
        //实例化数据集，并写入查询到的数据
        DataSet ds = new DataSet();
        SqlData.Fill(ds);

        //按行和列打印出数据
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                Data += ds.Tables[0].Rows[i][j].ToString().Trim() + "@";
                if (j == ds.Tables[0].Columns.Count - 1)
                {
                    string[] mData;
                    mData = Data.Split('@');
                    string Tmp = "";
                    for (int z = 0; z < mData.Length; z++)
                    {
                        if (mData[z] != "")
                        {
                            if (z == mData.Length - 2)
                            {
                                Tmp += mData[z];
                                mList.Add(mData[z]);
                            }
                            else
                            {
                                Tmp += mData[z] + "@";
                                mList.Add(mData[z]);
                            }
                        }
                    }
                    Data = "";
                }
            }
        }
        return mList;
    }
    //从表中随机固定数目的数据
    public void SetectInfoRandom(int Num, string TableName)
    {
        MySqlDataAdapter SqlData = null;
        string Que = string.Format("select* from {1} order by rand() limit {0}", Num, TableName);
        //   Debug.Log(Que);
        SqlData = new MySqlDataAdapter(Que, Connection);
        string Data = "";
        //实例化数据集，并写入查询到的数据
        DataSet ds = new DataSet();
        SqlData.Fill(ds);

        //按行和列打印出数据
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                Data += ds.Tables[0].Rows[i][j].ToString().Trim() + "@";
                if (j == ds.Tables[0].Columns.Count - 1)
                {
                    string[] mData;
                    mData = Data.Split('@');
                    string Tmp = "";
                    for (int z = 0; z < mData.Length; z++)
                    {
                        if (mData[z] != "")
                        {
                            if (z == mData.Length - 2)
                            {
                                Tmp += mData[z];
                            }
                            else
                            {
                                Tmp += mData[z] + "@";
                            }
                        }
                    }
                    Debug.Log(Tmp);
                    Data = "";
                }
            }
        }
    }
    /// <summary>
    /// 随机指定类型题目
    /// </summary>
    /// <param name="Target">目标名</param>
    /// <param name="TableName">查询的表名</param>
    /// <param name="Basis">依据名</param>
    /// <param name="BasInfo">依据</param>
    /// <param name="Num">数目</param>
    public List<string> LocalSelectInfo(string Target, string TableName, string Basis, string BasInfo, int Num)
    {
        List<string> mList = new List<string>();
        MySqlDataAdapter SqlData = null;
        string Que = string.Format("SELECT {0} FROM {1} WHERE {2}='{3}' order by rand() limit {4}", Target, TableName, Basis, BasInfo, Num);
        // Debug.Log(Que);
        SqlData = new MySqlDataAdapter(Que, Connection);
        string Data = "";
        //实例化数据集，并写入查询到的数据
        DataSet ds = new DataSet();
        SqlData.Fill(ds);
        //按行和列打印出数据
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                Data += ds.Tables[0].Rows[i][j].ToString().Trim() + "@";
                if (j == ds.Tables[0].Columns.Count - 1)
                {
                    string[] mData;
                    mData = Data.Split('@');
                    string Tmp = "";
                    for (int z = 0; z < mData.Length; z++)
                    {
                        if (mData[z] != "")
                        {
                            if (z == mData.Length - 2)
                            {
                                Tmp += mData[z];
                                mList.Add(mData[z]);
                            }
                            else
                            {
                                Tmp += mData[z] + "@";
                                mList.Add(mData[z]);
                            }
                        }
                    }
                    Data = "";
                }
            }
        }
        return mList;
    }
    //删除方法
    public bool Delete(string tableName, string col, string value)
    {
        string query = "delete from " + tableName + " where " + col + " = '" + value + "'";
        return executeSQL(query);
    }
    //创建MySqlCommand对象 MySql语句不带参数
    public MySqlCommand Command(string strMySql)
    {

        try
        {
            using (MySqlCommand cmd = new MySqlCommand(strMySql, Connection))
            {
                return cmd;
            }
        }
        catch
        {
            return null;
        }
    }
    //更新、添加、删除操作是否成功
    public bool executeSQL(string strMySql)
    {
        int rows = executeNonQuery(strMySql);
        if (rows > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    //返回受影响的行数 MySql语句不带参数(更新 添加 删除)
    public int executeNonQuery(string strMySql)
    {

        try
        {
            MySqlCommand cmd = Command(strMySql);
            return cmd.ExecuteNonQuery();
        }
        catch
        {
            return 0;
        }
    }
}