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.

///

// open the database connection
///
Private Static void openconnection ()
{< br> If (Conn. state = connectionstate. closed)
{< br> // 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
{< br> Conn. open ();
}< br> catch (exception e)
{< br> throw new exception (E. message );
}< BR >}< br> //

// close the current database connection
//
Private Static void closeconnection ()
{< br> If (Conn. state = connectionstate. open)
Conn. close ();
Conn. dispose ();
comm. dispose ();
}< BR ># endregion
///
// execute the SQL query statement
///
/// input SQL statement
Public static void executesql (string sqlstr)
{< br> try
{< br> openconnection ();
comm. commandtype = commandtype. text;
comm. commandtext = sqlstr;
comm. executenonquery ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> finally
{< br> closeconnection ();
}< BR >}

///

// execute the Stored Procedure
///
/// stored procedure name
// sqlparameters set
Public static void executeporcedure (string procname, sqlparameter [] Coll)
{< br> try
{< br> openconnection ();
for (INT I = 0; I {< br> comm. parameters. add (Coll);
}< br> comm. commandtype = commandtype. storedprocedure;
comm. commandtext = procname;
comm. executenonquery ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> finally
{< br> comm. parameters. clear ();
closeconnection ();
}< BR >}

/// <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 ();
}
}

///

// execute the SQL query statement and return the first record of the first row, when the returned value is an object, you need to unpack the box-> Unbox
///
/// input SQL statement
// Object return value
Public static object executescalar (string sqlstr)
{< br> Object OBJ = new object ();
try
{< br> openconnection ();
comm. commandtype = commandtype. text;
comm. commandtext = sqlstr;
OBJ = comm. executescalar ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> finally
{< br> closeconnection ();
}< br> 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 (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 (string sqlstr)
{
Sqldataadapter da = new sqldataadapter ();
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 (string procname, sqlparametercollection parameters)
{
Sqldataadapter da = new sqldataadapter ();
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 (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;
}
}

}