分享一个C# MySqlHelper.cs MYSQL数据库操作类文件
- 时间:2019-08-13
- 概述:数据库操作类
一个MYSQL数据库操作基类,适用于C#环境,将代码保存为MySqlHelper.cs,在项目中添加引用,这个基类同时还包括了获取分页SQL,执行查询,返回DataSet,在事务中执行查询,返回DataSet,执行 Transact-SQL 语句并返回受影响的行数以及执行查询,返回DataReader等。
using System; using System.Data; using System.Data.Common; using System.Text; #if MYSQL using MySql.Data.MySqlClient; #endif namespace DBUtility { /// <summary> /// 数据库操作基类(for MySql) /// </summary> internal class MySqlHelper : IDBHelper { /// <summary> /// 获取分页SQL /// </summary> /// <param name="tblName">表名</param> /// <param name="fldSort">排序字段,例如:id asc或简写id,可写多个字段(如果是可重复字段建议最后带上主键,例如name desc,id)</param> /// <param name="condition">条件(不需要where)</param> /// <param name="start">起始索引,如每页10条则第1页的起始索引为0,第2页的起始索引为10</param> /// <param name="count">要取得的数据条数</param> /// <returns>返回用于分页的SQL语句</returns> private string GetPagerSQL(string tblName, string fldSort, string condition, int start, int count) { StringBuilder strSql = new StringBuilder("select * from " + tblName); if (!string.IsNullOrEmpty(condition)) { strSql.AppendFormat(" where {0} order by {1}", condition, fldSort); } else { strSql.AppendFormat(" order by {0}", fldSort); } strSql.AppendFormat(" limit {0},{1}", start, count); return strSql.ToString(); } /// <summary> /// 分页获取数据 /// </summary> /// <param name="connectionString">连接字符串</param> /// <param name="tblName">表名</param> /// <param name="fldSort">排序字段,例如:id asc或简写id,可写多个字段(如果是可重复字段建议最后带上主键,例如name desc,id)</param> /// <param name="condition">条件(不需要where)</param> /// <param name="start">起始索引,如每页10条则第1页的起始索引为0,第2页的起始索引为10</param> /// <param name="count">要取得的数据条数</param> public DbDataReader GetPageList(string connectionString, string tblName, string fldSort, string condition, int start, int count) { string sql = GetPagerSQL(tblName, fldSort, condition, start, count); return ExecuteReader(connectionString, CommandType.Text, sql, null); } /// <summary> /// 执行查询,返回DataSet /// </summary> public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { #if MYSQL using (MySqlConnection conn = new MySqlConnection(connectionString)) { using (MySqlCommand cmd = new MySqlCommand()) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds, "ds"); cmd.Parameters.Clear(); return ds; } } } #else throw new SystemException("DBUtility未打开MYSQL编译开关。"); #endif } /// <summary> /// 在事务中执行查询,返回DataSet /// </summary> public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { #if MYSQL MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, "ds"); cmd.Parameters.Clear(); return ds; #else throw new SystemException("DBUtility未打开MYSQL编译开关。"); #endif } /// <summary> /// 执行 Transact-SQL 语句并返回受影响的行数。 /// </summary> public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { #if MYSQL MySqlCommand cmd = new MySqlCommand(); using (MySqlConnection conn = new MySqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } #else throw new SystemException("DBUtility未打开MYSQL编译开关。"); #endif } /// <summary> /// 在事务中执行 Transact-SQL 语句并返回受影响的行数。 /// </summary> public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { #if MYSQL MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; #else throw new SystemException("DBUtility未打开MYSQL编译开关。"); #endif } /// <summary> /// 执行查询,返回DataReader /// </summary> public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { #if MYSQL MySqlCommand cmd = new MySqlCommand(); MySqlConnection conn = new MySqlConnection(connectionString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch (Exception e) { conn.Close(); throw e; } #else throw new SystemException("DBUtility未打开MYSQL编译开关。"); #endif } /// <summary> /// 在事务中执行查询,返回DataReader /// </summary> public DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { #if MYSQL MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; #else throw new SystemException("DBUtility未打开MYSQL编译开关。"); #endif } /// <summary> /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// </summary> public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { #if MYSQL MySqlCommand cmd = new MySqlCommand(); using (MySqlConnection connection = new MySqlConnection(connectionString)) { PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } #else throw new SystemException("DBUtility未打开MYSQL编译开关。"); #endif } /// <summary> /// 在事务中执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// </summary> public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms) { #if MYSQL MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; #else throw new SystemException("DBUtility未打开MYSQL编译开关。"); #endif } /// <summary> /// 生成要执行的命令 /// </summary> private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms) { // 如果存在参数,则表示用户是用参数形式的SQL语句,可以替换 if (cmdParms != null && cmdParms.Length > 0) cmdText = cmdText.Replace("@", "?").Replace(":", "?"); 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 (DbParameter parm in cmdParms) { // 如果存在参数,则表示用户是用参数形式的SQL语句,可以替换 parm.ParameterName = parm.ParameterName.Replace("@", "?").Replace(":", "?"); if (parm.Value == null) parm.Value = DBNull.Value; cmd.Parameters.Add(parm); } } } } }
相关声明:
- 若“分享一个C# MySqlHelper.cs MYSQL数据库操作类文件”有损您的权益,请告之我们删除内容。
部分文章来源于网络,版权归原作者所有。