[★]. Net class to be called for database access

Using system;
Using system. componentmodel;
Using system. collections;
Using system. diagnostics;
Using system. Data;
Using system. Data. sqlclient;
Using system. configuration;
Using system. Web. sessionstate;
Using system. Web;

Namespace utilities
{

/// <Summary>
/// Dbaccess class, that is, the class to be called during Database Access
/// </Summary>
Public class dbasebs
{
// Retrieve the database connection string
Public static readonly string constr = configurationsettings. deleetask[ "connectionstring"];

/// <Summary>
/// Obtain the database connection object
/// </Summary>
/// <Returns> database connection object </returns>
Public static sqlconnection getconobject ()
{
Return new sqlconnection (constr );
}

/// <Summary>
/// Execute the Stored Procedure for operating the database
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Returns> Number of rows affected by execution of the stored procedure </returns>
Public static int executenonquery (string procname)
{
Sqlcommand cmd = new sqlcommand ();

Using (sqlconnection con = getconobject ())
{
Createcommand (CMD, Con, null, procname, null );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Cmd. Dispose ();
Return val;
}
}

/// <Summary>
/// Execute the Stored Procedure for operating the database
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "Param parms"> parameters used by the Stored Procedure </param>
/// <Returns> Number of rows affected by execution of the stored procedure </returns>
Public static int executenonquery (string procname, sqlparameter [] extends parms)
{
Sqlcommand cmd = new sqlcommand ();

Using (sqlconnection con = getconobject ())
{
Createcommand (CMD, Con, null, procname, callback parms );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Cmd. Dispose ();
Return val;
}
}

///

// execute the Stored Procedure for operating the database (transaction control)
///
/// transaction object associated with the database connection
// / stored procedure name
// parameters used by stored procedure
// Number of rows affected by execution of the stored procedure
Public static int executenonquery (sqltransaction trans, string procname, sqlparameter [] extends parms)
{< br> sqlcommand cmd = new sqlcommand ();
createcommand (CMD, trans. connection, trans, procname, partition parms);
int val = cmd. executenonquery ();
cmd. parameters. clear ();
cmd. dispose ();
return val;
}

/// <Summary>
/// Read the dataset and return it as a datareader
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "Param parms"> parameters used by the Stored Procedure </param>
/// <Returns> datareader object </returns>
Public static sqldatareader executereader (string procname, sqlparameter [] extends parms)
{
Sqlcommand cmd = new sqlcommand ();
Sqlconnection con = getconobject ();

Try
{
Createcommand (CMD, Con, null, procname, callback parms );
Sqldatareader DR = cmd. executereader (commandbehavior. closeconnection );
Cmd. Parameters. Clear ();
Return Dr;
}
Catch (exception ex)
{
Cmd. Dispose ();
Close (CON );
Throw ex;
}
}

/// <Summary>
/// Read the dataset and return it as a datareader
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Returns> datareader object </returns>
Public static sqldatareader executereader (string procname)
{
Sqlcommand cmd = new sqlcommand ();
Sqlconnection con = getconobject ();

Try
{
Createcommand (CMD, Con, null, procname, null );
Sqldatareader DR = cmd. executereader (commandbehavior. closeconnection );
Cmd. Parameters. Clear ();
Return Dr;
}
Catch (exception ex)
{
Cmd. Dispose ();
Close (CON );
Throw ex;
}
}

/// <Summary>
/// Read the dataset and return it as a dataset.
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "Param parms"> parameters used by the Stored Procedure </param>
/// <Returns> DataSet object </returns>
Public static dataset executedataset (string procname, sqlparameter [] extends parms)
{
Sqlcommand cmd = new sqlcommand ();
Sqlconnection con = getconobject ();
Dataset DS = new dataset ();

try
{< br> createcommand (CMD, Con, null, procname, callback parms );
sqldataadapter da = new sqldataadapter ();
da. selectcommand = cmd;
da. fill (DS);
cmd. parameters. clear ();
return Ds;
}< br> catch (exception ex)
{< br> throw ex;
}< br> finally
{< br> cmd. dispose ();
close (CON);
}< BR >}< br>

//

// read the dataset, returned in dataset format
///
/// stored procedure name
/ // datareader object
Public static dataset executedataset (string procname)
{< br> sqlcommand cmd = new sqlcommand ();
sqlconnection con = getconobject ();
dataset DS = new dataset ();

try
{< br> createcommand (CMD, Con, null, procname, null );
sqldataadapter da = new sqldataadapter ();
da. selectcommand = cmd;
da. fill (DS);
cmd. parameters. clear ();
return Ds;
}< br> catch (exception ex)
{< br> throw ex;
}< br> finally
{< br> cmd. dispose ();
close (CON);
}< BR >}

/// <Summary>
/// Create a database and execute the command
/// </Summary>
/// <Param name = "cmd"> database execution command object </param>
/// <Param name = "con"> database connection object </param>
/// <Param name = "trans"> Database Transaction object </param>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "Param parms"> parameter array used by the Stored Procedure </param>
Public static void createcommand (sqlcommand cmd, sqlconnection con, sqltransaction trans, string procname, sqlparameter [] cmdparms)
{
If (con. State! = Connectionstate. open)
Con. open ();

Cmd. Connection = con;
Cmd. commandtext = procname;

If (trans! = NULL)
{
Cmd. Transaction = trans;
}

Cmd. commandtype = commandtype. storedprocedure;

If (partition parms! = NULL)
{< br> foreach (sqlparameter parm in parallel parms)
cmd. parameters. add (parm);
}< BR >}< br>
///

// enter parameters.
///
/// parameter name
/// parameter data type
// parameter size
// parameter value
// input parameter
Public static sqlparameter makeinparam (string paramname, sqldbtype dbtype, int size, object invalue)
{< br> return makeparam (paramname, dbtype, size, parameterdirection. input, invalue);
}

///

// prepare the output parameters.
///
/// parameter name
/// parameter data type
// parameter size
// output parameter
Public static sqlparameter makeoutparam (string paramname, sqldbtype dbtype, int size)
{< br> return makeparam (paramname, dbtype, size, parameterdirection. output, null);
}

/// <Summary>
/// Prepare the parameters of the stored procedure.
/// </Summary>
/// <Param name = "paramname"> parameter name </param>
/// <Param name = "dbtype"> parameter data type </param>
/// <Param name = "size"> parameter size </param>
/// <Param name = "direction"> parameter direction </param>
/// <Param name = "invalue"> parameter value </param>
/// <Returns> input or output parameters </returns>
Public static sqlparameter makeparam (string paramname, sqldbtype dbtype, int size, parameterdirection direction, object invalue)
{
Sqlparameter Param;

If (size> 0)
Param = new sqlparameter (paramname, dbtype, size );
Else
Param = new sqlparameter (paramname, dbtype );

Param. Direction = direction;

If (! (Direction = parameterdirection. Output & invalue = NULL ))
Param. value = invalue;

Return Param;
}
 
/// <Summary>
/// Close the database connection
/// </Summary>
/// <Param name = "myconn"> database connection object </param>
Public static void close (sqlconnection myconn)
{
If (myconn! = NULL) & (myconn. State = connectionstate. Open ))
{
Myconn. Close ();
}
}

///

// directly execute the SQL statement to return the dataset
///
// SQL statement
// queried dataset
Public static dataset executedatasetsql (string strsql)
{< br> sqlcommand cmd = new sqlcommand ();
sqlconnection con = getconobject ();
dataset DS = new dataset ();
sqldataadapter da = NULL;

Try
{
DA = new sqldataadapter (strsql, con );
Da. Fill (DS );
Return Ds;
}
Catch (exception ex)
{
Throw ex;
}
Finally
{
If (Da! = NULL)
Da. Dispose ();

Cmd. Dispose ();
Close (CON );
}
}

/// <Summary>
/// Run the SQL statement directly
/// </Summary>
/// <Param name = "trans"> transaction </param>
/// <Param name = "strsql"> SQL statement </param>
Public static void executesql (sqltransaction trans, string strsql)
{
Sqlcommand cmd = new sqlcommand ();
Sqlconnection con = getconobject ();

Try
{
If (con. State! = Connectionstate. open)
Con. open ();

Cmd. Connection = trans. connection;
Cmd. Transaction = trans;
Cmd. commandtext = strsql;

cmd. executenonquery ();
}< br> catch (exception ex)
{< br> throw ex;
}< br> finally
{< br> cmd. dispose ();
close (CON);
}< BR >}