Explanation and application of C # database stored procedures

in a simple sales system using VS 2012+sql Server, it is common to encounter some use of stored procedures, and what exactly is a stored procedure, what is the benefit of it? If you create a stored procedure in SQL Server, how does C # contact the stored procedure? A. Stored Procedures A stored procedure (Stored Procedure) is a large database system with a set of SQL program code that completes a particular function. It is already compiled and stored in the database, and users can execute it by using the stored procedure name and parameters. As for the benefits of stored procedures:
1. Faster execution Speed
stored procedures are compiled and optimized when they are created, and when they are invoked, the relevant information is saved in the database, while the memory retains a stored procedure, and the next call can be made directly from memory, without consuming the cup resource.
2. Stored procedures can reduce network traffic
the operation of a hundred-line T-SQL code can be replaced by executing a procedure code without having to send hundreds of lines of code across the network.
3. Stored procedures allow modular design
when created, you can call multiple times in your program, which improves the maintainability of your application and allows you to access the database uniformly with your application.
4. Higher security for stored procedures
You can use the security mechanism of the database to restrict access to the database, such as authorizing a user to perform only the inability to modify stored procedures, while its stored procedures can strengthen the security of the application from SQL injection tools.
5. Stored procedures allow deferred binding
You can put a reference to a table that does not already exist, check the syntax only during the creation of the stored procedure, know that the stored procedure is compiled the first time, and if the referenced table does not exist, you can create the stored procedure, but the run will fail.
Here are some of my knowledge of the stored procedures, I recommend that you use the stored procedures when doing the system. Some of the above knowledge leads to Zhou Zhiqui's database system principles, and if you want to know more about stored procedures, you can access the blog (recommended): http://www.cnblogs.com/tjsquall/archive/2008/02/04/1064402.html
two. How SQL Server uses stored procedures

What if you use a stored procedure in SQL Server? The creation statement for the stored procedure is mainly as follows: When the creation completes, the stored procedure can be executed through execute .

Create procedure stored procedure name [parameter 1,.... parameter n]
as
begin
Transact SQL statement block;
End
--executes the
execute stored procedure name [parameter 1,.... parameter n]

For instance: I created a telephone table in the database using create, and inserted the data into the table using the INSERT, now want to use the stored procedure to query specific mobile phone brand "Nokia" Mobile information, the code is as follows:

--===========================================
--Create a stored procedure (i)
--Mobile Phone inventory query search phone brand
--======================= ====================
--Create a stored procedure
procedure Pselcetphonename
@Pname varchar as
begin
Select Phonename as mobile phone brand, phoneclass as mobile phone model, Phoneid as serial number,
price as factory prices, arrtime as purchase time, period as warranty time
fro M telephone where phonename= @Pname end-
-Delete stored procedure
drop procedure Pselcetphonename--
execute stored procedure
Execute pselcetphonename ' Nokia '

when the execut is executed, the following message is displayed:

At the same time, the following introduction of an updated stored procedure code, to facilitate everyone extrapolate. Its function is to update the user name "BBBBB" to "123456" and the user as "Salesperson". Execute code, then select Query Users table to find information update .

--===========================================--
Create a stored procedure (v)-
-Administrator modifies employee information stored procedures--
====================== =====================
CREATE PROCEDURE Pupdateuser
(@pwd varchar, @role varchar, @name varchar (20))
as begin
update Users 
set
userpwd = @pwd, userrole= @role
where username = @name
End
--Execute stored procedure
execute pupdateuser ' 123456 ', ' Salesperson ', ' bbbbb '

three. C # WinForm How to combine stored procedures 1. Interface Design

The design interface as shown in the following figure, mainly has DataGridView (display data table), button (button), the textbox composition.

2. Source Code Explanation

its core code and steps are as follows:

SqlConnection con = new SqlConnection ("Database Service");            Define SQL Server Connection object
Sqlconnection.open ();                                          Open database connection
SqlCommand com = new SqlCommand ("Stored Procedure name", database connection object);     Create a stored procedure using command
Sqlcommand.commandtype = CommandType.StoredProcedure;          Set Command object type to stored procedure
SqlCommand.Parameters.Add ();                                   Add parameters and assign values to 
SqlCommand.Parameters.Add ("@Pname", SqlDbType.NVarChar);   Add Narchar (20) type @pname
sqlcommand.executenonquery ();                                  Execute stored procedure
sqlconnection.close ();                                         Close connection
sqlconnection.dispose ();                                       Releasing resources

The source code is as follows, first Add Namespaces

The newly added namespace
using System.Data.SqlClient;
Using System.Reflection;

Click the button to modify its function as follows

private void Button1_Click (object sender, EventArgs e) {//Define database connection statement: Server =. ( Local) database name =phonems (mobile phone management System) String consqlserver = "Data source=.;i
    Nitial catalog=phonems;integrated security=true; ";
    Define SQL Server Connection object SqlConnection con = new SqlConnection (Consqlserver); Open Connection con.
    Open ();
    Define the database execute an SQL statement or stored procedure SqlCommand com = new SqlCommand ("Pselcetphonename", con);  
    Specifies that the type is stored procedure com.commandtype = CommandType.StoredProcedure; Stored procedures Add variables and assign values to textBox1 com. Parameters.Add ("@Pname", SqlDbType.NVarChar, 20).
    Value = TextBox1.Text.ToString ();
    Define FETCH data SqlDataAdapter da = new SqlDataAdapter (COM);

    DataSet ds = new DataSet (); try {da.                                  Fill (DS); Fill Data Datagridview1.datasource = ds.      Tables[0]; Display in DataGridView} catch (Exception msg) {MessageBox.Show (msg).                  message); Exception Handling} finally {con.         Close ();                          Close connection con.                                 Dispose (); Release the Connection da.                                  Dispose ();
 Releasing Resources}}

3. Operating results

Summary: Hope that the article for everyone to help, the author has tried, if there are insufficient and do not like the place, forgive me.
PS: The author encountered a problem, in Csdn blog, named 1.2.3.4.5 line spacing is very large, how to set it next to each other. Want to know the person to tell, thank you!
finally I solved, using the method is in CSDN blog, click the "source code" edit the article, the <p></p> from its paragraph replaced by the <br/> Line, I hope you want to reduce the spacing, you can also do this operation.

(by:eastmount 2013-9-11 19 point http://blog.csdn.net/eastmount)