DbHelper data operation class in asp.net

Source: Internet
Author: User
Tags commit datetime int size rollback
The code is as follows: Copy 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 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 parameters to region
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)
    {
DbParameter 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

# Region execution
Public DataSet ExecuteDataSet (DbCommand cmd)
    {
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)
    {
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)
    {
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 transactions
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
    {
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 ();
        }
    }
}
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. Input parameters, output parameters, and return values

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. Transaction usage

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 );

}

Database Connection string:

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.