c# MySQL 一些语句

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

public class DataBaceTool : MonoBehaviour {
    public MySqlConnection conn;
    

    public void OpenDataBace()
    {
        string host = "172.20.3.204";
        string id = "root";
        string pwd = "hololens";
        string database = "test";
        string result = "";
        string connectionString = string.Format("Server = {0}; Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd);
        try
        {
            conn = new MySqlConnection(connectionString);
            conn.Open();
            result = conn.ServerVersion;
        }
            Debug.Log("打开成功 MySql版本" + result);
        catch (Exception exc)
        {
            Debug.Log( "打开报错" + exc  );
        }
    }  

    public void CloseDataBace()
    {
        try
        {
            conn.Close();
            conn = null;
            Debug.Log("关闭成功");
        }
        catch (Exception ex)
        {
            Debug.Log(ex.ToString() + "关闭报错");
        }
    } 


   
    //删除表，传入表的名称
    public void ClearDataFromTable(string tableName)//删除表中数据
    {
        try
        {
            string sql = " delete from " + tableName + ";";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            MySqlDataReader rdr = cmd.ExecuteReader();
            rdr.Close();
        }
        catch (MySqlException exc)
        {
            Debug.Log("删除失败" + exc);
        }
    }
  
	//更改某个表中 某一列的状态tableName表名 flightNum这里是要更改flingtNum等于传进来的这个值(我的flightNum是唯一的,所以用flightNum查找),
	//key 是要更改的哪个字段  value 更改为的值
    public void UpdateDataFormTable(string tableName, string flightNum ,string key, string value)
    {
        try
        {
            string sql = "update " + tableName + " set " + key + "='" + value + "' " + "where " + "flightNum" + " ='" + flightNum + "';";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            print("修改成功");
        }
        catch (MySqlException)
        {
            print("修改失败");
        }
    }

  

    //机场表插入输入
    private void InsertAirportData(string TableName, List<Dictionary<string, object>> list)
    {
        //先清除一次 机场表
        ClearDataFromTable("AirportManager");
        for (int i = 0; i < list.Count; i++)
        {
            try
            {
                string sql = "insert into " + TableName + " (airportName,airportPos,id)values('" + list[i]["airportName"] + "','" + list[i]["airportPos"] + "','" + (i + 1) + "');";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();
                rdr.Close();
                print("机场信息储存成功");

            }
            catch (MySqlException)
            {
                print("机场信息已存在");
            }
        }
    }

	//查询表中所有数据
	public List<Dictionary<string, object>> QueryCountFormTable(string tableName)
	{
		string queryCount = "select count(*) id from " + tableName + "; ";
		MySqlCommand cmd1 = new MySqlCommand(queryCount, conn);
		MySqlDataReader rdr1 = cmd1.ExecuteReader();
		int count = 0;
		while (rdr1.Read())
		{
		    Debug.Log("=======count" + rdr1[0]);
		    int.TryParse(rdr1[0].ToString(), out count);
		}
	}
   
    //查询表中所有数据
    public List<Dictionary<string, object>> QueryAllDataFromTable(string tableName)
    {
        string sql = "select * from  " + tableName ;
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataReader rdr = cmd.ExecuteReader();

        List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
        while (rdr.Read())
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();

            if (tableName == "AirportManager")
            {
                AddAirportTableData(dic, rdr);
            }
            list.Add(dic);
        }
        rdr.Close();
        return list;
    }

    //获取表中指定列数据
    public List<Dictionary<string, object>>  QuerySpacificDataFromTable(string tableName, string key, string value)
    {
        List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();

        string sql = "select * from  " + tableName + " where " + key + "='" + value + "'; ";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataReader rdr = cmd.ExecuteReader();

        while (rdr.Read())
        {
            for (int  i = 0; i < rdr.FieldCount; i++)
            {
                //Debug.Log( rdr[i]);
            }

             if (tableName == "RouteManager")
            {
                AddFlightData(dic, rdr);
            }
            list.Add(dic);
        }
        Debug.Log("list数量" + list.Count);
        rdr.Close();
        return list;
    }



    //获取单张表中列的数据
    public Dictionary<string, object> QuerySingleDataFromTable(string tableName, string id)
    {
        string sql = "select * from  " + tableName + " where id=" + id + "; ";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataReader rdr = cmd.ExecuteReader();
        Dictionary<string, object> dic = new Dictionary<string, object>();
        while (rdr.Read())
        {
         
            if(tableName == "AirportManager")
            {
                AddAirportTableData(dic, rdr);
            }
        }
        rdr.Close();
        return dic;
    }
    //将Airport中所有列中查询的数据 添加到字典
    private void AddAirportTableData(Dictionary<string,object> dic, MySqlDataReader rdr)
    {
        dic.Add("airportName", rdr.GetString(rdr.GetOrdinal("airportName")));
        dic.Add("airportPos", rdr.GetString(rdr.GetOrdinal("airportPos")));
    }
}