[C#基础]c#连接mysql

最新推荐文章于 2025-02-15 22:08:35 发布

宏哥1995

最新推荐文章于 2025-02-15 22:08:35 发布

阅读量1.1k

点赞数 1

分类专栏： C#基础文章标签： c#连接mysql

本文链接：https://blog.youkuaiyun.com/lyh916/article/details/50985697

版权

C#基础专栏收录该内容

16 篇文章

订阅专栏

sql基础：http://www.w3school.com.cn/sql/index.asp

一、DataTable & DataSet

1.DataTable：表示内存中的一个数据表

2.DataSet：表示内存中的一个数据缓存，可以看作为DataTable的集合(字典)

// Put the next line into the Declarations section.
private System.Data.DataSet dataSet;

private void MakeDataTables()
{
    // Run all of the functions. 
    MakeParentTable();
    MakeChildTable();
    MakeDataRelation();
    BindToDataGrid();
}

private void MakeParentTable()
{
    // Create a new DataTable.
    System.Data.DataTable table = new DataTable("ParentTable");
    // Declare variables for DataColumn and DataRow objects.
    DataColumn column;
    DataRow row;

    // Create new DataColumn, set DataType, 
    // ColumnName and add to DataTable.    
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.Int32");
    column.ColumnName = "id";
    column.ReadOnly = true;
    column.Unique = true;
    // Add the Column to the DataColumnCollection.
    table.Columns.Add(column);

    // Create second column.
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "ParentItem";
    column.AutoIncrement = false;
    column.Caption = "ParentItem";
    column.ReadOnly = false;
    column.Unique = false;
    // Add the column to the table.
    table.Columns.Add(column);

    // Make the ID column the primary key column.
    DataColumn[] PrimaryKeyColumns = new DataColumn[1];
    PrimaryKeyColumns[0] = table.Columns["id"];
    table.PrimaryKey = PrimaryKeyColumns;

    // Instantiate the DataSet variable.
    dataSet = new DataSet();
    // Add the new DataTable to the DataSet.
    dataSet.Tables.Add(table);

    // Create three new DataRow objects and add 
    // them to the DataTable
    for (int i = 0; i<= 2; i++)
    {
        row = table.NewRow();
        row["id"] = i;
        row["ParentItem"] = "ParentItem " + i;
        table.Rows.Add(row);
    }
}

private void MakeChildTable()
{
    // Create a new DataTable.
    DataTable table = new DataTable("childTable");
    DataColumn column;
    DataRow row;

    // Create first column and add to the DataTable.
    column = new DataColumn();
    column.DataType= System.Type.GetType("System.Int32");
    column.ColumnName = "ChildID";
    column.AutoIncrement = true;
    column.Caption = "ID";
    column.ReadOnly = true;
    column.Unique = true;

    // Add the column to the DataColumnCollection.
    table.Columns.Add(column);

    // Create second column.
    column = new DataColumn();
    column.DataType= System.Type.GetType("System.String");
    column.ColumnName = "ChildItem";
    column.AutoIncrement = false;
    column.Caption = "ChildItem";
    column.ReadOnly = false;
    column.Unique = false;
    table.Columns.Add(column);

    // Create third column.
    column = new DataColumn();
    column.DataType= System.Type.GetType("System.Int32");
    column.ColumnName = "ParentID";
    column.AutoIncrement = false;
    column.Caption = "ParentID";
    column.ReadOnly = false;
    column.Unique = false;
    table.Columns.Add(column);

    dataSet.Tables.Add(table);

    // Create three sets of DataRow objects, 
    // five rows each, and add to DataTable.
    for(int i = 0; i <= 4; i ++)
    {
        row = table.NewRow();
        row["childID"] = i;
        row["ChildItem"] = "Item " + i;
        row["ParentID"] = 0 ;
        table.Rows.Add(row);
    }
    for(int i = 0; i <= 4; i ++)
    {
        row = table.NewRow();
        row["childID"] = i + 5;
        row["ChildItem"] = "Item " + i;
        row["ParentID"] = 1 ;
        table.Rows.Add(row);
    }
    for(int i = 0; i <= 4; i ++)
    {
        row = table.NewRow();
        row["childID"] = i + 10;
        row["ChildItem"] = "Item " + i;
        row["ParentID"] = 2 ;
        table.Rows.Add(row);
    }
}

private void MakeDataRelation()
{
    // DataRelation requires two DataColumn 
    // (parent and child) and a name.
    DataColumn parentColumn = 
        dataSet.Tables["ParentTable"].Columns["id"];
    DataColumn childColumn = 
        dataSet.Tables["ChildTable"].Columns["ParentID"];
    DataRelation relation = new 
        DataRelation("parent2Child", parentColumn, childColumn);
    dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
}

private void BindToDataGrid()
{
    // Instruct the DataGrid to bind to the DataSet, with the 
    // ParentTable as the topmost DataTable.
    dataGrid1.SetDataBinding(dataSet,"ParentTable");
}

更深入的：https://msdn.microsoft.com/zh-cn/library/system.data.datatable(v=vs.110).aspx#

二、访问数据库的两种方式：

1.客户端每次访问数据库时，都会发送请求

2.将信息存储到DataSet中，对DataSet进行访问，断开与数据库的连接时发送请求。该方法可以避免频繁操作数据库，但是可能会出现数据库不同步的问题

三、c#连接mysql

1.下载MySql.Data.dll，然后在项目中添加该引用，并且使用MySql.Data.MySqlClient这个命名空间

using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Text;

public class MySqlUtility {

    private static MySqlConnection mySqlConnection;
    private static DataSet dataSet;
    private static StringBuilder stringBuilder;

    /// <summary>
    /// 对于host，如果是本地的话，则写localhost
    /// 如果是局域网，则写本机的局域网IP
    /// </summary>
    /// <param name="host"></param>
    /// <param name="port"></param>
    /// <param name="database"></param>
    /// <param name="id"></param>
    /// <param name="password"></param>
    public static void Open(string host = "localhost", string port = "3306", string database = "test", string id = "hong", string password = "hong")
    {
        try
        {
            string s = string.Format("Server = {0};port = {1};Database = {2};User ID = {3};Password = {4};", host, port, database, id, password);
            mySqlConnection = new MySqlConnection(s);
            mySqlConnection.Open();
            dataSet = new DataSet();
        }
        catch(Exception e)
        {
            throw new Exception("服务器连接失败，请重新检查是否打开MySql服务。" + e.Message.ToString());
        }
      
    }

    public static void Close()
    {
        if (mySqlConnection != null)
        {
            mySqlConnection.Close();
            mySqlConnection = null;
            dataSet = null;
            stringBuilder = null;
        }   
    }

    private static void ExecuteNotReturnDataSet(string statement)
    {
        //Debug.Log(statement);
        MySqlCommand mySqlCommand = new MySqlCommand(statement, mySqlConnection);
        mySqlCommand.ExecuteNonQuery();
    }

    private static DataSet ExecuteReturnDataSet(string statement)
    {
        //Debug.Log(statement);
        MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(statement, mySqlConnection);
        mySqlDataAdapter.Fill(dataSet);
        return dataSet;
    }

    /// <summary>
    /// 插入一行中的全部数据，对于值，统一转化为字符串进行处理
    /// INSERT INTO 表名称 VALUES (值1, 值2,....)
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="values"></param>
    public static void Insert(string tableName, string[] values)
    {
        stringBuilder = new StringBuilder("insert into " + tableName + " values (" + "'" + values[0] + "'");
        for (int i = 1; i < values.Length; i++)
        {
            stringBuilder.Append("," + "'" + values[i] + "'");
        }
        stringBuilder.Append(")");

        ExecuteNotReturnDataSet(stringBuilder.ToString());
    }

    /// <summary>
    /// 插入一行中的部分数据
    /// INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="columnNames"></param>
    /// <param name="values"></param>
    public static void Insert(string tableName, string[] columnNames, string[] values)
    {
        stringBuilder = new StringBuilder("insert into " + tableName + " (" + columnNames[0]);
        for (int i = 1; i < columnNames.Length; i++)
        {
            stringBuilder.Append("," + columnNames[i]);
        }
        stringBuilder.Append(") values (" + "'" + values[0] +"'");
        for (int i = 1; i < values.Length; i++)
        {
            stringBuilder.Append("," + "'" + values[i] + "'");
        }
        stringBuilder.Append(")");

        ExecuteNotReturnDataSet(stringBuilder.ToString());
    }

    /// <summary>
    /// SELECT * FROM 表名称
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public static DataSet Select(string tableName)
    {
        stringBuilder = new StringBuilder("select * from " + tableName);

        return ExecuteReturnDataSet(stringBuilder.ToString());
    }

    /// <summary>
    /// SELECT 列名称 FROM 表名称
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="columnNames"></param>
    /// <returns></returns>
    public static DataSet Select(string tableName, string[] columnNames)
    {
        stringBuilder = new StringBuilder("select " + columnNames[0]);
        for (int i = 1; i < columnNames.Length; i++)
        {
            stringBuilder.Append("," + columnNames[i]);
        }
        stringBuilder.Append(" from " + tableName);

        return ExecuteReturnDataSet(stringBuilder.ToString());
    }

    /// <summary>
    /// SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="columnNames"></param>
    /// <param name="cols"></param>
    /// <param name="operations"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public static DataSet Select(string tableName, string[] columnNames, string[] cols, string[] operations, string[] values)
    {
        stringBuilder = new StringBuilder("select " + columnNames[0]);
        for (int i = 1; i < columnNames.Length; i++)
        {
            stringBuilder.Append("," + columnNames[i]);
        }

        stringBuilder.Append(" from " + tableName + " where " + cols[0] + operations[0] + "'" + values[0] + "'");
        for (int i = 1; i < cols.Length; i++)
        {
            stringBuilder.Append(" and " + cols[i] + operations[i] + "'" + values[i] + "'");
        }

        return ExecuteReturnDataSet(stringBuilder.ToString());
    }

    /// <summary>
    /// UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="columnNames"></param>
    /// <param name="newValues"></param>
    /// <param name="cols"></param>
    /// <param name="oldValues"></param>
    public static void Update(string tableName, string[] columnNames, string[] newValues, string[] cols, string[] oldValues)
    {
        stringBuilder = new StringBuilder("update " + tableName + " set " + columnNames[0] + "='" + newValues[0] +"'");
        for (int i = 1; i < columnNames.Length; i++)
        {
            stringBuilder.Append("," + columnNames[i] + "='" + newValues[i] + "'");
        }
        stringBuilder.Append(" where " + cols[0] + " ='" + oldValues[0] + "'");
        for (int i = 1; i < cols.Length; i++)
        {
            stringBuilder.Append(" and " + cols[i] + "='" + oldValues[i] + "'");
        }

        ExecuteNotReturnDataSet(stringBuilder.ToString());
    }

    /// <summary>
    /// DELETE FROM 表名称 WHERE 列名称 = 值
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="columnNames"></param>
    /// <param name="values"></param>
    public static void Delete(string tableName, string[] columnNames, string[] values)
    {
        stringBuilder = new StringBuilder("delete from " + tableName + " where " + columnNames[0] + "='" + values[0] + "'");
        for (int i = 1; i < columnNames.Length; i++)
        {
            stringBuilder.Append(" or " + columnNames[i] + "='" + values[i] + "'");
        }

        ExecuteNotReturnDataSet(stringBuilder.ToString());
    }

}

四、unity连接mysql

1.在Assets下建一个文件夹，命名为Plugins，然后放入这些dll：http://pan.baidu.com/s/1i56gDfB

2.复制上面的脚本

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

//若出现ReflectionTypeLoadException错误，需要将Player Settings中的
//Api Compatibility Level改为.Net2.0
public class NewBehaviourScript : MonoBehaviour {

	void Start () 
    {
        MySqlUtility.Open();
        DataSet ds = MySqlUtility.Select("human", new string[] { "*" }, new string[] { "age" }, new string[] { "=" }, new string[] { "222" });
        PrintLog(ds);
        MySqlUtility.Close();
	}

    public void PrintLog(DataSet ds)
    {
        if (ds == null)
        {
            Debug.Log("DebugLog-------------DataSet is null");
            return;
        };
        DataTable dt = ds.Tables[0];
        if (dt == null)
        {
            Debug.Log("DebugLog-------------DataTable is null");
        }
        foreach (DataRow row in dt.Rows)
        {
            foreach (DataColumn column in dt.Columns)
            {
                Debug.Log(row[column]);
            }
        }
    }

}