Manipulating data 66 in asp.net 2.0: Using existing stored procedures in TableAdapters-self-study process

Introduction:

In the previous article we examined how to let the TableAdapters Wizard automatically create stored procedures. In this article, we'll look at how to get TableAdapter to use existing stored procedures. Because the Northwind database has few existing stored procedures, we also need to examine how to manually add new stored procedures to the database in the Visual Studio environment.

　　Note: In the 61st chapter, "Encapsulation of database modifications in transactions", we have added some methods to TableAdapter to support transactions (e.g. (BeginTransaction, committransaction, etc.). We can manage the entire transaction in a single stored procedure without modifying the data access Layer code. In this article, we will also examine the T-SQL commands commands that execute stored procedures in transactions.

First step: Add a Stored procedure to the Northwind database

We can easily get through visual Studio adds a stored procedure to the database. Let's add a new stored procedure to the Northwind database that returns a product with a specific CategoryID value in the Products table. In the Server Resource Management window, expand the Northwind database, as we saw in the previous article, stored procedure files The folder contains the existing stored procedures. To add a new stored procedure, you simply right-click the Stored Procedures folder and select the Add new stored procedure item.

Figure 1: Right-click Stored Procedures Folder Select "Add a New Stored Procedure"

As shown in Figure 1, after selecting the "Add a New Stored Procedure" entry, a script window opens in Visual Studio. Enter the following script:

CREATE PROCEDURE dbo. Products_selectbycategoryid
(
 @CategoryID int
)
as

SELECT ProductID, ProductName, SupplierID, CategoryID,
 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel
 , discontinued from
Products
WHERE CategoryID = @CategoryID

When the script is executed, a new stored procedure named Products_selectbycategoryid is added to the database that accepts an input parameter (@CategoryID, type int) and returns all products that match the CategoryID value .

Execute the CREATE PROCEDURE script, add a stored procedure to the database, click the Save button on the toolbar, or press Ctrl+s. After this, the stored Procedures folder is refreshed to display the most recently added stored procedure, so that a ilai part is created by the "CREATE PROCEDURE dbo." Products_selectproductbycategoryid "converted to" ALTER PROCEDURE dbo. Products_selectproductbycategoryid ". The CREATE procedure is used to add new stored procedures, and alter procedure is used to update existing stored procedures. Since the beginning of the script has been converted to alter PROCEDURE, we can complete the update of the stored procedure by altering the input parameters or the SQL statements and clicking the Save button. Figure 2 shows the Save Products_ Selectbycategoryid the picture after the stored procedure.

figure 2:products_selectbycategoryid Stored procedures have been added to the database

Step two: Set TableAdapter to use existing stored procedures

Now that the stored procedure Products_selectbycategoryid has been added to the database, we will set up the data access layer to use the stored procedure. Specifically, we will add the Getproducstbycategoryid (CategoryID) method to ProductsTableAdapter, which will invoke the stored procedure we just created Products_ Selectbycategoryid.

Open the Northwindwithsprocs DataSet, right-click on ProductsTableAdapter, and select Add query to enable TableAdapter query Configuration Wizard. We'll use the stored procedure we just created Products_selectbycategoryid, so select the "Use existing stored procedure" item, and then click Next.

Figure 3: Select the "Use existing stored procedure" Item

The next screen is a drop-down list box that lists all of the existing stored procedures for the database, and when a stored procedure is selected, its input parameters are listed on the left, and the return column (if any) is listed to the right. Select the Products_selectbycategoryid stored procedure in the Drop-down list, And then click Next.

Figure 4: Selecting the Products_selectbycategoryid stored procedure.

The next screen asks us what type of data the stored procedure returns, and the type returned by the TableAdapter method. For example, if we specify that the Tabular data (table column) is returned, the method returns a productsdatatable instance instance; If we specify that a stored procedure returns a single value (a alone), TableAdapter returns an object that is assigned by the first column of the first row returned by the stored procedure. Due to stored procedure Products_ Selectbycategoryid will return all products for a category, select the first item "Tabular Data" and click Next.

Figure 5: The specified stored procedure returns tabular Data

Then you need to specify the method pattern and the name of the method. Both the fill a DataTable and return a DataTable items are selected. Rename these 2 methods to Fillbycategoryid and Getproductsbycategoryid. Click Next, confirm the correct words, and then click Finish to complete the setup.

Figure 6: Naming the method Fillbycategoryid and Getproductsbycategoryid

　　Note: We just added the Fillbycategoryid and Getproductsbycategoryid methods to execute an int type input parameter, which is passed in by @categoryid. If you want to change the parameters of the products_selectbycategory stored procedure, you must also update the parameters of these TableAdapter methods. As discussed in the previous article, either manually add or remove parameters from the parameter set, or run the TableAdapter Wizard again.

Step three: Add a Getproductsbycategoryid (CategoryID) method to the BLL layer

After setting the Getproductsbycategoryid method for the DAL layer, the next step is to add a method at the business logic layer to call the method. Open the file for the Productsbllwithsprocs class, and add the following methods:

[System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
Public northwindwithsprocs.productsdatatable Getproductbycategoryid (int categoryid)
{
 return Adapter.getproductsbycategoryid (CategoryID);

}

The BLL layer method only returns productsdatatable through the ProductsTableAdapter Getproductsbycategoryid () method. Because the Dataobjectmethodattribute property is used, the method appears in the Drop-down list of the Select label when we use the ObjectDataSource Set Data Source Wizard.

Fourth Step: Display Products

To test the newly added Products_selectbycategoryid stored procedures, as well as the corresponding methods in the Dal and BLL layers, we will create a ASP.net page that contains a DropDownList control and a The GridView control. DropDownList control lists all the category in the database, and when a category is selected, we will display all the products belonging to that category in the GridView.

　　Note: We created the master/detail report with the DropDownList control in the previous article, please refer to Chapter 7th "Master/from report using DropDownList filter"

Open the Existingsprocs.aspx page in the Advanceddal folder, drag a DropDownList control from the toolbox to the page, and set its ID to Categories,autopostback property to True. Next, in its smart tag, bind it to a The ObjectDataSource control named Categoriesdatasource. Sets the control to invoke the GetCategories method of the Categoriesbll class class, while in update, INSERT, And the Delete label, select (None).

Figure 7: GetCategories method to invoke CATEGORIESBLL class classes

Figure 8: Select (None) in Update, INSERT, and delete tags

After completing the ObjectDataSource Wizard, we set the DropDownList control to display the CategoryName column, The value passed is the CategoryID column. At this point, the declaration code for the DropDownList control and ObjectDataSource control looks similar to the following:

<asp:dropdownlist id= "Categories" runat= "Server" autopostback= "True"
 datasourceid= "Categoriesdatasource" Datatextfield= "CategoryName"
 datavaluefield= "CategoryID" >
</asp:DropDownList>

<asp:o Bjectdatasource id= "Categoriesdatasource" runat= "Server"
 oldvaluesparameterformatstring= "original_{0}"
 Selectmethod= "GetCategories" typename= "CATEGORIESBLL" >
</asp:ObjectDataSource>

Next, place a GridView control under the DropDownList control, with its ID of productsbycategory, and binds it to a ObjectDataSource control named Productsbycategorydatasource, which calls the Productsbllwithsprocs The Getproductsbycategoryid (CategoryID) method for class classes. Because the GridView control is used only to display data, select "(None)" and click Next in Update, INSERT, and delete labels.

Figure 9: Setting the ObjectDataSource control Productsbllwithsprocs Class

Figure 10: Calling the Getproductsbycategoryid (CategoryID) method

Next to select the source of parameters, we in the parameter source Drop-down list select "Control", in ControlID drop-down list selected "Categories". Click Finish to complete the setup.

Figure 11: Setting the parameter CategoryID from the DropDownList control with ID categories

When you complete the ObjectDataSource Wizard, Visual Studio automatically adds BoundFields columns and a CheckBoxField column. You can customize the look of it.

Log in to the page in the browser, and when you log on to the beverages (beverage category), the product will be displayed. If we choose a different kind of product, the corresponding products will be displayed. The following figure:

Figure 12:produce All the products of the class are displayed.

Step Fifth: Use transactions to encapsulate stored procedure commands

In the 61st chapter, "Encapsulation of Database modification in business" we explored the technique of encapsulating Database modification commands with transactions, either successfully or unsuccessfully. Techniques for using transactions include:

. Using classes in the System.Transactions namespace
Call the Ado.net classes class at the data Access layer layer, such as SqlTransaction
Add t-sqltransaction commands transaction commands directly in the stored procedure

In Chapter 63 we use the Ado.net Classe class at the DAL layer, and in the remainder of this article we will use T-SQL command commands to manage a transaction in a stored procedure.

The 3 main SQL command commands used to manually start, commit, and rollback transactions are begin TRANSACTION, commit TRANSACTION, and rollback TRANSACTION. Similar to the use of the Ado.net method, the following pattern should be used when using transactions in a stored procedure:

1. Indicates that the transaction has been opened
2. Execute transaction-contained SQL statements
3. If any statement in step two fails, the transaction is rolled back
4. If all statement in step two are correct, commit the transaction

You can use T-SQL syntax to perform this pattern, as follows:

Begin TRY
 begin TRANSACTION-Start the TRANSACTION ...

 Perform the SQL statements that makeup the transaction ...

 --If we reach here, success!
 COMMIT TRANSACTION End
TRY
BEGIN CATCH--
 whoops, there is an error
 ROLLBACK TRANSACTION

 --Raise Error
 with the--details 
 of the exception DECLARE @ErrMsg nvarchar (4000),
  @ErrSeverity int

 SELECT @ ErrMsg = Error_message (),
  @ErrSeverity = error_severity ()
 
 RAISERROR (@ErrMsg, @ErrSeverity, 1)
end Catch

The code starts with a try ... Catch mode--sql Server2005 new structure. Like the try...catch pattern in C #, the SQL TRY ... The catch mode executes statement in the try area and immediately goes to the catch area if any one of the statement errors occurs.

If performed correctly, commit transaction commits the change and completes the transaction, and if an error occurs, the rollback transaction of the catch area returns the database to the state before the start. The stored procedure also throws a SqlException exception via the RAISERROR command command.
Note: The try of the code above ... The catch pattern is newly added in SQL Server 2005, and if you are using a slightly older version of Microsoft SQL Server, the above code will not execute successfully. However, you can refer to this article "Managing transactions in SQL Server Stored procedures" (http://www.4guysfromrolla.com/webtech/080305-1. shtml) to seek help.

Let's look at a real example. There is a foreign key constraint between the Categories table and the Products table, which means that the The CategoryID column in the Products table must be consistent with the CategoryID value in the Categories table. If a category has a corresponding product, and we attempt to delete the category, it will result in a violation of the foreign KEY constraint. Let's do a demo, log on to this page (~/binarydata/updatinganddeleting.aspx), which lists all the category in the system, and each row contains the Edit and delete buttons (Figure 13). If you try to delete a category that has a corresponding product, such as beverages--deletion fails because the foreign key constraint is violated (as shown in Figure 14).

Figure 13: Each category record contains the Edit and delete buttons

Figure 14: You cannot delete a category that has a corresponding product

We want to be able to delete any category, regardless of whether there is a corresponding product. When you delete category, we also want to delete the corresponding product (although we can simply set the CategoryID value of these products to null). We can create a stored procedure that accepts an input parameter @categoryid. When called, it explicitly deletes all the corresponding product, and then the category is deleted.

The first reaction is to create a stored procedure similar to the following:

CREATE PROCEDURE dbo. Categories_delete
(
 @CategoryID int
)

as--The associated products ...
Delete from a products
WHERE CategoryID = @CategoryID

--now deletes the category
delete from Categories
W Here CategoryID = @CategoryID

The above code clearly removes the relevant product and the category. Just not in a business. Suppose there are other foreign-key constraints based on the Categorie table CategoryID value, then the problem comes out: for the category, the associated product is deleted, and the category is still retained because of a foreign key constraint with other tables In the database.

If the stored procedure is in a transaction, the deletion of the Categories table will result in a rollback of the delete operation on the Products table. The following stored procedure script uses a transaction to ensure atomic operations on these 2 DELETE statement:

CREATE PROCEDURE dbo. Categories_delete
(
 @CategoryID int
)
as

begin TRY
 begin TRANSACTION-Start the TRANSACTION -

 -The associated products ...
 Delete from a products
 WHERE CategoryID = @CategoryID


 --now deletes the category
 delete from Categories
 WHERE CategoryID = @CategoryID

 --If we reach here, success!
 COMMIT TRANSACTION End
TRY
BEGIN CATCH-
 Whoops, there is an error
 ROLLBACK TRANSACTION

 - Raise an error
 with the--details 
 of the exception DECLARE @ErrMsg nvarchar (4000),
  @ErrSeverity int

 SELECT @ErrMsg = Error_message (),
  @ErrSeverity = error_severity ()
 
 RAISERROR (@ErrMsg, @ErrSeverity, 1)
End CATCH

Take the time to add a stored procedure named Categories_delete to the Northwind database, which you can refer to in the first step.

Sixth step: Update Categoriestableadapter

Once we have added the Categories_delete stored procedure, the DAL layer can use Ad-hoc SQL Statements to perform the delete operation. However, we need to update categoriestableadapter to use categories_delete stored procedures.

Note: In the previous chapters we were dealing with the Northwindwithsprocs dataset, which has only one entity--productsdatatable, but we will be confronted with categories. So, later in this article, when I refer to the Data Access layer (Layer), I mean the Northwind dataset, the one we created in the 1th chapter, "Creating a data access layer."

Open the Northwind DataSet, select Categoriestableadapter, and open its Properties window, which lists the InsertCommand, UpdateCommand, used by the TableAdapter, DeleteCommand, and SelectCommand, and name and database connection information. Expand the DeleteCommand property to see its details. As shown in Figure 15, the Comamndtype property of DeleteCommand is set to text with textual information as a Ad-hoc SQL query.

Figure 15: Viewing its property information in the Categoriestableadapter Properties window

Let's make some changes. Select the (DeleteCommand) text, and then select "(New)" In the Drop-down list, which clears the CommandText, Commandtype,parameters property settings. Then set the CommandType property to StoredProcedure, and then enter the name of the stored procedure in the CommandText property (that is, dbo. Categories_delete). Visual Studio automatically generates parameters collection (parameter sets) if you follow the order of setting the CommandType property before setting the CommandText property. If you don't come in that order, you can just click on an oval region in the parameters attribute to open the Parameters Collection Editor dialog box, Add parameters manually. either automatically or manually add parameters, we should open the Parameters Collection Editor dialog box to check for correct parameters (Figure 16). If you don't see any arguments in the dialog box, Then manually add the parameter @categoryid (you do not need to add a parameter @return_value).

Figure 16: Make sure the parameters are set correctly

When the DAL completes the update, deleting a category will automatically delete all its corresponding product. These operations are in a business. Let's do a validation and go back to that page, and when you click on a category Delete button, the category and all of its product are deleted.

　　Note: It is a good idea to make a backup of the database before testing the categories_delete stored procedure, because the stored procedure deletes the selected category and its corresponding product. If you are using the Northwnd.mdf database in the App_Data folder, you only need to close Visual Studio and copy the MDF and LDF files in the folder to another folder. After testing, close Visual Studio, Then overwrite the App_Data folder file with the backup MDF and LDF files.

Conclusion:

Although the TableAdapter wizard can automatically generate stored procedures, at some point we need to use existing stored procedures. In this article, we examined how to manually add stored procedures in a Visual Studio environment and guide TableAdapter methods to use these stored procedures. In addition, we examine the T-SQL commands and script scripting patterns used to open, commit, and rollback transactions in stored procedures.

I wish you a happy programming!

Introduction of the author

Scott Mitchell, author of this series of tutorials, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.