Asp.net database connection and datatable class

Using system;
Using system. Data;
Using system. configuration;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Web. UI. htmlcontrols;
Using system. Data. sqlclient;
/// <Summary>
/// Summary of sqlconnections
/// </Summary>
Namespace system. mysqlconnection
{
Public class sqlconnections
{
// The number of connections currently.
Static int COUNT = 0;
// Maximum number of connections
Static int maxcount = 30;
Static sqlconnection [] sqlconns = new sqlconnection [maxcount];
// Create yourself
Static sqlconnections myconn = new sqlconnections ();
// Initialization
Private sqlconnections ()
{
For (INT I = 0; I <maxcount; I ++)
{
Sqlconns [I] = new sqlconnection (system. configuration. configurationsettings. deleettings ["adostr"]. tostring ());
}
}
// Obtain different tables and fields based on the conditions.
Public static datatable getdatatable (string sqltest, string tablename)
{
Try
{
Dataset myds = new dataset ();
Sqldataadapter myadapater = new sqldataadapter (sqltest, sqlconnections. getsqlconnection ());
Myadapater. Fill (myds, tablename );
Return myds. Tables [tablename];
}
Catch (sqlexception sqle)
{
Return NULL;
}
}
// Obtain the database connection
Public static sqlconnection getsqlconnection ()
{
Try
{
Try
{
Sqlconns [count]. Close ();
}
Catch (exception EEx)
{
}
Return sqlconns [count];
}
Finally
{
// The current number is automatically added ..
If (COUNT = (maxcount-1 ))
{
Count = 0;
}
Else
{
Count ++;
}
}
}
// Return a parameter
Public static string getnumonefield (string sqltxt)
{
Sqlconnection sqlconntemp = sqlconnections. getsqlconnection ();
Try
{
Sqlcommand sqlcommt = new sqlcommand (sqltxt, sqlconntemp );
Sqlconntemp. open ();
Return sqlcommt. executescalar (). tostring ();
}
Catch (exception sqle)
{
Return "no ";
}
Finally
{
Sqlconntemp. Close ();
}
}
// Return a parameter
Public static string getnumonefield (sqlconnection sqlconntemp, sqltransaction tempsqltran, string sqltxt)
{
Try
{
Sqlcommand sqlcommt = new sqlcommand (sqltxt, sqlconntemp );
Sqlcommt. Transaction = tempsqltran;
Return sqlcommt. executescalar (). tostring ();
}
Catch (exception sqle)
{
Return "no ";
}
Finally
{
// Sqlconntemp. Close ();
}
}
/// <Summary>
/// Do, modify, delete,
/// </Summary>
/// <Param name = "sqltxt"> executed SQL statement </param>
/// <Returns> Number of rows affected by returned results </returns>
Public static int updateordelete (string sqltxt)
{
Sqlconnection sqlconntemp = sqlconnections. getsqlconnection ();
Try
{
Sqlcommand sqlcommt = new sqlcommand (sqltxt, sqlconntemp );
Sqlconntemp. open ();
Return sqlcommt. executenonquery ();
}
Catch (sqlexception sqle)
{
Return 0;
}
Finally
{
// Sqlconntemp. Close ();
}
}
Public static int updateordelete (commandtype _ commandtype, sqlparameter [] _ sqlparas, string sqltxt)
{
Sqlconnection sqlconntemp = sqlconnections. getsqlconnection ();
Try
{
Sqlcommand sqlcommt = new sqlcommand (sqltxt, sqlconntemp );
Sqlcommt. commandtype = _ commandtype;
Sqlcommt. Parameters. Clear ();
For (int A = 0; A <_ sqlparas. length; A ++)
{
Sqlcommt. Parameters. Add (_ sqlparas [a]);
}
Sqlconntemp. open ();
Return sqlcommt. executenonquery ();
}
Catch (sqlexception sqle)
{
Return 0;
}
Finally
{
// Sqlconntemp. Close ();
}
}
/// <Summary>
/// Add, delete, and modify the Data Execution method with the transaction (warning: This method must be manually switched on and off, and there is also a manual transaction commit. Otherwise, data cannot be updated)
/// </Summary>
/// <Param name = "_ sqlconn"> database connection object </param>
/// <Param name = "_ sqltran"> transaction object </param>
/// <Param name = "sqltxt"> executed SQL statement </param>
/// <Returns> </returns>
Public static int updateordelete (sqlconnection _ sqlconn, sqltransaction _ sqltran, string sqltxt)
{
Sqlconnection sqlconntemp = _ sqlconn;
Try
{
Sqlcommand sqlcommt = new sqlcommand (sqltxt, sqlconntemp );
Sqlcommt. Transaction = _ sqltran;
Sqlconntemp. open ();
Return sqlcommt. executenonquery ();
}
Catch (sqlexception sqle)
{
Return 0;
}
Finally
{
// Sqlconntemp. Close ();
}
}
Public static int updateordelete (sqlconnection _ sqlconn, sqltransaction _ sqltran, commandtype _ commandtype, sqlparameter [] _ sqlparas, string sqltxt)
{
Sqlconnection sqlconntemp = _ sqlconn;
Try
{
Sqlcommand sqlcommt = new sqlcommand (sqltxt, sqlconntemp );
Sqlcommt. Transaction = _ sqltran;
Sqlcommt. commandtype = _ commandtype;
Sqlcommt. Parameters. Clear ();
For (int A = 0; A <_ sqlparas. length; A ++)
{
Sqlcommt. Parameters. Add (_ sqlparas [a]);
}
Return sqlcommt. executenonquery ();
}
Catch (sqlexception sqle)
{
Return 0;
}
Finally
{
// Sqlconntemp. Close ();
}
}
/// <Summary>
/// Saved queries
/// </Summary>
/// <Param name = "storedprocedurename"> name of the stored procedure </param>
/// <Param name = "sqlparas"> All parameters </param>
/// <Param name = "tablename"> table name </param>
/// <Returns> result set of the datatable </returns>
Public static datatable getdatatable (string storedprocedurename, sqlparameter [] sqlparas, string tablename)
{
Try
{
Dataset myds = new dataset ();
Sqldataadapter myadapater = new sqldataadapter (storedprocedurename, sqlconnections. getsqlconnection ());
Myadapater. selectcommand. commandtype = commandtype. storedprocedure;
For (INT I = 0; I <sqlparas. length; I ++)
{
// If (myadapater. selectcommand. Parameters. Contains (sqlparas [I])
// Myadapater. selectcommand. Parameters. removeat (sqlparas [I]. parametername );
Myadapater. selectcommand. Parameters. Add (sqlparas [I]);
}
Myadapater. Fill (myds, tablename );
Myadapater. selectcommand. Parameters. Clear ();
Myadapater. Dispose ();
Return myds. Tables [tablename];
}
Catch (sqlexception sqle)
{
Return NULL;
}
}
}
}