ASP. NET data handler class

Source: Internet
Author: User

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;
}
}

}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.