数据库助手连接MySQL设置_通用的数据库操作助手类

最新推荐文章于 2021-04-11 03:03:04 发布

Actor.又戈叔

最新推荐文章于 2021-04-11 03:03:04 发布

阅读量159

点赞数

文章标签：数据库助手连接MySQL设置

本文链接：https://blog.csdn.net/weixin_42513216/article/details/114019025

版权

这个博客介绍了一个名为`DbHelper`的C#类，用于方便地连接和操作MySQL数据库。类中包含了连接字符串获取、数据库连接对象、预处理命令、执行SQL语句（包括带事务的操作）以及获取DataTable的方法。`DbHelper`类使用了`DbProviderFactory`和配置文件来获取数据库连接，并提供了参数化查询的支持。

摘要由CSDN通过智能技术生成

using System;

using System.Data;

using System.Data.Common;

using System.Collections;

using System.Configuration;

namespace OA.Data

{

/// ///数据库访问类/// public class DbHelper

{

#region 私有变量

/// ///数据库连接字符串/// protected static string m_connectionstring = null ;

/// ///DbProviderFactory实例/// private static DbProviderFactory m_factory = null;

/// ///Parameters缓存哈希表/// private static Hashtable m_paramcache = Hashtable.Synchronized(new Hashtable());

private static object lockHelper = new object();

#endregion

#region 属性

/// ///数据库连接字符串/// public static string ConnectionString

{

get

{

if (m_connectionstring == null)

{

m_connectionstring =ConfigurationManager.ConnectionStrings["LocalDb"].ConnectionString;

}

return m_connectionstring;

}

set

{

m_connectionstring = value;

}

/// ///DbFactory实例/// public static DbProviderFactory Factory

{

get

{

if (m_factory == null)

{

m_factory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["LocalDb"].ProviderName);

}

return m_factory;

}

#endregion

#region 私有方法

/// ///预处理用户提供的命令,数据库连接/事务/命令类型/参数/// /// 要处理的DbCommand/// 数据库连接/// 一个有效的事务或者是null值/// 命令类型 (存储过程,命令文本, 其它.)/// 存储过程名或都SQL命令文本/// 和命令相关联的DbParameter参数数组,如果没有参数为'null'/// true如果连接是打开的,则为true,其它情况下为false. private static void PrepareCommand(DbCommand cmd, DbConnection connection, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] dbParms, out bool mustCloseConnection)

{

if (cmd == null) throw new ArgumentNullException("DbCommand");

if (cmdText == null || cmdText.Length == 0) throw new ArgumentNullException("CommandType");

//If the provided connection is not open, we will open it if (connection.State != ConnectionState.Open)

{

mustCloseConnection = true;

connection.Open();

}

else

{

mustCloseConnection = false;

}

//给命令分配一个数据库连接. cmd.Connection = connection;

//设置命令文本(存储过程名或SQL语句) cmd.CommandText = cmdText;

//分配事务 if (trans != null)

{

if (trans.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "trans");

cmd.Transaction = trans;

}

//设置命令类型. cmd.CommandType = cmdType;

//分配命令参数 if (dbParms != null)

{

AttachParameters(cmd, dbParms);

}

return;

}

#endregion 私有方法结束

/// ///获取连接对象/// /// /// public static DbConnection GetConn(string key)

{

DbConnection conn = Factory.CreateConnection();

conn.ConnectionString = ConfigurationManager.ConnectionStrings[key].ConnectionString;

conn.Open();

return conn;

}

#region ExecuteNonQuery方法

/// ///执行SQL语句/// /// 命令类型/// SQL语句/// 参数/// public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] dbParms)

{

if (ConnectionString == null || ConnectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

using (DbConnection connection = Factory.CreateConnection())

{

connection.ConnectionString = ConnectionString;

connection.Open();

return ExecuteNonQuery(connection, cmdType, cmdText, dbParms);

}

/// ///执行指定数据库连接对象的命令/// /// 一个有效的数据库连接对象/// 命令类型(存储过程,命令文本或其它.)/// T存储过程名称或SQL语句/// DbParamter参数数组/// 返回影响的行数 public static int ExecuteNonQuery(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] dbParms)

{

if (connection == null) throw new ArgumentNullException("connection");

DbCommand cmd = Factory.CreateCommand();

bool mustCloseConnection = false;

PrepareCommand(cmd, connection, (DbTransaction)null, cmdType, cmdText, dbParms, out mustCloseConnection);

int retval = cmd.ExecuteNonQuery();

//清除参数,以便再次使用. cmd.Parameters.Clear();

if (mustCloseConnection)

connection.Close();

return retval;

}

/// ///带事务执行一组命令/// /// 命令组/// public static int ExecuteNonQuery(DbCommand[] cmds)

{

DbTransaction trans = null;

int i = 0;

try

{

using (DbConnection connection = Factory.CreateConnection())

{

connection.ConnectionString = ConnectionString;

connection.Open();

trans = connection.BeginTransaction();

foreach (DbCommand cmd in cmds)

{

cmd.Transaction = trans;

i = cmd.ExecuteNonQuery();

}

trans.Commit();

return i;

}

catch (DbException err)

{

trans.Rollback();

throw err;

}

#endregion ExecuteNonQuery方法结束

#region GetTable方法

/// ///获取DataTable./// /// 命令类型 (存储过程,命令文本或其它)/// 存储过程名称或SQL语句/// DbParamter参数数组/// 返回一个包含结果集的DataTable public static DataTable GetTable(CommandType cmdType, string cmdText, params DbParameter[] dbParms)

{

if (ConnectionString == null || ConnectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

using (DbConnection connection = Factory.CreateConnection())

{

connection.ConnectionString = ConnectionString;

connection.Open();

//调用指定数据库连接字符串重载方法. return GetTable(connection, cmdType, cmdText, dbParms);

}

/// ///指定数据库连接对象获取DataTable./// /// 一个有效的数据库连接对象/// 命令类型 (存储过程,命令文本或其它)/// 存储过程名或SQL语句/// DbParamter参数数组/// 返回一个包含结果集的DataTable public static DataTable GetTable(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] dbParms)

{

if (connection == null) throw new ArgumentNullException("connection");

//预处理 DbCommand cmd = Factory.CreateCommand();

bool mustCloseConnection = false;

PrepareCommand(cmd, connection, (DbTransaction)null, cmdType, cmdText, dbParms, out mustCloseConnection);

//创建DbDataAdapter和DataTable. using (DbDataAdapter da = Factory.CreateDataAdapter())

{

da.SelectCommand = cmd;

DataTable dt = new DataTable();

//填充DataTable. da.Fill(dt);

cmd.Parameters.Clear();

if (mustCloseConnection)

connection.Close();

return dt;

}

#endregion GetTable

#region ExecuteReader 数据阅读器

/// ///枚举,标识数据库连接是由BaseDbHelper提供还是由调用者提供/// private enum DbConnectionOwnership

{

/// 由BaseDbHelper提供连接 Internal,

/// 由调用者提供连接 External

}

/// ///获取DataReader./// /// 命令类型 (存储过程,命令文本或其它)/// 存储过程名称或SQL语句/// DbParamter参数数组/// 返回一个包含结果集的DataReader public static DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] dbParms)

{

if (ConnectionString == null || ConnectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

DbConnection connection = null;

try

{

connection = Factory.CreateConnection();

connection.ConnectionString = ConnectionString;

connection.Open();

return ExecuteReader(connection, null, cmdType, cmdText, dbParms, DbConnectionOwnership.Internal);

}

catch (DbException err)

{

if (connection != null) connection.Close();

throw err;

}

/// ///指定连接字符串获取DataTable/// /// 连接字符串/// 命令类型/// 存储过程名称或SQL语句/// DbParamter参数数组/// public static DbDataReader ExecuteReader(string connectionString,CommandType cmdType, string cmdText, params DbParameter[] dbParms)

{

if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

DbConnection connection = null;

try

{

connection = Factory.CreateConnection();

connection.ConnectionString = connectionString;

connection.Open();

return ExecuteReader(connection, null, cmdType, cmdText, dbParms, DbConnectionOwnership.Internal);

}

catch (DbException err)

{

//If we fail to return the SqlDatReader, we need to close the connection ourselves if (connection != null) connection.Close();

throw err;

}

/// ///执行指定数据库连接对象的数据DataReader/// /// 一个有效的数据库连接对象/// 一个有效的事务,或者为 'null'/// 命令类型 (存储过程,命令文本或其它)/// 存储过程名或SQL语句/// DbParameters参数数组,如果没有参数则为'null'/// 标识数据库连接对象是由调用者提供还是由BaseDbHelper提供/// 返回包含结果集的DbDataReader private static DbDataReader ExecuteReader(DbConnection connection, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] dbParms, DbConnectionOwnership connectionOwnership)

{

if (connection == null) throw new ArgumentNullException("connection");

bool mustCloseConnection = false;

//创建命令 DbCommand cmd = Factory.CreateCommand();

try

{

PrepareCommand(cmd, connection, trans, cmdType, cmdText, dbParms, out mustCloseConnection);

//创建数据阅读器 DbDataReader dataReader;

if (connectionOwnership == DbConnectionOwnership.External)

{

dataReader = cmd.ExecuteReader();

}

else

{

dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

}

bool canClear = true;

foreach (DbParameter dbParm in cmd.Parameters)

{

if (dbParm.Direction != ParameterDirection.Input)

canClear = false;

}

if (canClear)

{

cmd.Parameters.Clear();

}

return dataReader;

}

catch

{

if (mustCloseConnection)

connection.Close();

throw;

}

/// ///准备输入参数./// /// 参数名称/// 参数数据类型/// 参数大小/// 参数值/// 输入参数 public static DbParameter MakeInParam(string paramName, DbType dbType, int size, object inValue)

{

return MakeParam(paramName, dbType, size, ParameterDirection.Input, inValue);

}

/// ///准备输入参数./// /// 参数名称/// 参数数据类型/// 参数值/// 输入参数 public static DbParameter MakeInParam(string paramName, DbType dbType, object inValue)

{

return MakeParam(paramName, dbType, 0, ParameterDirection.Input, inValue);

}

/// ///准备输出参数./// /// 参数名称/// 参数数据类型/// 参数大小/// 输出参数 public static DbParameter MakeOutParam(string paramName, DbType dbType, int size)

{

return MakeParam(paramName, dbType, size, ParameterDirection.Output, null);

}

/// ///准备存储过程的参数./// /// 参数名称/// 参数数据类型/// 参数大小/// 参数方向/// 参数值/// 输入或输出参数 public static DbParameter MakeParam(string paramName, DbType dbType, int size, ParameterDirection direction, object inValue)

{

DbParameter param = Factory.CreateParameter();

param.ParameterName = paramName;

if (size > 0)

{

param.DbType = dbType;

param.Size = size;

}

else

param.DbType = dbType;

param.Direction = direction;

if (!(direction == ParameterDirection.Output))