C# 操作MySQL-白红宇

C# 操作MySQL
阅读量：6574 次
发布时间：2019-06-24
本文共 10920 字，大约阅读时间需要 36 分钟。
using System;using System.Data;using System.Configuration;using MySql.Data.MySqlClient;using System.Web.UI.WebControls;using System.Web;public class DBQuery{    public DBQuery() {  //  // TODO: 在此处添加构造函数逻辑  //        }     public static void SetDataView(GridView dg)    {        if (GFDB.SysInfo.GridViewPage == 0)        {            GFDB.SysInfo.GridViewPage = int.Parse(ConfigurationManager.AppSettings["GridViewPage"].ToString());        }        if (GFDB.SysInfo.GridViewPage > 0)        {            dg.PageSize = GFDB.SysInfo.GridViewPage;// 15;//每页显示15条记录        }        dg.ShowHeaderWhenEmpty = false;        dg.RowDataBound += new GridViewRowEventHandler(SmartGridView_RowDataBound);    }    public static void SetDataView(GridView dg, bool ARowDataBound, bool AShowHeaderWhenEmpty)    {        if (GFDB.SysInfo.GridViewPage == 0)        {            GFDB.SysInfo.GridViewPage = int.Parse(ConfigurationManager.AppSettings["GridViewPage"].ToString());        }        if (GFDB.SysInfo.GridViewPage > 0)        {            dg.PageSize = GFDB.SysInfo.GridViewPage;// 15;//每页显示15条记录        }        dg.ShowHeaderWhenEmpty = AShowHeaderWhenEmpty;        if (ARowDataBound)        {            dg.RowDataBound += new GridViewRowEventHandler(SmartGridView_RowDataBound);        }    }    public static void SmartGridView_RowDataBound(object sender, GridViewRowEventArgs e)    {        if (e.Row.RowType == DataControlRowType.DataRow)        {            e.Row.Attributes.Add("onmouseover", "currentcolor = this.style.backgroundColor;this.style.backgroundColor='#cccccc'");            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor = currentcolor");            e.Row.Attributes["style"] = "Cursor:hand;height:28px;";        }    }       #region EXECUTE DATASET    //    DBConnectionPool        ///         /// This method returns the data in dataset form.         /// 
        /// 
    
     Command type        /// 
     
      Command text        /// 
      
       Data in the form of Dataset.
              /// 
              public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText)        {            DataSet dsData = new DataSet();            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();            MySqlCommand myCommand = new MySqlCommand();            try            {                try                {                    myCommand.Connection = DBConnectionPool.getPool().getConnection();                    myCommand.CommandType = cmdType;                    myCommand.CommandText =  CommandText; //"set names gbk;" +                    myDataAdapter.SelectCommand = myCommand;                    myDataAdapter.Fill(dsData);                    return dsData;                }                catch (MySql.Data.MySqlClient.MySqlException Ex)                {                    ExceptLog.SetExcept(Ex);                    return null;                    //throw new Exception(Ex.Message);                                    }            }            finally            {                DBConnectionPool.getPool().closeConnection(myCommand.Connection);            }                    }        ///         /// This method returns the data in dataset form.         /// 
        /// 
       
        Command type        /// 
        
         Command text        /// 
         
          MySqlParameters        /// 
          
           Data in the form of Dataset.
                  /// public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)        public static DataSet ExecuteDataSet(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)        {                         DataSet dsData = new DataSet();            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();            MySqlCommand myCommand = new MySqlCommand();            try            {                try                {                    myCommand.Connection = DBConnectionPool.getPool().getConnection();                    myCommand.CommandType = cmdType;                    myCommand.CommandText = CommandText;                                        for (int i = 0; i < mysqlParams.Length; i++)                    {                        myCommand.Parameters.Add(mysqlParams[i]);                    }                    myDataAdapter.SelectCommand = myCommand;                    myDataAdapter.Fill(dsData);                    return dsData;                }                catch (Exception Ex)                {                    ExceptLog.SetExcept(Ex);                    return null;                    //throw Ex;                }            }            finally            {                DBConnectionPool.getPool().closeConnection(myCommand.Connection);            }        }        public static bool ExecuteSQLParams(CommandType cmdType, string CommandText, MySqlParameter[] mysqlParams)        {            DataSet dsData = new DataSet();            MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();            MySqlCommand myCommand = new MySqlCommand();            try            {                try                {                    myCommand.Connection = DBConnectionPool.getPool().getConnection();                    myCommand.CommandType = cmdType;                    myCommand.CommandText = CommandText;                    for (int i = 0; i < mysqlParams.Length; i++)                    {                        myCommand.Parameters.Add(mysqlParams[i]);                    }                    myDataAdapter.SelectCommand = myCommand;                    myCommand.ExecuteNonQuery();                    //myDataAdapter.Fill(dsData);                    return true;                }                catch (Exception Ex)                {                    ExceptLog.SetExcept(Ex);                    return false;                    //throw Ex;                }            }            finally            {                DBConnectionPool.getPool().closeConnection(myCommand.Connection);            }        }    public static MySqlDataReader ExecuteDataReader(string CommandText)        {            MySqlCommand myCommand = new MySqlCommand();            MySqlDataReader myReader;            try            {                try                {                    myCommand.Connection = DBConnectionPool.getPool().getConnection();                    myCommand.CommandType = CommandType.Text;                    myCommand.CommandText = CommandText;                    myReader = myCommand.ExecuteReader();                    return myReader;                }                catch (Exception Ex)                {                    ExceptLog.SetExcept(Ex);                    return null;                    //throw Ex;                }            }            finally            {                DBConnectionPool.getPool().closeConnection(myCommand.Connection);            }        }    public static DataTable ExecuteDataTable(string CommandText)    {        MySqlCommand myCommand = new MySqlCommand();                MySqlDataReader myReader;        try        {            try            {                myCommand.Connection = DBConnectionPool.getPool().getConnection();                myCommand.CommandType = CommandType.Text;                myCommand.CommandText = CommandText;//"set names gbk;" +                myReader = myCommand.ExecuteReader();                DataTable Table = new DataTable("myDataTable");                Table.Load(myReader);                return Table;            }            catch (Exception Ex)            {                ExceptLog.SetExcept(Ex);                return null;             }        }        finally        {                        DBConnectionPool.getPool().closeConnection(myCommand.Connection);        }    }    public static DataTable ExecuteSqlReDataTable(string CommandText)    {        MySqlCommand myCommand = new MySqlCommand();        MySqlDataReader myReader;        try        {            try            {                myCommand.Connection = DBConnectionPool.getPool().getConnection();                myCommand.CommandType = CommandType.Text;                myCommand.CommandText = CommandText;//"set names gbk;" +                myReader = myCommand.ExecuteReader();                DataTable Table = new DataTable("myDataTable");                Table.Load(myReader);                return Table;            }            catch (Exception Ex)            {                throw Ex;                return null;            }        }        finally        {            DBConnectionPool.getPool().closeConnection(myCommand.Connection);        }    }    public static string GetParamValueMemo(string AParamName)    {         string strSQL = "select * from `sys_parameter_memo` where name ='" + AParamName + "'";        DataTable dt = DBQuery.ExecuteDataTable(strSQL);        if (dt.Rows.Count == 0)        {            return "";        }        string str = dt.Rows[0]["pvalue"].ToString().Replace("\r\n", "\n");               string[] slist = str.Split(new char[] {'\n'});        str = "";        for (int i = 0; i < slist.Length - 1;i++ )        {            if (slist[i].Trim().IndexOf("#") != 0)            {                str = str + slist[i];            }        }        return str;    }    /// 
              /// 执行一条SQL返回是否成功    /// 
    /// 
          
               /// 
           
                /// 
            
             真假
                public static bool ExecuteSql(CommandType cmdType, string CommandText,ref string ReError)    {        if ("".Equals(CommandText))         {            ReError = "SQL语为空！";            return false;        }        MySqlCommand myCommand = new MySqlCommand();        try        {            try            {                myCommand.Connection = DBConnectionPool.getPool().getConnection();                myCommand.CommandType = cmdType;                myCommand.CommandText = CommandText;                myCommand.ExecuteNonQuery();                return true;            }            catch (Exception Ex)            {                ReError = Ex.Message.ToString().Replace("'","`");                ExceptLog.SetExcept(Ex);                return false;            }        }        finally        {            DBConnectionPool.getPool().closeConnection(myCommand.Connection);        }    }    public static string GetParam(string PName)    {        try        {            DataTable dt = ExecuteDataTable("select Name,Pvalue from `sys_parameter_list` where Name='" + PName + "';");            if ((dt == null) || (dt.Rows.Count == 0))            {                return "";            }            return dt.Rows[0]["Pvalue"].ToString();        }        catch (Exception Ex)        {            return "";        }    }    public static bool ExecuteSql( string CommandText, ref string ReError)    {        if ("".Equals(CommandText))        {            ReError = "SQL语为空！";            return false;        }        MySqlCommand myCommand = new MySqlCommand();        try        {            try            {                myCommand.Connection = DBConnectionPool.getPool().getConnection();                myCommand.CommandType = CommandType.Text;                myCommand.CommandText = CommandText;                myCommand.ExecuteNonQuery();                return true;            }            catch (Exception Ex)            {                ReError = Ex.Message.ToString().Replace("'", "`");                ExceptLog.SetExcept(Ex);                return false;            }        }        finally        {            DBConnectionPool.getPool().closeConnection(myCommand.Connection);        }    }    public static bool ExecuteSql(string CommandText)    {        if ("".Equals(CommandText))        {            return false;        }        MySqlCommand myCommand = new MySqlCommand();        try        {            try            {                myCommand.Connection = DBConnectionPool.getPool().getConnection();                myCommand.CommandType = CommandType.Text;                myCommand.CommandText = CommandText;                myCommand.ExecuteNonQuery();                return true;            }            catch (Exception Ex)            {                ExceptLog.SetExcept(Ex);                return false;            }        }        finally        {            DBConnectionPool.getPool().closeConnection(myCommand.Connection);        }    }        #endregion    public static void ExportExcel(string ExecSQL, string FileName, HttpResponse resp)    {        try        {            string tmp = "";            int fc = 0;            string sFileName = FileName.ToLower();              if (sFileName == "")            {                sFileName = System.DateTime.Today.ToString("yyyyMMdd");            }            if (sFileName.IndexOf(".xls")<0){                sFileName = sFileName + ".xls";            }            resp.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");                 resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(sFileName).ToString());            //下面就列出常用的一些格式：            //1） 文本：vnd.ms-excel.numberformat:@            //2） 日期：vnd.ms-excel.numberformat:yyyy/mm/dd            //3） 数字：vnd.ms-excel.numberformat:#,##0.00            //4） 货币：vnd.ms-excel.numberformat:￥#,##0.00            //5） 百分比：vnd.ms-excel.numberformat: #0.00%            resp.ContentType = "vnd.ms-excel.numberformat:@";            //定义表对象与行对像，同时用DataSet对其值进行初始化             //DataTable dt = DBQuery.ExecuteDataSet(CommandType.Text, ExecSQL).Tables[0];            DataTable dt = DBQuery.ExecuteDataTable(ExecSQL);            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符             fc = dt.Columns.Count;            for (int i = 0; i < fc; i++)            {                tmp = tmp + "" + dt.Columns[i].Caption.Replace(" ", "") + "\t";            }            resp.Write(tmp + "\n");            for (int i = 0; i < dt.Rows.Count; i++)            {                tmp = "";                for (int j = 0; j < fc; j++)                {                    tmp = tmp + "" + dt.Rows[i].ItemArray[j] + "\t";                }                resp.Write(tmp + "\n");            }            //强制释放无用资源              GC.Collect();            //写缓冲区中的数据到HTTP头文件中             resp.End();            resp.Flush();        }        catch (Exception Ex)        {            ExceptLog.SetExcept(Ex);            throw Ex;        }           }        //使用连接后的连接获取方法//获取连接时，就不用创建连接直接从池中获取数据        //string strsql = "select * from Chapter";//SqlDataAdapter sqldap = new SqlDataAdapter(strsql, DBConnectionPool.getPool().getConnection());        //DataSet set = new DataSet();        //sqldap.Fill(set);        //GridView1.DataSource = set.Tables[0].DefaultView;        //GridView1.DataBind();}