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

}

}