Stored Procedure is a set of SQL statements set to complete specific functions, 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.storedProcedure11
/ *
(
@ Parameter1 DataType = Default Value,
@ parameter2 DataType Output)
* /
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_getpublisherinfo
AS
SELECT PUB_ID, PUB_NAME, CITY, State, Country
From publishers
Return
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 a lot of stored procedures with parameters in practical 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)
)
AS
Update 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-parameters to have 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: using 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 security = true; database = pubs");
/ / Create a SQLCOMMAND object and indicate that its command type is a stored procedure
CMDPUBS = New Sqlcommand ();
CMDPUBS.CONNECTION = CNPUBS;
CMDPUBS.COMMANDTYPE = CommandType.StoredProcedure;
cmdpubs.commandtext = "Up_getpublisherinfo";
// Create a SqlDataAdapter object, set 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, 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 to the data set object
Dapubs.Fill (DSPUBS);
Return DSPUBS;
}
After completing the Publishers class, we add a DataGrid control to the main form and use it to display the data in 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 is as follows:
Public Form1 ()
{
//
// Windows Form Designer Support
//
InitializationComponent ();
//
// Todo: Add any constructor code to add any constructor code // pubs = new public publishers ();
DS = pubs.getpublisherinfo ();
DataGrid1.datasource = DS.TABLES [0];
}
Such a DataGrid control that initiates the main form is displayed, the corresponding data used in the PUBS database is displayed in the Pubs database, and the program run is shown below:
six. Create a database application with parameter stored procedures:
Above we created an application that does not have a parameter, 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 definition above, although the Direction property is not explicitly given, its default is INPUT, so our needs are met. 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;
The SourceColumn property is used to match the DataColumn object in a DataTable object, which implicitly imports 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 exissrs (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)
End
Return
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 the constructor of this class Publishers () function, add the following:
// Create another SQLCOMMAND object, the object references the stored procedure for updating the publisher name
cmdupdpubs = new sqlcommand ();
CMDUPDPUBS.CONNECTION = CNPUBS;
CMDUPDPUBS.COMMANDTYPE = CommandType.StoredProcedure;
cmdupdpubs.commandtext = "Up_updatepublishername";
/ / Add the necessary parameters for the SQLCommand object above
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 changes
Dapubs.Update (dschanges);
}
After the application's business logic is perfect, add a button named "Update Dativity" on the main form and add the button's event response function as follows:
Private void button1_click (object sender, system.eventargs e) {ney (ds.haschangepublishername (ds.getchangepublishername); ds.clear (); ds = pubs.getpublisherinfo ();}}
So far, the application's business logic class and the main form class have been updated, and the current application can update the relevant content in the database according to the user's switch.
Seven. to sum up:
This article describes the basic knowledge of the stored procedure and how to build a data-driven application in conjunction with the SqlDataAdapter object, DataSet object, and the like in the .NET database application. In this article, we use two types of stored procedures: a class as a simple storage process that does not have parameters, which utilizes relatively easy; another class is a stored procedure with parameters, which is still available when calling this class stored procedure. Use the SQLParameter object. At the same time, we are not difficult to find that the data update business logic package is a good design method during the stored procedure, which improves the application's manageability, scalability, and database security. Similar, inserting data, business logic of deleting data can also be packaged in a stored procedure and is used in applications in an application. Finally, I hope this article has many helpful help.
