DBHelper classes that can use stored procedures

usingSystem; usingSystem.Collections.Generic; usingSystem.Linq; usingSystem.Text; usingSystem.Data; usingSystem.Data.SqlClient; namespaceBook.dal { Public classDBHelper {PrivateSqlConnection conn =NULL; PrivateSqlCommand cmd =NULL; PrivateSqlDataReader SDR =NULL;  PublicDBHelper () {stringConnStr ="data source=.; Database=mybookshop;uid=sa;pwd=123"; Conn=NewSqlConnection (CONNSTR); }            PrivateSqlConnection Getconn () {if(Conn. state = =connectionstate.closed) {conn.              Open (); }              returnConn; }            /// <summary>          ///perform additions and deletions without parameters to SQL statements or stored procedures/// </summary>          /// <param name= "Cmdtext" >adding or deleting SQL statements or stored procedures</param>          /// <param name= "ct" >Command Type</param>          /// <returns></returns>           Public intExecuteNonQuery (stringcmdtext, CommandType CT) {              intRes; Try{cmd=NewSqlCommand (Cmdtext, Getconn ()); Cmd.commandtype=CT; Res=cmd.              ExecuteNonQuery (); }              Catch(Exception ex) {Throwex; }              finally              {                  if(Conn. state = =ConnectionState.Open) {conn.                  Close (); }              }              returnRes; }            /// <summary>          ///perform additions and deletions with parameters to SQL statements or stored procedures/// </summary>          /// <param name= "Cmdtext" >adding or deleting SQL statements or stored procedures</param>          /// <param name= "ct" >Command Type</param>          /// <returns>int Value</returns>           Public intExecuteNonQuery (stringCmdtext, Sqlparameter[] paras, CommandType CT) {              intRes; using(cmd =NewSqlCommand (Cmdtext, Getconn ())) {Cmd.commandtype=CT; Cmd.                  Parameters.addrange (paras); Res=cmd.              ExecuteNonQuery (); }              returnRes; }            /// <summary>          ///execute a query SQL statement or stored procedure/// </summary>          /// <param name= "Cmdtext" >querying SQL statements or stored procedures</param>          /// <param name= "ct" >Command Type</param>          /// <returns>Table Value</returns>           PublicDataTable ExecuteQuery (stringcmdtext, CommandType CT) {DataTable dt=NewDataTable (); CMD=NewSqlCommand (Cmdtext, Getconn ()); Cmd.commandtype=CT; using(SDR =cmd. ExecuteReader (commandbehavior.closeconnection)) {dt.              Load (SDR); }              returnDT; }            /// <summary>          ///execute a query SQL statement or stored procedure with parameters/// </summary>          /// <param name= "Cmdtext" >querying SQL statements or stored procedures</param>          /// <param Name= "Paras" >Parameter Collection</param>          /// <param name= "ct" >Command Type</param>          /// <returns>Table Value</returns>           PublicDataTable ExecuteQuery (stringCmdtext, Sqlparameter[] paras, CommandType CT) {DataTable dt=NewDataTable (); CMD=NewSqlCommand (Cmdtext, Getconn ()); Cmd.commandtype=CT; Cmd.              Parameters.addrange (paras); using(SDR =cmd. ExecuteReader (commandbehavior.closeconnection)) {dt.              Load (SDR); }              returnDT; }              /// <summary>          ///executing a scalar query with parameters/// </summary>          /// <param name= "Cmdtext" >querying SQL statements or stored procedures</param>          /// <param Name= "Paras" >Parameter Collection</param>          /// <param name= "ct" >Command Type</param>          /// <returns>a value of type int</returns>           Public intExecutecheck (stringCmdtext, Sqlparameter[] paras, CommandType CT) {              intresult; using(cmd =NewSqlCommand (Cmdtext, Getconn ())) {Cmd.commandtype=CT; Cmd.                  Parameters.addrange (paras); Result=Convert.ToInt32 (cmd.              ExecuteScalar ()); }              returnresult; }      }  }  

Usage

    //determine if the user name password is correct             Public BOOLCheckUser (stringLoginId,stringloginpwd) {                  BOOLCheck =false; stringCmdtext ="Proccheckuser"; Sqlparameter[] Parm=Newsqlparameter[] {NewSqlParameter ("@LoginId", LoginId),NewSqlParameter ("@LoginPwd", Loginpwd)}; intone =DH.                  Executecheck (Cmdtext, Parm, CommandType.StoredProcedure); if(One >0)                  {                      return true; }                  returncheck; }                                             //Show All books of this type according to book type             PublicDataTable Selectallbytype (intTypeId) {                  stringCmdtext ="Procselectallbytype"; Sqlparameter[] Parm=Newsqlparameter[]{NewSqlParameter ("@TypeId", TypeId)}; DT=DH.                  ExecuteQuery (Cmdtext, Parm, CommandType.StoredProcedure); returnDT; }  

DBHelper classes that can use stored procedures