Custom public classes that you commonly use (tested)

Using System;
Using System. Data;
Using System. Data. SqlClient;
Using System. Configuration;
Using System. IO;
/*
* AUTHOR: ZHANGLEI
* Create date: 2007.1.5
* Function: The BLL layer encapsulates database operations.
* It also provides enough Parameter Combinations for executing stored procedures.
* DESCRIPTION: Method Overloading is used in this class.
* The ExecuteDataSet method implements four reloads in this class.
**/
Namespace job_17
{
/// <Summary>
/// Summary of job17.
/// </Summary>
Public class job17
{
Private readonly string P_Con = ConfigurationSettings. deleettings ["P_Con"]. ToString ();
Public job17 ()
{
//
// TODO: add the constructor logic here
//
}
# Region "Stored Procedure for executing any parameter combination"
/// <Summary>
/// Command preparation, which provides enough Parameter Combinations
/// An important method in this class
/// </Summary>
Public void preparecommand (SqlConnection myconn, SqlCommand mycomm, SqlTransaction trans, CommandType primitive type, string plain text, SqlParameter [] param) // note the parameters in
{
If (myconn. State! = ConnectionState. Open)
{
Myconn. Open ();
}
Mycomm. Connection = myconn;
Mycomm. CommandText = plain text;
If (trans! = Null)
{
Mycomm. Transaction = trans;
}
Mycomm. CommandType = primitive type;
If (param! = Null)
{
Foreach (SqlParameter parameter in param)
{
Mycomm. Parameters. Add (parameter );
}
}
}
/// <Summary>
/// The first method that returns the DataSet-type ExecuteDataSet
/// </Summary>
Public DataSet ExecuteDataSet (SqlConnection myconn, CommandType primitive type, string plain text, SqlParameter [] commandpara)
{
SqlCommand mycomm = new SqlCommand ();
Preparecommand (myconn, mycomm, (SqlTransaction) null, primitive type, plain text, commandpara );
SqlDataAdapter adpt = new SqlDataAdapter (mycomm); // call the preparecommand method above
DataSet ds = new DataSet ();
Adpt. Fill (ds );
Mycomm. Parameters. Clear ();
Return ds;
}
/// <Summary>
/// The second method that returns the DataSet-type ExecuteDataSet
/// Is implemented based on the first method, implementing the heavy load of the ExecuteDataSet Method
/// </Summary>
Public DataSet ExecuteDataSet (string connstr, CommandType primitive type, string plain text, SqlParameter [] partition para) // method overload, which is based on the first method to provide enough Parameter Combinations
{
Using (SqlConnection myconn = new SqlConnection (connstr ))
{
Return ExecuteDataSet (myconn, struct type, plain text, plain para );
}
}
/// <Summary>
/// The third method that returns the DataSet-type ExecuteDataSet
/// Provide a combination without parameters when using the Stored Procedure
/// </Summary>
Public DataSet ExecuteDataSet (SqlConnection myconn, CommandType publish type, string plain text)
{
Return ExecuteDataSet (myconn, struct type, plain text, (SqlParameter []) null );
}
/// <Summary>
/// The fourth method that returns the DataSet-type ExecuteDataSet
/// Provide a combination without parameters when using the Stored Procedure
/// </Summary>
Public DataSet ExecuteDataSet (string connstr, CommandType plain type, string plain text)
{
Return ExecuteDataSet (connstr, struct type, plain text, (SqlParameter []) null );
}
# Endregion
# Region "execute the SQL statement of the returned result"
/// <Summary>
/// The type of the returned result is DataTable.
/// </Summary>
Public DataTable ExecuteDataTablesql (string SQL)
{
SqlConnection myconn = new SqlConnection (P_Con );
SqlDataAdapter adpt = new SqlDataAdapter (SQL, myconn );
DataSet ds = new DataSet ();
Adpt. Fill (ds );
Return ds. Tables [0];
}
/// <Summary>
/// The type of the returned result is SqlDataReader.
/// </Summary>
Public SqlDataReader ExecuteDataReadersql (string SQL)
{
SqlConnection myconn = new SqlConnection (P_Con );
SqlDataReader dr = null;
SqlCommand mycomm = new SqlCommand (SQL, myconn );
Try
{
Myconn. Open ();
Dr = mycomm. ExecuteReader ();
}
Catch
{
// StreamWriter sw = new StreamWriter (@ "c: \ err.txt", true, System. Text. Encoding. GetEncoding ("GB2312 "));
// Sw. writeLine ("======================== error message ======== ========================== ");
// Sw. WriteLine ("error time:" + DateTime. Now. ToString () + "");
// Sw. WriteLine (ex. ToString ());
// Sw. Close ();
Throw;
}
Return dr;
}
/// <Summary>
/// The returned result type is DataSet.
/// </Summary>
Public DataSet ExecutesqlDS (string SQL)
{
SqlConnection myconn = new SqlConnection (P_Con );
SqlDataAdapter adpt = new SqlDataAdapter (SQL, myconn );
DataSet ds = new DataSet ();
Adpt. Fill (ds );
Return ds;
}
# Endregion
# Region "execute an SQL statement that does not return results"
/// <Summary>
/// Execute an SQL statement that does not return results
/// </Summary>
Public void ExecuteNonsql (string SQL)
{
SqlConnection myconn = new SqlConnection (P_Con );
SqlCommand mycomm = new SqlCommand (SQL, myconn );
Try
{
Myconn. Open ();
Mycomm. ExecuteNonQuery ();
Myconn. Close ();
}
Catch (Exception e)
{
// StreamWriter sw = new StreamWriter (@ "c: \ err.txt", true, System. Text. Encoding. GetEncoding ("GB2312 "));
// Sw. writeLine ("======================== error message ======== ========================== ");
// Sw. WriteLine ("error time:" + DateTime. Now. ToString () + "");
// Sw. WriteLine (e. ToString ());
// Sw. Close ();
Throw new Exception (e. Message, e );
}
}
# Endregion
# Region "enable SQL statements with transactions such as (insert, update )"
/// <Summary>
/// Use Transaction Processing
/// </Summary>
Public void ExecuteTransql (string SQL)
{
SqlConnection myconn = new SqlConnection (P_Con );
SqlCommand mycomm = new SqlCommand (SQL, myconn );
SqlTransaction trans = null;
Try
{
Myconn. Open ();
Trans = myconn. BeginTransaction ();
Mycomm. Transaction = trans;
Mycomm. ExecuteNonQuery ();
Trans. Commit ();
}
Catch (Exception ex)
{
Trans. Rollback ();
Throw new Exception (ex. Message, ex );
}
}
# Endregion
}
}