C # Using SQL stored procedure complete process

 C # Using SQL stored procedure complete processReprint Address: http://blog.csdn.net/yangyuankp/article/details/7057922Tags: storage sqlc# database Stringsecurity2011-12-09 17:29 8164 People read Comments (1)CollectionReportThis article has been included in:Category: Technical Article (a) Author of similar articles X

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Alas, only blame their own academic not fine, now learning SQL stored procedures, from the Internet to find a lot of information, but very messy, positioning is relatively high, not suitable for me to learn the side dishes, now I will summarize the C # Use SQL stored procedures complete process, take SQL2005 as an example.

Let's just say what a stored procedure is: a stored procedure is a SQL statement that is cured inside a SQL database system, and the benefit of doing so is to improve execution efficiency, improve database security, and reduce network traffic. The next step is to set up a stored procedure in the database.

Open the SQL2055 database, expand the Databases node, locate the database you are using (the destination database), expand the Database node, find the "programmability" node and expand, you can see the "stored procedure", right-click on the "Stored procedure" and create a new stored procedure. Then the query parser pops up, where you can enter the creation code.

The code is as follows:

[SQL]View PlainCopyPrint?

1. Create proc Myinsert --Create a stored procedure named Myinsert   
2. --Write the parameters here, if any;   
3. as   
4. --Write a specific statement here, can write n   
5. Go--can be added without adding, go means another page, equivalent to the next function block. If the bottom does not write the statement, can not add!   

Create proc Myinsert--Creates a stored procedure named myinsert--here to write the parameters, if any; no, it's empty. as--here to write a specific statement, you can write n go--can be added without, go means another page, equivalent to the next function block. If the bottom does not write the statement, can not add!

For example:

[SQL]View PlainCopyPrint?

2. Create proc Myinsert
4. @username varchar,--note the comma here, with multiple arguments separated by commas
6. @Password varchar(TEN),
8. @name varchar(TEN),
10. @usertype varchar(TEN),
12. @createpeople varchar($)
14. as   
16. Insert into systemusers (UserName,PassWord,Name, usertype,creatpeople)values(@username, @password, @name , @usertype, @createpeople)
18. Go

create proc myinsert@username varchar,--Notice the comma here, the parameters are separated by commas @password varchar (ten), @name varchar (ten), @usertype varchar (+), @createpeople varchar asinsert into systemusers (username,password,name,usertype,creatpeople) values (@username, @password, @name, @usertype, @createpeople) go

This stored procedure can insert a record into the Systemusers table.

The above is to manually set up the method of stored procedures, in fact, do not have to be so troublesome, directly click on the SQL2005 in the upper left corner of the "new query", open the Query Analyzer, and then add a sentence in the upper statement: "Use Jf_charging_system" It means using a database, which is the database in which the stored procedure is established.

For example:

[SQL]View PlainCopyPrint?

2. Use Jf_charging_system
4. Go
6. Create proc Myinsert
8. @username varchar(TEN),
10. @Password varchar(TEN),
12. @name varchar(TEN),
14. @usertype varchar(TEN),
16. @createpeople varchar($)
18. as   
20. Insert into systemusers (UserName,PassWord,Name, usertype,creatpeople)values(@username, @password, @name , @usertype, @createpeople)
22. Go

Use jf_charging_systemgocreate proc myinsert@username varchar, @password varchar, @name varchar, @usertype varchar (+), @createpeople varchar asinsert into systemusers (username,password,name,usertype,creatpeople) values (@username, @password, @name, @usertype, @createpeople) go

Here's how to call a stored procedure in C #. Nonsense, but say, a complete code + comments let you know everything! This C # code is completely corresponding to the stored procedure above.

[CSharp]View PlainCopyPrint?

2. string strSQL = "Data source=192.168.24.53;initial Catalog=jf_charging_system; Persist Security info=true; User Id=sa; Password=1 "; //Database link string   
4. string sql = "Myinsert" ; //The name of the stored procedure to invoke   
6. SqlConnection constr = new SqlConnection (strSQL); //sql database Connection object, with database link string as parameter   
8. SqlCommand comstr = new SqlCommand (sql, constr); The //sql statement executes the object, the first argument is the statement to be executed, and the second is the database connection object   
10. Comstr.commandtype = CommandType.StoredProcedure; //Because the stored procedure is to be used, the set execution type is stored procedure   
12. //Set parameters of the stored procedure in sequence   
14. COMSTR.PARAMETERS.ADD ("@username", SqlDbType.VarChar, 10).  Value = "one";
16. COMSTR.PARAMETERS.ADD ("@password", SqlDbType.VarChar, 10).  Value = "one";
18. COMSTR.PARAMETERS.ADD ("@name", SqlDbType.VarChar, 10).  Value = "one";
20. COMSTR.PARAMETERS.ADD ("@usertype", SqlDbType.VarChar, 10).  Value = "one";
22. COMSTR.PARAMETERS.ADD ("@createpeople", SqlDbType.VarChar, 10).  Value = "one";
24. Constr.open (); //Open database connection   
26. MessageBox.Show (Comstr.executenonquery (). ToString ()); //Execute stored procedure   
28. constr.close (); Close connection   

            String strSQL = "Data source=192.168.24.53;initial catalog=jf_charging_system; Persist Security info=true; User Id=sa; Password=1 ";//database link String sql =" Myinsert ";//the name of the stored procedure to invoke SqlConnection Constr = new SqlConnection ( strSQL);//sql database Connection object, the database link string is the parameter SqlCommand comstr = new SqlCommand (sql, CONSTR),//sql statement execution object, the first argument is the statement to execute, the second is            Database Connection Object Comstr.commandtype = commandtype.storedprocedure;//because you want to use a stored procedure, set the execution type to the stored procedure//Set the parameters of the stored procedure sequentially COMSTR.PARAMETERS.ADD ("@username", SqlDbType.VarChar, 10).            Value = "11"; COMSTR.PARAMETERS.ADD ("@password", SqlDbType.VarChar, 10).            Value = "11"; COMSTR.PARAMETERS.ADD ("@name", SqlDbType.VarChar, 10).            Value = "11"; COMSTR.PARAMETERS.ADD ("@usertype", SqlDbType.VarChar, 10).            Value = "11"; COMSTR.PARAMETERS.ADD ("@createpeople", SqlDbType.VarChar, 10).            Value = "11"; Constr.open ();//Open database Connection MessageBox.Show (Comstr.executenonquery (). Tostring ());//execute Stored procedure constr.close ();//close connection 

hope to help everyone!

C # Using SQL stored procedure complete process