Share dbhelper Common database Access Help class

Usually when we develop the use of database access Help classes, we individually write the appropriate data access Help classes for a particular type of data, such as Sqlhelper,oraclehelper,oledbhelper, In fact, all of these help class implementations inherit and implement the database access base classes in ado.net, such as Sqlcommand->dbcommand,sqlconnection->dbconnection, before the data access layer is optimized, The usual frame structure looks like this:

In fact, we can now optimize the data access module such as sqlserver,oracle, merge it into a common DBHelper data access class, and optimize the access framework as follows:

This allows you to use the Dbheper help class directly when using data access, which has nothing to do with which database to use, which means that when you replace the database, you do not need any existing data access logic code, which has a great effect on our system maintenance, when you change the database, Simply configure the database provider that you are using

Database provider Configuration Actions

The code is as follows

Copy Code

<?xml version= "1.0" encoding= "Utf-8"?> <!-- File: Dbhelper.config Author: Yangdeyong Time: April 10, 2012 10:22:23 Absrtact: Combined with the DBHelper help class, the configuration files for various database providers support multiple database providers such as ODBC, OLE DB, OracleClient, SqlClient, SqlServerCe, and so on --> <configuration> <appSettings> <!--generally have the following configuration name to install the corresponding database provider first System.Data.Odbc System.Data.OleDb System.Data.OracleClient System.Data.SqlClient system.data.sqlserverce.3.5 IAnywhere.Data.SQLAnywhere --> <!--Config database provider name Key name do not change--> <add key= "Dbprovidername" value= "System.Data.SqlClient"/> <!--Configure database Link field String value Key name do not change--> <add key= "dbconnectionstring" value= "Data source=localhostsqlexpress;initial catalog=ydytest;integrated Security =sspi "/> </appSettings> </configuration>

In order to satisfy this general data access operation, I have written a DbHelper.cs class library, welcome everyone to download the use, hope can help everybody in the database access operation more convenient, fast,

The specific generic database Access Help class code is shown below
Common database Access Help class DbHelper.cs

The code is as follows

Copy Code

File: DbHelper.cs Author: Yangdeyong Time: April 10, 2012 10:12:45 Summary:. NET common database operations Help class to support multiple database provider operations such as ODBC, OLE DB, OracleClient, SqlClient, SqlServerCe, etc. Using System; Using System.Collections.Generic; Using System.Data; Using System.Data.Common; Using System.Linq; Using System.Text; Namespace DBHelper { <summary> Description:. NET common database operations Help class to support multiple database provider operations such as ODBC, OLE DB, OracleClient, SqlClient, SqlServerCe, etc. Author: Yangdeyong Time: 2012-04-10 10:12:45 </summary> <remarks> Author: Yangdeyong Time: 2012-04-10 10:12:45 </remarks> public sealed class DBHelper { #region Field Properties #region static public fields <summary> Gets the provider name value for the current database configuration Dbprovidername </summary> public static readonly String dbprovidername = system.configuration.configurationmanager.appsettings["Dbprovidername "]; <summary> Gets the connection string value for the current database configuration dbconnectionstring </summary> public static readonly String dbconnectionstring = system.configuration.configurationmanager.appsettings[" Dbconnectionstring "]; #endregion #region Private Fields <summary> The database provider for the current default configuration DbProviderFactory </summary> Private DbProviderFactory _dbfactory = null; <summary> Current database Link DbConnection object </summary> Private DbConnection _dbconnection = null; <summary> The current database provider </summary> private string _dbprovidername = null; <summary> The current database connection string </summary> private string _dbconnectionstring = null; #endregion #endregion #region Constructors <summary> Initializes an instance of this object based on a configured database provider and link string </summary> Public DBHelper () : This (dbhelper.dbconnectionstring, dbhelper.dbprovidername) { } <summary> Initializes an instance of this object based on the database link string and the database provider name two parameters </summary> <param name= "connectionString" > Database connection Configuration string </param> <param name= "ProviderName" > Database provider name </param> Public DBHelper (String connectionString, String providername) { if (!string. IsNullOrEmpty (ProviderName)) { This._dbfactory = Dbhelper.createdbproviderfactory (providername);//Create a default configured database provider } Else { throw new ArgumentNullException ("ProviderName", "the database provider name parameter value cannot be empty, configure the key value in the configuration file!"); } if (!string. IsNullOrEmpty (connectionString)) { This._dbconnection = Dbhelper.createdbconnection (connectionString, providername);//Create the current Database link object } Else { throw new ArgumentNullException ("ConnectionString", "Database link string parameter value cannot be empty, configure the key value in the configuration file!"); } Save the current connection string and database provider name this._dbconnectionstring = connectionString; This._dbprovidername = providername; } #endregion #region Method function #region Create a DbProviderFactory object (static method) <summary> Creates a database-configured provider DbProviderFactory object based on the Dbprovidername name of the configured database provider </summary> public static DbProviderFactory Createdbproviderfactory () { DbProviderFactory dbfactory = dbhelper.createdbproviderfactory (dbhelper.dbprovidername); return dbfactory; } <summary> Create a database provider DbProviderFactory object based on the parameter name </summary> <param name= "Dbprovidername" > Database provider name </param> public static DbProviderFactory Createdbproviderfactory (String dbprovidername) { DbProviderFactory dbfactory = dbproviderfactories.getfactory (dbprovidername); return dbfactory; } #endregion #region Create a DbConnection object (static method) <summary> Create a database link based on the configured database provider and link string. </summary> public static DbConnection Createdbconnection () { DbConnection dbconn = dbhelper.createdbconnection (dbhelper.dbconnectionstring, dbhelper.dbprovidername); return dbconn; } <summary> Create a database link based on the database connection string parameter. </summary> <param name= "connectionString" > Database connection Configuration string </param> <param name= "Dbprovidername" > Database provider name </param> <returns></returns> public static DbConnection Createdbconnection (String connectionString, String dbprovidername) { DbProviderFactory dbfactory = dbhelper.createdbproviderfactory (dbprovidername); DbConnection dbconn = Dbfactory.createconnection (); dbconn.connectionstring = ConnectionString; return dbconn; } #endregion #region Get DbCommand Object <summary> To build the DbCommand object for the current database link based on the stored procedure name </summary> <param name= "StoredProcedure" > Stored procedure name </param> Public DbCommand Getstoredprocedurecommond (string storedprocedure) { DbCommand dbcmd = This._dbconnection.createcommand (); Dbcmd.commandtext = StoredProcedure; Dbcmd.commandtype = CommandType.StoredProcedure; return dbcmd; } <summary> To build the DbCommand object for the current database link based on the SQL statement </summary> <param name= "sqlquery" >sql query statement </param> Public DbCommand Getsqlstringcommond (string sqlquery) { DbCommand dbcmd = This._dbconnection.createcommand (); Dbcmd.commandtext = sqlquery; Dbcmd.commandtype = CommandType.Text; return dbcmd; } #endregion #region Add DbCommand parameter <summary> To add a collection of parameters to a DbCommand object </summary> <param name= "cmd" > Database command Action Object </param> <param name= "dbparametercollection" > Database operation set </param> public void Addparametercollection (DbCommand cmd, dbparametercollection dbparametercollection) { if (cmd!= null) { foreach (DbParameter dbparameter in dbparametercollection) { Cmd. Parameters.Add (DbParameter); } } } <summary> Adding output parameters to the DbCommand object </summary> <param name= "cmd" > Database command Action Object </param> <param name= "parametername" > Parameter name </param> <param name= "DbType" > Parameter type </param> <param name= the size of the "size" > Parameters </param> public void Addoutparameter (DbCommand cmd, string parametername, DbType DbType, int size) { if (cmd!= null) { DbParameter dbparameter = cmd. CreateParameter (); Dbparameter.dbtype = DbType; Dbparameter.parametername = parametername; Dbparameter.size = Size; Dbparameter.direction = ParameterDirection.Output; Cmd. Parameters.Add (DbParameter); } } <summary> Add input parameters to the DbCommand object </summary> <param name= "cmd" > Database command Action Object </param> <param name= "parametername" > Parameter name </param> <param name= "DbType" > Parameter type </param> <param name= "value" > Parameter value </param> public void Addinparameter (DbCommand cmd, string parametername, DbType DbType, object value) { if (cmd!= null) { DbParameter dbparameter = cmd. CreateParameter (); Dbparameter.dbtype = DbType; Dbparameter.parametername = parametername; Dbparameter.value = Value; Dbparameter.direction = ParameterDirection.Input; Cmd. Parameters.Add (DbParameter); } } <summary> Add the return parameter to the DbCommand object </summary> <param name= "cmd" > Database command Action Object </param> <param name= "parametername" > Parameter name </param> <param name= "DbType" > Parameter type </param> public void Addreturnparameter (DbCommand cmd, string parametername, DbType DbType) { if (cmd!= null) { DbParameter dbparameter = cmd. CreateParameter (); Dbparameter.dbtype = DbType; Dbparameter.parametername = parametername; Dbparameter.direction = ParameterDirection.ReturnValue; Cmd. Parameters.Add (DbParameter); } } <summary> Gets the corresponding parameter object from the DbCommand object based on the parameter name </summary> <param name= "cmd" > Database command Action Object </param> <param name= "parametername" > Parameter name </param> Public DbParameter getparameter (dbcommand cmd, string parametername) { if (cmd!= null && cmd. Parameters.count > 0) { DbParameter param = cmd. Parameters[parametername]; return param; } return null; } #endregion #region Execute SQL Script statement <summary> Executes the corresponding SQL command, returning a DataSet data collection </summary> <param name= "sqlquery" > SQL statement to execute </param> <returns> returns a dataset data set </returns> Public DataSet ExecuteDataset (string sqlquery) { DataSet ds = new DataSet (); if (!string. IsNullOrEmpty (SQLQuery)) { DbCommand cmd = Getsqlstringcommond (sqlquery); ds = ExecuteDataset (cmd); } return DS; } <summary> Executes the corresponding SQL command, returning a DataTable dataset </summary> <param name= "sqlquery" > SQL statement to execute </param> <returns> Returns a DataTable dataset </returns> Public DataTable executedatatable (string sqlquery) { DataTable dt = new DataTable (); if (!string. IsNullOrEmpty (SQLQuery)) { DbCommand cmd = Getsqlstringcommond (sqlquery); DT = executedatatable (cmd); } return DT; } <summary> Executes the appropriate SQL command, returns a DbDataReader data object, and returns a null value if not </summary> <param name= "sqlquery" > SQL commands to execute </param> <returns> returns a DbDataReader data object, if not, returns a null value </returns> Public DbDataReader ExecuteReader (string sqlquery) { if (!string. IsNullOrEmpty (SQLQuery)) { DbCommand cmd = Getsqlstringcommond (sqlquery); DbDataReader reader = ExecuteReader (cmd); return reader; } return null; } <summary> Executes the corresponding SQL command, returns the number of data records affected, or returns 1 if unsuccessful </summary> <param name= "sqlquery" > SQL commands to execute </param> <returns> returns the number of data records affected, or returns -1</returns> if unsuccessful public int ExecuteNonQuery (string sqlquery) { if (!string. IsNullOrEmpty (SQLQuery)) { DbCommand cmd = Getsqlstringcommond (sqlquery); int retVal = ExecuteNonQuery (cmd); return retVal; } return-1; } <summary> Executes the corresponding SQL command, returns the value of the first column of the first row in the result set, and returns a null value if unsuccessful </summary> <param name= "sqlquery" > SQL commands to execute </param> <returns> returns the value of the first column in the first row of the result set, and returns a null value if it is unsuccessful </returns> public Object ExecuteScalar (string sqlquery) { if (!string. IsNullOrEmpty (SQLQuery)) { DbCommand cmd = Getsqlstringcommond (sqlquery); Object retVal = ExecuteScalar (cmd); return retVal; } return null; } #endregion #region Execute DbCommand command <summary> Executes the appropriate command to return a DataSet data collection </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <returns> returns a dataset data set </returns> Public DataSet executedataset (dbcommand cmd) { DataSet ds = new DataSet (); if (cmd!= null) { DbDataAdapter DbDataAdapter = This._dbfactory.createdataadapter (); Dbdataadapter.selectcommand = cmd; Dbdataadapter.fill (DS); } return DS; } <summary> Executes the corresponding command, returning a DataTable data collection </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <returns> returns a DataTable data set </returns> Public DataTable executedatatable (dbcommand cmd) { DataTable datatable = new DataTable (); if (cmd!= null) { DbDataAdapter DbDataAdapter = This._dbfactory.createdataadapter (); Dbdataadapter.selectcommand = cmd; Dbdataadapter.fill (dataTable); } return dataTable; } <summary> Executes the appropriate command, returns a DbDataReader data object, and returns a null value if not </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <returns> returns a DbDataReader data object, if not, returns a null value </returns> Public DbDataReader ExecuteReader (dbcommand cmd) { if (cmd!= null && cmd. Connection!= null) { if (cmd. Connection.state!= ConnectionState.Open) { Cmd. Connection.Open (); } DbDataReader reader = cmd. ExecuteReader (commandbehavior.closeconnection);//Automatically close the database link when reader finishes return reader; } return null; } <summary> Executes the appropriate command, returns the number of data records affected, or returns 1 if unsuccessful </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <returns> returns the number of data records affected, or returns -1</returns> if unsuccessful public int ExecuteNonQuery (DbCommand cmd) { if (cmd!= null && cmd. Connection!= null) { if (cmd. Connection.state!= ConnectionState.Open) { Cmd. Connection.Open (); } int retVal = cmd. ExecuteNonQuery (); Cmd. Connection.close (); return retVal; } return-1; } <summary> Executes the corresponding command, returns the value of the first column of the first row in the result set, and returns a null value if unsuccessful </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <returns> returns the value of the first column in the first row of the result set, and returns a null value if it is unsuccessful </returns> public Object ExecuteScalar (DbCommand cmd) { if (cmd!= null && cmd. Connection!= null) { if (cmd. Connection.state!= ConnectionState.Open) { Cmd. Connection.Open (); } Object retVal = cmd. ExecuteScalar (); Cmd. Connection.close (); return retVal; } return null; } #endregion #region Perform dbtransaction transactions <summary> Executes the appropriate command in a transactional manner, returning a DataSet data collection </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <param name= "trans" > Database Transaction Object </param> <returns> returns a dataset data set </returns> Public DataSet executedataset (dbcommand cmd, Trans Trans) { DataSet ds = new DataSet (); if (cmd!= null) { Cmd. Connection = trans. Connection; Cmd. Transaction = trans. Transaction; DbDataAdapter DbDataAdapter = This._dbfactory.createdataadapter (); Dbdataadapter.selectcommand = cmd; Dbdataadapter.fill (DS); } return DS; } <summary> Executes the appropriate command in a transactional manner, returning a DataTable data collection </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <param name= "trans" > Database Transaction Object </param> <returns> returns a DataTable data set </returns> Public DataTable executedatatable (dbcommand cmd, Trans Trans) { DataTable datatable = new DataTable (); if (cmd!= null) { Cmd. Connection = trans. Connection; Cmd. Transaction = trans. Transaction; DbDataAdapter DbDataAdapter = This._dbfactory.createdataadapter (); Dbdataadapter.selectcommand = cmd; Dbdataadapter.fill (dataTable); } return dataTable; } <summary> Executes the appropriate command in a transactional manner, returns a DbDataReader data object, and returns a null value if not </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <param name= "trans" > Database Transaction Object </param> <returns> returns a DbDataReader data object, if not, returns a null value </returns> Public DbDataReader ExecuteReader (dbcommand cmd, Trans Trans) { if (cmd!= null) { Cmd. Connection.close (); Cmd. Connection = trans. Connection; Cmd. Transaction = trans. Transaction; DbDataReader reader = cmd. ExecuteReader (); return reader; } return null; } <summary> Executes the appropriate command in a transaction, returns the number of data records affected, or returns 1 if unsuccessful </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <param name= "trans" > Database Transaction Object </param> <returns> returns the number of data records affected, or returns -1</returns> if unsuccessful public int ExecuteNonQuery (dbcommand cmd, Trans Trans) { if (cmd!= null) { Cmd. Connection.close (); Cmd. Connection = trans. Connection; Cmd. Transaction = trans. Transaction; int retVal = cmd. ExecuteNonQuery (); return retVal; } return-1; } <summary> Executes the appropriate command in a transaction, returns the value of the first column in the first row of the result set, or returns a null value if unsuccessful </summary> <param name= "cmd" > DbCommand Command object to be executed </param> <param name= "trans" > Database Transaction Object </param> <returns> returns the value of the first column in the first row of the result set, and returns a null value if it is unsuccessful </returns> public Object ExecuteScalar (dbcommand cmd, Trans Trans) { if (cmd!= null) { Cmd. Connection.close (); Cmd. Connection = trans. Connection; Cmd. Transaction = trans. Transaction; Object retVal = cmd. ExecuteScalar (); return retVal; } return null; } #endregion #endregion } <summary> Description: Database Transaction Action Object Author: Yangdeyong Time: 2012-04-10 10:19:23 </summary> <remarks> Author: Yangdeyong Time: 2012-04-10 10:12:45 </remarks> public sealed class Trans:idisposable { #region Field Properties Private DbConnection connection = null; <summary> Get the current database link object </summary> Public DbConnection Connection { Get { return this.connection; } } Private dbtransaction transaction = NULL; <summary> Get the current database transaction object </summary> Public Dbtransaction Transaction { Get { return this.transaction; } } #endregion #region Constructors <summary> Create this transaction object based on the configured database provider and connection string </summary> Public Trans () : This (dbhelper.dbconnectionstring, dbhelper.dbprovidername) { } <summary> Create this transaction object based on the database connection string </summary> <param name= "connectionString" > Database connection string </param> <param name= "Dbprovidername" > Database provider name </param> Public Trans (String connectionString, String dbprovidername) { if (!string. IsNullOrEmpty (connectionString)) { This.connection = Dbhelper.createdbconnection (connectionString, dbprovidername); This. Connection.Open (); This.transaction = this. Connection.begintransaction (); } Else { throw new ArgumentNullException ("ConnectionString", "Database link string parameter value cannot be empty!"); } } #endregion #region Method function <summary> Commit This database transaction operation </summary> public void Commit () { This.Transaction.Commit (); This. Close (); } <summary> Rollback This database transaction operation </summary> public void RollBack () { This. Transaction.rollback (); This. Close (); } <summary> Close this database transaction link </summary> public void Close () { if (this. Connection.state!= System.Data.ConnectionState.Closed) { This. Connection.close (); } } #endregion #region IDisposable Members <summary> Performs an application-defined task related to releasing or resetting an unmanaged resource. </summary> public void Dispose () { This. Close (); } #endregion } }