C # dbhelper data operation class for database programming [dbhelper. CS]

Http://www.cnblogs.com/fanrong/articles/726526.html

In fact, Microsoft has a very good data operation class in its enterprise database. however, many companies (at least some of the companies I met ...), I don't dare to use something "encapsulated", although I have recommended Microsoft's Enterprise Library Framework... but you still need to "evaluate "... one evaluation is a few months... in addition, some companies simply develop bare ado.net, or their own encapsulated database operations are very awkward and not easy to use.
Here I will share with you a database operation class written in the encoding style of the data operation component in the enterprise database.ProgramThe code is very comfortable (at least I think it is good). below isCodeIt is very simple, without any redundant encapsulation, but it only changes the coding steps of ADO. NET and facilitates the programmer who develops the specific database operation code.

Using system;
Using system. Data;
Using system. Data. Common;
Using system. configuration;

Public class dbhelper
{
Private Static string dbprovidername = configurationmanager. deleetpipeline ["dbhelperprovider"];
Private Static string dbconnectionstring = configurationmanager. etettings ["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 (dbhelper. dbprovidername );
Dbconnection dbconn = dbfactory. createconnection ();
Dbconn. connectionstring = dbhelper. dbconnectionstring;
Return dbconn;
}
Public static dbconnection createconnection (string connectionstring)
{
Dbproviderfactory dbfactory = dbproviderfactories. getfactory (dbhelper. dbprovidername );
Dbconnection dbconn = dbfactory. createconnection ();
Dbconn. connectionstring = connectionstring;
Return dbconn;
}

Public dbcommand getstoredproccommond (string storedprocedure)
{
Dbcommand = connection. createcommand ();
Dbcommand. commandtext = storedprocedure;
Dbcommand. commandtype = commandtype. storedprocedure;
Return dbcommand;
}
Public dbcommand getsqlstringcommond (string sqlquery)
{
Dbcommand = connection. createcommand ();
Dbcommand. commandtext = sqlquery;
Dbcommand. commandtype = commandtype. text;
Return dbcommand;
}

Add parameter # Add parameter for Region
Public void addparametercollection (dbcommand cmd, dbparametercollection)
{
Foreach (dbparameter in dbparametercollection)
{
Cmd. Parameters. Add (dbparameter );
}
}
Public void addoutparameter (dbcommand cmd, string parametername, dbtype, int size)
{
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, object value)
{
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)
{
Dbparameter = cmd. createparameter ();
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

Run # region run
Public dataset executedataset (dbcommand cmd)
{
Dbproviderfactory dbfactory = dbproviderfactories. getfactory (dbhelper. dbprovidername );
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 = dbfactory. createdataadapter ();
Dbdataadapter. selectcommand = cmd;
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

Execute transaction # 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 = 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 = dbfactory. createdataadapter ();
Dbdataadapter. selectcommand = cmd;
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
{
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 to use it? Below are some basic examples to meet most of your database operations.
1) directly execute SQL statements

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) return Dataset

Dbhelper DB = new dbhelper ();
Dbcommand cmd = dB. getsqlstringcommond ("select * from T1 ");
Dataset DS = dB. executedataset (CMD );

4) return datatable

Dbhelper DB = new dbhelper ();
Dbcommand cmd = dB. getsqlstringcommond ("t1_findall ");
Datatable dt = dB. executedatatable (CMD );

5) use of input/output/return values (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 Parameter
Int r = convert. toint32 (db. getparameter (CMD, "@ returnvalue"). Value); // Return Value

6) Use datareader

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 Parameter
Int r = convert. toint32 (db. getparameter (CMD, "@ returnvalue"). Value); // Return Value

7) Use of transactions (Code-level transactions are essential when basic database operations need to be combined into a complete business flow in the project)

Pubic void dobusiness ()
{
Using (trans T = new Trans ())
{
Try
{
D1 (t );
Throw new exception (); // if an exception exists, the system will roll back the drops.
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 didn't seem to have specified a database connection string. 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>

In fact, dbhelper only requires two strings. You can modify them and encrypt them...
Well, in this case, the dbhelper code is very simple and transparent, but a small package is made on ado.net, changing the coding method of the programmers who use it, remove some of the more "physical-level" programming concepts, such as connection open and close, so that programmers are more focused on business logic code writing, less dead point brain cells. In addition, the style and format of data operation code at the data operation layer are unified, which is easy to maintain ~~~
In addition, you can use the above Code at will without having to pay for your copyright. Hey hey, if you find any bugs or have a better data operation implementation method, please contact me.