Step by step. NET three-layer architecture analysis III. SQLHelper Design

After the database is designed, we began to design SQLHelper, which is an SQL base class.

Connect to the Data source:

Private SqlConnection myConnection = null;
Private readonly string RETURNVALUE = "RETURNVALUE ";

Open the database connection.

Private void Open ()
{
// Open the database connection
If (myConnection = null)
{
// MyConnection = new SqlConnection (ConfigurationManager. ConnectionStrings ["ConnectionString"]. ConnectionString );
MyConnection = new SqlConnection (ConfigurationManager. etettings ["ConnectionString"]. ToString ());

}
If (myConnection. State = ConnectionState. Closed)
{
Try
{
/// Open the database connection
MyConnection. Open ();
}
Catch (Exception ex)
{

SystemError. CreateErrorLog (ex. Message );
}
Finally
{
/// Close the opened database connection
}
}
}

Close database connection

Public void Close ()
{
/// Determine whether the connection has been created
If (myConnection! = Null)
{
/// Determine whether the connection status is enabled
If (myConnection. State = ConnectionState. Open)
{
MyConnection. Close ();
}
}
}

Release resources

Public void Dispose ()
{
// Confirm whether the connection is closed
If (myConnection! = Null)
{
MyConnection. Dispose ();
MyConnection = null;
}
}

Executes the stored procedure without parameters and returns the int type.

Public int RunProc (string procName)
{
SqlCommand cmd = CreateProcCommand (procName, null );
Try
{
/// Execute the Stored Procedure
Cmd. ExecuteNonQuery ();
}
Catch (Exception ex)
{
/// Record error logs
SystemError. CreateErrorLog (ex. Message );
}
Finally
{
/// Close the database connection
Close ();
}

/// Return the Stored Procedure Parameter Value
Return (int) cmd. Parameters [RETURNVALUE]. Value;
}

Execute the stored procedure of input parameters and return int type

Public int RunProc (string procName, SqlParameter [] prams)
{
SqlCommand cmd = CreateProcCommand (procName, prams );
Try
{
/// Execute the Stored Procedure
Cmd. ExecuteNonQuery ();
}
Catch (Exception ex)
{
/// Record error logs
SystemError. CreateErrorLog (ex. Message );
}
& N