Using System; Using System.Data; Using System.Data.SqlClient; namespace Sysclasslibrary {//<summary>//DataAccess Summary description. <description> data processing base class, call Mode: Dataaccess.dataset (String) sqlstr), or Dataaccess.dataset (string) sqlstr,ref DataSet DS); </description>//</summary> public class DataAccess {#region property protected static SqlConnection conn=new SQL Connection (); protected static SqlCommand comm=new SqlCommand (); #endregion public DataAccess () {//init ()} does not execute the DataAccess () constructor in the static method of #region intrinsic function///<summary>//Open database connection///&L t;/summary> private static void OpenConnection () {if (conn. state = = connectionstate.closed) {//sysconfig.connectionstring is a connection string in 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 current database connection//</summary> private static void CloseconNection () {if (conn. state = = ConnectionState.Open) Conn. Close (); Conn. Dispose (); Comm. Dispose (); } #endregion///<summary>///Execute SQL QUERY statements///</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>////Execute Stored Procedure///</summary>//<param name= "procname" > Stored procedure name </param>//<param NA Me= "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); } comm.commandtype=commandtype.storedprocedure; Comm.commandtext =procname; Comm. ExecuteNonQuery (); } catch (Exception e) {throw new Exception (e.message);} finally {comm. Parameters.clear (); Closeconnection (); }}///<summary>///execute stored procedure and return data set///</summary>//<param name= "procname" > Stored procedure name </param>//&L T;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); } 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>///execute the SQL query statement and return the first record of the first row, the return value for object requires a unboxing operation, Unbox///</summary>/<param Name = "SQLSTR" > Incoming SQL statement </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 SQL query statements while doing transactional 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 statement </param>//<returns>sqldatareader object </ 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 close this object after use, 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 statement </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 statement </param>/ <param name= "DS" > Incoming reference DataSet object </param> public static void DataSet (String Sqlstr,ref dataSet ds) {SqlData Adapter 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 for 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>///execute 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 datat Able 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>////execute with parameter stored procedure and return data set///</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; } } }