Use C # To Call The Stored Procedure and organize the code properly using the function to make the program clearer (example)

Although calling stored procedures is not difficult to implement, reasonable code organization in the program can make the program structure clearer and easier to maintain.

The following example divides the calling of a stored procedure into three parts:

1. Prepare various data for calling stored procedures: database connection strings and stored procedure parameters

2. Prepare parameters for executing commands, use various data in 1 to establish a connection with the database, prepare parameters and set variables for calling stored procedures

3. Execute stored procedures, such as query or update operations.

The sample code is as follows:

 

Using system; using system. collections. generic; using system. LINQ; using system. text; using system. data; using system. data. sqlclient; namespace use stored procedure {class program {// configure the connection string public static string connstring = @ "Data Source = Q-PC \ sqlexpress; Integrated Security = sspi; uid = sa; pwd =; initial catalog = hangyun "; static void main (string [] ARGs) {program P = new program (); sqlparameter [] parms = new sqlparameter [] // Configure the parameter {New sqlparameter ("@ parms0", sqldbtype for the stored procedure. nvarchar), new sqlparameter ("@ parms1", sqldbtype. nvarchar ),....}; // assign parms [0] to the parameter. value = the value in the Stored Procedure parms [1]. value = parms [2]. value = .... p. excstorenonquery (commandtype. storedprocedure, "qhc_domesticcompanymanage", parms); console. readline () ;}# update the region Stored Procedure /// <summary> /// execute a sqlcommand that does not require a return value, and specify a dedicated connection string. /// Provide the parameter list in the form of parameter arrays /// </Summary> /// <remarks> /// example: // int result = excstorenonquery (connstring, commandtype. storedprocedure, "publishorders", new sqlparameter ("@ prodid", 24 )); /// </remarks> /// <Param name = "connectionstring"> A valid database connection string </param> /// <Param name = "commandtype"> sqlcommand command type (stored procedure, t-SQL statement, and so on .) </Param> /// <Param name = "commandtext"> stored procedure name or T-SQL statement </param> /// <Param name = "commandparameters"> in array form list of parameters used in the sqlcommand </param> // <returns> Returns a data value indicating the number of rows affected by the execution of the sqlcommand </returns> Public int excstorenonquery (string connstring, commandtype parameter type, string parameter text, Params sqlparameter [] commandparameters) {sqlcommand cmd = new sqlcommand (); sqlconnection conn = new sqlconnection (connstri NG); try {// pass the parameters one by one to the preparecommand (CMD, Conn, null, struct type, plain text, commandparameters) in the sqlcommand parameter set using the preparecommand method; int val = cmd. executenonquery (); // clears the parameter list CMD in sqlcommand. parameters. clear (); Return val;} catch (exception e) {Throw new exception ("failed to execute the task:" + E. message + "" + plain text);} finally {cmd. dispose (); Conn. close (); Conn = NULL ;}# endregion # query the region stored procedure. // <summary> /// Execute a sqlcommand that returns the result set, use an existing database connection // use the parameter array to provide the parameter /// </Summary> /// <remarks> // usage example: /// datatable table = storegettable (Conn, commandtype. storedprocedure, "publishorders "); /// </remarks> /// <Param name = "connecttionstring"> an existing database connection </param> /// <Param name = "polictye"> sqlcommand type </param> /// <Param name = "plain text"> stored procedure name or T-SQL statement </param> /// <returns> Returns a table (datatable) indicates the queried dataset. </returns> Public datatable stroregettable (commandtype cmdtye, string cmdtext, Params sqlparameter [] commandparameters) {sqlcommand cmd = new sqlcommand (); datatable DS = new datatable (); sqlconnection conn = new sqlconnection (connstring); try {preparecommand (CMD, Conn, null, Tye, plain text, commandparameters); sqldataadapter adapter = new sqldataadapter (); adapter. selectcommand = cmd; adapter. fill (DS );/ /Return a table set return Ds;} catch (exception e) {Throw new exception ("failed to execute the task:" + E. message + "" + plain text);} finally {cmd. dispose (); Conn. close (); Conn = NULL ;}} # endregion # region prepare parameters for the execution command /// <summary> /// prepare parameters for the execution command /// </Summary> /// <Param name = "cmd"> sqlcommang command </param> /// <Param name = "conn"> existing database connection </param> /// <Param name = "trans"> Database Transaction process </param> /// <Param name = "partition type"> sqlcommand command type (stored procedure, t-S QL statement, and so on .) </Param> /// <Param name = "plain text"> command text, T-SQL statement, for example: select * From sufei </param> // <Param name = "Param parms"> return the command with parameters </param> Private Static void preparecommand (sqlcommand cmd, sqlconnection Conn, sqltransaction trans, commandtype parameter type, string parameter text, sqlparameter [] parameter parms) {// determine the database connection status if (Conn. state! = Connectionstate. Open) Conn. open (); cmd. Connection = conn; cmd. commandtext = plain text; // determine whether transaction processing is required if (trans! = NULL) {cmd. Transaction = trans;} cmd. commandtype = partition type; If (partition parms! = NULL) {foreach (sqlparameter parm in parallel parms) cmd. Parameters. Add (parm) ;}# endregion }} turnvalue output