[Reprinted] common database classes of DbHelper

Link: http://hi.baidu.com/x2p2/blog/item/0492cad3c50a10d8a9ec9a1d.html

Database Operations are troublesome. The following is a common DbHelper class based on engineering principles.

Using System; using System. data; using System. data. common; using System. configuration; public class DbHelper {private static string dbProviderName = ConfigurationManager. appSettings ["DbHelperProvider"]; private static string dbConnectionString = ConfigurationManager. appSettings ["DbHelperConnectionString"]; private DbConnection connection; public DbHelper () {this. connection = CreateConnection (DbHelper. dbConn EctionString);} public DbHelper (string connectionString) {this. connection = CreateConnection (connectionString);} public static DbConnection CreateConnection () {DbProviderFactory dbfactory = DbProviderFactories. getFactory (DbHelper. dbProviderName); DbConnection dbconn = dbfactory. createConnection (); dbconn. connectionString = DbHelper. dbConnectionString; return dbconn;} public static DbConnection CreateConne Ction (string connectionString) {DbProviderFactory dbfactory = DbProviderFactories. getFactory (DbHelper. dbProviderName); DbConnection dbconn = dbfactory. createConnection (); dbconn. connectionString = connectionString; return dbconn;} public DbCommand GetStoredProcCommond (string storedProcedure) {DbCommand dbCommand = connection. createCommand (); dbCommand. commandText = storedProcedure; dbCommand. commandType = CommandType. storedProcedure; return dbCommand;} public DbCommand GetSqlStringCommond (string sqlQuery) {DbCommand dbCommand = connection. createCommand (); dbCommand. commandText = sqlQuery; dbCommand. commandType = CommandType. text; return dbCommand ;}# Add the public void AddParameterCollection (DbCommand cmd, DbParameterCollection dbParameterCollection) {foreach (DbParameter dbParameter in dbParameterCo Llection) {cmd. parameters. add (dbParameter) ;}} public void AddOutParameter (DbCommand cmd, string parameterName, DbType dbType, int size) {DbParameter dbParameter = cmd. createParameter (); dbParameter. dbType = dbType; dbParameter. parameterName = parameterName; dbParameter. size = size; dbParameter. direction = ParameterDirection. output; cmd. parameters. add (dbParameter);} public void AddInParameter (DbCommand cmd, String parameterName, DbType dbType, object value) {DbParameter dbParameter = cmd. createParameter (); dbParameter. dbType = dbType; dbParameter. parameterName = parameterName; dbParameter. value = value; dbParameter. direction = ParameterDirection. input; cmd. parameters. add (dbParameter);} public void AddReturnParameter (DbCommand cmd, string parameterName, DbType dbType) {DbParameter dbParameter = cmd. createPar Ameter (); dbParameter. dbType = dbType; dbParameter. parameterName = parameterName; dbParameter. direction = ParameterDirection. returnValue; cmd. parameters. add (dbParameter);} public DbParameter GetParameter (DbCommand cmd, string parameterName) {return cmd. parameters [parameterName] ;}# endregion # region executes public DataSet ExecuteDataSet (DbCommand cmd) {DbProviderFactory dbfactory = DbProviderFactories. getFactor Y (DbHelper. dbProviderName); DbDataAdapter dbDataAdapter = dbfactory. createDataAdapter (); dbDataAdapter. selectCommand = cmd; DataSet ds = new DataSet (); dbDataAdapter. fill (ds); return ds;} public DataTable ExecuteDataTable (DbCommand cmd) {DbProviderFactory dbfactory = DbProviderFactories. getFactory (DbHelper. dbProviderName); DbDataAdapter dbDataAdapter = dbfactory. createDataAdapter (); dbDataAdapter. selectCom Mand = cmd; DataTable dataTable = new DataTable (); dbDataAdapter. fill (dataTable); return dataTable;} public DbDataReader ExecuteReader (DbCommand cmd) {cmd. connection. open (); DbDataReader reader = cmd. executeReader (CommandBehavior. closeConnection); return reader;} public int ExecuteNonQuery (DbCommand cmd) {cmd. connection. open (); int ret = cmd. executeNonQuery (); cmd. connection. close (); return ret;} public object ExecuteScalar (DbCommand cmd) {cmd. connection. open (); object ret = cmd. executeScalar (); cmd. connection. close (); return ret ;}# endregion # region executes the public DataSet ExecuteDataSet (DbCommand cmd, Trans t) {cmd. connection = t. dbConnection; cmd. transaction = t. dbTrans; DbProviderFactory dbfactory = DbProviderFactories. getFactory (DbHelper. dbProviderName); DbDataAdapter dbDataAdapter = dbfactory. createDataAdapt Er (); dbDataAdapter. selectCommand = cmd; DataSet ds = new DataSet (); dbDataAdapter. fill (ds); return ds;} public DataTable ExecuteDataTable (DbCommand cmd, Trans t) {cmd. connection = t. dbConnection; cmd. transaction = t. dbTrans; DbProviderFactory dbfactory = DbProviderFactories. getFactory (DbHelper. dbProviderName); DbDataAdapter dbDataAdapter = dbfactory. createDataAdapter (); dbDataAdapter. selectCommand = cmd; Da TaTable dataTable = new DataTable (); dbDataAdapter. fill (dataTable); return dataTable;} public DbDataReader ExecuteReader (DbCommand cmd, Trans t) {cmd. connection. close (); cmd. connection = t. dbConnection; cmd. transaction = t. dbTrans; DbDataReader reader = cmd. executeReader (); DataTable dt = new DataTable (); return reader;} public int ExecuteNonQuery (DbCommand cmd, Trans t) {cmd. connection. close (); cmd. connectio N = t. dbConnection; cmd. transaction = t. dbTrans; int ret = cmd. executeNonQuery (); return ret;} public object ExecuteScalar (DbCommand cmd, Trans t) {cmd. connection. close (); cmd. connection = t. dbConnection; cmd. transaction = t. dbTrans; object ret = cmd. executeScalar (); return ret ;}# endregion} public class Trans: IDisposable {private DbConnection conn; private DbTransaction dbTrans; public DbConnection DbConnec Tion {get {return this. conn ;}} public DbTransaction DbTrans {get {return this. dbTrans ;}} public Trans () {conn = DbHelper. createConnection (); conn. open (); dbTrans = conn. beginTransaction ();} public Trans (string connectionString) {conn = DbHelper. createConnection (connectionString); conn. open (); dbTrans = conn. beginTransaction ();} public void Commit () {dbTrans. commit (); this. colse ();} public void RollBack () {dbT Rans. rollback (); this. colse ();} public void Dispose () {this. colse ();} public void Colse () {if (conn. state = System. data. connectionState. open) {conn. close () ;}} so how to use it? Below are some basic examples to meet most of your database operations. 1) directly execute the SQL statement DbHelper db = new DbHelper (); DbCommand cmd = db. getSqlStringCommond ("insert t1 (id) values ('hahahaha')"); db. executeNonQuery (cmd); 2) execute the Stored Procedure DbHelper db = new DbHelper (); DbCommand cmd = db. getStoredProcCommond ("t1_insert"); db. addInParameter (cmd, "@ id", DbType. string, "heihei"); db. executeNonQuery (cmd); 3) DataSetDbHelper db = new DbHelper (); DbCommand cmd = db. getSqlStringCommond ("select * from t1"); DataSet ds = db. executeDataSet (cmd); 4) returns ableabledbhelper db = new DbHelper (); DbCommand cmd = db. getSqlStringCommond ("t1_findall"); DataTable dt = db. executeDataTable (cmd); 5) use of input/output parameters/return values (more important) DbHelper db = new DbHelper (); DbCommand cmd = db. getStoredProcCommond ("t2_insert"); db. addInParameter (cmd, "@ timeticks", DbType. int64, DateTime. now. ticks); db. addOutParameter (cmd, "@ outString", DbType. string, 20); db. addReturnParameter (cmd, "@ returnValue", DbType. int32); db. executeNonQuery (cmd); string s = db. getParameter (cmd, "@ outString "). value as string; // out parameterint r = Convert. toInt32 (db. getParameter (cmd, "@ returnValue "). value); // return value6) DataReader uses DbHelper db = new DbHelper (); DbCommand cmd = db. getStoredProcCommond ("t2_insert"); db. addInParameter (cmd, "@ timeticks", DbType. int64, DateTime. now. ticks); db. addOutParameter (cmd, "@ outString", DbType. string, 20); db. addReturnParameter (cmd, "@ returnValue", DbType. int32); using (DbDataReader reader = db. executeReader (cmd) {dt. load (reader);} string s = db. getParameter (cmd, "@ outString "). value as string; // out parameterint r = Convert. toInt32 (db. getParameter (cmd, "@ returnValue "). value); // return value7) use of the transaction. (when the project needs to combine basic database operations into a complete business flow, code-level transactions are essential.) public void DoBusiness () {using (Trans t = new Trans () {try {D1 (t); throw new Exception (); // if an Exception exists, D2 (t) will be rolled back ); t. commit ();} catch {t. rollBack () ;}} public void D1 (Trans t) {DbHelper db = new DbHelper (); DbCommand cmd = db. getStoredProcCommond ("t2_insert"); db. addInParameter (cmd, "@ timeticks", DbType. int64, DateTime. now. ticks); db. addOutParameter (cmd, "@ outString", DbType. string, 20); db. addReturnParameter (cmd, "@ returnValue", DbType. int32); if (t = null) db. executeNonQuery (cmd); elsedb. executeNonQuery (cmd, t); string s = db. getParameter (cmd, "@ outString "). value as string; // out parameter int r = Convert. toInt32 (db. getParameter (cmd, "@ returnValue "). value); // return value} public void D2 (Trans t) {DbHelper db = new DbHelper (); DbCommand cmd = db. getSqlStringCommond ("insert t1 (id) values ('.. ') "); if (t = null) db. executeNonQuery (cmd); elsedb. executeNonQuery (cmd, t);} We didn't seem to have specified a database connection string above. If you look at the DbHelper code, you will know that to use it, you must configure two parameters in config, as shown below: <deleetask> <add key = "DbHelperProvider" value = "System. data. sqlClient "/> <add key =" DbHelperConnectionString "value =" Data Source = (local); Initial Catalog = DbHelperTest; Persist Security Info = True; User ID = sa; password = sa "/> </appSettings>