C#:连接MySql数据库以及对数据库的操作

using System;
using MySql.Data.MySqlClient;
using System.Data;
using System.IO;
using System.Collections.Generic;

public class DataMgr: IDisposable
{

    MySqlConnection sqlConn;

    //单例模式
    public static DataMgr instance;

    public DataMgr()
    {
        instance = this;
        Connect();
    }

    //连接
    public bool Connect()
    {
        //数据库
        string connStr = "Database=mydb;Data Source=192.168.2.38;";
        connStr += "User Id=root;Password=mysql;port=3306";
        sqlConn = new MySqlConnection(connStr);
        try
        {
            sqlConn.Open();
            return true;
            
        }
        catch (Exception e)
        {
            Console.Write("[DataMgr]Connect " + e.Message);
            return false;
        }
    }


    List<string> list = new List<string>();//查询内容列表
    List<string> listcolum = new List<string>();//字段名列表
    List<string> listkeyword = new List<string>();//字段名列表



    //查询数据
    /// <summary>
    ///查询数据方法
    /// </summary>
    ///<param name = "TableName" >表名</ param >
    ///<param name = "listColumn" >字段列表</ param >
    ///<param name = "listkeyword" >查询值列表</ param >
    public List<string> SelectData(string TableName, string[] listColumn, string[] listkeyword)
    {
        string cloumns = listColumn[0] + ""; for (int a = 1; a < listColumn.Length; ++a) { cloumns += "," + listColumn[a] + ""; }
        string columnvelve = listColumn[0] + " = ‘" + listkeyword[0]+ "‘"; for (int a = 1; a < listColumn.Length; ++a) { columnvelve += " and " + listColumn[a] + "=‘" + listkeyword[a] + "‘"; }

        string query = "select * from " + TableName + " where " + columnvelve;

        string strSQL = string.Format("SELECT  COLUMN_NAME  FROM  `information_schema`.`COLUMNS`   where `TABLE_SCHEMA`=‘mydb‘  and  `TABLE_NAME`=‘主表‘   ;");
        // string query  = " select * from 主表 where "+columnvelve ;
        string cmdStr = string.Format(query);

        #region---------------查询，利用 MySQLDataReader,依次读取每一条数据-------------------
        MySqlCommand mySQLCommand = new MySqlCommand(cmdStr, sqlConn);
        MySqlDataReader mySQLReader = (MySqlDataReader)mySQLCommand.ExecuteReader();
        int i = 0;
        while (mySQLReader.Read())
        {
            i++;
            for (int j = 0; j < mySQLReader.FieldCount; j++)
            {
                string strname =   mySQLReader[j].ToString();
      
                list.Add(strname);
            }
        }
      mySQLReader.Close();
        MySqlCommand mySQLCommandColumn = new MySqlCommand(strSQL, sqlConn);
        MySqlDataReader mySQLReaderColumn = (MySqlDataReader)mySQLCommandColumn.ExecuteReader();
        while (mySQLReaderColumn.Read())
        {
            i++;
            for (int j = 0; j < mySQLReaderColumn.FieldCount; j++)
            {
                string tempcolum = mySQLReaderColumn[j].ToString();
                listcolum.Add(tempcolum);
            }
        }
        mySQLReaderColumn.Close();
        for (int j=0;j<list.Count;j++)
        {
            if (list[j] == "")
            {
                list[j] = "null";
            }
            Console.WriteLine(/*listcolum[j] + ":    \n" +*/ list[j] + "\t");
        }
        Console.WriteLine("-----------------操作结束--------------------------------");
        #endregion
        return list;
    }

    //查询整张表信息
    public void SelectTableData(string TableName)
    {
        string cmdStr = string.Format("select * from " + TableName +";");
        MySqlCommand mySQLCommand = new MySqlCommand(cmdStr, sqlConn);
        MySqlDataReader mySQLReader = (MySqlDataReader)mySQLCommand.ExecuteReader();
        int i = 0;
        while (mySQLReader.Read())
        {
            i++;
            for (int j = 0; j < mySQLReader.FieldCount; j++)
            {
               string strtable = mySQLReader[j].ToString();
                list.Add(strtable);
            }
        }
        for (int j = 0; j < list.Count; j++)
        {
            if (list[j] == "")
            {
                list[j] = "null";
            }
            Console.WriteLine(/*listcolum[j]+ ":    \n" +*/ list[j] + "\t");
        }
        mySQLReader.Close();

    }



    //添加数据
    /// <summary>
    ///插入数据方法
    /// </summary>
    ///<param name = "TableName" >表名</ param >
    ///<param name = "listColumn" >字段数组列表</ param >
    ///<param name = "listKeyWord" >添加的值</ param >
    public void InsertInto(string TableName, string[] listColumn, string[] listKeyWord)
    {
        string query = "INSERT INTO " + TableName +
        "(" + listColumn[0]; for (int i = 1; i < listColumn.Length; ++i) { query += ", " + listColumn[i]; }query += ")"
        + " VALUES (‘" + listKeyWord[0] + "‘"; for (int i = 1; i < listKeyWord.Length; ++i) { query += ", ‘" + listKeyWord[i] + "‘"; } query += ")";
        string cmdStr = string.Format(query);
        if (listKeyWord != null)
        {
            MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
            try
            {
                cmd.ExecuteNonQuery();

                cmd.Dispose();
                sqlConn.Close();
                 
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]Register " + e.Message);
            }
        }
    }

    public void Inst(string tablename,string coumn,string values)
    {
        string str = "INSERT INTO "+tablename+"("+coumn+")"+" VALUES( ‘"+values+"‘ )";
        string cmdstr = string.Format(str);
        MySqlCommand cmd = new MySqlCommand(cmdstr,sqlConn);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch(Exception e)
        {
            Console.WriteLine("[DataMgre]Register" +e.Message);
        }

    }
    //修改数据
    /// <summary>
    ///修改数据方法
    /// </summary>
    ///<param name = "TableName" >表名</ param >
    ///<param name = "Column" >字段数组列表</ param >
    ///<param name = "Columnvalues" >添加的值</ param >
    ///<param name = "KeyWord" >主键字段名</ param >
    ///<param name = "Keyvalue" >主键字段值</ param >
    public void UpdateValue(string TableName, string[] listColumn, string[] Columnvalues, string KeyWord,string Keyvalue)
    {

        string query = "UPDATE " + TableName + " SET " + listColumn[0] + " = " +"‘"+ Columnvalues[0]+"‘";

        for (int i = 1; i < Columnvalues.Length; ++i)
        {

            query += ", " + listColumn[i] + " =" + "‘"+Columnvalues[i]+"‘";
        }

        query += " WHERE " + KeyWord + " = ‘" + Keyvalue + " ‘";

    
      string cmdStr = string.Format(query);

        MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
        try
        {
            cmd.ExecuteNonQuery();
            // return true;
        }
        catch (Exception e)
        {
            Console.WriteLine("[DataMgr]Register " + e.Message);
            //  return false;
        }

    }



    //保存数据
    /// <summary>
    ///保存数据方法
    /// </summary>
    ///<param name = "TableName" >表名</ param >
    ///<param name = "RootNode" >XML父节点</ param >
    ///<param name = "ChildNode" >XML子节点</ param >
    public void setxml(string TableName,string RootNode,string ChildNode)
    {
        String strFileName = "E:/MySql_XML/XML/out.xml";//保存XML文件路径
        string cmdStr = string.Format("select * from "+ TableName + ";");
        MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
        DataSet _DataSet1 = new DataSet(RootNode);

        MySqlDataAdapter _MySqlDataAdapter1 = new MySqlDataAdapter(cmd);
        _MySqlDataAdapter1.Fill(_DataSet1, ChildNode);
        FileStream myFs = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Write);
        _DataSet1.WriteXml(myFs);
        myFs.Close();
    }



    //删除数据
    /// <summary>
    ///查询数据方法
    /// </summary>
    ///<param name = "TableName" >表名</ param >
    ///<param name = "cols" >字段数组列表</ param >
    ///<param name = "colsvalues" >添加的值</ param >
    public void DeleteDate(string TableName, string[] listColumn, string[] listColumnvalues)
    {

        string query = "DELETE FROM " + TableName + " WHERE " + listColumn[0] + " = " +"‘"+ listColumnvalues[0]+"‘";

        for (int i = 1; i < listColumnvalues.Length; ++i)
        {

            query += " or " + listColumn[i] + " = " +"‘"+ listColumnvalues[i]+"‘";
        }

        string cmdStr = string.Format(query);
        MySqlCommand cmd = new MySqlCommand(cmdStr, sqlConn);
        try
        {
            cmd.ExecuteNonQuery();
            // return true;
        }
        catch (Exception e)
        {
            Console.WriteLine("[DataMgr]Register " + e.Message);
            //  return false;
        }

    }


    void IDisposable.Dispose()
    {
        sqlConn.Close();
        sqlConn.Dispose();
    }


}