asp.net| Access | data using System;
Using System.Data;
Using System.Data.SqlClient;
Namespace Sysclasslibrary
{
<summary>
Summary description of the DataAccess.
<description> data base class, invocation mode: Dataaccess.dataset ((String) sqlstr), or Dataaccess.dataset ((string) sqlstr,ref DataSet DS); </description>
</summary>
public class DataAccess
{
#region Properties
Protected static SqlConnection conn=new SqlConnection ();
protected static SqlCommand comm=new SqlCommand ();
#endregion
Public DataAccess ()
{
Init ();
}
The DataAccess () constructor is not executed in the static method of #region intrinsic function
<summary>
Open a database connection
</summary>
private static void OpenConnection ()
{
IF (Conn. state = = connectionstate.closed)
{
Sysconfig.connectionstring the connection string for the system configuration class, such as: "server=localhost;database=databasename;uid=sa;pwd=;"
Conn. ConnectionString = sysconfig.connectionstring;
Comm. Connection =conn;
Try
{
Conn. Open ();
}
catch (Exception e)
{
throw new Exception (e.message);
}
}
}
<summary>
Close the current database connection
</summary>
private static void CloseConnection ()
{
IF (Conn. state = = ConnectionState.Open)
Conn. Close ();
Conn. Dispose ();
Comm. Dispose ();
}
#endregion
<summary>
Execute SQL query statement
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
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 ();
}
}
<summary>
Executing stored procedures
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Coll" >sqlparameters collection </param>
public static void Executeporcedure (String procname,sqlparameter[] coll)
{
Try
{
OpenConnection ();
for (int i=0;i<coll. length;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 ();
}
}
<summary>
Execute the stored procedure and return the dataset
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Coll" >sqlparameter collection </param>
<param name= "DS" >dataset </param>
public static void Executeporcedure (String procname,sqlparameter[] Coll,ref DataSet DS)
{
Try
{
SqlDataAdapter Da=new SqlDataAdapter ();
OpenConnection ();
for (int i=0;i<coll. length;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 ();
}
}
<summary>
Executes the SQL query statement and returns the first record of the first row, which requires a unboxing operation when object is used-> Unbox
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<returns>object return value </returns>
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;
}
<summary>
Execute the SQL query statement while transaction processing
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
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 ();
}
}
<summary>
Returns the SqlDataReader of the specified SQL statement, note that after use, close this object and automatically call CloseConnection () to close the database connection
method to close the database connection
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<returns>sqldatareader Objects </returns>
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;
}
<summary>
Returns the SqlDataReader of the specified SQL statement, note that after use, close this object and automatically call CloseConnection () to close the database connection
method to close the database connection
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<param name= "Dr" > Incoming ref DataReader object </param>
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 ();
}
}
}
<summary>
Returns the dataset for the specified SQL statement
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<returns>DataSet</returns>
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;
}
<summary>
Returns the dataset for the specified SQL statement
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<param name= "DS" > Incoming reference DataSet object </param>
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 ();
}
}
<summary>
Returns a datatable of the specified SQL statement
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<returns>DataTable</returns>
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;
}
<summary>
Executes the specified SQL statement while assigning the incoming DataTable
</summary>
<param name= "SQLSTR" > Incoming SQL statements </param>
<param name= "DT" >ref DataTable DT </param>
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 ();
}
}
<summary>
Executes a stored procedure with parameters and returns a collection of data
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Parameters" >sqlparametercollection input parameters </param>
<returns></returns>
public static DataTable DataTable (string procname,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;
}
}
}
