C # Database Programming DBHelper Data Manipulation class "DBHelper.CS"

In fact, there is a very good data manipulation class in Microsoft's corporate library. However, a number of companies (at least I met a few ...), some of the "encapsulation" of something is not too bold, although I recommended the Microsoft's Corporate library framework ... But it still has to be "evaluated" ... A review is a few months ... Moreover, some companies are basically bare ado.net development, or their own encapsulated database operation class is very awkward, very bad. Here I give you a share of my reference to the data manipulation component in the Enterprise Library code style write database operations class, for programmers who use it, coding is very comfortable (at least I feel good about it). Here's the code, very simply, without doing any extra encapsulation, just changing the ado.net encoding step, Facilitate the development of specific database operation code programmer.

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.dbconnectionstring);
 Public DBHelper (String connectionString) {this.connection = CreateConnection (connectionString); public static DbConnection CreateConnection () {DbProviderFactory dbfactory = dbproviderfactories.getfactory (DbHelpe
  R.dbprovidername); DbConnection dbconn = dbfactory.
  CreateConnection (); Dbconn.
  ConnectionString = dbhelper.dbconnectionstring;
 return dbconn; public static DbConnection CreateConnection (String connectionString) {DbProviderFactory dbfactory = Dbproviderfacto Ries.
  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; #region Add parameter public void addparametercollection (DbCommand cmd, dbparametercollection dbparametercollection) {Fore Ach (dbparameter dbparameter in dbparametercollection) {cmd.
  Parameters.Add (DbParameter); } public void Addoutparameter (DbCommand cmd, string parametername, DbType DbType, int size) {DbParameter Dbparamet er = 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 Dbparamet er = 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.c
  Reateparameter ();
  Dbparameter.dbtype = DbType;
  Dbparameter.parametername = parametername;
  Dbparameter.direction = ParameterDirection.ReturnValue; Cmd.
 Parameters.Add (DbParameter); DbParameter getparameter (dbcommand cmd, string parametername) {return cmd.
 Parameters[parametername]; #endregion #region Execute public DataSet executedataset (dbcommand cmd) {dbproviderfactory dbfactory = Dbproviderfac ToRies.
  GetFactory (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 (DbH
  Elper.dbprovidername); DbDataAdapter DbDataAdapter = dbfactory.
  Createdataadapter ();
  Dbdataadapter.selectcommand = 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 Execute transaction public DataSet ExecuteDataset (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;
  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;
  DataTable 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.
  Connection = 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 DbConnection {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 () {dbtrans.rollback (); This.
 Colse (); public void Dispose () {this.
 Colse (); public void Colse () {if (conn). state = = System.Data.ConnectionState.Open) {Conn.
  Close ();
 }
 }
}

So how do you use it? I'll give you some basic examples of usage that basically meet most of your database operations needs.
1) Execute SQL statement directly

DBHelper db = new DBHelper ();
DbCommand cmd = db. Getsqlstringcommond ("Insert T1 (ID) VALUES (' haha ')");
Db. ExecuteNonQuery (CMD);

2) Execute stored procedure

DBHelper db = new DBHelper ();
 DbCommand cmd = db. Getstoredproccommond ("T1_insert");
 Db. Addinparameter (cmd, "@id", dbtype.string, "Heihei");
 Db. ExecuteNonQuery (CMD);

3) return dataset

DBHelper db = new DBHelper ();
 DbCommand cmd = db. Getsqlstringcommond ("SELECT * from T1");
 DataSet ds = db. ExecuteDataset (CMD);

4) Return to DataTable

DBHelper db = new DBHelper ();
 DbCommand cmd = db. Getsqlstringcommond ("T1_findall");
 DataTable dt = db. Executedatatable (CMD);

5 input parameter/output parameter/return value use (more important OH)

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);
 Db. Addreturnparameter (cmd, "@returnValue", dbtype.int32);

 Db. ExecuteNonQuery (cmd);

 string s = db. GetParameter (cmd, "@outString"). Value as string;//out parameter
 int r = Convert.ToInt32 (db. GetParameter (cmd, "@returnValue"). value);//return value

6) DataReader use

  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);
 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 parameter
 int r = Convert.ToInt32 (db. GetParameter (cmd, "@returnValue"). value);//return value

7) The use of the transaction. (Code-level transactions are essential when you combine basic database operations into a complete business flow in your project.)

pubic void Dobusiness () {using (Trans t = new Trans ()) {try {D1 (t);
   throw new Exception ();//If there is an exception, roll back the Drop D2 (t);
  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); Else db.

 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); Else db.
ExecuteNonQuery (cmd, t); }

We don't seem to specify the database connection string, so if you look at the DBHelper code, you know that to use it you must configure two parameters in Config, as follows:

In fact, the dbhelper need is only two strings, you can modify their own, to make encryption or something ...
OK, so, DBHelper's code is very simple and transparent, just a little bit of packaging on the ado.net, changing the way programmers use it to encode,
Remove some of the more "physical" programming concepts, such as connection open and close, so that programmers are more focused on writing business logic code,
Less dead. Brain cells, in addition, the unified data operation layer of data manipulation code style and format, maintenance is very convenient for the spread ~ ~ ~ ~
Another: The above code can be used at will, do not need to give me the copyright fee, hehe. If you find any bugs, or better ways to implement the data manipulation class, please contact me oh.


Reprint: http://www.cnblogs.com/fanrong/articles/726526.html