C # encapsulate SQL Server databases

Class sqlhelper <br/>{</P> <p> private string sqlconnstring; // database connection string <br/> private sqlconnection sqlconn = new sqlconnection (); // sqldatabase connection object </P> <p> private connectionstate sqlconnprestate = connectionstate. closed; // connection status of the original database </P> <p> // <summary> <br/> // database connection string attribute. <Br/> // </Summary> <br/> Public String sqlconnectionstring <br/> {<br/> Get <br/>{< br/> return sqlconnstring; <br/>}< br/> set <br/> {<br/> sqlconnstring = value; <br/> sqlconn. connectionstring = sqlconnstring; <br/>}</P> <p> Public sqlhelper () <br/>{</P> <p >}< br/> // <summary> <br/> // constructor. <Br/> /// </Summary> <br/> /// <Param name = "strsqlcon"> database connection string. </Param> <br/> Public sqlhelper (string strsqlcon) <br/>{< br/> sqlconnstring = strsqlcon; <br/> sqlconn. connectionstring = sqlconnstring; </P> <p >}</P> <p> // <summary> <br/> // destructor. <Br/> // </Summary> <br/> ~ Sqlhelper () <br/>{< br/> try <br/>{< br/> close (); <br/> sqlconn. dispose (); <br/>}< br/> catch (exception ex) <br/>{</P> <p> throw ex; <br/>}</P> <p> // <summary> <br/> // open the database connection. <Br/> // </Summary> <br/> Public void open () <br/>{< br/> try <br/>{< br/> If (sqlconn. state = connectionstate. closed) <br/>{< br/> sqlconn. open (); <br/> sqlconnprestate = connectionstate. open; <br/>}< br/> catch (exception ex) <br/>{< br/> throw ex; <br/>}</P> <p> // <summary> <br/> // close the database connection. <Br/> // </Summary> <br/> Public void close () <br/>{< br/> try <br/>{< br/> If (sqlconn. state! = Connectionstate. closed) <br/>{< br/> sqlconn. close (); <br/> sqlconnprestate = connectionstate. closed; <br/>}< br/> catch (exception ex) <br/>{< br/> throw ex; <br/>}</P> <p> // <summary> <br/> // obtain the queried data table. <Br/> /// </Summary> <br/> /// <Param name = "strsql"> SQL statement to be queried. </Param> <br/> // <Param name = "parametes"> the input parameter. If no parameter exists, null is used. </Param> <br/> // <returns> </returns> <br/> Public datatable getdatatable (string strsql, Params sqlparameter [] parametes) <br/>{< br/> dataset DS = new dataset (); <br/> datatable dt = new datatable (); </P> <p> try <br/> {<br/> // sqlconn. open (); <br/> If (sqlconn. state = connectionstate. closed & sqlconn. state = connectionstate. closed) // if the original status is closed and the current connection is not opened <br/>{< br/> sqlconn. open (); <br/>}</P> <P> sqlcommand sqlcmd = sqlconn. createcommand (); <br/> sqlcmd. commandtext = strsql; </P> <p> If (parametes! = NULL) <br/>{< br/> sqlcmd. parameters. clear (); <br/> sqlcmd. parameters. addrange (parametes); <br/>}</P> <p> sqldataadapter da = new sqldataadapter (sqlcmd); <br/> da. fill (DS); <br/> dt = Ds. tables [0]; <br/>}< br/> catch (exception ex) <br/>{< br/> throw ex; <br/>}< br/> finally <br/> {<br/> If (sqlconnprestate = connectionstate. closed & sqlconn. state! = Connectionstate. closed) // if the original status is closed and the current connection is not closed, it is closed <br/>{< br/> sqlconn. close (); <br/>}< br/> return DT; </P> <p >}</P> <p> // <summary> <br/> // obtain the queried dataset. <Br/> /// </Summary> <br/> /// <Param name = "strsql"> SQL statement to be queried. </Param> <br/> // <Param name = "parametes"> the input parameter. If no parameter exists, null is used. </Param> <br/> // <returns> </returns> <br/> Public dataset getdataset (string strsql, Params sqlparameter [] parametes) <br/>{< br/> dataset DS = new dataset (); <br/> try <br/>{< br/> // sqlconn. open (); <br/> If (sqlconn. state = connectionstate. closed & sqlconn. state = connectionstate. closed) // if the original status is closed and the current connection is not opened <br/>{< br/> sqlconn. open (); <br/>}< br/> sqlcommand sqlcmd = sqlconn. createcomman D (); <br/> sqlcmd. commandtext = strsql; </P> <p> If (parametes! = NULL) <br/>{< br/> sqlcmd. parameters. clear (); <br/> sqlcmd. parameters. addrange (parametes); <br/>}</P> <p> sqldataadapter da = new sqldataadapter (sqlcmd); <br/> da. fill (DS); <br/> sqlconn. close (); <br/>}< br/> catch (exception ex) <br/>{< br/> throw ex; <br/>}< br/> finally <br/> {<br/> If (sqlconnprestate = connectionstate. closed & sqlconn. state! = Connectionstate. closed) // if the original status is closed and the current connection is not closed, it is closed <br/>{< br/> sqlconn. close (); <br/>}< br/> return Ds; <br/>}</P> <p> // <summary> <br/> // return the sqldatareader object. This function needs to enable and disable connections externally. <Br/> /// </Summary> <br/> /// <Param name = "strsql"> SQL statement. </Param> <br/> // <Param name = "parametes"> the input parameter. If no parameter exists, null is used. </Param> <br/> // <returns> </returns> <br/> Public sqldatareader executereader (string strsql, Params sqlparameter [] parametes) <br/>{< br/> sqldatareader reader; <br/> try <br/>{</P> <p> sqlcommand sqlcmd = sqlconn. createcommand (); <br/> sqlcmd. commandtext = strsql; </P> <p> If (parametes! = NULL) <br/>{< br/> sqlcmd. parameters. clear (); <br/> sqlcmd. parameters. addrange (parametes); <br/>}</P> <p> reader = sqlcmd. executereader (); <br/>}< br/> catch (exception ex) <br/>{< br/> throw ex; <br/>}< br/> return reader; <br/>}</P> <p> // <summary> <br/> // returns the number of rows affected by the execution of T-SQL statements. <Br/> /// </Summary> <br/> /// <Param name = "strsql"> SQL statement executed. </Param> <br/> // <Param name = "parametes"> the input parameter. If no parameter exists, null is used. </Param> <br/> // <returns> </returns> <br/> Public int executenonquery (string strsql, Params sqlparameter [] parametes) <br/>{</P> <p> int sqlint =-1; <br/> try <br/>{< br/> If (sqlconn. state = connectionstate. closed & sqlconn. state = connectionstate. closed) // if the original status is closed and the current connection is not opened <br/>{< br/> sqlconn. open (); <br/>}</P> <p> sqlcommand sqlcmd = sqlconn. createcommand (); <br/> sqlcmd. commandtext = Strsql; </P> <p> If (parametes! = NULL) <br/>{< br/> sqlcmd. parameters. clear (); <br/> sqlcmd. parameters. addrange (parametes); <br/>}</P> <p> sqlint = sqlcmd. executenonquery (); <br/>}< br/> catch (exception ex) <br/>{< br/> throw ex; <br/>}< br/> finally <br/> {<br/> If (sqlconnprestate = connectionstate. closed & sqlconn. state! = Connectionstate. closed) // if the original status is closed and the current connection is not closed, it is closed <br/>{< br/> sqlconn. close (); <br/>}< br/> return sqlint; </P> <p >}</P> <p> // <summary> <br/> // execute the query and return the value of the first column in the first row. <Br/> /// </Summary> <br/> /// <Param name = "strsql"> SQL statement executed. </Param> <br/> // <Param name = "parametes"> the input parameter. If no parameter exists, null is used. </Param> <br/> // <returns> </returns> <br/> Public object executescalar (string strsql, Params sqlparameter [] parametes) <br/>{< br/> Object OBJ; <br/> try <br/>{< br/> If (sqlconn. state = connectionstate. closed & sqlconn. state = connectionstate. closed) // if the original status is closed and the current connection is not opened <br/>{< br/> sqlconn. open (); <br/>}</P> <p> sqlcommand sqlcmd = sqlconn. createcommand (); <br/> sqlcmd. commandtext = strsq L; </P> <p> If (parametes! = NULL) <br/>{< br/> sqlcmd. parameters. clear (); <br/> sqlcmd. parameters. addrange (parametes); <br/>}</P> <p> OBJ = sqlcmd. executescalar (); <br/>}< br/> catch (exception ex) <br/>{< br/> throw ex; <br/>}< br/> finally <br/> {<br/> If (sqlconnprestate = connectionstate. closed & sqlconn. state! = Connectionstate. closed) // if the original status is closed and the current connection is not closed, it is closed <br/>{< br/> sqlconn. close (); <br/>}< br/> return OBJ; <br/>}</P> <p>}