One. Preface: Stored Procedure is a set of SQL statements set to complete specific features, stored in the database after compiling. The user performs it by specifying the name of the stored procedure and gives the parameters (if the stored procedure has parameters). The stored procedure is an important object in the database, and any designed database application should be used in the stored procedure. In general, the stored procedure has some advantages:
◆ Storage procedure allows standard component programming
◆ The stored procedure can achieve faster execution speed
◆ Storage procedure can reduce network traffic
◆ The stored procedure can be taken out as a security mechanism.
The author of this article describes the application of the stored procedure in the .NET database application, and how to use the SqlDataAdapter objects in ADO.NET, using the DataSet object, etc. to improve the overall performance of the .NET database application.
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. Create a simple stored procedure:
Here I will introduce you how to use Visual Studio.NET IDE to create a stored procedure. It is very easy and intuitive to create a stored procedure using Visual Studio.NET IDE. You will find various database objects, including stored procedures, as needed in the server resource manager and expand node. Indicated.
On the stored procedure node, click the right button to pop up a menu, which contains the "New Stored Procedure" command. After the new store is created, the code editing window in the IDE appears as shown below:
Create Procedure DBo.StoredProcedure1 / * (@ parameter1 datatype = default value, @ parameter2 datatype outprut) * / as / * set nocount on * / return
The above code template meets the sympathetic rules that create stored procedures, the complete syntax rules are as follows:
Create Proc [EDURE] Procedure_name [; Number] [{@Parameter Data_Type} [Varying] [= default] [OUTPUT]] [, ... n] [with {recompile | encryption | Recompile, Encryption}] [for replication] As SQL_Statement [... n]
Limited to the space, the meaning of each parameter is not described here. Interested readers can refer to information about the SQL Server 2000 database management system. Below I will introduce the various grammar components in the code template. CREATE Procedure declares creates a stored procedure, followed by the name of the stored procedure. The ingredients in "/ * ... * /" are the parameters of the stored procedure, which can include input parameters and output parameters. The contents of the AS keyword are the main part of the stored procedure, where is any number and type of SQL statement included in the stored procedure. Return keywords indicate that the stored procedure ends and can return to the caller. Let's take a simple stored procedure and use:
Create Procedure DBo.up_getpublisherinfoas Select Pub_ID, Pub_Name, City, State, Countryfrom Publishersreturn, COUNTRYFROM PUBLISTURN
Save it after creating the above stored procedures. After saving, the node corresponding to the stored procedure will appear in the Server Explorer. At the same time, please note that the CREATE keyword in the code editing window changes to the AlTer key, the keyword is used to change any existing stored procedures. To run the above stored procedure, simply click on its node and select "Run Store" in the right-click pop-up menu, the result is shown below: IV. Create a stored procedure with parameters: We have created a simple stored procedure without parameters, and often uses many stored procedures with parameters in actual applications. Stored procedures with parameters are typically used to update data or insert data. Below we can use the same way to create a stored procedure with parameters:
CREATE PROCEDURE dbo.up_UpdatePublisherInfo (@pub_id char (4), @pub_name varchar (40), @ city varchar (20), @state char (2), @ country varchar (30)) ASUPDATE publishers SET pub_name = @pub_name, city = @city, state = @state, country = @country where (pub_id = @PUB_ID) Return
In the code of the creation stored procedure above, we declare the local variable-parameter of the stored procedure by adding a "@" flag before the name, and also declares the type of each parameter, determines the direction value of each parameter, that is It is indicated that the parameter is an input type or an output type or an input and output type or return value. The user can call the stored procedure by the corresponding stored procedure name and the correct and valid parameters. Also, you can use the Output keyword to add an output type parameter in the parameter, please refer to the above syntax rules. The output type parameters can be returned to the information related to the caller. The above stored procedure can update information about the corresponding publishers in the publishers table. You can select "Run Store" to perform it by clicking on the node of the stored procedure. Once executed, the IDE pops up a dialog box to enter the publisher information (as shown in Figure 3). Fill in the dialog box into the correct and efficient update information, pay attention to the value of the PUB_ID must exist in the original table, and then click the "OK" button to update the data. Fives. Create a database application for a simple stored procedure: Below we use the above-mentioned non-parametric stored procedures to a database application, which also uses the SqlDataAdapter object in ADO.NET and the DataSet object. The SqlDataAdapter objects are linked together as the bridge of the SQL Server database and the DataSet object. The SQLDataAdapter object contains two common methods: Fill () methods and update () methods. The Fill () method can get the corresponding data from the database and populate it into the DataSet object, and the Update () method is the meaning of the update data set. Before calling a fill () method, we must set the SELECTCOMMAND attribute of the SqlDataAdapter object, which is actually a SQLCommand object. The SelectCommand property contains a valid SQL statement and can obtain the corresponding data from the database from the database and pop it into the DataSet object. First, we create a Windows Forms application, the programming language is C #. After creating a new item in Visual Studio.NET, add a new class -Publishers class to the project, which encapsulates the business logic that connects to the background database and gets the data set object. The steps are as follows: 1. Add the necessary namespace reference: use system.data.sqlclient; 2. Add some of the necessary variables to this class: private sqlConnection CNPUBS; Private SqlCommand CMDPUBS; Private SqlDataAdapter Dapubs; Private Dataset DSPUBS;
3. Complete the connection background database in this class, obtain business logic, etc. of SqlDataAdapter objects:
Public Publishers () {Try {// Create a database connection object cnpubs = new SqlConnection ("Server = localhost; integrated; // Create a SQLCOMMAND object and specify its command type to store procedure cmdPubs = new SqlCommand (); cmdPubs.Connection = cnPubs; cmdPubs.CommandType = CommandType.StoredProcedure; cmdPubs.CommandText = "up_GetPublisherInfo"; // Create a SqlDataAdapter object, which is set as above SelectCommand property SqlCommand object daPubs = new SqlDataAdapter (); dapubs.selectcommand = cmdpubs; // Create a DataSet object dspus = new dataset ();} catch (exception) {}} 4. Finally, a getPublisherInfo () method is provided, which fills the DataSet object with the SqlDataAdapter object and returns the padded DataSet object. The method is as follows (notably: the SqlDataAdapter object implicitly opens the database connection and hidden after obtaining the data Turn off the connection, this is to say that the DataSet object is working in non-connection mode. And when you explicitly open the database connection and get the data, the SqlDataAdapter object does not turn the connection to close): public dataset getpublisherinfo () { // Call the Fill () method of the SQLDataAdapter object and return Data Set 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 the DataSet object. data. First add the following member variables to the main form class:
Private Publishers Pubs; Private Dataset DS; After modifying the constructor of the main form class, the constructor is as follows: public form1 () {// // Windows Form Designer Support for // InitializeComponent (); //// Todo: Add any constructor code // pubs = new public publisherinfo (); datagrid1.datasource = DS.TABLES [0];} This application starts a DataGrid control of the main form. In the case of the corresponding data acquired from the PUBS database, the program run is shown below: 6. Create a database application for stored procedures with parameters: Above we created an application with a stored procedure with parameters, let's create a more complex database application. In actual database applications, we often need to get data and update, insert or delete data. At this time, we need to use the parameters stored procedures, while we call it UPDATE () when we use the SqlDataAdapter object. method. This Update () method automatically completes the corresponding operation according to the changes in the records of each record in the DataSet object. The SqlDataAdapter object also contains updateCommand, InsertCommand, deleteCommand, etc., which are actually SQLCommand objects. Update () method selection according to the type of operations. When establishing a database application with parameters, we generally use the SQLParameter class, which encapsulates the properties and methods related to the SQL parameter. The properties include ParameterName, SqldbType, Direction, Size, Value, SourceColumn, and SourceVersion. Where parametername, SqldbType, Direction, Size, etc. are used to match the parameters defined during the stored procedure. For example, the SQLParameter objects defined below are "@PUB_ID" parameters used to match the previously defined UP_UPDATEPUBLISHERINFO process. SQLParameter Updparam = New SqlParameter ("@Pub_ID", SqldbType.char, 4); In the above definition, although the Direction property is not explicitly given, its default value is INPUT, so our needs are satisfied. And if a Direction property of a SqlParameter object is InputOutput or Output or ReturnValue, its Direction property must clearly explain, such as the following code, clearly declares that a Direction property of a SQLParameter object is Output. OPAram.direction = parameterDirection.output; where the SourceColumn property is used to match the DataColumn object in a DataTable object, this match can be implicitly imported into the desired SQLParameter object when the Update () method updates the DataTable object. If you do not declare this property when defined, you must explicitly explain the SourceColumn property of the SQLParameter object in your code.
The default value of the SourceVersion property is the current value in the DATAROW object, that is, to update the value in the database. Of course, the SourceVersion property can also point to the original value in the DataRow object corresponding field, that is, the initial value obtained from the database. In the database transaction system, the synchronization problem of data is very important. Let's create a stored procedure that can detect data synchronization. CREATE PROCEDURE dbo.up_UpdatePublisherName (@pub_id char (4), @ pub_name varchar (40), @Original_pub_name varchar (40)) AS if exists (select pub_id from publishers where (pub_id = @pub_id) AND (pub_name = @Original_pub_name)) Begin Update Publishers Set Pub_Name = @Pub_Name WHERE (Pub_ID = @PUB_ID) Endreturn Next, we call this stored procedure in the above application to update the publisher's name. First, improve its business logic class -Publishers based on the original application:
1. Add a new SQLCOMMAND object that can be used as the UpdateCommand property of the SqlDataAdapter object: private SQLCommand cmdupdpubs; 2. Update this class's constructor () function, add the following: // Create another SQLCommand object, the object references the stored procedure cmdupdpubs = new sqlcommand (); cmdupdpubs.commandtype = CommandType.StoredProcedure; cmdUpdPubs.CommandText = "up_UpdatePublisherName"; // add to the above essential parameters SqlCommand object cmdUpdPubs.Parameters.Add ( "@pub_id", SqlDbType.Char, 4, "pub_id"); cmdUpdPubs.Parameters.Add ( "@pub_name", SqlDbType.VarChar, 40, "pub_name"); SqlParameter updParam = new SqlParameter ( "@Original_pub_name", SqlDbType.VarChar, 40, "pub_name"); updParam.SourceVersion = DataRowVersion.Original; cmdUpdPubs.Parameters .Add (Updparam); 3. Specifies the UpdateCommand property of the SqldataAdapter object to the SQLCommand object defined above: dapubs.UpdateCommand = cmdupdpubs; 4. Add Method UpdatePublisherName (): Public Void UpdatePublisherName (Dataset DSChanges) {// Update All Change Dapubs.Update (DSChanges);} After the application's business logic is perfect, add a name "Update Data Set" on the main form The button and the event response function added to this button is as follows:
