SQL stored procedure data category

Source: Internet
Author: User
//////////// 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.