//////////// Data Encryption Class Using system; using system. componentmodel; using system. collections; using system. web. security; using system. diagnostics; using system. data; using system. data. sqlclient; using system. configuration; using system. text. regularexpressions; namespace webapp. COMPONENTS {/// <summary> /// common database processing class, connected to the database through ado.net /// </Summary> public class database: idisposable {// connection data source private sqlconnectio N con; # region executes a stored procedure without parameters, return the Stored Procedure return value // <summary> // execute the Stored Procedure // </Summary> /// <Param name = "procname"> stored procedure without Parameters </param> /// <returns> return the Stored Procedure return value </returns> Public int runproc (string procname) {sqlcommand cmd = createcommand (procname, null); cmd. executenonquery (); this. close (); Return (INT) cmd. parameters ["returnvalue"]. value ;}# endregion # region executes a stored procedure with parameters and returns the returned value of the stored procedure /// <summary> /// executes the Stored Procedure /// </Summary> /// <Param name = "procname"> stored procedure name with parameters </param> /// <Param name = "prams"> parameters required for Stored Procedure </param> // <returns> returns the Stored Procedure return value </returns> Public int runproc (string procname, sqlparameter [] prams) {sqlcommand cmd = createcommand (procname, prams); cmd. executenonquery (); this. close (); Return (INT) cmd. parameters ["returnvalue"]. value ;}# endregion # region executes a stored procedure without parameters and returns the sqldatareader object through the output parameter /// <summary> /// Run the Stored Procedure // </Summary> /// <Param name = "procname"> name of the stored procedure without parameters </param> /// <Param name = "datareader"> return sqldatareader object through output parameters </param> Public void runproc (string procname, out sqldatareader datareader) {sqlcommand cmd = createcommand (procname, null); datareader = cmd. executereader (system. data. commandbehavior. closeconnection) ;}# endregion # region executes the stored procedure with parameters, and returns the sqldatareader object through the output parameters /// <summary> // execution Row Stored Procedure // </Summary> /// <Param name = "procname"> name of a stored procedure with parameters </param> /// <Param name =" prams "> parameters required for Stored Procedures </param> // <Param name =" datareader "> returns the sqldatareader object through output parameters </param> Public void runproc (string procname, sqlparameter [] prams, out sqldatareader datareader) {sqlcommand cmd = createcommand (procname, prams); datareader = cmd. executereader (system. data. commandbehavior. closeconnection) ;}# endregion # Region create a sqlcommand object // <summary> // create a sqlcommand object to execute the Stored Procedure // </Summary> // <Param name = "procname"> name of the stored procedure </param> /// <Param name = "prams"> parameters required for the stored procedure </param> /// <returns> returns the sqlcommand object </returns> private sqlcommand createcommand (string procname, sqlparameter [] prams) {// confirm to open the connection open (); sqlcommand cmd = new sqlcommand (procname, con); cmd. commandtype = commandtype. storedprocedure; // transmits parameters in sequence Stored Procedure if (prams! = NULL) {foreach (sqlparameter parameter in prams) cmd. parameters. add (parameter);} // Add the return parameter cmd. parameters. add (New sqlparameter ("returnvalue", sqldbtype. int, 4, parameterdirection. returnvalue, false, 0, 0, String. empty, datarowversion. default, null); Return cmd ;}# endregion # region open the database connection // <summary> // open the database connection. /// </Summary> private void open () {// open the database connection if (con = NULL) {con = new Sqlconnection (configurationmanager. appsettings ["connectionstring"]);} If (con. state = system. data. connectionstate. closed) con. open () ;}# endregion # region closes the database connection // <summary> /// closes the database connection /// </Summary> Public void close () {If (con! = NULL) con. close () ;}# endregion # region releases resources /// <summary> // releases resources /// </Summary> Public void dispose () {// confirm whether the connection has been closed if (con! = NULL) {con. dispose (); con = NULL ;}} # endregion # region input parameters /// <summary> // input parameters /// </Summary> /// <Param name = "paramname"> stored procedure name </param> /// <Param name = "dbtype"> parameter type </param> /// <Param name = "size"> parameter size </param> // /<Param name = "value"> parameter value </param> // <returns> New Parameter object </returns> Public sqlparameter makeinparam (string paramname, sqldbtype dbtype, int size, object Value) {return m Akeparam (paramname, dbtype, size, parameterdirection. input, value );} # endregion # region Input Output parameter /// <summary> // input output parameter /// </Summary> /// <Param name = "paramname"> stored procedure name </param> /// <Param name = "dbtype"> parameter type </param> /// <Param name = "size"> parameter size </param> // /<returns> New Parameter object </returns> Public sqlparameter makeoutparam (string paramname, sqldbtype dbtype, int size) {return makeparam (paramname, Dbtype, size, parameterdirection. output, null );} # endregion # region Input return value parameter /// <summary> // input return value parameter /// </Summary> /// <Param name = "paramname"> stored procedure name </param> /// <Param name = "dbtype"> parameter type </param> /// <Param name = "size"> parameter size </param> // /<returns> New Parameter object </returns> Public sqlparameter makereturnparam (string paramname, sqldbtype dbtype, int size) {return makeparam (paramname, dbtype, size, Parameterdirection. returnvalue, null );} # endregion # region generate stored procedure parameters /// <summary> // generate stored procedure parameters /// </Summary> /// <Param name = "paramname"> storage procedure name </param> /// <Param name = "dbtype"> parameter type </param> /// <Param name = "size"> parameter size </param> /// <Param name = "direction"> parameter direction </param> /// <Param name = "value"> parameter value </param> /// <returns> new Parameter object </returns> Public sqlparameter makeparam (string paramname, sqldbty PE dbtype, int32 size, parameterdirection direction, object Value) {sqlparameter Param; If (size> 0) Param = new sqlparameter (paramname, dbtype, size ); else Param = new sqlparameter (paramname, dbtype); Param. direction = direction; If (! (Direction = parameterdirection. output & value = NULL) Param. value = value; return Param ;} # endregion # region convert datareader to able // <summary> // convert datareader to datatable /// </Summary> /// <Param name = "datareader"> datareader </param> Public static datatable convertdatareadertodatatable (sqldatareader reader) {try {datatable objdatatable = new datatable (); int intfieldcount = reader. fie Ldcount; For (INT intcounter = 0; intcounter <intfieldcount; ++ intcounter) {objdatatable. columns. add (reader. getname (intcounter), reader. getfieldtype (intcounter);} objdatatable. beginloaddata (); object [] objvalues = new object [intfieldcount]; while (reader. read () {reader. getvalues (objvalues); objdatatable. loaddatarow (objvalues, true);} reader. close (); objdatatable. endloaddata (); Return objdat Atable;} catch (exception ex) {Throw new exception ("An error occurred while converting datareader to datatable! ", Ex );}} # endregion # region number determination // <summary> // number determination // </Summary> // <Param name = "str"> string parameter </Param> Public static bool isnumber (string strnumber) {RegEx objnotnumberpattern = new RegEx ("[^ 0-9. -] "); RegEx objtwodotpattern = new RegEx (" [0-9] * [.] [0-9] * [.] [0-9] * "); regEx objtwominuspattern = new RegEx ("[0-9] * [-] [0-9] * [-] [0-9] *"); string strvalidrealpattern = "^ ([-] | [.] | [-.] | [0-9]) [0-9] * [.] * [0 -9] + $ "; string strvalidintegerpattern =" ^ ([-] | [0-9]) [0-9] * $ "; regEx objnumberpattern = new RegEx ("(" + strvalidrealpattern + ") | (" + strvalidintegerpattern + ")"); return! Objnotnumberpattern. ismatch (strnumber )&&! Objtwodotpattern. ismatch (strnumber )&&! Objtwominuspattern. ismatch (strnumber) & objnumberpattern. ismatch (strnumber) ;}# endregion }/// // use sqldatareader datareader = NULL; database data = new database (); sqlparameter [] prams = {data. makeinparam ("@ linkid", sqldbtype. int, 4, linkid),}; try {data. runproc ("Stored Procedure", prams, out datareader); Return datareader;} catch (exception ex) {Throw new exception ("link Read error! ", Ex) ;}// these are part of the code about data access. There are three types of stored procedures: No query is returned, a dataset is returned, and a single value is returned. |