Using system;
Using system.data;
Using system.data.sqlclient;
Namespace SysclassLibrary
{
///
A summary description of /// DataAccess.
///
///
Public Class DataAccess
{
#region attribute
Protected static sqlconnection conn = new sqlConnection ();
protected static sqlcommand comm = new sqlcommand ();
#ndregion
Public DataAccess ()
{
// init ();
}
#Region does not execute a DataAccess () constructor in the internal function static method
///
// / Open the database connection
///
Private static void OpenConnection ()
{
IF (Conn.State == ConnectionsState.Closed)
{
//Sysconfig.connectionstring is connected to the system configuration class, such as "Server = localhost; database = DatabaseName; UID = SA; PWD =;"
Conn.connectionstring = sysconfig.connectionstring;
Comm.Connection = conn;
Try
{
Cn.open ();
}
Catch (Exception E)
{
Throw new Exception (E.MESSAGE);
}
}
}
///
/// Close the current database connection
///
Private static void closeconnection ()
{
IF (Conn.State == ConnectionsState.Open)
CONN.CLOSE ();
Conn.dispose ();
Comm.dispose ();
}
#ndregion
///
/ / / Execute SQL query statement
///
/// Inferred SQL statement
Public Static Void ExecuteSQL (String SQLSTR)
{
Try
{
OpenConnection ();
Comm.commandType = commandType.Text;
Comm.commandtext = SQLSTR;
Comm.executenonQuery ();
}
Catch (Exception E)
{
Throw new Exception (E.MESSAGE);
}
Finally
{
CloseConnection ();
}
}
///
/// Execute a stored procedure
///
/// Stored Procedure /// SQLParameters collection
Public Static Void ExecutePorcedure (SQLPARAMETER [] COLL)
{
Try
{
OpenConnection ();
For (int i = 0; i { Comm.Parameters .add (COLL [I]); } Comm.commandtype = commandtype.storedProcedure; Comm.commandtext = procname; Comm.executenonQuery (); } Catch (Exception E) { Throw new Exception (E.MESSAGE); } Finally { Comm.Parameters.clear (); CloseConnection (); } } /// / / / Execute a stored procedure and return a data set /// /// Stored Procedure Name /// SQLParameter Collection /// dataset Public Static Void ExecutePorcedure (String Procname, Sqlparameter [] COLL, REF DATASET DS) { Try { Sqldataadapter Da = new SqlDataAdapter (); OpenConnection (); For (int i = 0; i { Comm.Parameters .add (COLL [I]); } 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 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 (); Try { OpenConnection (); Comm.commandType = commandType.Text; Comm.commandtext = SQLSTR; Obj = comm.executescalar (); Catch (Exception E) { Throw new Exception (E.MESSAGE); } Finally { CloseConnection (); } Return Obj; } /// / / / Execute a SQL query statement while performing transaction processing /// /// Inferred SQL statement 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 (); } } /// /// Returns the SQLDataReader of the specified SQL statement, please note that the object is turned off after use, and will automatically call CloseConnection () to turn off the database connection. // / Method Close the database connection /// /// Inferred SQL statement /// 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; } /// /// Returns the SQLDataReader of the specified SQL statement, please note that the object is turned off after use, and will automatically call CloseConnection () to turn off the database connection. // / Method Close the database connection /// /// Inferred SQL statement /// Incoming Ref DataReader object 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 (); } } } /// // / 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 (); 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; } /// // / 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 (); 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 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 (); 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; } /// // / 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 (); 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 (); } } /// /// 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 (); 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 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; } } }
