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 the following advantages: ◆ Storage procedure allows standard component programming ◆ Stored procedures can achieve faster execution speed ◆ Storage procedures can reduce network traffic ◆ Stored procedures can be taken 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 in the sample program). 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 memory process, the code editing window in the IDE appears as follows: create procedure dbo.storedProcedure1 / * (@ parameter1 datatype = default value, @ parameter2 dattype outprut) * / as / * set nocount on * / RETURN The code template is compliant with the simplified syntax rules for creating a stored procedure, the complete syntax rules are as follows: Create Proc [Edu] Procedure_name [; Number] [{@Parameter Data_Type} [varying] [= default] [Output] , ... n] [with {recompile | Encryption | Recompile, Encryption}] [for replication] AS SQL_STATEMENT [... N] is limited to space, the meaning of each parameter is not introduced here, interested readers You can refer to the 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 create a simple stored procedure and use: create procedure dbo.up_getpublisherinfoas select pub_id, pub_name, city, state, countryfrom publishersreturn creates above stored procedures, save it. 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 operation method to create a parameter stored procedure: 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 = @PUB_NAME, City = @PUB_NAME, CITY = @PUB_NAME, CITY = @PUB_NAME, CITY = @PUB_NAME, CITY = @PUB_NAME, CITY = @PUB_COUNTRY (PUB_ID = @PUB_ID) Return's Create Store code, we pass Add a "@" flag to declare the local variable - parameters of the stored procedure, and also declare the type of each parameter, determine the direction value of each parameter, that is, the parameter is input or output type or Enter the 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, get the logical logic of the SqlDataAdapter object: public publishers () {Try {// Create a database connection object cnpubs = new sqlConnection ("Server = localhost; integrated security = true; dataBase = pubs "); // create a SqlCommand object, and indicates its command type is stored 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 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 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 is as follows: public form1 () {// // Windows Form Designer Support required / / Initializecomponent (); /// Todo: Add any constructor code after INITIALIZEComponent call // pubs = new public publishers (); ds = pubs.getpublisherinfo (); datagrid1.datasource = ds.tables [0];} This application shows the corresponding data that uses the above-mentioned non-parametric stored proximal storage procedure from the Pubs database, and 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 on the basis of the original application - Publishers class: 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 Buttons and add this button's event response function as follows: private void button1_click (Object sender, system.eventargs e) {if (ds.haschange code ()) {pubs.Updatepublishername (ds.getchanges ()); ds.clear ); DS = Pubs.getPublisherInfo ();}} To this, 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 change. Seven. Summary: This article describes the basic knowledge of the stored procedure and how to combine the SQLDataAdapter object, DataSet object, and the like build data-driven applications 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.
