C# 数据库访问公共类
Web.config配置
<appSettings> <!--数据连接字符串--> <add key="DbProviderType" value="Oracle" /> <add key="ConnectionString" value="Data Source=(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.88)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=mestest))); User Id=hmcs;Password=hmcstest;"/> </appSettings> <system.data> <DbProviderFactories> <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342"/> <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.58.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"/> <add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSQL Database" type="Npgsql.NpgsqlFactory, Npgsql, Version=1.98.4.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/> </DbProviderFactories> </system.data>
数据库公共类调用方法
DataTable dt = DbUtility.DbUtility.GetInstance().ExecuteDataTable(sql);
数据库公共类
using System; using System.Data.Common; using System.Data; using System.Collections; using System.Collections.Generic; using System.Configuration; namespace DbUtility { /// <summary> /// 单例模式实例化数据库操作辅助类 /// </summary> public class DbUtility { #region 私有字段 private static readonly string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; private static readonly string strDbProviderType = ConfigurationManager.AppSettings["DbProviderType"]; private readonly DbProviderFactory providerFactory; #endregion #region 公共字段 /// <summary> /// 获取 Web.config文件中配置的 MSSQL 连接的字符串 /// </summary> public string ConnectionString { get; private set; } #endregion #region 创建通用数据库操作类的单一实例 static volatile DbUtility instance = null; static readonly object padlock = new object(); /// <summary> /// 单例模式实例化数据库操作通用类 /// </summary> private DbUtility() { ConnectionString = connectionString; providerFactory = ProviderFactory.GetDbProviderFactory(DbProviderType.SqlServer); if (providerFactory == null) { throw new ArgumentException("Can't load DbProviderFactory for given value of providerType"); } } /// <summary> /// 单例模式实例化数据库操作通用类 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="providerType">数据库类型枚举</param> private DbUtility(string connectionString, DbProviderType providerType) { ConnectionString = connectionString; providerFactory = ProviderFactory.GetDbProviderFactory(providerType); if (providerFactory == null) { throw new ArgumentException("Can't load DbProviderFactory for given value of providerType"); } } /// <summary> /// 创建MSSQL数据库操作通用类的单一实例 /// </summary> /// <returns>获取<see cref="LiFeiLin.DBUtility.DbUtility"/>的实例</returns> public static DbUtility GetInstance() { // return GetInstance(connectionString, DbProviderType.SqlServer); return GetInstance(connectionString,(DbProviderType)Enum.Parse(typeof(DbProviderType), strDbProviderType)); } /// <summary> /// 创建通用数据库操作类的单一实例 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="providerType">数据库类型枚举</param> /// <returns>获取<see cref="LiFeiLin.DBUtility.DbUtility"/>的实例</returns> public static DbUtility GetInstance(string connectionString, DbProviderType providerType) { if (instance == null) { lock (padlock) { if (instance == null) { instance = new DbUtility(connectionString, providerType); } } } return instance; } #endregion #region 获取某个表的记录数量 /// <summary> /// 获取某个表的记录数量 /// </summary> /// <param name="tableName"></param> /// <returns></returns> public int GetDataRecordCount(string tableName) { return GetDataRecordCount(tableName); } /// <summary> /// 获取某个表的记录数量 /// </summary> /// <param name="tableName">表名</param> /// <param name="where">条件</param> /// <returns></returns> public int GetDataRecordCount(string tableName, string where) { string strsql = "select count(1) from " + tableName; if (where != "") { strsql += " where " + where; } object obj = ExecuteScalar(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } #endregion #region 获取指定表中指定列的最大值 /// <summary> /// 获取指定表中指定列的最大值 /// </summary> /// <param name="fieldName">字段名</param> /// <param name="tableName">表名</param> /// <returns></returns> public int GetMaxID(string fieldName, string tableName) { string strsql = "select max(" + fieldName + ")+1 from " + tableName; object obj = ExecuteScalar(strsql); if (obj == null) { return 1; } else { return int.Parse(obj.ToString()); } } #endregion #region 执行一个查询 SQL 语句,并根据返回值判断执行结果是否存在 /// <summary> /// 执行一个查询 SQL 语句,并根据返回值判断执行结果是否存在 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public bool Exists(string strSql) { object obj = ExecuteScalar(strSql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 执行一个查询 SQL 语句,并根据返回值判断执行结果是否存在 /// </summary> /// <param name="strSql">需要执行的 SQL 查询语句</param> /// <param name="cmdParms">结构化参数数组</param> /// <returns></returns> public bool Exists(string strSql, params DbParameter[] cmdParms) { object obj = ExecuteScalar(strSql, cmdParms); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 表是否存在 /// </summary> /// <param name="tableName">表名</param> /// <returns></returns> public bool TabExists(string tableName) { string strsql = "select count(*) from sysobjects where id = object_id(N'[" + tableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"; object obj = ExecuteScalar(strsql); int cmdresult; if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { cmdresult = 0; } else { cmdresult = int.Parse(obj.ToString()); } if (cmdresult == 0) { return false; } else { return true; } } #endregion #region 执行多条SQL语句,实现数据库事务 /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="sqlStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param> public void ExecuteSqlTran(Hashtable sqlStringList) { using (DbConnection connection = providerFactory.CreateConnection()) { connection.Open(); using (DbTransaction trans = connection.BeginTransaction()) { DbCommand command = null; try { foreach (DictionaryEntry myDE in sqlStringList) { string cmdText = myDE.Key.ToString(); DbParameter[] cmdParms = (DbParameter[])myDE.Value; CreateDbCommand(command, connection, trans, cmdText, cmdParms); command.ExecuteNonQuery(); command.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } } #endregion #region 执行一个查询语句,返回一个关联的DataReader实例 /// <summary> /// 执行一个查询语句,返回一个关联的DataReader实例 /// </summary> /// <param name="sqlString">要执行的查询语句</param> /// <returns></returns> public DbDataReader ExecuteReader(string sqlString) { return ExecuteReader(sqlString, CommandType.Text, 0, null); } /// <summary> /// 执行一个查询语句,返回一个关联的DataReader实例 /// </summary> /// <param name="sqlString">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数</param> /// <returns></returns> public DbDataReader ExecuteReader(string sqlString, DbParameter[] parameters) { return ExecuteReader(sqlString, CommandType.Text, 0, parameters); } /// <summary> /// 执行一个查询语句,返回一个关联的DataReader实例 /// </summary> /// <param name="sqlString">要执行的查询语句</param> /// <param name="commandType">要执行查询语句的类型,如存储过程或者SQl文本命令</param> /// <returns></returns> public DbDataReader ExecuteReader(string sqlString, CommandType commandType) { return ExecuteReader(sqlString, commandType, 0, null); } /// <summary> /// 执行一个查询语句,返回一个关联的DataReader实例 /// </summary> /// <param name="sqlString">要执行的查询语句</param> /// <param name="times">超时时间</param> /// <param name="parameters">执行SQL查询语句所需要的参数</param> /// <returns>SqlDataReader</returns> public DbDataReader ExecuteReader(string sqlString, int times, params DbParameter[] parameters) { return ExecuteReader(sqlString, CommandType.Text, times, parameters); } /// <summary> /// 执行一个查询语句,返回一个关联的DataReader实例 /// </summary> /// <param name="sqlString">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数</param> /// <param name="commandType">执行的SQL语句的类型</param> /// <param name="times">超时时间</param> /// <returns></returns> public DbDataReader ExecuteReader(string sqlString, CommandType commandType, int times, DbParameter[] parameters) { DbCommand command = CreateDbCommand(sqlString, commandType, times, parameters); command.Connection.Open(); return command.ExecuteReader(CommandBehavior.CloseConnection); } #endregion #region 执行存储过程 /// <summary> /// 执行存储过程 (使用该方法切记要手工关闭SqlDataReader和连接) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlDataReader</returns> public DbDataReader RunProcedure(string storedProcName, IDbDataParameter[] parameters) { DbConnection connection = providerFactory.CreateConnection(); connection.ConnectionString = ConnectionString; DbDataReader returnReader; connection.Open(); DbCommand command = CreateDbQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(); //Connection.Close(); 不能在此关闭,否则,返回的对象将无法使用 return returnReader; } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <returns>DataSet</returns> public DataSet RunProcedure(string storedProcName, IDbDataParameter[] parameters, string tableName) { using (DbConnection connection = providerFactory.CreateConnection()) { DataSet dataSet = new DataSet(); connection.ConnectionString = ConnectionString; connection.Open(); DbDataAdapter sqlDA = providerFactory.CreateDataAdapter(); sqlDA.SelectCommand = CreateDbQueryCommand(connection, storedProcName, parameters); sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="tableName">DataSet结果中的表名</param> /// <param name="times">储存过程执行超时时间</param> /// <returns>DataSet</returns> public DataSet RunProcedure(string storedProcName, IDbDataParameter[] parameters, string tableName, int times) { using (DbConnection connection = providerFactory.CreateConnection()) { connection.ConnectionString = ConnectionString; DataSet dataSet = new DataSet(); connection.Open(); DbDataAdapter sqlDA = providerFactory.CreateDataAdapter(); sqlDA.SelectCommand = CreateDbQueryCommand(connection, storedProcName, parameters); sqlDA.SelectCommand.CommandTimeout = times; sqlDA.Fill(dataSet, tableName); connection.Close(); return dataSet; } } /// <summary> /// 执行存储过程后返回执行结果(标识) /// </summary> /// <param name="storedProcName">储存过程名称</param> /// <param name="parameters">储存过程参数</param> /// <returns></returns> public string RunProcedureState(string storedProcName, IDbDataParameter[] parameters) { using (DbConnection connection = providerFactory.CreateConnection()) { connection.ConnectionString = ConnectionString; connection.Open(); DbDataAdapter sqlDA = providerFactory.CreateDataAdapter(); DbParameter parameter = providerFactory.CreateParameter(); parameter.ParameterName = "ReturnValue"; parameter.DbType = DbType.Int32; parameter.Size = 4; parameter.Direction = ParameterDirection.ReturnValue; parameter.SourceColumnNullMapping = false; parameter.SourceColumn = string.Empty; parameter.SourceVersion = DataRowVersion.Default; parameter.Value = null; sqlDA.SelectCommand = CreateDbQueryCommand(connection, storedProcName, parameters); sqlDA.SelectCommand.Parameters.Add(parameter); //增加存储过程的返回值参数 sqlDA.SelectCommand.ExecuteNonQuery(); connection.Close(); return sqlDA.SelectCommand.Parameters["ReturnValue"].Value.ToString(); } } /// <summary> /// 执行存储过程,返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public int RunProcedure(string storedProcName, IDbDataParameter[] parameters, out int rowsAffected) { using (DbConnection connection = providerFactory.CreateConnection()) { connection.ConnectionString = ConnectionString; int result; connection.Open(); DbCommand command = CreateDbCommand(connection, storedProcName, parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; return result; } } #endregion #region 执行查询语句并返回DataSet /// <summary> /// 执行查询语句并返回 <see cref="System.Data.DataSet"/> 对象 /// </summary> /// <param name="sqlString">查询语句</param> /// <returns>DataSet</returns> public DataSet ExecuteQuery(string sqlString) { return ExecuteQuery(sqlString, CommandType.Text, 0, null); } /// <summary> ///执行查询语句并返回 <see cref="System.Data.DataSet"/> 对象 /// </summary> /// <param name="sqlString"></param> /// <param name="times"></param> /// <returns></returns> public DataSet ExecuteQuery(string sqlString, int times) { return ExecuteQuery(sqlString, CommandType.Text, times, null); } /// <summary> /// 执行查询语句并返回 <see cref="System.Data.DataSet"/> 对象 /// </summary> /// <param name="sqlString">查询语句</param> /// <param name="cmdParms">结构化参数</param> /// <returns>DataSet</returns> public DataSet ExecuteQuery(string sqlString, DbParameter[] parameters) { return ExecuteQuery(sqlString, CommandType.Text, 0, parameters); } /// <summary> /// 执行查询语句并返回 <see cref="System.Data.DataSet"/> 对象 /// </summary> /// <param name="sqlString"></param> /// <param name="times"></param> /// <param name="cmdParms"></param> /// <returns></returns> public DataSet ExecuteQuery(string sqlString, CommandType commandType, int times, DbParameter[] parameters) { using (DbCommand command = CreateDbCommand(sqlString, commandType, times, parameters)) { command.Connection.Open(); using (DbDataAdapter da = providerFactory.CreateDataAdapter()) { da.SelectCommand = command; DataSet ds = new DataSet(); da.Fill(ds, "ds"); command.Parameters.Clear(); command.Connection.Close(); return ds; } } } #endregion #region 对数据库执行增删改操作,返回受影响的行数。 /// <summary> /// 对数据库执行增删改操作,返回受影响的行数。 /// </summary> /// <param name="sqlString">要执行的sql命令</param> /// <returns></returns> public int ExecuteNonQuery(string sqlString) { return ExecuteNonQuery(sqlString, CommandType.Text, 0, null); } /// <summary> /// 对数据库执行增删改操作,返回受影响的行数。 /// </summary> /// <param name="sqlString">要执行的增删改的SQL语句</param> /// <param name="times">超时时间</param> /// <returns></returns> public int ExecuteNonQuery(string sqlString, int times) { return ExecuteNonQuery(sqlString, CommandType.Text, times, null); } /// <summary> /// 对数据库执行增删改操作,返回受影响的行数。 /// </summary> /// <param name="sqlString">要执行的增删改的SQL语句</param> /// <param name="parameters">结构化的参数列表</param> /// <returns></returns> public int ExecuteNonQuery(string sqlString, DbParameter[] parameters) { return ExecuteNonQuery(sqlString, CommandType.Text, 0, parameters); } /// <summary> /// 对数据库执行增删改操作,返回受影响的行数。 /// </summary> /// <param name="sqlString">要执行的增删改的SQL语句</param> /// <param name="commandType">要执行查询语句的类型,如存储过程或者sql文本命令</param> /// <returns></returns> public int ExecuteNonQuery(string sqlString, CommandType commandType) { return ExecuteNonQuery(sqlString, commandType, 0, null); } /// <summary> /// 对数据库执行增删改操作,返回受影响的行数。 /// </summary> /// <param name="sqlString">要执行的增删改的SQL语句</param> /// <param name="commandType">执行的SQL语句的类型</param> /// <param name="times">超时时间</param> /// <param name="parameters">执行增删改语句所需要的参数</param> /// <returns></returns> public int ExecuteNonQuery(string sqlString, CommandType commandType, int times, DbParameter[] parameters) { using (DbCommand command = CreateDbCommand(sqlString, commandType, times, parameters)) { command.Connection.Open(); int affectedRows = command.ExecuteNonQuery(); command.Connection.Close(); return affectedRows; } } #endregion #region 执行一个查询,返回结果集的首行首列。忽略其他行,其他列 /// <summary> /// 执行一个查询,返回结果集的首行首列。忽略其他行,其他列 /// </summary> /// <param name="sqlString">要执行的SQl命令</param> /// <returns></returns> public Object ExecuteScalar(string sqlString) { return ExecuteScalar(sqlString, CommandType.Text, 0, null); } /// <summary> /// 执行一个查询,返回结果集的首行首列。忽略其他行,其他列 /// </summary> /// <param name="sqlString">要执行的SQl命令</param> /// <param name="times">执行超时时间</param> /// <returns></returns> public Object ExecuteScalar(string sqlString, int times) { return ExecuteScalar(sqlString, CommandType.Text, times, null); } /// <summary> /// 执行一个查询,返回结果集的首行首列。忽略其他行,其他列 /// </summary> /// <param name="sqlString">要执行的SQl命令</param> /// <param name="cmdParms">结构化的查询语句</param> /// <returns></returns> public object ExecuteScalar(string sqlString, params DbParameter[] cmdParms) { return ExecuteScalar(sqlString, CommandType.Text, 0, cmdParms); } /// <summary> /// 执行一个查询语句,返回查询结果的第一行第一列 /// </summary> /// <param name="sql">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数</param> /// <param name="commandType">执行的SQL语句的类型</param> /// <returns></returns> public Object ExecuteScalar(string sqlString, CommandType commandType, int times, DbParameter[] parameters) { using (DbCommand command = CreateDbCommand(sqlString, commandType, times, parameters)) { command.Connection.Open(); object result = command.ExecuteScalar(); command.Connection.Close(); return result; } } #endregion #region 执行一个查询语句,返回一个包含查询结果的DataTable /// <summary> /// 执行一个查询语句,返回一个包含查询结果的DataTable /// </summary> /// <param name="sql">要执行的sql文本命令</param> /// <returns>返回查询的结果集</returns> public DataTable ExecuteDataTable(string sql) { return ExecuteDataTable(sql, CommandType.Text, 0, null); } /// <summary> /// 执行一个查询语句,返回一个包含查询结果的DataTable /// </summary> /// <param name="sqlString">要执行的sql文本命令</param> /// <param name="parameters">执行SQL查询语句所需要的参数</param> /// <returns></returns> public DataTable ExecuteDataTable(string sqlString, DbParameter[] parameters) { return ExecuteDataTable(sqlString, CommandType.Text, 0, parameters); } /// <summary> /// 执行一个查询语句,返回一个包含查询结果的DataTable /// </summary> /// <param name="sqlString">要执行的sql语句</param> /// <param name="commandType">要执行的查询语句的类型,如存储过程或者sql文本命令</param> /// <returns>返回查询结果集</returns> public DataTable ExecuteDataTable(string sqlString, CommandType commandType) { return ExecuteDataTable(sqlString, commandType, 0, null); } /// <summary> /// 执行一个查询语句,返回一个包含查询结果的DataTable /// </summary> /// <param name="sqlString">要执行的sql语句</param> /// <param name="commandType">要执行的查询语句的类型</param> /// <param name="times">超时时间</param> /// <returns></returns> public DataTable ExecuteDataTable(string sqlString, CommandType commandType, int times) { return ExecuteDataTable(sqlString, commandType, times, null); } /// <summary> /// 执行一个查询语句,返回一个包含查询结果的DataTable /// </summary> /// <param name="sqlString">要执行的查询语句</param> /// <param name="times">超时时间</param> /// <param name="commandType">执行的SQL语句的类型</param> /// <param name="parameters">执行SQL查询语句所需要的参数</param> /// <returns></returns> public DataTable ExecuteDataTable(string sqlString, CommandType commandType, int times, DbParameter[] parameters) { using (DbCommand command = CreateDbCommand(sqlString, commandType, times, parameters)) { using (DbDataAdapter adapter = providerFactory.CreateDataAdapter()) { adapter.SelectCommand = command; DataTable data = new DataTable(); adapter.Fill(data); return data; } } } #endregion #region 内部私有方法 /// <summary> /// 构建 DbCommand 对象(用来返回一个结果集,而不是一个整数值) /// </summary> /// <param name="connection"></param> /// <param name="storedProcName"></param> /// <param name="parameters"></param> /// <returns></returns> private DbCommand CreateDbQueryCommand(DbConnection connection, string storedProcName, IDbDataParameter[] parameters) { DbCommand command = providerFactory.CreateCommand(); command.CommandText = storedProcName; command.Connection = connection; command.CommandType = CommandType.StoredProcedure; foreach (DbParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(((ICloneable)parameter).Clone()); } } return command; } /// <summary> /// /// </summary> /// <param name="connection"></param> /// <param name="storedProcName"></param> /// <param name="parameters"></param> /// <returns></returns> private DbCommand CreateDbCommand(DbConnection connection, string storedProcName, IDbDataParameter[] parameters) { DbCommand command = CreateDbQueryCommand(connection, storedProcName, parameters); DbParameter parameter = providerFactory.CreateParameter(); parameter.ParameterName = "ReturnValue"; parameter.DbType = DbType.Int32; parameter.Size = 4; parameter.Direction = ParameterDirection.ReturnValue; parameter.SourceColumnNullMapping = false; parameter.SourceColumn = string.Empty; parameter.SourceVersion = DataRowVersion.Default; parameter.Value = null; command.Parameters.Add(((ICloneable)parameter).Clone()); return command; } /// <summary> /// 创建一个DbCommand对象 /// </summary> /// <param name="cmd">DbCommand对象</param> /// <param name="conn">DbConnection数据库连接</param> /// <param name="trans">DbTransaction事务对象</param> /// <param name="cmdText">执行的SQL语句</param> /// <param name="cmdParms">DbParameter参数数组</param> private void CreateDbCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, DbParameter[] cmdParms) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) { cmd.Transaction = trans; } cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (DbParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(((ICloneable)parameter).Clone()); } } } /// <summary> /// 创建一个DbCommand对象 /// </summary> /// <param name="sqlString">要执行的查询语句</param> /// <param name="parameters">执行SQL查询语句所需要的参数</param> /// <param name="commandType">执行的SQL语句的类型</param> /// <returns></returns> private DbCommand CreateDbCommand(string sqlString, CommandType commandType, int times, DbParameter[] parameters) { DbConnection connection = providerFactory.CreateConnection(); DbCommand command = providerFactory.CreateCommand(); connection.ConnectionString = ConnectionString; command.CommandText = sqlString; command.CommandType = commandType; command.Connection = connection; if (times > 0) { command.CommandTimeout = times; } if (!(parameters == null || parameters.Length == 0)) { foreach (DbParameter parameter in parameters) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(((ICloneable)parameter).Clone()); } } return command; } /// <summary> /// 创建一个DbCommand对象 /// </summary> /// <param name="sqlString">要执行的查询语句</param> /// <param name="times">超时时间</param> /// <param name="parameters">执行SQL查询语句所需要的参数</param> /// <returns></returns> private DbCommand CreateDbCommand(string sqlString, int times, DbParameter[] parameters) { DbConnection connection = providerFactory.CreateConnection(); DbCommand command = providerFactory.CreateCommand(); connection.ConnectionString = ConnectionString; command.CommandText = sqlString; command.CommandType = CommandType.Text; command.Connection = connection; command.Transaction = connection.BeginTransaction(); if (times > 0) { command.CommandTimeout = times; } if (!(parameters == null || parameters.Length == 0)) { foreach (DbParameter parameter in parameters) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(((ICloneable)parameter).Clone()); } } return command; } #endregion #region 获取安全的SQL字符串 /// <summary> /// 获取安全的SQL字符串 /// </summary> /// <param name="sql"></param> /// <returns></returns> public string GetSafeSQLString(string sql) { sql = sql.Replace(",", ","); sql = sql.Replace(".", "。"); sql = sql.Replace("(", "("); sql = sql.Replace(")", ")"); sql = sql.Replace(">", ">"); sql = sql.Replace("<", "<"); sql = sql.Replace("-", "-"); sql = sql.Replace("+", "+"); sql = sql.Replace("=", "="); sql = sql.Replace("?", "?"); sql = sql.Replace("*", "*"); sql = sql.Replace("|", "|"); sql = sql.Replace("&", "&"); return sql; } #endregion #region 返回当前连接的数据库中所有用户创建的数据库 /// <summary> /// 返回当前连接的数据库中所有用户创建的数据库 /// </summary> /// <returns></returns> public DataTable GetTables() { DataTable table = null; using (DbConnection con = providerFactory.CreateConnection()) { con.Open(); table = con.GetSchema("Tables"); } return table; } #endregion } #region 数据库类型枚举 /// <summary> /// 数据库类型枚举 /// </summary> public enum DbProviderType : byte { /// <summary> /// 微软 SqlServer 数据库 /// </summary> SqlServer, /// <summary> /// 开源 MySql数据库 /// </summary> MySql, /// <summary> /// 嵌入式轻型数据库 SQLite /// </summary> SQLite, /// <summary> /// 甲骨文 Oracle /// </summary> Oracle, /// <summary> /// 开放数据库互连 /// </summary> ODBC, /// <summary> /// 面向不同的数据源的低级应用程序接口 /// </summary> OleDb, /// <summary> /// 跨平台的关系数据库系统 Firebird /// </summary> Firebird, /// <summary> ///加州大学伯克利分校计算机系开发的关系型数据库 PostgreSql /// </summary> PostgreSql, /// <summary> /// IBM出口的一系列关系型数据库管理系统 DB2 /// </summary> DB2, /// <summary> /// IBM公司出品的关系数据库管理系统(RDBMS)家族 Informix /// </summary> Informix, /// <summary> /// 微软推出的一个适用于嵌入到移动应用的精简数据库产品 SqlServerCe /// </summary> SqlServerCe } #endregion #region DbProviderFactory工厂类 /// <summary> /// DbProviderFactory工厂类 /// </summary> public class ProviderFactory { private readonly static Dictionary<DbProviderType, string> providerInvariantNames = new Dictionary<DbProviderType, string>(); private readonly static Dictionary<DbProviderType, DbProviderFactory> providerFactoies = new Dictionary<DbProviderType, DbProviderFactory>(20); /// <summary> /// 加载已知的数据库访问类的程序集 /// </summary> static ProviderFactory() { providerInvariantNames.Add(DbProviderType.SqlServer, "System.Data.SqlClient"); providerInvariantNames.Add(DbProviderType.OleDb, "System.Data.OleDb"); providerInvariantNames.Add(DbProviderType.ODBC, "System.Data.ODBC"); providerInvariantNames.Add(DbProviderType.Oracle, "Oracle.DataAccess.Client"); providerInvariantNames.Add(DbProviderType.MySql, "MySql.Data.MySqlClient"); providerInvariantNames.Add(DbProviderType.SQLite, "System.Data.SQLite"); providerInvariantNames.Add(DbProviderType.Firebird, "FirebirdSql.Data.Firebird"); providerInvariantNames.Add(DbProviderType.PostgreSql, "Npgsql"); providerInvariantNames.Add(DbProviderType.DB2, "IBM.Data.DB2.iSeries"); providerInvariantNames.Add(DbProviderType.Informix, "IBM.Data.Informix"); providerInvariantNames.Add(DbProviderType.SqlServerCe, "System.Data.SqlServerCe"); } /// <summary> /// 获取指定数据库类型对应的程序集名称 /// </summary> /// <param name="providerType">数据库类型枚举</param> /// <returns></returns> public static string GetProviderInvariantName(DbProviderType providerType) { return providerInvariantNames[providerType]; } /// <summary> /// 获取指定类型的数据库对应的DbProviderFactory /// </summary> /// <param name="providerType">数据库类型枚举</param> /// <returns></returns> public static DbProviderFactory GetDbProviderFactory(DbProviderType providerType) { //如果还没有加载,则加载该DbProviderFactory if (!providerFactoies.ContainsKey(providerType)) { providerFactoies.Add(providerType, ImportDbProviderFactory(providerType)); } return providerFactoies[providerType]; } /// <summary> /// 加载指定数据库类型的DbProviderFactory /// </summary> /// <param name="providerType">数据库类型枚举</param> /// <returns></returns> private static DbProviderFactory ImportDbProviderFactory(DbProviderType providerType) { string providerName = providerInvariantNames[providerType]; DbProviderFactory factory = null; try { //从全局程序集中查找 factory = DbProviderFactories.GetFactory(providerName); } catch (ArgumentException e) { factory = null; } return factory; } } #endregion }