文档章节

用于Mysql操作的MySqlHelper类

深圳大道
 深圳大道
发布于 2016/12/29 15:23
字数 1815
阅读 3
收藏 0
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Collections;
using MySql.Data.Common;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Configuration;
using System.IO;

namespace MSCL
{
    /// <summary>  
    /// 先引用官网MySql.Data.dll文件
    /// </summary>  
    public abstract class MYSQLHelper
    {

        /// <summary>
        /// a valid database connectionstring
        /// </summary>
        public static string connectionStringManager = ConfigurationManager.AppSettings["MySqlConnStr"].ToString();

        /// <summary>
        /// a valid database connectionstring
        /// </summary>
        public static string ConnectionStringManager
        {
            get { return connectionStringManager; }
        }


        //hashtable to store the parameter information, the hash table can store any type of argument   
        //Here the hashtable is static types of static variables, since it is static, that is a definition of global use.  
        //All parameters are using this hash table, how to ensure that others in the change does not affect their time to read it  
        //Before ,the method can use the lock method to lock the table, does not allow others to modify.when it has readed then  unlocked table.  
        //Now .NET provides a HashTable's Synchronized methods to achieve the same function, no need to manually lock, completed directly by the system framework   
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>  
        /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring   
        /// The parameter list using parameters that in array forms  
        /// </summary>  
        /// <remarks>  
        /// Usage example:   
        /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure,  
        /// "PublishOrders", new MySqlParameter("@prodid", 24));  
        /// </remarks>  
        /// <param name="connectionString">a valid database connectionstring</param>  
        /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.) </param>  
        /// <param name="cmdText">stored procedure name or T-SQL statement</param>  
        /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param>  
        /// <returns>Returns a value that means number of rows affected</returns>  
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>  
        /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring   
        /// The parameter list using parameters that in array forms  
        /// </summary>  
        /// <remarks>  
        /// Usage example:   
        /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure,  
        /// "PublishOrders", new MySqlParameter("@prodid", 24));  
        /// </remarks>  
        /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.) </param>  
        /// <param name="connectionString">a valid database connectionstring</param>  
        /// <param name="cmdText">stored procedure name or T-SQL statement</param>  
        /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param>  
        /// <returns>Returns true or false </returns>  
        public static bool ExecuteNonQuery(CommandType cmdType, string connectionString, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                try
                {
                    int val = cmd.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>  
        /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring   
        /// Array of form parameters using the parameter list   
        /// </summary>  
        /// <param name="conn">connection</param>  
        /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.)</param>  
        /// <param name="cmdText">stored procedure name or T-SQL statement</param>  
        /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param>  
        /// <returns>Returns a value that means number of rows affected</returns>  
        public static int ExecuteNonQuery(MySqlConnection conn, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>  
        /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring   
        /// Array of form parameters using the parameter list   
        /// </summary>  
        /// <param name="conn">sql Connection that has transaction</param>  
        /// <param name="cmdType">SqlCommand command type (stored procedures, T-SQL statement, and so on.)</param>  
        /// <param name="cmdText">stored procedure name or T-SQL statement</param>  
        /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param>  
        /// <returns>Returns a value that means number of rows affected </returns>  
        public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>  
        /// Call method of sqldatareader to read data  
        /// </summary>  
        /// <param name="connectionString">connectionstring</param>  
        /// <param name="cmdType">command type, such as using stored procedures: CommandType.StoredProcedure</param>  
        /// <param name="cmdText">stored procedure name or T-SQL statement</param>  
        /// <param name="commandParameters">parameters</param>  
        /// <returns>SqlDataReader type of data collection</returns>  
        public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception we want to   
            // close the connection throw code, because no datareader will exist, hence the   
            // commandBehaviour.CloseConnection will not work  
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>  
        /// use the ExectueScalar to read a single result  
        /// </summary>  
        /// <param name="connectionString">connectionstring</param>  
        /// <param name="cmdType">command type, such as using stored procedures: CommandType.StoredProcedure</param>  
        /// <param name="cmdText">stored procedure name or T-SQL statement</param>  
        /// <param name="commandParameters">parameters</param>  
        /// <returns>a value in object type</returns>  
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
        {
            MySqlCommand cmd = new MySqlCommand();

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }
        
        /// <summary>
        /// Call method of dataset to read data 
        /// </summary>
        /// <param name="connectionString">connectionstring</param>
        /// <param name="cmdText">stored procedure name or T-SQL statement</param>
        /// <param name="commandParameters">parameters</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSet(string connectionString, string cmdText, params MySqlParameter[] commandParameters)
        {
            DataSet retSet = new DataSet();
            using (MySqlDataAdapter msda = new MySqlDataAdapter(cmdText, connectionString))
            {
                msda.Fill(retSet);
            }
            return retSet;
        }

        /// <summary>
        /// Call method of datatable to read data 
        /// </summary>
        /// <param name="connectionString">connectionstring</param>
        /// <param name="cmdText">stored procedure name or T-SQL statement</param>
        /// <param name="commandParameters">parameters</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string connectionString, string cmdText, params MySqlParameter[] commandParameters)
        {
            DataSet retSet = new DataSet();
            using (MySqlDataAdapter msda = new MySqlDataAdapter(cmdText, connectionString))
            {
                msda.Fill(retSet);
            }
            return retSet.Tables[0];
        }

        /// <summary>  
        /// cache the parameters in the HashTable  
        /// </summary>  
        /// <param name="cacheKey">hashtable key name</param>  
        /// <param name="commandParameters">the parameters that need to cached</param>  
        public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>  
        /// get parameters in hashtable by cacheKey  
        /// </summary>  
        /// <param name="cacheKey">hashtable key name</param>  
        /// <returns>the parameters</returns>  
        public static MySqlParameter[] GetCachedParameters(string cacheKey)
        {
            MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>  
        ///Prepare parameters for the implementation of the command  
        /// </summary>  
        /// <param name="cmd">mySqlCommand command</param>  
        /// <param name="conn">database connection that is existing</param>  
        /// <param name="trans">database transaction processing </param>  
        /// <param name="cmdType">SqlCommand command type (stored procedures, T-SQL statement, and so on.) </param>  
        /// <param name="cmdText">Command text, T-SQL statements such as Select * from Products</param>  
        /// <param name="cmdParms">return the command that has parameters</param>  
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
        }
        #region parameters
        /// <summary>  
        /// Set parameters  
        /// </summary>  
        /// <param name="ParamName">parameter name</param>  
        /// <param name="DbType">data type</param>  
        /// <param name="Size">type size</param>  
        /// <param name="Direction">input or output</param>  
        /// <param name="Value">set the value</param>  
        /// <returns>Return parameters that has been assigned</returns>  
        public static MySqlParameter CreateParam(string ParamName, MySqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            MySqlParameter param;


            if (Size > 0)
            {
                param = new MySqlParameter(ParamName, DbType, Size);
            }
            else
            {

                param = new MySqlParameter(ParamName, DbType);
            }


            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
            {
                param.Value = Value;
            }


            return param;
        }

        /// <summary>  
        /// set Input parameters  
        /// </summary>  
        /// <param name="ParamName">parameter names, such as:@ id </param>  
        /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param>  
        /// <param name="Size">size parameters, such as: the length of character type for the 100</param>  
        /// <param name="Value">parameter value to be assigned</param>  
        /// <returns>Parameters</returns>  
        public static MySqlParameter CreateInParam(string ParamName, MySqlDbType DbType, int Size, object Value)
        {
            return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }

        /// <summary>  
        /// Output parameters   
        /// </summary>  
        /// <param name="ParamName">parameter names, such as:@ id</param>  
        /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param>  
        /// <param name="Size">size parameters, such as: the length of character type for the 100</param>  
        /// <param name="Value">parameter value to be assigned</param>  
        /// <returns>Parameters</returns>  
        public static MySqlParameter CreateOutParam(string ParamName, MySqlDbType DbType, int Size)
        {
            return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }

        /// <summary>  
        /// Set return parameter value   
        /// </summary>  
        /// <param name="ParamName">parameter names, such as:@ id</param>  
        /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param>  
        /// <param name="Size">size parameters, such as: the length of character type for the 100</param>  
        /// <param name="Value">parameter value to be assigned<</param>  
        /// <returns>Parameters</returns>  
        public static MySqlParameter CreateReturnParam(string ParamName, MySqlDbType DbType, int Size)
        {
            return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
        }

        /// <summary>  
        /// Generate paging storedProcedure parameters  
        /// </summary>  
        /// <param name="CurrentIndex">CurrentPageIndex</param>  
        /// <param name="PageSize">pageSize</param>  
        /// <param name="WhereSql">query Condition</param>  
        /// <param name="TableName">tableName</param>  
        /// <param name="Columns">columns to query</param>  
        /// <param name="Sort">sort</param>  
        /// <returns>MySqlParameter collection</returns>  
        public static MySqlParameter[] GetPageParm(int CurrentIndex, int PageSize, string WhereSql, string TableName, string Columns, Hashtable Sort)
        {
            MySqlParameter[] parm = {   
                                   MYSQLHelper.CreateInParam("@CurrentIndex",  MySqlDbType.Int32,      4,      CurrentIndex    ),  
                                   MYSQLHelper.CreateInParam("@PageSize",      MySqlDbType.Int32,      4,      PageSize        ),  
                                   MYSQLHelper.CreateInParam("@WhereSql",      MySqlDbType.VarChar,  2500,    WhereSql        ),  
                                   MYSQLHelper.CreateInParam("@TableName",     MySqlDbType.VarChar,  20,     TableName       ),  
                                   MYSQLHelper.CreateInParam("@Column",        MySqlDbType.VarChar,  2500,    Columns         ),  
                                   MYSQLHelper.CreateInParam("@Sort",          MySqlDbType.VarChar,  50,     GetSort(Sort)   ),  
                                   MYSQLHelper.CreateOutParam("@RecordCount",  MySqlDbType.Int32,      4                       )  
                                   };
            return parm;
        }
        /// <summary>  
        /// Statistics data that in table  
        /// </summary>  
        /// <param name="TableName">table name</param>  
        /// <param name="Columns">Statistics column</param>  
        /// <param name="WhereSql">conditions</param>  
        /// <returns>Set of parameters</returns>  
        public static MySqlParameter[] GetCountParm(string TableName, string Columns, string WhereSql)
        {
            MySqlParameter[] parm = {   
                                   MYSQLHelper.CreateInParam("@TableName",     MySqlDbType.VarChar,  20,     TableName       ),  
                                   MYSQLHelper.CreateInParam("@CountColumn",  MySqlDbType.VarChar,  20,     Columns         ),  
                                   MYSQLHelper.CreateInParam("@WhereSql",      MySqlDbType.VarChar,  250,    WhereSql        ),  
                                   MYSQLHelper.CreateOutParam("@RecordCount",  MySqlDbType.Int32,      4                       )  
                                   };
            return parm;
        }
        /// <summary>  
        /// Get the sql that is Sorted   
        /// </summary>  
        /// <param name="sort"> sort column and values</param>  
        /// <returns>SQL sort string</returns>  
        private static string GetSort(Hashtable sort)
        {
            string str = "";
            int i = 0;
            if (sort != null && sort.Count > 0)
            {
                foreach (DictionaryEntry de in sort)
                {
                    i++;
                    str += de.Key + " " + de.Value;
                    if (i != sort.Count)
                    {
                        str += ",";
                    }
                }
            }
            return str;
        }

        /// <summary>  
        /// execute a trascation include one or more sql sentence(author:donne yin)  
        /// </summary>  
        /// <param name="connectionString"></param>  
        /// <param name="cmdType"></param>  
        /// <param name="cmdTexts"></param>  
        /// <param name="commandParameters"></param>  
        /// <returns>execute trascation result(success: true | fail: false)</returns>  
        public static bool ExecuteTransaction(string connectionString, CommandType cmdType, string[] cmdTexts, params MySqlParameter[][] commandParameters)
        {
            MySqlConnection myConnection = new MySqlConnection(connectionString);       //get the connection object  
            myConnection.Open();                                                        //open the connection  
            MySqlTransaction myTrans = myConnection.BeginTransaction();                 //begin a trascation  
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = myConnection;
            cmd.Transaction = myTrans;

            try
            {
                for (int i = 0; i < cmdTexts.Length; i++)
                {
                    PrepareCommand(cmd, myConnection, null, cmdType, cmdTexts[i], commandParameters[i]);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                myTrans.Commit();
            }
            catch
            {
                myTrans.Rollback();
                return false;
            }
            finally
            {
                myConnection.Close();
            }
            return true;
        }
        #endregion
    }
}

本文转载自:http://blog.csdn.net/smartsmile2012/article/details/38369385

深圳大道
粉丝 3
博文 877
码字总数 0
作品 0
深圳
架构师
私信 提问
ASP.NET Core使用EF Core操作MySql数据库

ASP.NET Core操作MySql数据库, 这样整套环境都可以布署在Linux上 使用微软的 Microsoft.EntityFrameworkCore(2.1.4) 和MySql出的 MySql.Data.EntityFrameworkCore(8.0.13) 软件版本 Asp.ne...

蜗牛丨
2018/11/30
0
0
C# 基于MySQL的数据层基类(MySQLHelper)

using System; using System.Data; using MySql.Data; using MySql.Data.MySqlClient; namespace DBUtility { region 数据库连接字符串 endregion region PrepareCommand endregion region E......

Yamazaki
2012/06/18
0
0
.NET Core开发日志——Entity Framework与PostgreSQL

Entity Framework在.NET Core中被命名为Entity Framework Core。虽然一般会用于对SQL Server数据库进行数据操作,但其实它还支持其它数据库,这里就以PostgreSQL作为例子。 PostgreSQL Postg...

Ken.W
2018/10/02
0
0
WEB开发的C++类库--WebAppLib

WebAppLib是一系列主要用于类Unix操作系统环境下WEB开发的C++类库。 设计目的是通过提供使用简单方便、相对独立的C++类和函数来简化CGI程序开发过程中的常见操作,提高开发效率,降低系统维护...

pi1ot
2012/06/07
6.8K
0
数据库操作辅助类--wuxiu.sqlhelper

sqlhelper 是以.NET Framework平台开发的数据库操作辅助类,其中封装了对sqlserver、access、oracle、mysql相关类支持。在sqlhelper中可以轻松实现对数据库类的获取和集合类的赋值,还可以通...

aspx8855
2014/05/20
1K
1

没有更多内容

加载失败,请刷新页面

加载更多

springboot 整合redis

springboot整合redis官方是有文档的: 英文看不懂可以翻译,代码应该看得懂, 这个是自动注入的。当然也可以xml注入,手动配置。 整合步骤: pom文件: <!-- spring boot web --> ...

jason_kiss
25分钟前
2
0
手机耗电问题,大部分是没有正确使用这个“锁”

当安卓设备闲置时,设备很快会进入休眠状态,以达到省电和减少CPU占用的目的。但有些应用在手机灭屏甚至系统休眠时,依然频繁占用CPU处理事件或唤醒屏幕提示用户消息,这类行为会导致手机耗电...

安卓绿色联盟
26分钟前
1
0
UI 设计中的渐变

>**简评:**渐变是通过两种或多种不同的色彩来绘制一个元素,同时在颜色的交界处进行衰减变化的一种设计。从拟物到扁平再到渐变,人们慢慢发现它能创造出从未有过的一种色彩感觉 —— 独特、...

极光推送
32分钟前
2
0
powerdesigner name 转注释vb脚本

Option Explicit ValidationMode = True InteractiveMode = im_BatchDim mdl ' the current model' get the current active model Set mdl = ......

zhu97
36分钟前
2
0
V2Ray的安装与使用

1 1. bash <(curl -s -L https://git.io/v2ray.sh)yum update -y && yum install curl -y安装好 curl 之后就能安装脚本了输入快捷管理命令v2ray后,开始进行v2ray服务端配置...

吕湘颖
38分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部