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