one. Foreword:
Stored Procedure is a set of SQL statements designed to complete specific functions, which are compiled and stored in the database. The user executes a stored procedure by specifying its name and giving parameters (if the stored procedure has parameters). Stored procedures are an important object in the 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 utilized as a security mechanism
The author of this article will introduce to you the application of stored procedures in .NET database applications, and how to use it in conjunction with the SqlDataAdapter object, DataSet object, etc. 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 (including the Pubs database used in the sample program)
3. Create a simple stored procedure:
Here I will introduce to you how to use Visual Studio.NET IDE to create a stored procedure. It is very easy and intuitive to create stored procedures using Visual Studio.NET IDE. As long as you navigate to the Pubs database in the server explorer and expand the node, you will find various database objects including stored procedures, as shown in Figure 1 Show.
Right-click on the stored procedure node to pop up a menu that contains the "New Stored Procedure" command. After creating a new stored procedure, the code template shown below will appear in the code editing window in the IDE:
CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT )
*/
AS
/* SET NOCOUNT ON */
RETURN
The above code template conforms to the simplified syntax rules for creating 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 ]
Due to space limitations, the meaning of each parameter will not be introduced here. Interested readers can refer to the information about the SQL Server 2000 database management system.
Below I will briefly introduce each grammatical component in this code template. The CREATE PROCEDURE statement creates a stored procedure, followed by the name of the stored procedure. The components in "/*...*/" are the parameters of the stored procedure, which can include input parameters and output parameters. The content following the AS keyword is the main body of the stored procedure, which is any number and type of SQL statements contained in the stored procedure. The RETURN keyword indicates the end of the stored procedure and can return an integer status value to the caller. Let's create a simple stored procedure without parameters and use it:
CREATE PROCEDURE dbo.up_GetPublisherInfo
AS
SELECT pub_id, pub_name, city, state, country
FROM publishers
RETURN
After creating the above stored procedure, save it. Once saved, the node corresponding to the stored procedure will appear in Server Explorer. Please also note that the CREATE keyword in the code editing window has changed to the ALTER keyword, which is used to change any existing stored procedures. To run the above stored procedure, just click on its node and select "Run Stored Procedure" in the right-click pop-up menu. The result of the operation is as follows:
Four. Create a stored procedure with parameters:
Above we created a simple stored procedure without parameters, but in actual applications many stored procedures with parameters are often used. Stored procedures with parameters are generally used to update data or insert data. Below we can use the same operation method to create a stored procedure with parameters:
CREATE PROCEDURE dbo.up_UpdatePublisherInfo
(
@pub_id char (4),
@pub_name varchar (40),
@cityvarchar(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 above code to create a stored procedure, we declare the local variables-parameters of the stored procedure by adding an "@" sign before the name. We also declare the type of each parameter and determine the direction value of each parameter, that is, Indicates whether the parameter is input type or output type or input-output type or return value type. Users can call the stored procedure through the corresponding stored procedure name and correct and valid parameters. Also, you can add output parameters to the parameters by using the OUTPUT keyword. Please refer to the syntax rules above for specific methods. Output parameters can return relevant information to the caller.
The above stored procedure can update the corresponding publisher information in the publishers table. You can execute it by clicking on the node of the stored procedure and selecting "Run Stored Procedure" in the right-click pop-up menu. Once executed, a dialog box for entering publisher information will pop up in the IDE (as shown in Figure 3). Fill in the correct and valid update information in this dialog box. Note that the value of pub_id must exist in the original table, and then click the "OK" button to update the data.
five. Create a database application with a simple stored procedure:
Next, we will use the above stored procedure without parameters to create a database application, which also uses the SqlDataAdapter object and DataSet object in ADO.NET. The SqlDataAdapter object serves as a bridge between the SQL Server database and the DataSet object to connect the two. The SqlDataAdapter object contains two commonly used methods: Fill() method and Update() method. The Fill() method can obtain the corresponding data from the database and fill it into the DataSet object, and the Update() method, as the name suggests, updates the data set. 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 valid SQL statements, and can obtain corresponding data from the database and fill it into the DataSet object.
First, we create a Windows Forms application, the programming language is C#. After creating a new project in Visual Studio.NET, add a new class to the project - the Publishers class, which encapsulates the business logic of connecting to the backend database and obtaining the data set object. The steps are as follows:
1. Add necessary namespace references: using System.Data.SqlClient;
2. Add the following necessary variables to this class:
private SqlConnection cnPubs;
private SqlCommand cmdPubs;
private SqlDataAdapter daPubs;
private DataSet dsPubs;
3． In the constructor of this class, complete the connection to the backend database and obtain the SqlDataAdapter object and other business logic:
public Publishers()
{
try
{
//Create a database connection object
cnPubs = new SqlConnection( "server=localhost;integrated security=true;database=pubs" );
//Create a SqlCommand object and specify its command type as a stored procedure
cmdPubs = new SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.CommandType = CommandType.StoredProcedure;
cmdPubs.CommandText = "up_GetPublisherInfo";
//Create a SqlDataAdapter object and set its SelectCommand property to the above SqlCommand object
daPubs = new SqlDataAdapter();
daPubs.SelectCommand = cmdPubs;
//Create a DataSet object
dsPubs = new DataSet();
}
catch(Exception) {}
}
4. Finally, a GetPublisherInfo() method is provided for this class, which fills the DataSet object with the SqlDataAdapter object and returns the filled DataSet object. The method is as follows (it is worth noting that the SqlDataAdapter object will implicitly open the database connection and implicitly open the database connection after obtaining the data. Close the connection formally, which means that the DataSet object works in non-connection mode and when you explicitly open the database connection and obtain the data, the SqlDataAdapter object will not close the connection):
public DataSet GetPublisherInfo().
{
// Call the Fill() method of the SqlDataAdapter object and return the 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 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 as follows:
public Form1()
{
//
// Required for Windows Forms Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after the InitializeComponent call
// pubs = new Publishers();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
In this way, as soon as the application is started, the corresponding data obtained from the Pubs database using the above stored procedure without parameters will be displayed in the DataGrid control of the main form. The program running diagram is as follows:
6. Create a database application with a stored procedure with parameters:
Above we created an application with a stored procedure without parameters, and now we will create a more complex database application. In actual database applications, we often need to obtain data and update, insert or delete data. At this time, we need to use stored procedures with parameters. At the same time, when using the SqlDataAdapter object, we will call its Update() method. The Update() method will automatically complete the corresponding operation based on the changes in each record in the DataTable object in the DataSet object. The SqlDataAdapter object also contains properties such as UpdateCommand, InsertCommand, DeleteCommand, etc. These properties are actually SqlCommand objects. The Update() method selects the appropriate attributes based on the type of operation.
When using stored procedures with parameters to build database applications, we generally use the SqlParameter class, which encapsulates various properties and methods related to Sql parameters. The properties include ParameterName, SqlDBType, Direction, Size, Value, SourceColumn and SourceVersion, etc. Among them, ParameterName, SqlDBType, Direction, Size and other attributes 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 meets our needs. And if the Direction property of a SqlParameter object is InputOutput or Output or ReturnValue, then its Direction property must be clearly stated. For example, the following code clearly declares that the 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. This matching can implicitly import the required SqlParameter object when the Update() method is called to update the DataTable object. If this property is not declared when defining, then you must explicitly state the SourceColumn property of the SqlParameter object in your code.
The default value of the SourceVersion property is the current value in the corresponding field of the DataRow object, which is the value to be updated to the database. Of course, the SourceVersion property can also point to the original value in the corresponding field of the DataRow object, that is, the initial value obtained from the database. In a database transaction processing system, the issue of data synchronization is very important. Let's build 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)
End
RETURN
Next, we call the stored procedure in the above application to update the publisher's name. First, improve its business logic class-Publishers class 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 Publishers() function of this class to add the following:
// Create another SqlCommand object that references the stored procedure that updates the publisher name
cmdUpdPubs = new SqlCommand();
cmdUpdPubs.Connection = cnPubs;
cmdUpdPubs.CommandType = CommandType.StoredProcedure;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
//Add necessary parameters to the above 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． Specify the UpdateCommand property of the SqlDataAdapter object as 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 completed, add a button named "Update Data Set" on the main form, and add the event response function of 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 class and main form class have been updated. Now the application can update the relevant content in the database according to the user's changes.
seven. Summary:
This article introduces you to the basic knowledge of stored procedures and how to combine SqlDataAdapter objects, DataSet objects, etc. to build data-driven applications in .NET database applications. In this article, we have used two types of stored procedures: one is a simple stored procedure without parameters, which is relatively easy to use; the other is a stored procedure with parameters, and you have to call this type of stored procedure. Apply to SqlParameter object. At the same time, it is not difficult to find that encapsulating data update business logic in a stored procedure is a good design method, which can improve the manageability, scalability and database security of the application. Similarly, the business logic for inserting and deleting data can be encapsulated in stored procedures and used in applications in a similar way. Finally, I hope this article is of great help to everyone.