To improve programming efficiency, develop a class dedicated to connect to the database.
Using system; using system.data.sqlclient; Namespace SysclassLibrary {/// /// DataAccess's summary description.
/// Data processing base class, call mode: dataAccess.dataset (String) SQLSTR); or DataAccess.DataSet (String) SQLSTR, REF DATASET DS); /// public class DataAccess {#region property protected static SqlConnection conn = new SqlConnection (); protected static SqlCommand comm = new SqlCommand (); #endregion public DataAccess () {// init ();} #region internal static method does not function Execute dataAccess () constructor /// /// Open database connection /// private static void openconnection () {if (conn.state == connectionState.closed) {//sysconfig.connectionstring The connection string in the system configuration class, such as "Server = localhost; database = database = sa; pwd =;" conn.connectionstring = sysconfig.connectionstring; comm.connection = conn; try {conn.open ();} Catch (Exception E) {throw new exception (E.MESSAGE);}}}} /// /// Close current database connection /// private static void closeconnection () {ix (conn.state == connectionState.open) Conn.close (); conn.dispose (); comm.dispose ();} #ndregion /// // //// //// Sql statement incoming public static void ExecuteSql (string sqlstr) {try {openConnection (); comm.CommandType = CommandType.Text; comm.CommandText = sqlstr; comm.ExecuteNonQuery ();} catch ( Exception e) {throw new exception;} finally {closeConnection ();}} /// // / execution stored procedure /// /// Store process name /// SQLParameters collection public static void executeporcedure (string procname, sqlparameter [] coll) {{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 ();}} /// // / / / OUNTS //// /// < Param name = "procname"> 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 /// execute SQL query statement and returns the first record of the first line, and the return value needs to be removed when using Object. Box Operation-> unbox /// /// Inferred SQ Statement l /// object Return Value 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 ();} returnobj;} /// // / Execute the SQL query statement, while transaction /// /// 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 SQLDataReader for specifying the SQL statement, please note that you will turn off this object after use, and will automatically call CloseConnection () to close Database connection /// Method Close Database connection /// /// Inferred SQL statement /// SqlDataReader object 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 the CloseConnection will be called automatically. ) To turn off database connection /// Method to turn off database connection /// /// Introduced SQL statement /// Inferred 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 specified SQL statement DataSet /// /// Inferred SQL statement /// dataset public static dataset dataset (SQLDATADAPTER 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 ();} returnif DS;} /// /// Returns DataSet /// /// biography of the specified SQL statement Into SQL statement /// incoming reference DataSet object public static void 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 ();}} /// /// Returns a DataTable /// /// incoming SQL statement /// DataTable public static DataTable dataTable (string sqlstr) {SqlDataAdapter da = new SqlDataAdapter (); DataTable datatable = new DataTable (); try {openConnection (); comm.CommandType = Comma ndType.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 the incoming DataTable /// /// Inferred SQL statement / // ref DataTable dt public static void dataTable (string sqlstr, ref DataTable dt) {SqlDataAdapter da = new SqlDataAdapter (); try {openConnection (); comm.CommandType = CommandType. TEXT;
