2023年6月21日发(作者：)

.NET通⽤的数据库操作类（⽀持多种数据库）通⽤的操作类(⽀持多种数据库) 我们在软件开发过程中,数据库是⼀个永久不变的话题,但是根据软件与客户要求的不同我们会选择不同的数据库,在C#中不同数据库操作编写代码不尽相同,下⾯提供⼀种通⽤的数据库操作⽅案,只需要根据config的配置就可以动态的选择不同的数据库.在配置⽂件中providerName指定不同的数据库类型. 代码如下:

using System;using tions;using lized;using ;using uration;using ;namespace DataBase{ ///

/// 数据访问基础类 /// class DataHelper { protected static string connectionString = tionStrings["ConnectionString"].ConnectionString; DbProviderFactory provider; public DataHelper() { provider = tory(tionStrings["ConnectionString"].ProviderName); } #region 执⾏简单SQL语句 /// /// 执⾏SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public int ExecuteSql(string SQLString) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { tion = connection; dText = SQLString; try { { (); int rows = eNonQuery(); return rows; } catch (DbException E) { (); e(); throw new Exception(e); } } } } /// /// 执⾏多条SQL语句，实现数据库事务。 /// /// 多条SQL语句

public void ExecuteSqlTran(ArrayList SQLStringList) { using (DbConnection conn = Connection()) { tionString = connectionString; (); using (DbCommand cmd = Command()) { tion = conn; using (DbTransaction tx = ransaction()) { ction = tx; try { for (int n = 0; n < ; n++) { string strsql = SQLStringList[n].ToString(); if (().Length > 1) { dText = strsql; eNonQuery(); } } (); } catch (DbException ex) { ck(); (); e(); throw ex; } } } } } ///

/// /// 执⾏⼀条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） public object GetSingle(string SQLString) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { tion = connection; dText = SQLString; try { (); object obj = eScalar(); if (((obj, null)) || ((obj, ))) { return null; } else { return obj; } } catch (DbException e) { (); e(); throw new Exception(e); } } } } /// /// 执⾏查询语句，返回SqlDataReader /// /// 查询语句 /// SqlDataReader public DbDataReader ExecuteReader(string strSQL) { DbConnection connection = Connection(); tionString = connectionString; DbCommand cmd = Command(); tion = connection; dText = strSQL; try { (); DbDataReader myReader = eReader(onnection); return myReader; } catch (ption e) { { (); e(); throw new Exception(e); } } /// /// 执⾏查询语句，返回DataSet /// /// 查询语句 /// DataSet public DataSet GetDataSet(string SQLString) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { tion = connection; dText = SQLString; try { DataSet ds = new DataSet(); DbDataAdapter adapter = DataAdapter(); Command = cmd; (ds, "ds"); return ds; } catch (DbException ex) { (); e(); throw new Exception(e); } } } } #endregion #region 执⾏带参数的SQL语句 /// /// 执⾏SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public int ExecuteSql(string SQLString, DbParameter[] cmdParms) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { tion = connection; dText = SQLString; dText = SQLString; try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = eNonQuery(); (); return rows; } catch (DbException E) { (); e(); throw new Exception(e); } } } } /// /// 执⾏多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public void ExecuteSqlTran(Hashtable SQLStringList) { using (DbConnection conn = Connection()) { tionString = connectionString; (); using (DbTransaction trans = ransaction()) { using (DbCommand cmd = Command()) { try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = ng(); DbParameter[] cmdParms = (DbParameter[]); PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = eNonQuery(); (); } (); } catch (DbException ex) { ck(); (); e(); throw ex; } } } } } } /// /// 执⾏⼀条计算查询结果语句，返回查询结果（object）,返回⾸⾏⾸列的值; /// /// 计算查询结果语句 /// 查询结果（object） public object GetSingle(string SQLString, DbParameter[] cmdParms) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = eScalar(); (); if (((obj, null)) || ((obj, ))) { return null; } else { return obj; } } catch (DbException e) { (); e(); throw new Exception(e); } } } } /// /// 执⾏查询语句，返回SqlDataReader /// /// 查询语句 /// SqlDataReader public DbDataReader ExecuteReader(string SQLString, DbParameter[] cmdParms) { DbConnection connection = Connection(); tionString = connectionString; DbCommand cmd = Command(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); DbDataReader myReader = eReader(onnection); (); return myReader; } catch (DbException e) catch (DbException e) { (); e(); throw new Exception(e); } } /// /// 执⾏查询语句，返回DataSet /// /// 查询语句 /// DataSet public DataSet GetDataSet(string SQLString, DbParameter[] cmdParms) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { using (DbDataAdapter da = DataAdapter()) { PrepareCommand(cmd, connection, null, SQLString, cmdParms); Command = cmd; DataSet ds = new DataSet(); try { (ds, "ds"); (); return ds; } catch (DbException ex) { (); e(); throw new Exception(e); } } } } } private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cm { if ( != ) { (); } tion = conn; dText = cmdText; if (trans != null) { ction = trans; } dType = ;//cmdType; if (cmdParms != null) { foreach (DbParameter parm in cmdParms) { (parm); } } } #endregion #region 存储过程操作 /// /// 执⾏存储过程; /// /// 存储过程名 /// 所需要的参数 /// 返回受影响的⾏数 public int RunProcedureExecuteSql(string storeProcName, DbParameter[] parameters) { using (DbConnection connection = Connection()) { tionString = connectionString; DbCommand cmd = BuildQueryCommand(connection, storeProcName, parameters); int rows = eNonQuery(); (); (); return rows; } } /// /// 执⾏存储过程,返回⾸⾏⾸列的值 /// /// 存储过程名 /// 存储过程参数 /// 返回⾸⾏⾸列的值 public Object RunProcedureGetSingle(string storeProcName, DbParameter[] parameters) { using (DbConnection connection = Connection()) { tionString = connectionString; try { DbCommand cmd = BuildQueryCommand(connection, storeProcName, parameters); object obj = eScalar(); (); if (((obj, null)) || ((obj, ))) { return null; } else { return obj; } } catch (DbException e) { (); e(); throw new Exception(e); } } } /// /// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// SqlDataReader public DbDataReader RunProcedureGetDataReader(string storedProcName, DbParameter[] parameters) { DbConnection connection = Connection(); tionString = connectionString; DbDataReader returnReader; DbCommand cmd = BuildQueryCommand(connection, storedProcName, parameters); dType = Procedure; returnReader = eReader(onnection); (); return returnReader; } /// /// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataSet public DataSet RunProcedureGetDataSet(string storedProcName, DbParameter[] parameters) { using (DbConnection connection = Connection()) { tionString = connectionString; DataSet dataSet = new DataSet(); DbDataAdapter sqlDA = DataAdapter(); Command = BuildQueryCommand(connection, storedProcName, parameters); (dataSet); (); e(); return dataSet; } } /// /// 执⾏多个存储过程，实现数据库事务。 /// /// 存储过程的哈希表（value为存储过程语句，key是该语句的DbParameter[]） public bool RunProcedureTran(Hashtable SQLStringList) { using (DbConnection connection = Connection()) { tionString = connectionString; (); using (DbTransaction trans = ransaction()) { using (DbCommand cmd = Command()) { try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { tion = connection; string storeName = ng(); DbParameter[] cmdParms = (DbParameter[]); ction = trans; dText = storeName; dType = Procedure; if (cmdParms != null) { foreach (DbParameter parameter in cmdParms) { (parameter); } } int val = eNonQuery(); (); } (); return true; } catch { ck(); (); e();

return false; } } } } } ///

/// 执⾏多个存储过程，实现数据库事务。 /// /// 存储过程的哈希表（value为存储过程语句，key是该语句的DbParameter[]） public bool RunProcedureTran(C_HashTable SQLStringList) { using (DbConnection connection = Connection()) { tionString = connectionString; (); using (DbTransaction trans = ransaction()) { using (DbCommand cmd = Command()) { try { //循环 foreach (DbParameter[] cmdParms in ) { tion = connection; string storeName = SQLStringList[cmdParms].ToString(); ction = trans; dText = storeName; dType = Procedure; if (cmdParms != null) { foreach (DbParameter parameter in cmdParms) { (parameter); } } int val = eNonQuery(); (); } (); return true; } catch { ck(); (); e(); return false;

} } } } } ///

/// 构建 SqlCommand 对象(⽤来返回⼀个结果集，⽽不是⼀个整数值) /// /// 数据库连接 /// 存储过程名 /// 存储过程参数 /// SqlCommand private DbCommand BuildQueryCommand(DbConnection connection, string storedProcName, DbParameter[] parameters) { if ( != ) { (); } DbCommand command = Command(); dText = storedProcName; tion = connection; dType = Procedure; if (parameters != null) { foreach (DbParameter parameter in parameters) { (parameter); } } return command; } #endregion }}

2023年6月21日发(作者：)

.NET通⽤的数据库操作类（⽀持多种数据库）通⽤的操作类(⽀持多种数据库) 我们在软件开发过程中,数据库是⼀个永久不变的话题,但是根据软件与客户要求的不同我们会选择不同的数据库,在C#中不同数据库操作编写代码不尽相同,下⾯提供⼀种通⽤的数据库操作⽅案,只需要根据config的配置就可以动态的选择不同的数据库.在配置⽂件中providerName指定不同的数据库类型. 代码如下:

using System;using tions;using lized;using ;using uration;using ;namespace DataBase{ ///

/// 数据访问基础类 /// class DataHelper { protected static string connectionString = tionStrings["ConnectionString"].ConnectionString; DbProviderFactory provider; public DataHelper() { provider = tory(tionStrings["ConnectionString"].ProviderName); } #region 执⾏简单SQL语句 /// /// 执⾏SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public int ExecuteSql(string SQLString) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { tion = connection; dText = SQLString; try { { (); int rows = eNonQuery(); return rows; } catch (DbException E) { (); e(); throw new Exception(e); } } } } /// /// 执⾏多条SQL语句，实现数据库事务。 /// /// 多条SQL语句

public void ExecuteSqlTran(ArrayList SQLStringList) { using (DbConnection conn = Connection()) { tionString = connectionString; (); using (DbCommand cmd = Command()) { tion = conn; using (DbTransaction tx = ransaction()) { ction = tx; try { for (int n = 0; n < ; n++) { string strsql = SQLStringList[n].ToString(); if (().Length > 1) { dText = strsql; eNonQuery(); } } (); } catch (DbException ex) { ck(); (); e(); throw ex; } } } } } ///

/// /// 执⾏⼀条计算查询结果语句，返回查询结果（object）。 /// /// 计算查询结果语句 /// 查询结果（object） public object GetSingle(string SQLString) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { tion = connection; dText = SQLString; try { (); object obj = eScalar(); if (((obj, null)) || ((obj, ))) { return null; } else { return obj; } } catch (DbException e) { (); e(); throw new Exception(e); } } } } /// /// 执⾏查询语句，返回SqlDataReader /// /// 查询语句 /// SqlDataReader public DbDataReader ExecuteReader(string strSQL) { DbConnection connection = Connection(); tionString = connectionString; DbCommand cmd = Command(); tion = connection; dText = strSQL; try { (); DbDataReader myReader = eReader(onnection); return myReader; } catch (ption e) { { (); e(); throw new Exception(e); } } /// /// 执⾏查询语句，返回DataSet /// /// 查询语句 /// DataSet public DataSet GetDataSet(string SQLString) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { tion = connection; dText = SQLString; try { DataSet ds = new DataSet(); DbDataAdapter adapter = DataAdapter(); Command = cmd; (ds, "ds"); return ds; } catch (DbException ex) { (); e(); throw new Exception(e); } } } } #endregion #region 执⾏带参数的SQL语句 /// /// 执⾏SQL语句，返回影响的记录数 /// /// SQL语句 /// 影响的记录数 public int ExecuteSql(string SQLString, DbParameter[] cmdParms) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { tion = connection; dText = SQLString; dText = SQLString; try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = eNonQuery(); (); return rows; } catch (DbException E) { (); e(); throw new Exception(e); } } } } /// /// 执⾏多条SQL语句，实现数据库事务。 /// /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） public void ExecuteSqlTran(Hashtable SQLStringList) { using (DbConnection conn = Connection()) { tionString = connectionString; (); using (DbTransaction trans = ransaction()) { using (DbCommand cmd = Command()) { try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = ng(); DbParameter[] cmdParms = (DbParameter[]); PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = eNonQuery(); (); } (); } catch (DbException ex) { ck(); (); e(); throw ex; } } } } } } /// /// 执⾏⼀条计算查询结果语句，返回查询结果（object）,返回⾸⾏⾸列的值; /// /// 计算查询结果语句 /// 查询结果（object） public object GetSingle(string SQLString, DbParameter[] cmdParms) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); object obj = eScalar(); (); if (((obj, null)) || ((obj, ))) { return null; } else { return obj; } } catch (DbException e) { (); e(); throw new Exception(e); } } } } /// /// 执⾏查询语句，返回SqlDataReader /// /// 查询语句 /// SqlDataReader public DbDataReader ExecuteReader(string SQLString, DbParameter[] cmdParms) { DbConnection connection = Connection(); tionString = connectionString; DbCommand cmd = Command(); try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); DbDataReader myReader = eReader(onnection); (); return myReader; } catch (DbException e) catch (DbException e) { (); e(); throw new Exception(e); } } /// /// 执⾏查询语句，返回DataSet /// /// 查询语句 /// DataSet public DataSet GetDataSet(string SQLString, DbParameter[] cmdParms) { using (DbConnection connection = Connection()) { tionString = connectionString; using (DbCommand cmd = Command()) { using (DbDataAdapter da = DataAdapter()) { PrepareCommand(cmd, connection, null, SQLString, cmdParms); Command = cmd; DataSet ds = new DataSet(); try { (ds, "ds"); (); return ds; } catch (DbException ex) { (); e(); throw new Exception(e); } } } } } private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cm { if ( != ) { (); } tion = conn; dText = cmdText; if (trans != null) { ction = trans; } dType = ;//cmdType; if (cmdParms != null) { foreach (DbParameter parm in cmdParms) { (parm); } } } #endregion #region 存储过程操作 /// /// 执⾏存储过程; /// /// 存储过程名 /// 所需要的参数 /// 返回受影响的⾏数 public int RunProcedureExecuteSql(string storeProcName, DbParameter[] parameters) { using (DbConnection connection = Connection()) { tionString = connectionString; DbCommand cmd = BuildQueryCommand(connection, storeProcName, parameters); int rows = eNonQuery(); (); (); return rows; } } /// /// 执⾏存储过程,返回⾸⾏⾸列的值 /// /// 存储过程名 /// 存储过程参数 /// 返回⾸⾏⾸列的值 public Object RunProcedureGetSingle(string storeProcName, DbParameter[] parameters) { using (DbConnection connection = Connection()) { tionString = connectionString; try { DbCommand cmd = BuildQueryCommand(connection, storeProcName, parameters); object obj = eScalar(); (); if (((obj, null)) || ((obj, ))) { return null; } else { return obj; } } catch (DbException e) { (); e(); throw new Exception(e); } } } /// /// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// SqlDataReader public DbDataReader RunProcedureGetDataReader(string storedProcName, DbParameter[] parameters) { DbConnection connection = Connection(); tionString = connectionString; DbDataReader returnReader; DbCommand cmd = BuildQueryCommand(connection, storedProcName, parameters); dType = Procedure; returnReader = eReader(onnection); (); return returnReader; } /// /// 执⾏存储过程 /// /// 存储过程名 /// 存储过程参数 /// DataSet public DataSet RunProcedureGetDataSet(string storedProcName, DbParameter[] parameters) { using (DbConnection connection = Connection()) { tionString = connectionString; DataSet dataSet = new DataSet(); DbDataAdapter sqlDA = DataAdapter(); Command = BuildQueryCommand(connection, storedProcName, parameters); (dataSet); (); e(); return dataSet; } } /// /// 执⾏多个存储过程，实现数据库事务。 /// /// 存储过程的哈希表（value为存储过程语句，key是该语句的DbParameter[]） public bool RunProcedureTran(Hashtable SQLStringList) { using (DbConnection connection = Connection()) { tionString = connectionString; (); using (DbTransaction trans = ransaction()) { using (DbCommand cmd = Command()) { try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { tion = connection; string storeName = ng(); DbParameter[] cmdParms = (DbParameter[]); ction = trans; dText = storeName; dType = Procedure; if (cmdParms != null) { foreach (DbParameter parameter in cmdParms) { (parameter); } } int val = eNonQuery(); (); } (); return true; } catch { ck(); (); e();

return false; } } } } } ///

/// 执⾏多个存储过程，实现数据库事务。 /// /// 存储过程的哈希表（value为存储过程语句，key是该语句的DbParameter[]） public bool RunProcedureTran(C_HashTable SQLStringList) { using (DbConnection connection = Connection()) { tionString = connectionString; (); using (DbTransaction trans = ransaction()) { using (DbCommand cmd = Command()) { try { //循环 foreach (DbParameter[] cmdParms in ) { tion = connection; string storeName = SQLStringList[cmdParms].ToString(); ction = trans; dText = storeName; dType = Procedure; if (cmdParms != null) { foreach (DbParameter parameter in cmdParms) { (parameter); } } int val = eNonQuery(); (); } (); return true; } catch { ck(); (); e(); return false;

} } } } } ///

/// 构建 SqlCommand 对象(⽤来返回⼀个结果集，⽽不是⼀个整数值) /// /// 数据库连接 /// 存储过程名 /// 存储过程参数 /// SqlCommand private DbCommand BuildQueryCommand(DbConnection connection, string storedProcName, DbParameter[] parameters) { if ( != ) { (); } DbCommand command = Command(); dText = storedProcName; tion = connection; dType = Procedure; if (parameters != null) { foreach (DbParameter parameter in parameters) { (parameter); } } return command; } #endregion }}