Home>

Although the stored procedure can be automatically generated through the tableadapter wizard,But at some point we need to use existing stored procedures.This article will explain how to manually add stored procedures in the visual studio environment.And guide the tableadapter method to use these stored procedures.

preface:

In the previous article we looked at how to make the tableadapters wizard automatically create stored procedures.And in this article,We will examine how to make tableadapter use existing stored procedures.Because there are very few stored procedures in the northwind database,We also need to look at how to manually add new stored procedures to the database in the visual studio environment.

Note:In Chapter 61,Encapsulating database modifications in transactions》 In the tableadapter we have added some methods to support transactions (such as (begintransaction, committransaction, etc.). We can do this without modifying the data access layer code.Manage the entire transaction in one stored procedure.In this article, we will also examine the t-sql commands that execute stored procedures in transactions.

Step 1:Add stored procedures to the northwind database

We easily add stored procedures to the database through visual studio.Let's add a new stored procedure to the northwind database,It returns products 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,The stored procedure folder contains existing stored procedures.To add a new stored procedure,Just need to right click on the stored procedure folder,Select the "Add new stored procedure" item,

Figure 1:Right-click the stored procedures folder and select "add a new stored procedure"

As shown in Figure 1, after selecting the "add a new stored procedure" item, a script window will open 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 [email protected]

When the script is executed,A new stored procedure named products_selectbycategoryid will be added to the database,The stored procedure accepts an input parameter (@categoryid, type int) and returns all products that match the categoryid value.

The execution of the create procedure script will add stored procedures to the database,Click the Save button on the toolbar or press ctrl + s. After that,Refresh the stored procedure folder to show the recently added stored procedures,In this way, an ilai part is changed from "create procedure dbo.products_selectproductbycategoryid" to "alter procedure dbo.products_selectproductbycategoryid". Create procedure is used to add a new stored procedure,The alter procedure is used to update an existing stored procedure.Since the beginning of the script has been changed to alter procedure, we can change the input parameters or sql statements and click the save button.This completes the update of the stored procedure.Figure 2 shows the screen after saving the products_selectbycategoryid stored procedure.

Figure 2:The products_selectbycategoryid stored procedure has been added to the database

Step 2:Set the tableadapter to use an existing stored procedure

Now the stored procedure products_selectbycategoryid has been added to the database,We will set up the data access layer to use this stored procedure.Specifically, we will add the getproducstbycategoryid (categoryid) method to the productstableadapter, which will call the stored procedure products_selectbycategoryid we just created.

Open the northwindwithsprocs dataset, right-click on the productstableadapter,Select "Add query" to enable tableadapter query configuration wizard. We will use the stored procedure products_selectbycategoryid that we just created, so select the "use existing stored procedure" item and click next.

Figure 3:Select the "use existing stored procedure" item

The next screen is a drop-down list box,Lists all stored procedures existing in the database,When selecting a stored procedure,The input parameters are listed on the left,The right column will be listed (if any). Select the products_selectbycategoryid stored procedure in the drop-down listClick next.

Figure 4:Select products_selectbycategoryid stored procedure.

The next screen asks us what type of data the stored procedure returns,And the type returned by the tableadapter's method.For example, if we specify to return tabular data, the method will return a productsdatatable instance instance;if we specify that the stored procedure returns a single value, the tableadapter will return an object. The object is assigned by the first column of the first row returned by the stored procedure.Because the stored procedure products_selectbycategoryid will return all products of a category,Select the first item "tabular data" and click next.

Figure 5:Specifying stored procedure to return tabular data

Then you need to specify the method pattern used and the name of the method.Select both "fill a datatable" and "return a datatable". Rename these two methods to fillbycategoryid and getproductsbycategoryid. Click next, and if it is correct,Click finish to complete the setting.

Figure 6:Name the methods fillbycategoryid and getproductsbycategoryid

Note:We just added the fillbycategoryid and getproductsbycategoryid methods to execute an input parameter of type int,It was passed in by @categoryid.If i 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 delete parameters from the parameter set,Either run the tableadapter wizard again.

Step 3:Add a getproductsbycategoryid (categoryid) method in the bll layer

After setting the getproductsbycategoryid method of the dal layer, in the next step we will add a method in the business logic layer to call the method.Open the files of productsbllwithsprocs class,Add the following method:

[system.componentmodel.dataobjectmethodattribute
 (system.componentmodel.dataobjectmethodtype.select, false)]
public northwindwithsprocs.productsdatatable getproductbycategoryid (int categoryid)
{
 return adapter.getproductsbycategoryid (categoryid);
}

The bll layer method simply returns productsdatatable through the productstableadapter's getproductsbycategoryid () method. Because the dataobjectmethodattribute attribute is used, when we use the objectdatasource set data source wizard,The method will appear in the drop-down list of the select tag.

Step 4:Display the product

new The newly added products_selectbycategoryid stored procedure for testing,And the corresponding methods in the dal and bll layers, We will create an asp.net page, which contains a dropdownlist control and a gridview control. The dropdownlist control lists all categories in the database. When a category is selected, we will include all products belonging to the category in the gridview Show it.

Note:We used the dropdownlist control to create the master/slave report in the previous article. For more details, please refer to Chapter 7 "Master/slave report filtered using dropdownlist》

Open the existingsprocs.aspx page in the advanceddal folder, drag a dropdownlist control from the toolbox to the page,Set its id to categories and autopostback property to true. Next, bind it to an objectdatasource control named categoriesdatasource in its smart tag. Set the control to call the getcategories method of the categoriesbll class, and update, insert, and Select "(none)" in the delete tag.

Figure 7:Invoking the getcategories method of the categoriesbll class

Figure 8:Select "(none)" in the update, insert, and delete tags

After completing the objectdatasource wizard, we set the dropdownlist control to display the categoryname column and the value passed as the categoryid column. At this point, the declaration code for the dropdownlist control and the objectdatasource control looks similar to the following:

<asp:dropdownlist runat="server" autopostback="true"
 datasourceid="categoriesdatasource" datatextfield="categoryname"
 datavaluefield="categoryid">
</asp:dropdownlist>
<asp:objectdatasource runat="server"
 oldvaluesparameterformatstring="original_ {0}"
 selectmethod="getcategories" typename="categoriesbll">
</asp:objectdatasource>

Next, put a gridview control under the dropdownlist control, the id is productsbycategory, and bind it to an objectdatasource control named productsbycategorydatasource. This control calls the getproductsbycategoryid (categoryid) method of productsbllwithsprocs class. Since the gridview control is only used to display data,Therefore, select "(none)" in the update, insert, and delete tags and click next.

Figure 9:Setting the objectdatasource control productsbllwithsprocs class

Figure 10:Calling the getproductsbycategoryid (categoryid) method

Next, select the parameter source.We select "control" in the parameter source drop-down list and "categories" in the controlid drop-down list. Click finish to complete the setup.

Figure 11:Set the parameter categoryid from the dropdownlist control with the id category

After completing the objectdatasource wizard, visual studio will automatically add the boundfields column and a checkboxfield column. You can customize its appearance as much as you like.

Log in to this page in your browser,When you select the beverages when you log in, the products of this category will be displayed.If we choose another kind,All corresponding products will be displayed.As shown below:

Figure 12:All products of the produce category are displayed

Step 5:Encapsulate stored procedure commands with transactions

Chapter 61Encapsulating database modifications in transactionsIn this article, we explored the technology of encapsulating database modification commands with transactions.These modification operations either succeeded or failed.Techniques for using transactions include:

Use classes from the system.transactions namespace

Call ado.net classes in the data access layer, such as sqltransaction

Add t-sqltransaction commands directly to the stored procedure

In chapter 63 we used the ado.net classe class in the dal layer, and for the rest of this article,We will use t-sql command in a stored procedure to manage a transaction.

The three main SQL command commands for manually starting, committing, and rolling back transactions are begin transaction, commit transaction, and rollback transaction. Similar to using the ado.net method,When using transactions in a stored procedure,The following model should be used:

1. Point out that the transaction has started

2. Execute the SQL statements contained in the transaction

3. If any statement in the second step is wrong, roll back the transaction

4. If all the statements in the second step are executed correctly,Commit transaction

You can use t-sql syntax to execute this mode,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 was 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

The code starts as a try ... catch mode-a new structure of sql server2005.Just like the try ... catch mode in c#,The sql try ... catch mode executes a statement in the try area. If any statement fails, it immediately goes to the catch area.

If performed correctly,commit transaction will commit changes and complete the transaction;If execution fails,Then the rollback transaction in the catch area returns the database to the state before the start.The stored procedure also throws a sqlexception exception through the raiseerror command.

Note:The try ... catch mode of the above code is newly added in SQL Server 2005.If you are using a slightly older version of Microsoft SQL Server,The above code will not execute successfully.But you can refer to this article "managing transactions in sql server stored procedures" () for help.

Let's see a real example.There is a foreign key constraint between the categories table and the products table,this means,The categoryid column in the products table must match the categoryid value in the categories table. If a category has a corresponding product, and we try to delete the category, it will violate the foreign key constraint.Let ’s demonstrateLogin to this page (~/binarydata/updatinganddeleting.aspx), this page lists all categories in the system, and each line contains edit and delete buttons (see Figure 13), if you try to delete a category with a corresponding product , Such as beverages-delete failed,Because the foreign key constraint is violated (as shown in Figure 14).

Figure 13:Each category record contains edit and delete buttons

Figure 14:You cannot delete categories with corresponding products

hope We hope to delete any category, regardless of whether it has a corresponding product.When deleting categories, we also want to delete their corresponding products (although we can simply set the categoryid value of these products to null). To this end, we can create a stored procedure,It accepts an input parameter @categoryid. When calling it explicitly delete all corresponding products, and then delete this category.

People's first reaction was to create a stored procedure similar to the following:

create procedure dbo.categories_delete
(
 @categoryid int
)
as
-first, delete the associated products ...
delete from products
where [email protected]
-now delete the category
delete from categories
where [email protected]

The above code clearly deletes the related product and the category, but it is not in a transaction.Suppose there are other foreign key constraints based on the categoryid value of the category table.Then the problem arises in this case:for the category, its related products are deleted,This category remains in the database because it has foreign key constraints with other tables.

If the stored procedure is in a transaction,Failure to delete the categories table will cause the delete operation on the products table to be rolled back.The following stored procedure script uses a transaction to ensure atomic operations on these two delete statements:

create procedure dbo.categories_delete
(
 @categoryid int
)
as
begin try
 begin transaction-start the transaction
 -first, delete the associated products ...
 delete from products
 where [email protected]
 -now delete the category
 delete from categories
 where [email protected]
 -if we reach here, success!
 commit transaction
end try
begin catch
 -whoops, there was 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 a moment to add a stored procedure named categories_delete to the northwind database,The specific steps can refer to the first step.

Step 6:Update the categoriestableadapter

Once we have added the categories_delete stored procedure,The dal layer can then use ad-hoc sql statements to perform the delete operation.However, we need to update the categoriestableadapter to use the categories_delete stored procedure.

Note:In the previous chapters we dealt with the northwindwithsprocs dataset. This dataset has only one entity, productsdatatable, but we will encounter the case of categories. So, later in this article,When I refer to the data access layer, I am referring to the northwind dataset, which is what we described in Chapter 1Create a data access layerThe one created in ``.

Open the northwind dataset, select categoriestableadapter and open its properties window,This window lists the insertcommand, updatecommand, deletecommand, and selectcommand used by the tableadapter, as well as the name and database connection information.Expand the deletecommand property to see its details.As shown in Figure 15, deletecommand's comamndtype attribute is set to text, and its text information is used as an ad-hoc sql query.

Figure 15:View the properties information of the categoriestableadapter in the properties window

Let's make some changes.Select the "(deletecommand)" text and then select "(new)" in the drop-down list. This will clear the settings of the commandtext, commandtype, parameters properties.Then set the commandtype property to storedprocedure, and then enter the name of the stored procedure in the commandtext property (ie dbo.categories_delete). If you follow the order of setting the commandtype property and then the commandtext property,visual studio will automatically generate the parameters collection (parameter set). If you do not follow this order,You can only click on an oval area in the parameters property to open the parameters collection editor dialog box and add parameters manually.Whether you add parameters automatically or manually,We should all open the parameters collection editor dialog box to check whether the parameters are correct (see Figure 16). If you don't see any parameters in the dialog box,Then manually add the parameter @categoryid, (you don't need to add the parameter @return_value).

Figure 16:Make sure the parameters are set correctly

When dal finishes updating,Deleting a category will automatically delete all its corresponding products. These operations are placed in a transaction.Let ’s do a verification.Back to that page,When you click the delete button of a category, the category and all its products will be deleted.

Note:Before testing the categories_delete stored procedure,It is best to make a backup of the database,Because the stored procedure will delete the selected category and its corresponding product. If you use the northwnd.mdf database in the app_data folder,You just need to close visual studio and copy the mdf and ldf files in the folder to other folders.After testing,Close visual studio, and then overwrite the corresponding files in the app_data folder with the backed up mdf and ldf files.

Conclusion:

Although the tableadapter wizard can automatically generate stored procedures,But at some point we need to use existing stored procedures.In this article, we looked at how to manually add stored procedures in the visual studio environment.And guide the tableadapter method to use these stored procedures.In addition, we also examined the t-sql commands and script script modes used to start, commit, and roll back transactions in stored procedures.

Happy programming!

About the Author

  • Previous Sixty-one of Manipulating Data in ASPNET 20: Encapsulating Database Modifications in Transactions
  • Next Seventy-one of Manipulating Data in ASPNET 20: Protecting Connection Strings and Other Setting Information