操作mysql数据库

using System;
using System.Configuration;
using System.Collections;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace MyData
{
    /// <summary>
    /// 通用数据库类MySQL
    /// </summary>
    public class MySQL
    {
        //public static string ConnStr = @"server=localhost;uid=root;pwd=;database=my_soft;charset=utf8";
        public static string ConnStr = MyData.Properties.Settings.Default.my_soft_mysqlConn;
       

        //打开数据库链接
        public static MySqlConnection Open_Conn(string ConnStr)
        {
            try
            {
                MySqlConnection Conn = new MySqlConnection(ConnStr + "Connect Timeout=5;");
                Conn.Open();
                return Conn;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        //关闭数据库链接
        public static void Close_Conn(MySqlConnection Conn)
        {
            if(Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
            GC.Collect();
        }

        //运行MySql语句
        public static int Run_SQL(string SQL, string ConnStr)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            MySqlCommand Cmd = Create_Cmd(SQL, Conn);

            try
            {
                int result_count = Cmd.ExecuteNonQuery();
                Close_Conn(Conn);
                return result_count;
            }
            catch
            {
                Close_Conn(Conn);
                return 0;
            }
        }   

        // 生成Command对象
        public static MySqlCommand Create_Cmd(string SQL, MySqlConnection Conn)
        {
            MySqlCommand Cmd = new MySqlCommand(SQL, Conn);
            return Cmd;
        }

        // 运行MySql语句返回 DataTable
        public static DataTable Get_DataTable(string SQL, string ConnStr, string Table_name)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, Conn);
            DataTable dt = new DataTable(Table_name);
            Da.Fill(dt);
            Close_Conn(Conn);
            return dt;
        }

        // 运行MySql语句返回 MySqlDataReader对象
        public static MySqlDataReader Get_Reader(string SQL, string ConnStr)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            MySqlCommand Cmd = Create_Cmd(SQL, Conn);
            MySqlDataReader Dr;
            try
            {
                Dr = Cmd.ExecuteReader(CommandBehavior.Default);
            }
            catch
            {
                throw new Exception(SQL);
            }
            Close_Conn(Conn);
            return Dr;
        }

        // 运行MySql语句返回 MySqlDataAdapter对象
        public static MySqlDataAdapter Get_Adapter(string SQL, string ConnStr)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, Conn);
            return Da;
        }

        // 运行MySql语句,返回DataSet对象
        public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, Conn);
            try
            {
                Da.Fill(Ds);
            }
            catch(Exception Err)
            {
                throw Err;
            }
            Close_Conn(Conn);
            return Ds;
        }

        // 运行MySql语句,返回DataSet对象
        public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, string tablename)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, Conn);
            try
            {
                Da.Fill(Ds,tablename);
            }
            catch(Exception Ex)
            {
                throw Ex;
            }
            Close_Conn(Conn);
            return Ds;
        }

        // 运行MySql语句,返回DataSet对象，将数据进行了分页
        public static DataSet Get_DataSet(string SQL, string ConnStr, DataSet Ds, int StartIndex, int PageSize, string tablename)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, Conn);
            try
            {
                Da.Fill(Ds, StartIndex, PageSize, tablename);
            }
            catch(Exception Ex)
            {
                throw Ex;
            }
            Close_Conn(Conn);
            return Ds;
        }

        // 返回MySql语句执行结果的第一行第一列
        public static string Get_Row1_Col1_Value(string SQL, string ConnStr)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            string result;
            MySqlDataReader Dr ;
            try
            {
                Dr = Create_Cmd(SQL,Conn).ExecuteReader();
                if (Dr.Read())
                {
                    result = Dr[0].ToString();
                    Dr.Close();
                }
                else
                {
                    result = "";
                    Dr.Close();
                }
            }
            catch
            {
                throw new Exception(SQL);
            }
            Close_Conn(Conn);
            return result;
        }

        /*
        ///////////////////////////////////////////////////////////////////////////////////////////////////
        // 存储过程 相关操作
        ///////////////////////////////////////////////////////////////////////////////////////////////////

        // 生成一个存储过程使用的MySqlcommand.
        // procName 存储过程名, prams 存储过程入参数组, 返回 MySqlCommand对象
        public static MySqlCommand Proc_Create_Cmd(string procName, MySqlParameter[] prams, string ConnStr)
        {
            MySqlConnection Conn = Open_Conn(ConnStr);
            MySqlCommand Cmd = new MySqlCommand(procName, Conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            if (prams != null)
            {
                foreach (MySqlParameter parameter in prams)
                {
                    if(parameter != null)
                    {
                        Cmd.Parameters.Add(parameter);
                    }
                }
            }
            return Cmd;
        }

        // 生成一个存储过程使用的MySqlCommand.
        // procName 存储过程名, prams 存储过程入参数组, 返回 MySqlCommand对象
        private static MySqlCommand Proc_Create_Cmd(string procName, MySqlParameter[] prams, string ConnStr, MySqlDataReader Dr)
        {
            MySqlCommand Cmd = Proc_Create_Cmd(procName, prams, ConnStr);
            if (prams != null)
            {
                foreach (MySqlParameter parameter in prams)
                Cmd.Parameters.Add(parameter);
            }
            Cmd.Parameters.Add(
            new MySqlParameter("ReturnValue", MySqlDbType.Int32, 4,
            ParameterDirection.ReturnValue, false, 0, 0,
            string.Empty, DataRowVersion.Default, null));

            return Cmd;
        }

        // 运行存储过程,返回 MySqlDataReader对象
        public static void Proc_Get_Reader(string procName, MySqlParameter[] prams, string ConnStr, MySqlDataReader Dr)
        {
            MySqlCommand Cmd = Proc_Create_Cmd(procName, prams, ConnStr, Dr);
            Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            return;
        }

        // 运行存储过程,返回执行结果的第一行第一列
        public static string Proc_Get_Value(string procName, MySqlParameter[] prams, string ConnStr)
        {
            MySqlDataReader Dr;
            MySqlCommand Cmd = Proc_Create_Cmd(procName, prams, ConnStr);
            Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            if(Dr.Read())
            {
                return Dr.GetValue(0).ToString();
            }
            else
            {
                return "";
            }
        }

        // 运行存储过程,返回 DataSet对象
        public static DataSet Proc_Get_DataSet(string procName, MySqlParameter[] prams, string ConnStr, DataSet Ds)
        {
            MySqlCommand Cmd = Proc_Create_Cmd(procName, prams, ConnStr);
            MySqlDataAdapter Da = new MySqlDataAdapter(Cmd);
            try
            {
                Da.Fill(Ds);
            }
            catch(Exception Ex)
            {
                throw Ex;
            }
            return Ds;
        }
        */
    }
}