C # Calling stored procedures

A stored procedure (Stored Procedure) is a process used to store data table operations, an object that stores data table operations together and is stored in a database.

Pros : 1. Reduce the amount of network traffic: Pass the name and parameter of the stored procedure to invoke execution without transferring SQL.

2. Efficient execution: SQL Server compiles stored procedures into executable binaries beforehand, without having to compile again when running stored procedures.

3. Encapsulation: can also be called modular programming, the implementation of a function of a number of SQL encapsulated into an object, multiple calls can be repeated, the portability is strong.

4. Security: Different stored procedures can be used for users with different permissions.

Here's how C # invokes a well-written stored procedure, which encapsulates several functions for data manipulation

1  //SQL statements that perform additions and deletions to tables2  Public Static intExecuteCommand (stringtext)3         {4             using(SqlConnection conn =NewSqlConnection (connectionString))5             {6 Conn. Open ();7                 using(SqlCommand cmd =NewSqlCommand (text, conn))8                 {9                     inti =cmd. ExecuteNonQuery ();Ten                     returni; One                 }                A             }             -         } -  the  Public Static intExecstoredprocedure (stringProcName,paramssqlparameter[] Parameters) -         { -             intRTN =0; -             using(SqlConnection conn =NewSqlConnection (connectionString)) +             { - Conn. Open (); +                 using(SqlCommand cmd =Conn. CreateCommand ()) A                 {                    atSqlTransaction st =Conn. BeginTransaction (); -Cmd. Transaction =St; -                     Try -                     { -Cmd.commandtext =procname; -Cmd.commandtype =CommandType.StoredProcedure; in cmd. Parameters.addrange (Parameters); -rtn=cmd. ExecuteNonQuery (); to st.commit (); +                         returnRtn; -                     } the                     Catch(SqlException Sqlex) *                     { $ St. Rollback ();Panax Notoginseng                         ThrowSqlex; -                     }                                        the                 } +             } A         } the          Public Static intExecutestoredprocedure (stringprocname, +             paramssqlparameter[] Parameters) -         { $             using(SqlConnection conn =NewSqlConnection (connectionString)) $             { - Conn. Open (); -                 using(SqlCommand cmd =Conn. CreateCommand ()) the                 { -Cmd.commandtext =procname;WuyiCmd.commandtype =CommandType.StoredProcedure; the cmd. Parameters.addrange (Parameters); -                     returncmd. ExecuteNonQuery (); Wu                 } -             } About              $}

View Code

Here are three ways to execute a stored procedure:

1 protected voidButton1_Click (Objectsender, EventArgs e)2     {3         stringMainname ="Dream Group";4         stringDetailname="Liu can | zhao Benshan | journal | Ouyang Fung";5         stringDetailage="23|32|18|19";6         intRtn=dbhelper.executecommand (string. Format ("EXEC dbo. Proc_testbatchmaindetailins ' {0} ', ' {1} ', ' {2} '", Mainname, Detailname,detailage));7     }8     protected voidButton2_Click (Objectsender, EventArgs e)9     {Ten         stringMainname ="set sail Group 2"; One         stringDetailname="Chi Yi 2| Qiao 2| Yang over 2| Li Mo Sorrow 2"; A         stringDetailage="18|28|jj|35"; -sqlparameter[] SPS =Newsqlparameter[] { -         NewSqlParameter ("@mainName", Mainname), the             NewSqlParameter ("@detailNameStr", Detailname), -              NewSqlParameter ("@detailAgeStr", Detailage) -         }; -         Try +         {            -             intRTN = Dbhelper.executestoredprocedure ("dbo. Proc_testbatchmaindetailins", SPS); +         } A         Catch(Exception ex) at         { - Response.Write (ex. Message); -         } -          -     } -     protected voidButton3_Click (Objectsender, EventArgs e) in     { -         stringMainname ="set sail Group 2"; to         stringDetailname ="Chi Yi 2| Qiao 2| Yang over 2| Li Mo Sorrow 2"; +         stringDetailage ="18|28|jj|35"; -sqlparameter[] SPS =Newsqlparameter[] { the         NewSqlParameter ("@mainName", Mainname), *             NewSqlParameter ("@detailNameStr", Detailname), $              NewSqlParameter ("@detailAgeStr", Detailage)Panax Notoginseng         }; -         Try the         { +             intRTN = Dbhelper.execstoredprocedure ("dbo. Proc_testbatchmaindetailins", SPS); A         } the         Catch(Exception ex) +         { - Response.Write (ex. Message); $         } $}

View Code

It is recommended to use the third method, the transaction processing, if only one does not pass all rollback, to avoid dirty data generation. Transactions can also be used in stored procedures, and subsequent updates will continue ...

C # Calling stored procedures