Use of SQL Stored procedure instances in. net

◆ Standard component programming is allowed for stored procedures

◆ Fast execution of stored procedures

◆ Stored procedures can reduce network traffic

◆ Stored procedures can be fully utilized as a security mechanism

The author of this article will introduce it to you. NET database application stored procedures, and how to associate it with ADO.. NET SqlDataAdapter object, DataSet object, and so on. the overall performance of the. NET database application.

Create a simple stored procedure

1. Stored Procedures for queries without parameters

The code is as follows:	Copy code
Create proc [dbo]. [SelectUsers] AS Begin SELECT * from dbo. FMS_Users End

Run the following sentence to obtain the result:

Exec SelectUsers

After creating the stored procedure, save it. After saving, the node corresponding to the stored procedure appears in the server resource manager. Note that the CREATE keyword in the code editing window is changed to the ALTER keyword, which is used to change any existing stored procedure. To run the above stored procedure, just click its node and right-click the menu to execute the stored procedure ",
 

Well, we will call it in the IDE environment:

Write a method and call it in a proper place.

 

The code is as follows:

Copy code

Private void Stroedprocedure ()         { String con = System. Configuration. ConfigurationManager. ConnectionStrings ["consql"]. ToString (); SqlConnection conn = new SqlConnection (con ); Conn. Open (); // Name of the stored procedure and database connection SqlCommand cmd = new SqlCommand ("SelectUsers", conn ); // Cmd. CommandType = CommandType. StoredProcedure; // Cmd. CommandText = "SelectUsers "; // Cmd. Connection = conn; SqlDataAdapter da = new SqlDataAdapter (); Da. SelectCommand = cmd; DataTable ds = new DataTable (); Da. Fill (ds );             GridView1.DataSource = ds; GridView1.DataBind ();                                }

OK has completed the instance call of the stored procedure without parameters!

Second, call the stored procedure with parameters (the same principle)

Create stored procedure statement

The code is as follows:	Copy code
Create proc [dbo]. [SelectParUsers] ( @ Id int ) As Begin Select * from dbo. FMS_Users where userId = @ id End

Run the following statement to obtain the result:

Exec SelectParUsers 22

Search for data by a specific ID

 

The code is as follows:

Copy code

Private static DataTable Stroedprocedure (int id)         { String con = System. Configuration. ConfigurationManager. ConnectionStrings ["consql"]. ToString (); SqlConnection conn = new SqlConnection (con ); Conn. Open (); SqlCommand cmd = new SqlCommand ("SelectParUsers", conn ); Cmd. CommandType = CommandType. StoredProcedure; // Cmd. CommandText = "SelectParUsers "; // Cmd. Connection = conn; SqlDataAdapter da = new SqlDataAdapter (cmd ); Cmd. Parameters. Add ("@ id", SqlDbType. Int, 32). Value = id; // SqlDataAdapter da = new SqlDataAdapter (cmd ); // Da. SelectCommand = cmd; DataTable ds = new DataTable (); Da. Fill (ds );             Return ds                     }

OK, the basic data query stored procedure ends. Next, add, delete, and modify operations.