/ / In order to avoid a timely problem with the database connection is closed, you need to keep the connection to control the closure of the connection.
// Use .NET must pay attention to closing the connected connection and resources, because .NET's garbage collection is not ideal, and .NET programs cannot take up more than 1G memory
Using system;
Using system.data;
Using system.threading;
Using system.collections;
Using system.runtime.remoting.lifetime;
Using system.data.sqlclient;
Namespace Webindex
{
///
A summary description of /// DataAccess.
///
///
Public Class Sqlconn
{
#region attribute
Public struct OCONNtion
{
Public SqlConnection CONN;
Public Bool Connlock;
Public DateTime Outtime;
Public String Sqlstr;
Public String Error;
}
Public struct sqlerror
{
Public String Sqlstr;
PUBLIC STRING Errinfo;
Public DateTime Ertime;
}
Public static sqlerror [] sqlerror;
Public static oconntion [] sconn;
Public static queue open = new queue ();
Public static queue close = new queue ();
Public static int nConn = 100;
Public static int mastConn = 0;
Public static int exconn = 0;
Public static int errorconn = 0;
Public static int tererror = 0;
Public static int okconn = 0;
Protected static bool format = true;
#ndregion
Public sqlconn ()
{
}
#Region internal function database connection pool
Private static int getopened ()
{
int RET = -1;
INT peek = 0;
For (int i = 0; i { IF (OPENED.count == 0) Return -1; Peek = (int) Opened.peek (); IF (sconn [peek] .conn.state == connectionState.closed) { // Close connection Back to close queue Sconn [peek] .conn.dispose (); Sconn [peek] .connlock = false; Closed.enqueue (OPENED.DEQUEUE ()); CONTINUE; } IF (sconn [peek] .connlock == false) { // Unconnected connection Opened.enqueue (OPENED.DEQUE ()); RET = peek; Break; } Else { OPENED.Enqueue (OPENED.DEQUE ()); } } Return Ret; } Private static int getClosed () { int RET = -1; IF (closed.count> 0) { Ret = (int) closed.dequeue (); Opened.enqueue (re); } Return Ret; } Private static int getConn () { int RET = -1; LOCK (Typeof (Sqlconn)) { IF (Format) { // format data Sconn = New OCONNTION [NCONN]; SQLERROR = New SQLERROR [NCONN]; For (int f = 0; f { Sconn [f] .conn = new sqlConnection (); Sconn [f] .connlock = false; Sconn [f] .sqlstr = ""; Sconn [f]. Error = "" //sclosed.push (NCONN-F-1); Closed.enqueue (f); } Format = false; } Ret = getopened (); IF (RET <0) RET = getClosed (); IF (RET <0) { // Didn't find available connections, forcibly allocate one! MastConn ; Ret = (int) Opened.dequeue (); Opened.enqueue (re); } Sconn [Ret] .connlock = true; } OKCONN ; EXCONN = RET; IF (Sconn [Ret] .conn.state! = connectionState.closed) Sconn [Ret] .conn.close (); Sconn [Ret] .conn.dispose (); Return Ret; } Private Static Void FreeConn (int FreeConn) { IF (sconn [freeconn] .conn.state == connectionState.Open) Sconn [freeConn] .conn.close (); Sconn [freeConn] .conn.dispose (); Sconn [freeConn] .connlock = false; } Public static int newconn () { Return getConn (); } Public Static Void CloseConn (INT i) { FREECONN (I); } Public Static Void LoGerror (Exception E, STRING SQL) { IF (Othererror> 99) OthersRROR = 0; SQLERROR [Others] .sqlstr = SQL; SQLERROR [OTHERROR] .ERRINFO = E.toString (); SQLERROR [Others] .ERRTIME = system.datetime.now; Othererror ; } #ndregion #Region does not execute an Ooconn () constructor /// // / Open the database connection /// Private static void OpenoConnection (int iconn) { IF (sconn [iconn] .conn.state! = connectionState.Open) { / / Judgment whether it is a training server IF (sconn [iconn] .conn.state! = connectionState.closed) sconn [iconn] .conn.close (); Sconn [iconn] .conn.connectionstring = system.configuration.configurationSettings.appsettings ["SqlConnectionstring"]; Try { Sconn [iconn] .conn.open (); } Catch (Exception E) { ErrorConn ; Sconn [iconn]. Error = E.toTOString (); Throw new Exception (E.MESSAGE); } } } /// // / Open the database connection /// Private static void OpenOconnection (SqlConnection Conn) { OKCONN ; IF (conn.state! = connectionState.Open) { / / Judgment whether it is a training server IF (conn.state! = connectionState.closed) Conn.close (); Conn.connectionstring = system.configuration.configurationSettings.appsettings ["SqlConnectionstring"]; Try { Cn.open (); } Catch (Exception E) { ErrorConn ; Throw new Exception (E.MESSAGE); } } } /// /// Close the current database connection /// Private static void closeoconnection () { } #ndregion /// / / / Execute SQL query statement /// /// Inferred SQL statement Public Static Void ExecuteSQL (String SQLSTR) { INT i = 0; Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = SQLSTR; Openoconnection (i); Ocomm.commandtype = commandtype.text; Ocomm.commandtext = SQLSTR; Ocomm.executenonQuery (); Ocomm.dispose (); } Catch (Exception E) { ErrorConn ; LOGERROR (E, SQLSTR); Sconn [i]. Error = E.toTOString (); throw new exception (E.MESSAGE); } Finally { FREECONN (I); } } /// /// Execute a stored procedure /// /// Store process name /// SQLParameters collection Public Static Void ExecutePorcedure (SQLPARAMETER [] COLL) { INT i = 0; Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = procname; Openoconnection (i); For (int J = 0; j { Ocomm.Parameters .add (COLL [J]); } Ocomm.commandtype = commandtype.storedProcedure; Ocomm.commandtext = procname; Ocomm.executenonQuery (); Ocomm.Parameters.clear (); Ocomm.dispose (); } Catch (Exception E) { ErrorConn ; LOGERROR (E, Procname); Sconn [i]. Error = E.toString (); Throw new Exception (E.MESSAGE); } Finally { FREECONN (I); } } /// /// Execute SQL query statements and return to SQLDataAdapter /// /// connection number /// Inferred SQL statement Public Static SqlDataAdapter DataAdapter (SqlConnection CONN, STRING SQLSTR) { SqldataAdapter Ret = NULL; Try { SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = conn; OpenoConnection (conn); Ret = New SqldataAdapter (SQLSTR, CONN); Ocomm.dispose (); } Catch (Exception E) { LOGERROR (E, SQLSTR); CONN.CLOSE (); Conn.dispose (); Throw new Exception (E.MESSAGE); } Finally { } Return Ret; } /// /// Execute SQL query statements and return to SQLDataAdapter /// /// connection number /// Inferred SQL statement public static sqldataadapter DataAdapter (int NumConn, String Sqlstr) { SqldataAdapter Ret = NULL; INT i = 0; Try { i = Numconn; SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = SQLSTR; Openoconnection (i); RET = New SqldataAdapter (Sqlstr, Sconn [i] .conn); Ocomm.dispose (); } Catch (Exception E) { ErrorConn ; LOGERROR (E, SQLSTR); Sconn [i]. Error = E.toString (); FREECONN (I); Throw new Exception (E.MESSAGE); } Finally { } Return Ret; } /// / / / Execute the SQL query statement and return the first record of the first line, the return value needs to be removed when using Object. -> Unbox /// /// Inferred SQL statement /// Public Static Object ExecuteScalar (String SQLSTR) { Object obj = new object (); INT i = 0; Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = SQLSTR; Openoconnection (i); Ocomm.commandtype = commandtype.text; Ocomm.commandtext = SQLSTR; Obj = Ocomm.executescalar (); Ocomm.dispose (); } Catch (Exception E) { ErrorConn ; LOGERROR (E, SQLSTR); Sconn [i]. Error = E.toString (); Throw new Exception (E.MESSAGE); } Finally { FREECONN (I); } Return Obj; } /// / / / Execute a SQL query statement while performing transaction processing /// /// Inferred SQL statement Public Static Void ExecuteSQLWITHTRANSACTION (STRING SQLSTR) { Sqltransaction trans; INT i = 0; i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = SQLSTR; OpenOConnection (i); TRANS = SCONN [I] .conn.begintransaction (); Ocomm.Transaction = TRANS; Try { Ocomm.commandtype = commandtype.text; Ocomm.commandtext = SQLSTR; Ocomm.executenonQuery (); TRANS.COMMIT (); } Catch (Exception E) { ErrorConn ; LOGERROR (E, SQLSTR); Sconn [i]. Error = E.toString (); TRANS. ROLLBACK (); Throw new Exception (E.MESSAGE); } Finally { Ocomm.dispose (); FREECONN (I); } } /// /// Returns the SQLDATAREADER of the specified SQL statement, please note that the object is turned off after use, and will automatically call closeoconnection () to turn off the database connection. // / Method Close the database connection /// /// Inferred SQL statement /// Public Static SqlDataReader DataReader (SqlConnection CONN, STRING SQLSTR) { SqlDataReader Dr = NULL; Try { SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = conn; OpenoConnection (conn); Ocomm.commandtext = SQLSTR; Ocomm.commandtype = commandtype.text; DR = Ocomm.executeReader (Commandbehavior.CloseConnection); Ocomm.dispose (); } Catch (Exception E) { LOGERROR (E, SQLSTR); Try { IF (DR! = NULL &&! Dr.isclosed) Dr.close (); IF (conn.state! = connectionState.closed) CONN.CLOSE (); Conn.dispose (); } Catch { } } Finally { } Return DR; } /// /// Returns the SQLDATAREADER of the specified SQL statement, please note that the object is turned off after use, and will automatically call closeoconnection () to turn off the database connection. // / Method Close the database connection /// /// Inferred SQL statement /// Incoming Ref DataReader object Public Static Void DataReader (SqlConnection CONN, STRING SQLSTR, REF SQLDATAREADER DR) { Try { SQLCOMMAND OCOMM = New Sqlcommand (); Ocomm.connection = Conn; OpenoConnection (conn); Ocomm.commandtext = SQLSTR; Ocomm.commandtype = commandtype.text; DR = Ocomm.executeReader (Commandbehavior.CloseConnection); Ocomm.dispose (); } Catch (Exception E) { LOGERROR (E, SQLSTR); ErrorConn ; Try { IF (DR! = NULL &&! Dr.isclosed) Dr.close (); IF (conn.state! = connectionState.closed) CONN.CLOSE (); Conn.dispose (); } Catch { } } Finally { } } /// // / Return to the DataSet of the specified SQL statement /// /// Inferred SQL statement /// Public Static DataSet DataSet (String Sqlstr) { DataSet DS = New Dataset (); Sqldataadapter Da = new SqlDataAdapter (); INT i = 0; Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = SQLSTR; Openoconnection (i); Ocomm.commandtype = commandtype.text; Ocomm.commandtext = SQLSTR; Da.selectCommand = OCOMM; Da.fill (DS); Ocomm.dispose (); } Catch (Exception E) { LOGERROR (E, SQLSTR); ErrorConn ; Sconn [i]. Error = E.toString (); Throw new Exception (E.MESSAGE); } Finally { FREECONN (I); } Return DS; } /// // / Return to the DataSet of the specified SQL statement /// /// Inferred SQL statement /// Inferred reference DataSet object Public Static Void Dataset (String Sqlstr, Ref Dataset DS) { Sqldataadapter Da = new SqlDataAdapter (); INT i = 0; Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = SQLSTR; OpenOConnection (i); Ocomm.commandtype = commandtype.text; Ocomm.commandtext = SQLSTR; Da.selectCommand = OCOMM; Da.fill (DS); Ocomm.dispose (); } Catch (Exception E) { LOGERROR (E, SQLSTR); ErrorConn ; Sconn [i]. Error = E.toString (); Throw new Exception (E.MESSAGE); } Finally { FREECONN (I); } } /// / / / Return to the DataTable specified by the SQL statement /// /// Inferred SQL statement /// Public Static DataTable DataTable (String SQLSTR) { Sqldataadapter Da = new SqlDataAdapter (); DataTable DataTable = New DataTable (); INT i = 0; SqlConnection conn = new sqlConnection (); Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = conn; OpenoConnection (conn); Ocomm.commandtype = commandtype.text; Ocomm.commandtext = SQLSTR; Da.selectCommand = OCOMM; Da.fill (DataTable); Ocomm.dispose (); } Catch (Exception E) { LOGERROR (E, SQLSTR); Throw new Exception (E.MESSAGE); } Finally { CONN.CLOSE (); Conn.dispose (); } Return DataTable; } /// // / Execute the specified SQL statement, and assign a value to incoming DataTable /// /// Inferred SQL statement /// Ref dataable dt Public Static void DataTable (String Sqlstr, Ref DataTable DT) { Sqldataadapter Da = new SqlDataAdapter (); INT i = 0; Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = SQLSTR; Openoconnection (i); Ocomm.commandtype = commandtype.text; Ocomm.commandtext = SQLSTR; Da.selectCommand = Ocomm; da.fill (dt); Ocomm.dispose (); } Catch (Exception E) { LOGERROR (E, SQLSTR); ErrorConn ; Sconn [i]. Error = E.toString (); Throw new Exception (E.MESSAGE); } Finally { FREECONN (I); } } /// /// Execute a parameter stored procedure and return a collection of data /// /// Stored Procedure Name /// SQLParameterCollection input parameter /// Public Static DataTable DataTable (SQLParametercollection Parameters) { Sqldataadapter Da = new SqlDataAdapter (); DataTable DataTable = New DataTable (); INT i = 0; Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = procname; Openoconnection (i); Ocomm.Parameters.clear (); Ocomm.commandtype = commandtype.storedProcedure; Ocomm.commandtext = procname; Foreach (Sqlparameter Para in Parameters) { Sqlparameter P = (SQLParameter) Para; Ocomm.Parameters.Add (p); } Da.selectCommand = OCOMM; Da.fill (DataTable); Ocomm.dispose (); } Catch (Exception E) { LOGERROR (E, Procname); ErrorConn ; Sconn [i]. Error = E.toString (); Throw new Exception (E.MESSAGE); } Finally { FREECONN (I); } Return DataTable; } Public Static DataView DataView (String SQLSTR) { Sqldataadapter Da = new SqlDataAdapter (); DataView DV = New DataView (); DataSet DS = New Dataset (); INT i = 0; Try { i = getConn (); SQLCommand Ocomm = new SQLCOMMAND (); Ocomm.connection = sconn [i] .conn; Sconn [i] .sqlstr = SQLSTR; Openoconnection (i); Ocomm.commandtype = commandtype.text; Ocomm.commandtext = SQLSTR; Da.selectCommand = Ocomm; Da.Fill (DS); DV = ds.tables [0] .defaultView; Ocomm.dispose (); } Catch (Exception E) { ErrorConn ; LOGERROR (E, SQLSTR); Sconn [i]. Error = E.toString (); Throw new Exception (E.MESSAGE); } Finally { FREECONN (I); } Return DV; } } }
