My DBHelper data manipulation class

In fact, Microsoft's corporate library has a very good data manipulation class. But, a lot of companies (at least I met a few ...), some of the "encapsulation" of what things are not too bold, although I recommended the Microsoft Enterprise 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["Dbhelperprovi
        Der "];

        private static string dbconnectionstring = configurationmanager.appsettings["dbhelperconnectionstring"];
        private DbConnection Connection;
        Public DBHelper () {this.connection = CreateConnection (dbhelper.dbconnectionstring); Public DBHelper (String connectionString) {this.connection = CreateConnection (Connectionstri
        NG); public static DbConnection CreateConnection () {DbProviderFactory dbfactory = dbproviderfact Ories.
            GetFactory (Dbhelper.dbprovidername); DbConnection dbconn = dbfactory.
            CreateConnection (); Dbconn.
            ConnectionString = dbhelper.dbconnectionstring;
        return dbconn; } public StatIC dbconnection createconnection (string connectionString) {DbProviderFactory dbfactory = Dbproviderfa Ctories.
            GetFactory (Dbhelper.dbprovidername); DbConnection dbconn = dbfactory.
            CreateConnection (); Dbconn.
            ConnectionString = ConnectionString;
        return dbconn; Public DbCommand Getstoredproccommond (string storedprocedure) {DbCommand DbCommand = Conne Ction.
            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) {foreach (DbParameter dbparameter in dbparametercollection) { 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) {Dbparam Eter dbparameter = cmd.
            CreateParameter ();
            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) {DbPro
            Viderfactory 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) {dbproviderfactory dbfactory = Dbproviderfactori Es.
            GetFactory (Dbhelper.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:

    <appSettings>
        <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>

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 the programmer uses it to get rid of some of the more "physical" programming concepts, such as the connection open and close, So that the programmer more focused on the business logic code to write, 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.