C# 连接数据库 以及数据库增删改查

using System;using System.Collections.Generic;using System.Linq;using System.Text;using MySql.Data;using MySql.Data.MySqlClient;using System.Data;    class DataBaceTool    {    public MySqlConnection conn;    //打开数据库    public void OpenDataBace()    {        string host = "localhost";        string id = "root";        string pwd = "hololens";        string database = "airport";        string result = "";        string connectionString = string.Format("Server = {0}; Database = {1}; User ID = {2}; Password = {3};", host, database, id, pwd);        conn = new MySqlConnection(connectionString);        try        {            conn.Open();            result = conn.ServerVersion;            Console.Write("打开成功 MySql版本" + result);        }        catch (Exception ex)        {            Console.Write(ex.ToString() + "打开报错");        }    }    // 关闭数据库    public void CloseDataBace()        {            try            {                conn.Close();                conn = null;                Console.Write("关闭成功");            }            catch (Exception ex)            {                Console.Write(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)            {                Console.Write("删除失败" + exc);            }        }    //根据key更新表中 一行里多个列的值 我的flightNum是关键key     public void UpdateMultiDataFromTable(string tableName,string flightNum,Dictionary<string, object> updateDic)    {        try        {            string sql = "update " + tableName + " set ";            foreach(string key in updateDic.Keys)            {                sql += (key + "='" + updateDic[key] + "',");            }            sql  =  sql.Remove(sql.LastIndexOf(','),  1);            sql +=( "where " + "flightNum" + " ='" + flightNum + "';");            MySqlCommand cmd = new MySqlCommand(sql, conn);            cmd.ExecuteNonQuery();            Console.Write("修改成功");        }        catch (MySqlException exc)        {            Console.Write("修改失败" + exc);        }    }    //根据表明更新某一列的指 我的flightNum是关键key    public void UpdateDataFormTable(string tableName, string keyword, string key, string value)        {            try            {            string  sql = "update " + tableName + " set " + key + "='" + value + "' " + "where " + "flightNum" + " ='" + keyword + "';";                        MySqlCommand cmd = new MySqlCommand(sql, conn);                cmd.ExecuteNonQuery();                Console.Write("修改成功");            }            catch (MySqlException)            {                Console.Write("修改失败");            }        }        //机场表插入输入    public 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,tag,weather)values('" + list[i]["airportName"] + "','" + list[i]["airportPos"] + "','" + (i + 1) +"','"+list[i]["tag"] + "','" + list[i]["weather"]  + "');";                    MySqlCommand cmd = new MySqlCommand(sql, conn);                    MySqlDataReader rdr = cmd.ExecuteReader();                    rdr.Close();                    Console.Write("机场信息储存成功");             }                catch (MySqlException exc)                {                Console.Write("机场信息存储错误" + exc);                }        }    }      //删除一行    public  void DeleteLine(string tableName, string key, string value)    {        try        {            string sql = "delete from " + tableName + " where " + key + " = '" + value + "';";            MySqlCommand cmd = new MySqlCommand(sql, conn);            MySqlDataReader rdr = cmd.ExecuteReader();            rdr.Close();            Console.Write("删除成功");        }        catch(MySqlException exc)        {            Console.WriteLine("删除失败" + exc);        }    }     //插入数据    public void InsertFlighInfoData(string TableName, List<Dictionary<string, object>> list)    {        for (int i = 0; i < list.Count; i++)        {            try            {                string sql = "insert into " + TableName + " (flightNum,departurePlace,departureTime,startStation,endStation,arrivalTime,destinationPlace,state,speed,flightHeight,flightState,comName,delayDT,actualAT,oilInfo,timeProportion)values('" + list[i]["flightNum"] + "','" + list[i]["departurePlace"] + "','" + list[i]["departureTime"] + "','" + list[i]["startStation"] + "','" + list[i]["endStation"] + "','" + list[i]["arrivalTime"] + "','" + list[i]["destinationPlace"] + "','" + list[i]["state"] + "','" + list[i]["speed"] + "','" + list[i]["flightHeight"] + "','" + list[i]["flightState"] + "','" + list[i]["comName"] + "','" + list[i]["delayDT"] + "','" + list[i]["actualAT"] + "','" + list[i]["oilInfo"] + "','" + list[i]["timeProportion"] + "');";                MySqlCommand cmd = new MySqlCommand(sql, conn);                MySqlDataReader rdr = cmd.ExecuteReader();                rdr.Close();                Console.Write("航班列表存入成功");            }            catch (MySqlException exc)            {                Console.Write("航班列表存入失败" + exc);            }        }    }  // 查询为完成的航班信息 查询特定条件（这里查询state = 2的值）  public  List<Dictionary<string,object>> QueryDataFromCondition(string tableName)    {        //新改的返回列的数据        string sql = "select * from  " + tableName + " where state <> 2";        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 == "RouteManager")            {                //这个方法时把值添加到字典里                AddFlightmanagementTableData(dic, rdr);            }                list.Add(dic);        }        rdr.Close();             return list;    }    //查询表中所有数据    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 == "RouteManager")                {                    AddFlightmanagementTableData(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())            {                Dictionary<string, object> dic = new Dictionary<string, object>();                if (tableName == "RouteManager")                {                    AddFlightData(dic, rdr);                }                list.Add(dic);            }            Console.Write("list数量" + list.Count);            rdr.Close();            return list;        }        public void AddFlightData(Dictionary<string, object> dic, MySqlDataReader rdr)        {            dic.Add("flightNum", rdr.GetString(rdr.GetOrdinal("flightNum")));            dic.Add("departurePlace", rdr.GetString(rdr.GetOrdinal("departurePlace")));             dic.Add("ID", rdr.GetInt64(rdr.GetOrdinal("ID")));    }        //获取单张表中行的数据        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 == "RouteManager")                {                    AddFlightmanagementTableData(dic, rdr);                }             }            rdr.Close();            return dic;        }