CopyCode The 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
///
// execute an SQL statement
///
/// SQL statement to be executed
Public static void executesql (string sqlstring)
{< br> try
{< br> connectionopen ();
comm. commandtype = commandtype. text;
comm. commandtext = sqlstring;
comm. executenonquery ();
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< BR >}
///
// execute the Stored Procedure
///
/// stored procedure name
// set of parameters required by the Stored Procedure
Public static void executeprocedure (string procedurename, params sqlparameter [] Coll)
{< br> try
{< br> connectionopen ();
comm. commandtype = commandtype. storedprocedure;
comm. commandtext = procedurename;
comm. parameters. clear ();
for (INT I = 0; I {< br> comm. parameters. add (Coll [I]);
}< br> comm. executenonquery ();
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< BR >}
///
// execute the SQL query and return the first record of the first row. The object is returned, unbox
///
/// input SQL statement
// returns the first record of the first row of the object type
Public static object executescalar (string sqlstring)
{< br> Object OBJ = new object ();
try
{< br> connectionopen ();
comm. commandtype = commandtype. text;
comm. commandtext = sqlstring;
OBJ = comm. executescalar ();
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< br> return OBJ;
}
///
// run the SQL statement, concurrent transaction processing
///
/// SQL statement to be executed
public static void executetransactionsql (string sqlstring)
{< br> sqltransaction trans;
trans = Conn. begintransaction ();
comm. transaction = trans;
try
{< br> connectionopen ();
comm. commandtype = commandtype. text;
comm. commandtext = sqlstring;
comm. executenonquery ();
trans. commit ();
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< BR >}
///
// execute the specified SQL query, return dataset
///
/// SQL statement to be executed
// / dataset
Public static dataset getdatasetbysql (string sqlstring)
{< br> sqldataadapter da = new sqldataadapter ();
dataset DS = new dataset ();
try
{< br> connectionopen ();
comm. commandtype = commandtype. text;
comm. commandtext = sqlstring;
da. selectcommand = comm;
da. fill (DS);
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< br> return Ds;
}
///
// return dataset through the Stored Procedure
///
// stored procedure name
// sqlparameter set
// dataset
Public static dataset getdatasetbyprocedure (string procedurename, params sqlparameter [] Coll)
{< br> sqldataadapter da = new sqldataadapter ();
dataset DS = new dataset ();
try
{< br> connectionopen ();
comm. commandtype = commandtype. storedprocedure;
comm. parameters. clear ();
for (INT I = 0; I {< br> comm. parameters. add (Coll [I]);
}< br> comm. commandtext = procedurename;
da. selectcommand = comm;
da. fill (DS);
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< br> return Ds;
}
///
// return dataset through the Stored Procedure
///
// stored procedure name
// dataset
Public static dataset getdatasetbyprocedure (string procedurename)
{< br> sqldataadapter da = new sqldataadapter ();
dataset DS = new dataset ();
try
{< br> connectionopen ();
comm. commandtype = commandtype. storedprocedure;
comm. commandtext = procedurename;
comm. parameters. clear ();
da. selectcommand = comm;
da. fill (DS);
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< br> return Ds;
}
//
// return the datatable of the specified SQL statement
///
// input SQL statement
// datatable
Public static datatable getdatatablebysql (string sqlstring)
{< br> sqldataadapter da = new sqldataadapter ();
datatable dt = new datatable ();
try
{< br> connectionopen ();
comm. commandtype = commandtype. text;
comm. commandtext = sqlstring;
da. selectcommand = comm;
da. fill (DT);
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< br> return DT;
}
///
// return the datatable according to the stored procedure
///
// stored procedure name
// sqlparameter set
// datatable
Public static datatable getdatatablebyprocedure (string procedurename, params sqlparameter [] Coll)
{< br> sqldataadapter da = new sqldataadapter ();
datatable dt = new datatable ();
try
{< br> connectionopen ();
comm. parameters. clear ();
comm. commandtype = commandtype. storedprocedure;
comm. commandtext = procedurename;
for (INT I = 0; I {< br> comm. parameters. add (Coll [I]);
}< br> da. selectcommand = comm;
da. fill (DT);
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< br> return DT;
}
///
// return the datatable according to the stored procedure
///
// stored procedure name
// datatable
Public static datatable getdatatablebyprocedure (string procedurename)
{< br> sqldataadapter da = new sqldataadapter ();
datatable dt = new datatable ();
try
{< br> connectionopen ();
comm. parameters. clear ();
comm. commandtype = commandtype. storedprocedure;
comm. commandtext = procedurename;
da. selectcommand = comm;
da. fill (DT);
}< br> catch (exception ex)
{< br> try
{< br> connectionclose ();
}< br> catch (exception e)
{< br> throw new exception (E. message);
}< br> throw new exception (ex. message);
}< br> finally
{< br> connectionclose ();
}< br> return DT;
}< BR >}