. Application of stored procedures in the net database application

program | stored Procedure | data | database

A Objective:

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to complete a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures. In general, stored procedures have the following advantages:

Stored procedures allow standard component programming.

Stored procedures can achieve faster execution speed.

Stored procedures can reduce network traffic.

Stored procedures can be fully exploited as a security mechanism.

The author of this article will introduce to you. NET database application, and how to use it in conjunction with SqlDataAdapter objects, dataset objects, and so on in ado.net to improve the overall performance of. NET database applications.

Two System Requirements:

Development tools: Visual Studio.NET

Database management system: SQL Server 2000 (which contains the pubs database used by the sample program)

three. To create a simple stored procedure:

Here I'll show you how to use the Visual Studio.NET IDE to create stored procedures. Using the Visual Studio.NET IDE to create stored procedures is very easy and intuitive, and you will find a variety of database objects, including stored procedures, as long as you are directed to the pubs database and expand the nodes in Server Explorer.

Right-click on the stored Procedure node to pop up a menu that contains the command for new stored procedure. After you create a new stored procedure, the Code editing window in the IDE appears with the following code template:

CREATE PROCEDURE dbo. StoredProcedure1/* (@parameter1 datatype = default value, @parameter2 datatype OUTPUT) */AS/* SET NOCOUNT on/return the code template above conforms to the simplified syntax rules for creating stored procedures, and the complete syntax rules are as follows: Create PROC [edure] procedure_name [; number] [{@parameter data_type} [VAR YING] [= default] [OUTPUT]] [,... n] [with {RECOMPILE | Encryption | RECOMPILE, encryption}] [for REPLICATION] as sql_statement [... n]

Limited to space, the meaning of each parameter is not much described here, interested readers can refer to the SQL Server 2000 database management System information.

Here's a brief introduction to the various grammatical components in the code template. The CREATE PROCEDURE declaration creates a stored procedure followed by the name of the stored procedure. The ingredient in "/*......*/" is the parameter of the stored procedure, which can include input parameters and output parameters. The AS keyword follows the body part of the stored procedure, which is any number and type of SQL statements contained in the stored procedure. The return keyword indicates that the stored procedure ends and returns an integral state value to the caller. Here we'll create a simple stored procedure with no parameters and use it:

CREATE PROCEDURE dbo.up_getpublisherinfoas SELECT pub_id, pub_name, City, state, Countryfrom Publishersreturn

After you create the above stored procedure, save the. Once saved, the node that corresponds to the stored procedure appears in Server Explorer. Also, note that the Create keyword in the Code editing window becomes the ALTER keyword, which is used to change any existing stored procedure. To run the stored procedure above, simply click on its node and select "Run Stored Procedures" in the right-click pop-up menu.

Four. To create a stored procedure with parameters:

We have created a simple stored procedure with no parameters, and many stored procedures with parameters are often used in practical applications. Stored procedures with parameters are typically used to update data or to insert data. Here's how we can create a stored procedure with parameters using the same procedure:

CREATE PROCEDURE Dbo.up_updatepublisherinfo (@pub_id char (4), @pub_name varchar (), @city varchar (), @state char (2), @country varchar) asupdate publishers SET pub_name = @pub_name, city = @city, state = @state, country = @country whe RE (pub_id = @pub_id) return

In the above code to create the stored procedure, we declare the local variable-parameter of the stored procedure by adding a "@" flag before the name. It also declares the types of each parameter, determines the directional value of each parameter, and indicates whether the parameter is an input or output type or a return value type. The user can invoke the stored procedure name and the correct valid parameters. Also, you can add output parameters to parameters by using the Output keyword, refer to the syntax rules above. Output-type parameters can return information to the caller.

The above stored procedure can update the information of the corresponding publisher in the publishers table. You can execute it by clicking on the node of the stored procedure and selecting "Run Stored Procedures" in the right-click pop-up menu. Once executed, a dialog box for entering publisher information pops up in the IDE. Fill in the correct and valid update information in the dialog box, note that the value of pub_id must exist in the original table, and then click OK to update the data.

Five. To create a database application for a simple stored procedure:

Here we use the above stored procedure without parameters to a database application, which also uses the SqlDataAdapter object and DataSet object in Ado.net. The SqlDataAdapter object is linked together as a bridge between the SQL Server database and the DataSet object. The SqlDataAdapter object contains two commonly used methods: the Fill () method and the update () method. The fill () method can fetch the corresponding data from the database and populate it into the DataSet object, and the update () method is the name of the update dataset. Before calling the fill () method, we must set the SelectCommand property of the SqlDataAdapter object, which is actually a SqlCommand object. The SelectCommand property contains a valid SQL statement that can then be fetched from the database and populated into the DataSet object.

First, we create a Windows Forms application, the programming language is C #. After you create a new project in Visual Studio.NET, add a new class-publishers class to the project that encapsulates the business logic for connecting to the background database and getting the DataSet object. The steps are as follows:

1. Add the necessary namespace reference: using System.Data.SqlClient;

2. Add the following necessary variables to the class:

Private SqlConnection cnpubs; Private SqlCommand Cmdpubs;private SqlDataAdapter dapubs; Private DataSet dspubs;

3. Complete the connection to the backend database in the constructor of the class, and obtain business logic such as SqlDataAdapter object:

Public Publishers () {try{//Create a database Connection object cnpubs = new SqlConnection ("Server=localhost;integrated security=true;database =pubs "); Creates a SqlCommand object and indicates that its command type is stored procedure cmdpubs = new SqlCommand (); Cmdpubs.connection = cnpubs; Cmdpubs.commandtype = CommandType.StoredProcedure; Cmdpubs.commandtext = "Up_getpublisherinfo"; Creates a SqlDataAdapter object, sets its SelectCommand property to the SqlCommand object above dapubs = new SqlDataAdapter (); Dapubs.selectcommand = cmdpubs; Create a DataSet Object dspubs = new DataSet ();} catch (Exception) {}}

4. Finally, the class is provided with a getpublisherinfo () method that populates the DataSet object with the SqlDataAdapter object and returns the filled DataSet object. The method is as follows (it is noteworthy that the SqlDataAdapter object implicitly opens the database connection and closes the connection implicitly after fetching the data, which means that the DataSet object works in connectionless mode.) And when you explicitly open a database connection and get the data, the SqlDataAdapter object does not close the connection:

The public DataSet Getpublisherinfo () {//calls the Fill () method of the SqlDataAdapter object and returns the DataSet Object Dapubs.fill (dspubs); return dspubs;}

After completing the design of the publishers class, we add a DataGrid control to the main form and use it to display data from the DataSet object. First, add the following member variables to the main form class:

Private publishers pubs; Private DataSet DS; After that, modify the constructor of the main form class as follows: Public Form1 () {//////InitializeComponent () required for Windows Forms Designer support;////TODO: In Initia Add any constructor code//pubs = new Publishers ();d s = pubs after lizecomponent call. Getpublisherinfo ();d Atagrid1.datasource = ds. Tables[0];}

This allows the application to start the DataGrid control of the main form to display the corresponding data obtained from the pubs database using the stored procedure with no parameters described above.

Six. To create a database application with a stored procedure with parameters

Above we created an application with no parameters stored procedures, and here we will create a more complex database application. In the actual database application, we often need to get the data and update, insert or delete data, then we need to use the stored procedures with parameters, while using the SqlDataAdapter object, we will call its update () method. The update () method automatically completes the action based on the changes in the records in the DataTable object in the DataSet object. The SqlDataAdapter object also contains properties such as UpdateCommand, InsertCommand, DeleteCommand, which are actually SqlCommand objects. The Update () method chooses the appropriate properties based on the type of operation.

When using stored procedures with parameters to build database applications, we typically use the SqlParameter class, which encapsulates a variety of properties and methods related to SQL parameters. The properties include Parametername,sqldbtype,direction,size,value,sourcecolumn and SourceVersion. Where properties such as parametername,sqldbtype,direction,size are used to match the parameters defined in the stored procedure. For example, the SqlParameter object defined below is used to match the "@pub_id" parameter in the previously defined Up_updatepublisherinfo stored procedure.

SqlParameter Updparam = new SqlParameter ("@pub_id", SqlDbType.Char, 4);

In the above definition, although the direction attribute is not explicitly given, its default value is input, so it satisfies our needs. If the direction attribute of a SqlParameter object is inputoutput or output or returnvalue, then its direction attribute must be explicitly stated, For example, the following code explicitly declares that a SqlParameter object's direction property is output.

Oparam.direction = ParameterDirection.Output;

The SourceColumn property is used to match the DataColumn object in a DataTable object, which implicitly imports the required SqlParameter object when the update () method is invoked to update the DataTable object. If you do not declare the attribute at definition, you must explicitly state the SourceColumn property of the SqlParameter object in your code.

The default value for the SourceVersion property is the current value in the corresponding field in the DataRow object, which is the value to update to the database. Of course, the SourceVersion property can also point to the original value in the corresponding field of the DataRow object, which is the initial value obtained from the database. In the database transaction processing system, the data synchronization problem is very important, below we can establish one to be able to detect the data synchronization the stored procedure.

CREATE PROCEDURE dbo.up_updatepublishername (@pub_id char (4), @pub_name varchar (), @Original_pub_name varchar) as If exists (select pub_id from publishers where (pub_id = @pub_id) and (pub_name = @Original_pub_name)) Begin UPDATE Publis Hers SET pub_name = @pub_name WHERE (pub_id = @pub_id) Endreturn

We then call the stored procedure in the above application to update the publisher's name. First of all, on the basis of existing applications to improve their business logic class-publishers class:

1. Adds a new SqlCommand object that can be used as the UpdateCommand property of the SqlDataAdapter object:

Private SqlCommand cmdupdpubs;

2. Update the constructor Publishers () function for the class and add the following:

Creates another SqlCommand object that references the stored procedure cmdupdpubs = new SqlCommand () that updates the Publisher name; Cmdupdpubs.connection = Cnpubs;cmdupdpubs.commandtype = Commandtype.storedprocedure;cmdupdpubs.commandtext = "Up_ Updatepublishername ";//Add necessary parameters to the SqlCommand object above CmdUpdPubs.Parameters.Add (" @pub_id ", SqlDbType.Char, 4," pub_id "); CMDUPDPUBS.PARAMETERS.ADD ("@pub_name", SqlDbType.VarChar, "pub_name"); SqlParameter Updparam = new SqlParameter ("@Original_pub_name", SqlDbType.VarChar, "pub_name"); Updparam.sourceversion = Datarowversion.original;cmdupdpubs.parameters.add (Updparam); 3. Specifies that the UpdateCommand property of the SqlDataAdapter object is the SqlCommand object defined above: Dapubs.updatecommand = cmdupdpubs; 4. Add Method Updatepublishername (): public void Updatepublishername (DataSet dschanges) {//Update all Changes dapubs.update (dschanges);}

After the application's business logic class is perfected, add a button named "Update DataSet" on the main form and add the event response function for the button as follows:

private void Button1_Click (object sender, System.EventArgs e) {if (DS). HasChanges ()) {pubs. Updatepublishername (ds. GetChanges ()); Ds. Clear (); ds = pubs. Getpublisherinfo (); } }

So far, the application's business logic classes and the main form classes have all been updated, and now the application can update the relevant content in the database based on the user's conversion. After you update the data, you can also open the corresponding table in SQL Server and verify that the data has been updated successfully.

Seven. Summary:

This article introduces you to the basics of stored procedures as well as the. NET database applications to build data-driven applications that combine SqlDataAdapter objects, dataset objects, and so on. In this paper, we use two kinds of stored procedures: a simple stored procedure with no parameters, the application of the method is relatively easy, and the other is a stored procedure with parameters, in the invocation of this class of stored procedures must also apply to the SqlParameter object. At the same time, it is not difficult to find that it is a good design method to encapsulate data update business logic in stored procedures, which can improve the manageability, scalability and security of the database. Similarly, the business logic for inserting data and deleting data can also be encapsulated in stored procedures and used in similar ways in applications. Finally, I hope this article has a lot of help to everyone.