C # complete SQL stored procedure process

Alas, I only blame myself for being academic and not refined. Now I have learned the SQL stored procedure. I have found a lot of information from the Internet, but it is very messy and the positioning is high. It is not suitable for me to study such a dish, now I will summarize the complete process of using SQL stored procedures in C #. Take sql2005 as an example.

First, let's briefly talk about the stored procedure: the stored procedure is the SQL statement that is solidified in the SQL database system. The advantage of this is that it can improve execution efficiency, improve database security, and reduce network traffic. Next we will explain how to create a stored procedure in the database.

Open the sql2055 database, expand the "Database" node, find the database you are using (target database), expand the database node, find the "programmable" node, and expand, you can see the stored procedure. Right-click the stored procedure and choose create stored procedure. Then the query analyzer will pop up, where the input is createdCodeYou can.

The Code is as follows:

 
Create proc myinsert -- create a stored procedure named myinsert -- write parameters here, if any; if not, empty as -- write specific statements here, you can write n go -- add or remove it. Go indicates a new page, which is equivalent to the next function block. If you do not write the following statement, you can skip it!
 

For example:

Create proc myinsert @ username varchar (10), -- note the comma here. Multiple parameters are separated by commas @ password varchar (10), @ name varchar (10 ), @ usertype varchar (10), @ createpeople varchar (10) 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 describes how to manually create a stored procedure. In fact, you don't have to worry so much about it. Click "new query" in the upper left corner of sql2005 to open the query analyzer, and then add a sentence to the preceding statement: "Use jf_charging_system" means to use a database, that is, to create a stored procedure in which database.

For example:

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

The following describes how to call a stored procedure in C. A complete piece of code + Comments can help you understand everything! The C # code exactly corresponds to the stored procedure above.

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"; // name of the stored procedure to be called sqlconnection constr = new sqlconnection (strsql); // sqldatabase connection object, take the database link string as the parameter sqlcommand comstr = new sqlcommand (SQL, constr); // The SQL statement execution object. The first parameter is the statement to be executed, the second is the database connection object comstr. commandtype = commandtype. storedprocedure; // because the stored procedure is used, set the execution type to stored procedure. // set the Stored Procedure Parameter comstr in sequence. 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 the database connection MessageBox. show (comstr. executenonquery (). tostring (); // execute the Stored Procedure constr. close (); // close the connection

hope to help you!