Database Operations Encapsulation Class DBHelper.cs

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingsystem.web;usingSystem.Configuration;usingSystem.Data.Common;usingSystem.Data;namespacemodel{/// <summary>    ///Summary description of DBHelper/// </summary>     Public classDBHelper {//Database Link String        Private Static ReadOnly stringstrconn ="Data Source=.;i Nitial catalog=graduation_dedign;integrated security=true"; //Data Provider        Private Static stringDbPro ="System.Data.SqlClient"; //Data Factory        Private StaticDbProviderFactory db =dbproviderfactories.getfactory (DbPro); Private StaticDbConnection Conn; Private StaticDataSet DT; Private StaticDbDataAdapter da; //Create and open a connection         Public StaticDbConnection Conn {Get            {                if(conn = =NULL) {Conn=db.                    CreateConnection (); Conn. ConnectionString=strconn; }                Switch(Conn. State) { CaseConnectionState.Broken:conn.                        Close (); Conn.                        Open ();  Break;  CaseConnectionState.Closed:conn.                        Open ();  Break; }                returnConn; }        }        /// <summary>        ///Create command/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "CommandType" ></param>        /// <param name= "Sqlparams" ></param>        /// <returns></returns>         Public StaticDbCommand CreateCommand (stringSQL, CommandType commandtype,paramsdbparameter[] sqlparams) {DbCommand Comm=db.            CreateCommand (); Comm. Connection=Conn; Comm.commandtype=CommandType; Comm.commandtext=SQL; if(Sqlparams! =NULL) {Comm.                Parameters.clear (); Comm.            Parameters.addrange (Sqlparams); }            returnComm; }        /// <summary>        ///returns the number of rows affected/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "CommandType" ></param>        /// <param name= "Sqlparams" ></param>        /// <returns></returns>         Public Static intExecuteNonQuery (stringSQL, CommandType commandtype,paramsdbparameter[] sqlparams) {DbCommand Comm=createcommand (SQL, CommandType, sqlparams); intnum =Comm.            ExecuteNonQuery (); Comm.            Connection.close (); returnnum; }        /// <summary>        ///executes multiple SQL statements to implement database transactions. /// </summary>        /// <param name= "Sqlstringlist" >more than one SQL statement</param>               /// <returns>number of records affected</returns>         Public Static intExecutesqltran (list<string>sqlstringlist) {DbCommand Comm=db.            CreateCommand (); Comm. Connection=Conn; Dbtransaction TX=conn.begintransaction (); Comm. Transaction=TX; Try            {                intCount =0;  for(intn =0; n < Sqlstringlist.count; n++)                {                    stringstrSQL =Sqlstringlist[n]; if(strSQL. Trim (). Length >1) {Comm.commandtext=strSQL; Count+=Comm.                    ExecuteNonQuery ();                }} tx.commit (); returncount; }            Catch{TX.                Rollback (); return 0; }        }        /// <summary>        ///Returns the first column of the first row/// </summary>        /// <typeparam name= "T" ></typeparam>        /// <param name= "SQL" ></param>        /// <param name= "CommandType" ></param>        /// <param name= "Sqlparams" ></param>        /// <returns></returns>         Public StaticT executescalar<t> (stringSQL, CommandType commandtype,paramsdbparameter[] sqlparams) {DbCommand Comm=createcommand (SQL, CommandType, sqlparams); T T=(T) Comm.            ExecuteScalar (); Comm.            Connection.close (); returnT; }        /// <summary>        ///Back to DataReader/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "CommandType" ></param>        /// <param name= "Sqlparams" ></param>        /// <returns></returns>         Public StaticDbDataReader ExecuteReader (stringSQL, CommandType commandtype,paramsdbparameter[] sqlparams) {DbCommand Comm=createcommand (SQL, CommandType, sqlparams); returnComm.        ExecuteReader (commandbehavior.closeconnection); }        /// <summary>        ///Return Data Set/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "CommandType" ></param>        /// <param name= "Sqlparams" ></param>        /// <returns></returns>         Public StaticDataTable getdatatable (stringSQL, CommandType commandtype,paramsdbparameter[] sqlparams) {DbCommand Comm=createcommand (SQL, CommandType, sqlparams); Da=db.            Createdataadapter (); Da. SelectCommand=Comm; DT=NewDataSet (); Da.            Fill (DT); returnDt. tables[0]; }    }}

Database Operations Encapsulation Class DBHelper.cs