ASP. NET data handler class

Using System;
Using System. Data;
Using System. Data. SqlClient;

Namespace SysClassLibrary
{
/// <Summary>
/// Summary of DataAccess.
/// <Description> data processing base class. Call method: DataAccess. dataSet (string) sqlstr); or DataAccess. dataSet (string) sqlstr, ref DataSet ds); </description>
/// </Summary>
Public class DataAccess
{
# Region attributes
Protected static SqlConnection conn = new SqlConnection ();
Protected static SqlCommand comm = new SqlCommand ();
# Endregion
Public DataAccess ()
{
// Init ();
}
# The static method of region internal functions does not execute the DataAccess () constructor.

/// <Summary>
/// Open the database connection
/// </Summary>
Private static void openConnection ()
{
If (conn. State = ConnectionState. Closed)
{
// SysConfig. ConnectionString is the connection string in the System Configuration class, for example, "server = localhost; database = databasename; uid = sa; pwd = ;"

Conn. ConnectionString = SysConfig. ConnectionString;
Comm. Connection = conn;
Try
{
Conn. Open ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
}
}
/// <Summary>
/// Close the current database connection
/// </Summary>
Private static void closeConnection ()
{
If (conn. State = ConnectionState. Open)
Conn. Close ();
Conn. Dispose ();
Comm. Dispose ();
}
# Endregion
/// <Summary>
/// Execute the SQL query statement
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
Public static void ExecuteSql (string sqlstr)
{
Try
{
OpenConnection ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = sqlstr;
Comm. ExecuteNonQuery ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
CloseConnection ();
}
}

/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "procName"> stored procedure name </param>
/// <Param name = "coll"> SqlParameters set </param>
Public static void ExecutePorcedure (string procName, SqlParameter [] coll)
{
Try
{
OpenConnection ();
For (int I = 0; I <coll. Length; I ++)
{
Comm. Parameters. Add (coll );
}
Comm. CommandType = CommandType. StoredProcedure;
Comm. CommandText = procName;
Comm. ExecuteNonQuery ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
Comm. Parameters. Clear ();
CloseConnection ();
}
}

/// <Summary>
/// Execute the stored procedure and return the dataset
/// </Summary>
/// <Param name = "procName"> stored procedure name </param>
/// <Param name = "coll"> SqlParameter set </param>
/// <Param name = "ds"> DataSet </param>
Public static void ExecutePorcedure (string procName, SqlParameter [] coll, ref DataSet ds)
{
Try
{
SqlDataAdapter da = new SqlDataAdapter ();
OpenConnection ();
For (int I = 0; I <coll. Length; I ++)
{
Comm. Parameters. Add (coll );
}
Comm. CommandType = CommandType. StoredProcedure;
Comm. CommandText = procName;

Da. SelectCommand = comm;
Da. Fill (ds );
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
Comm. Parameters. Clear ();
CloseConnection ();
}
}

/// <Summary>
/// Execute the SQL query statement and return the first record of the first line. The returned value is the Unbox operation required when the object is used-> Unbox
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Returns> object return value </returns>
Public static object ExecuteScalar (string sqlstr)
{
Object obj = new object ();
Try
{
OpenConnection ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = sqlstr;
Obj = comm. ExecuteScalar ();
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
CloseConnection ();
}
Return obj;
}

/// <Summary>
/// Execute the SQL query statement and process the transaction simultaneously
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
Public static void ExecuteSqlWithTransaction (string sqlstr)
{
SqlTransaction trans;
Trans = conn. BeginTransaction ();
Comm. Transaction = trans;
Try
{
OpenConnection ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = sqlstr;
Comm. ExecuteNonQuery ();
Trans. Commit ();
}
Catch
{
Trans. Rollback ();
}
Finally
{
CloseConnection ();
}
}

/// <Summary>
/// Return the SqlDataReader of the specified SQL statement. Note that this object should be closed after use and closeConnection () will be automatically called to close the database connection.
/// Method to close the database connection
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Returns> SqlDataReader object </returns>
Public static SqlDataReader dataReader (string sqlstr)
{
SqlDataReader dr = null;
Try
{
OpenConnection ();
Comm. CommandText = sqlstr;
Comm. CommandType = CommandType. Text;
Dr = comm. ExecuteReader (CommandBehavior. CloseConnection );
}
Catch
{
Try
{
Dr. Close ();
CloseConnection ();
}
Catch
{
}
}
Return dr;
}
/// <Summary>
/// Return the SqlDataReader of the specified SQL statement. Note that this object should be closed after use and closeConnection () will be automatically called to close the database connection.
/// Method to close the database connection
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Param name = "dr"> input ref DataReader object </param>
Public static void dataReader (string sqlstr, ref SqlDataReader dr)
{
Try
{
OpenConnection ();
Comm. CommandText = sqlstr;
Comm. CommandType = CommandType. Text;
Dr = comm. ExecuteReader (CommandBehavior. CloseConnection );
}
Catch
{
Try
{
If (dr! = Null &&! Dr. IsClosed)
Dr. Close ();
}
Catch
{
}
Finally
{
CloseConnection ();
}
}
}

/// <Summary>
/// Return the DataSet of the specified SQL statement
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Returns> DataSet </returns>
Public static DataSet dataSet (string sqlstr)
{
DataSet ds = new DataSet ();
SqlDataAdapter da = new SqlDataAdapter ();
Try
{
OpenConnection ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = sqlstr;
Da. SelectCommand = comm;
Da. Fill (ds );
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
CloseConnection ();
}
Return ds;
}

/// <Summary>
/// Return the DataSet of the specified SQL statement
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Param name = "ds"> input reference DataSet object </param>
Public static void dataSet (string sqlstr, ref DataSet ds)
{
SqlDataAdapter da = new SqlDataAdapter ();
Try
{
OpenConnection ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = sqlstr;
Da. SelectCommand = comm;
Da. Fill (ds );
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
CloseConnection ();
}
}
/// <Summary>
/// Return the DataTable of the specified SQL statement
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Returns> DataTable </returns>
Public static DataTable dataTable (string sqlstr)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataTable datatable = new DataTable ();
Try
{
OpenConnection ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = sqlstr;
Da. SelectCommand = comm;
Da. Fill (datatable );
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
CloseConnection ();
}
Return datatable;
}

/// <Summary>
/// Execute the specified SQL statement and assign a value to the input able.
/// </Summary>
/// <Param name = "sqlstr"> input SQL statement </param>
/// <Param name = "dt"> ref DataTable dt </param>
Public static void dataTable (string sqlstr, ref DataTable dt)
{
SqlDataAdapter da = new SqlDataAdapter ();
Try
{
OpenConnection ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = sqlstr;
Da. SelectCommand = comm;
Da. Fill (dt );
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
CloseConnection ();
}
}
/// <Summary>
/// Execute the stored procedure with parameters and return the data set
/// </Summary>
/// <Param name = "procName"> stored procedure name </param>
/// <Param name = "parameters"> SqlParameterCollection input parameters </param>
/// <Returns> </returns>
Public static DataTable dataTable (string procName, SqlParameterCollection parameters)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataTable datatable = new DataTable ();

Try
{
OpenConnection ();
Comm. Parameters. Clear ();
Comm. CommandType = CommandType. StoredProcedure;
Comm. CommandText = procName;
Foreach (SqlParameter para in parameters)
{
SqlParameter p = (SqlParameter) para;
Comm. Parameters. Add (p );
}
Da. SelectCommand = comm;
Da. Fill (datatable );
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
CloseConnection ();
}
Return datatable;
}

Public static DataView dataView (string sqlstr)
{
SqlDataAdapter da = new SqlDataAdapter ();
DataView dv = new DataView ();
DataSet ds = new DataSet ();
Try
{
OpenConnection ();
Comm. CommandType = CommandType. Text;
Comm. CommandText = sqlstr;
Da. SelectCommand = comm;
Da. Fill (ds );
Dv = ds. Tables [0]. DefaultView;
}
Catch (Exception e)
{
Throw new Exception (e. Message );
}
Finally
{
CloseConnection ();
}
Return dv;
}
}

}