Asp.net database connection code (SQL)

Copy codeThe Code is as follows:
Public class SqlOperation
{
# Region attributes
/// <Summary>
/// The connection string stored in Web. config
/// </Summary>
Protected static string connectionstring = System. Configuration. ConfigurationManager. ConnectionStrings ["hao"]. ConnectionString;
/// <Summary>
/// SqlConnection object
/// </Summary>
Protected static SqlConnection conn = new SqlConnection ();
/// <Summary>
/// SqlCommand object
/// </Summary>
Protected static SqlCommand comm = new SqlCommand ();
# Endregion

# Region internal functions
/// <Summary>
/// Open the database connection
/// </Summary>
Private static void ConnectionOpen ()
{
If (conn. State! = ConnectionState. Open)
{
Conn. Close ();
Conn. ConnectionString = connectionstring;
Comm. Connection = conn;
Try
{
Conn. Open ();
}
Catch (Exception ex)
{
Throw new Exception (ex. Message );
}
}
}

/// <Summary>
/// Close the database connection
/// </Summary>
Private static void ConnectionClose ()
{
Conn. Close ();
Conn. Dispose ();
Comm. Dispose ();
}

# Endregion

/// <Summary>
/// Execute the SQL statement
/// </Summary>
/// <Param name = "SqlString"> SQL statement to be executed </param>
Public static void ExecuteSQL (string SqlString)
{
Try
{
ConnectionOpen ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = SqlString;
Comm. ExecuteNonQuery ();
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "ProcedureName"> stored procedure name </param>
/// <Param name = "coll"> set of parameters required by the Stored Procedure </param>
Public static void ExecuteProcedure (string ProcedureName, params SqlParameter [] coll)
{
Try
{
ConnectionOpen ();
Comm. CommandType = CommandType. StoredProcedure;
Comm. CommandText = ProcedureName;
Comm. Parameters. Clear ();
For (int I = 0; I <coll. Length; I ++)
{
Comm. Parameters. Add (coll [I]);
}
Comm. ExecuteNonQuery ();
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
}

/// <Summary>
/// Execute the SQL query and return the first record of the first line. The object is returned. You need to unpack the box before use.
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Returns> returns the first record of the first row of the object type </returns>
Public static object ExecuteScalar (string SqlString)
{
Object obj = new object ();
Try
{
ConnectionOpen ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = SqlString;
Obj = comm. ExecuteScalar ();
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
Return obj;
}

/// <Summary>
/// Execute the SQL statement and process the transaction simultaneously
/// </Summary>
/// <Param name = "sqlstr"> SQL statement to be executed </param>
Public static void ExecuteTransactionSQL (string SqlString)
{
SqlTransaction trans;
Trans = conn. BeginTransaction ();
Comm. Transaction = trans;
Try
{
ConnectionOpen ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = SqlString;
Comm. ExecuteNonQuery ();
Trans. Commit ();
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
}

/// <Summary>
/// Execute the specified SQL query and return DataSet
/// </Summary>
/// <Param name = "sqlstr"> SQL statement to be executed </param>
/// <Returns> DataSet </returns>
Public static DataSet GetDataSetBySQL (string SqlString)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataSet ds = new DataSet ();
Try
{
ConnectionOpen ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = SqlString;
Da. SelectCommand = comm;
Da. Fill (ds );
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
Return ds;
}

/// <Summary>
/// Return DataSet through the Stored Procedure
/// </Summary>
/// <Param name = "ProcedureName"> stored procedure name </param>
/// <Param name = "coll"> SqlParameter set </param>
/// <Returns> DataSet </returns>
Public static DataSet GetDataSetByProcedure (string ProcedureName, params SqlParameter [] coll)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataSet ds = new DataSet ();
Try
{
ConnectionOpen ();
Comm. CommandType = CommandType. StoredProcedure;
Comm. Parameters. Clear ();
For (int I = 0; I <coll. Length; I ++)
{
Comm. Parameters. Add (coll [I]);
}
Comm. CommandText = ProcedureName;
Da. SelectCommand = comm;
Da. Fill (ds );
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
Return ds;
}


/// <Summary>
/// Return DataSet through the Stored Procedure
/// </Summary>
/// <Param name = "ProcedureName"> stored procedure name </param>
/// <Returns> DataSet </returns>
Public static DataSet GetDataSetByProcedure (string ProcedureName)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataSet ds = new DataSet ();
Try
{
ConnectionOpen ();
Comm. CommandType = CommandType. StoredProcedure;
Comm. CommandText = ProcedureName;
Comm. Parameters. Clear ();
Da. SelectCommand = comm;
Da. Fill (ds );
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
Return ds;
}

/// <Summary>
/// Return the DataTable of the specified SQL statement
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Returns> DataTable </returns>
Public static DataTable GetDataTableBySQL (string SqlString)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataTable dt = new DataTable ();
Try
{
ConnectionOpen ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = SqlString;
Da. SelectCommand = comm;
Da. Fill (dt );
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
Return dt;
}

/// <Summary>
/// Return the DataTable according to the stored procedure
/// </Summary>
/// <Param name = "ProcedureName"> stored procedure name </param>
/// <Param name = "coll"> SqlParameter set </param>
/// <Returns> DataTable </returns>
Public static DataTable GetDataTableByProcedure (string ProcedureName, params SqlParameter [] coll)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataTable dt = new DataTable ();
Try
{
ConnectionOpen ();
Comm. Parameters. Clear ();
Comm. CommandType = CommandType. StoredProcedure;
Comm. CommandText = ProcedureName;
For (int I = 0; I <coll. Length; I ++)
{
Comm. Parameters. Add (coll [I]);
}
Da. SelectCommand = comm;
Da. Fill (dt );
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
Return dt;
}

/// <Summary>
/// Return the DataTable according to the stored procedure
/// </Summary>
/// <Param name = "ProcedureName"> stored procedure name </param>
/// <Returns> DataTable </returns>
Public static DataTable GetDataTableByProcedure (string ProcedureName)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataTable dt = new DataTable ();
Try
{
ConnectionOpen ();
Comm. Parameters. Clear ();
Comm. CommandType = CommandType. StoredProcedure;
Comm. CommandText = ProcedureName;
Da. SelectCommand = comm;
Da. Fill (dt );
}
Catch (Exception ex)
{
Try
{
ConnectionClose ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Throw new Exception (ex. Message );
}
Finally
{
ConnectionClose ();
}
Return dt;
}
}