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