Home>

There are some limitations to using the tableadapter wizard,Can only create stored procedures without joins,Then this article will explain for everyone,How to use stored procedures with join in tableadapters.

preface:

In a relational database,The data we process often spans several data tables.Example:When displaying product information, we are likely to want to list the corresponding category of each product and the name of the supplier.Admittedly, the products table contains the categoryid and supplierid values, but in fact the category and supplier names are defined in the categories and supplier tables, respectively. To get information from other related tables,We can use correlated subqueries or joins. A correlated subquerie is a nested selection that references the outer query column. For example, in Chapter 1, "Create a data access layerIn the main query of productstableadapter, we use 2 correlated subqueries to return the category and supplier names of each product. Join is a sql structure that combines the related rows of 2 different tables.Chapter 46Retrieve data using the sqldatasource control》 We use join to display the category information of each product.

avoid We avoid using join in tableadapters because insert, update, and delete statements automatically generated by the tableadapter wizard have their limitations.Specifically,If a tableadapter's main query contains any joins, the tableadapter cannot automatically create ad-hoc sql statements or stored procedures for its insertcommand, updatecommand, and deletecommand attributes.Before you start,Let's first briefly compare related subqueries and joins.

Compare related subqueries and joins

We know that the productstableadapter created in the northwind dataset in Chapter 1 uses correlated subqueries to return the category and supplier name corresponding to each product. The main query of the productstableadapter is as follows:

select productid, productname, supplierid, categoryid, quantityperunit, unitprice, unitsinstock, unitsonorder, reorderlevel, discontinued, (select categoryname from categories where categories.categoryid =
  products.categoryid) as categoryname, (select companyname from suppliers where suppliers.supplierid =
  products.supplierid) as suppliername
from products

We notice that these 2 related subqueries — "(select categoryname from categories where categories.categoryid=products.categoryid)" and "(select companyname from suppliers where suppliers.supplierid=products.supplierid)" are both select queries that return a Single value,And as an extra column of the outer select statement.

In addition, we can use join to return the supplier and category name of each product. The following query has the same effect as the above code.But use join:

select productid, productname, products.supplierid, products.categoryid, quantityperunit, unitprice, unitsinstock, unitsonorder, reorderlevel, discontinued, categories.categoryname, suppliers.companyname as suppliername
from products
 left join categories on
 categories.categoryid=products.categoryid
 left join suppliers on
 suppliers.supplierid=products.supplierid

Join combines records from one table with records from another table based on some criteria.For example, in the above code,"Left join categories on categories.categoryid=products.categoryid" instructs the SQL server to merge each product record with the category record,The criterion is that the categoryid value of the category record matches the categoryid value of the product record.In the result of the merge,We can process the corresponding category fields (such as categoryname) for each product.

Note:join is usually used to query data from related databases.If you are new to join syntax or review its usage,I recommend you read the article "sql join tutorial" () on the w3 schools forum;you can also read the "join fundamentals" and "subquery fundamentals" sections of sql books online.

When using typed datasets to build the data access layer,It's better to use correlated subqueries.Specifically,If the main query contains any joins, the tableadapter's setup wizard will not automatically generate the corresponding insert, update, and delete statements. Instead, use related subqueries.

verification To verify this,We create a temporary typed dataset in the ~/app_code/dal folder.Select ad-hoc sql statements in the tableadapter setup wizard, and type the following select query (see Figure 1):

select productid, productname, products.supplierid, products.categoryid, quantityperunit, unitprice, unitsinstock, unitsonorder, reorderlevel, discontinued, categories.categoryname, suppliers.companyname as suppliername
from products
 left join categories on
 categories.categoryid=products.categoryid
 left join suppliers on
 suppliers.supplierid=products.supplierid

Figure 1:Type a main query with a join

by default,tableadapter automatically creates insert, update, and delete statements based on the main query. If you click the "advanced" button,You will see that the feature is activated.Ignoring these settings,The tableadapter will not be able to create insert, update, and delete statements because the main query contains joins.

Figure 2:Type a main query with a join

Click Finish to complete the wizard.At this time, the dataset designer will only contain a table adapter, which contains a data table that lists the columns returned by the select query.Include categoryname and suppliername, as shown in Figure 3.

Figure 3:The datatable contains the returned columns

In addition, the insertcommand, updatecommand, and deletecommand attributes of tableadapter are empty.You can select tableadapter in the designer to see the properties window.You will see that the insertcommand, updatecommand, and deletecommand attributes are set to "(none)".

Figure 4:insertcommand, updatecommand, deletecommand attributes are "(none)"

verification In order to verify this disadvantage,We can manually write SQL statements and parameters for the insertcommand, updatecommand, and deletecommand properties through the properties window.At the beginning we can set the main query of the tableadapter to not include any joins. This will allow insert, update, and delete statements to be generated automatically. After completing the wizard setup,We can manually modify the tableadapter's selectcommand through the properties window to include the join syntax.

Although this method works fine,But it is fragile.Because we can reset the main query at any time through the wizard settings,Automatically regenerate insert, update, and delete statements. This means that the user customizations we just performed can easily be lost.

Fortunately, the vulnerability of insert, update, and delete statements automatically generated by tableadapter is only for ad-hoc sql statements. If your tableadapter uses stored procedures,You can customize the selectcommand, insertcommand, updatecommand, or deletecommand stored procedures.Don't worry about the stored procedure being modified when re-running the tableadapter setup wizard.

In the next few steps we will create a tableadapter. Initially we use a main query without a join.In order to automatically generate the corresponding insert, update, and delete stored procedures.Next, we will update the selectcommand to use join to return additional columns from related tables. Finally, we will create a corresponding business logic layer class and use the tableadapter on the asp.net page.

Step 1:Create a tableadapter using a simple main query

In this article, we will add a tableadapter and a strongly typed datatable to the employees table of the northwindwithsprocs dataset dataset. The employees table contains a reportsto column that specifies the employeeid value of the employee's manager. For example:the reportto value of employee anne dodsworth It is 5, which is the employeeid value of Steven Buchanan. Therefore, the manager of employee anne dodsworth is Steven Buchanan. In addition to returning the reportsto value of each employee, we also want to return the name of their manager.For this we can use join. But we know thatWhen the tableadapter was originally created, the wizard would not be able to automatically generate the corresponding insert, update, delete attributes. Therefore, we did not include any joins in its main query when the tableadapter was initially created. In step 2, we will Update the main query stored procedure,Get the manager's name by using join.

We open the northwindwithsprocs dataset in the ~/app_code/dal folder. Right-click in the designer,Select the "add" item and then "tableadapter". This will open the tableadapter setup wizard.As shown in Figure 5, let the wizard create a new stored procedure,Click next. For details, please refer to Chapter 65.Create new stored procedures in tableadapters》

Figure 5:Select the "create new stored procedures" item

The select statement of the main query of the tableadapter is as follows:

select employeeid, lastname, firstname, title, hiredate, reportsto, country
from employees

Because the query does not contain any joins, the tableadapter wizard will use the corresponding insert, update, delete statements to create the stored procedure.

Next the wizard asks us to name the stored procedure.Name it with employees_select, employees_insert, employees_update, and employees_delete, as shown in Figure 6.

Figure 6:Name the stored procedure of the tableadapter

The final wizard asked us to name the tableadapter method,We named them fill and getemployees. Also check the "create methods to send updates directly to the database (generatedbdirectmethods)" option.

Figure 7:Name the tableadapter methods fill and gettemployees

After setting up,Take a moment to check the stored procedures in the database,You can see 4 new stored procedures:employees_select, employees_insert, employees_update, employees_delete. Next, look at the employeesdatatable and employeestableadapter that we just created and created. The datatable contains each column returned by the main query.Select tableadapter and enter the properties window,You will see that the insertcommand, updatecommand, deletecommand properties call the corresponding stored procedure.

Figure 8:tableadapter contains insert, update, delete attributes

When insert, update, delete stored procedures are automatically generated,After properly setting the properties of insertcommand, updatecommand, deletecommand, we can customize the stored procedure of selectcommand.To return this information to the employee's manager.Specifically,We need to update the employees_select stored procedure,Use join to return the manager's firstname and lastname values. When done, we want to update the datatable to include these additional columns.We will implement it in steps 2 and 3.

Step 2:Customize the stored procedure with join

In Server Explorer,Expand the stored procedure folder of the northwind database,Open the stored procedure employees_select. If you don't find the stored procedure,Right-click the stored procedure folder and select "Refresh". Update the stored procedure,To make it use a left join to return the manager's first and last name:

select employees.employeeid, employees.lastname, employees.firstname, employees.title, employees.hiredate, employees.reportsto, employees.country, manager.firstname as managerfirstname, manager.lastname as managerlastname
from employees
 left join employees as manager on
 employees.reportsto=manager.employeeid

After updating the select statement,Select "save employees_select" in the "File" menu to save the changes. Of course, you can also click the save icon in the toolbar or press ctrl + s. After saving, right-click the stored procedure employees_select in the server explorer and select "Execute". This will execute the stored procedure and display the results in the output window,As shown in Figure 9.

Figure 9:The results of the stored procedure are displayed in the output window

Step 3:Update the columns of the datatable

At this point, the employees_select stored procedure returns the values ​​of managerfirstname and managerlastname. But these two columns are not included in the employeesdatatable. You can add them by the following methods:

Manually-right-click the datatable in the designer, select "column" in the "add" menu. Then name the column and set its properties

Automatic-The tableadapter setup wizard will update the datatable columns to map the fields returned by the selectcommand stored procedure. If you are using ad-hoc sql statements, the wizard will remove the insertcommand, updatecommand, and deletecommand attributes, because selectcommand now Contains a join. But if you use stored procedures,These command attributes will still exist.

We are in Chapter 35 "Implement master/slave report with repeater and datalist single pageAnd Chapter 52Upload files using fileupload》 In the case of manually adding columns,We will see more details of this process in future articles,But in this article,We add it automatically by using the tableadapter setup wizard.

Right-click on the employeeestable adapter and select "Configure". This will launch the tableadapter setup wizard,It lists stored procedures for select, insert, updat, delete.There are also the values ​​and parameters (if any) returned. As shown in Figure 10, we can see that the employees_select stored procedure now returns the managerfirstname and managerlastname columns.

Figure 10:Wizard showing updated columns for employees_select stored procedure

Click finish to complete the setting,Back in the dataset designer,The employeesdatatable now contains 2 newly added columns managerfirstname and managerlastname.

Figure 11:The employeesdatatable now contains 2 new columns

In order to verify whether the updated employees_select stored procedure works,As well as the insert, update, delete functions of the tableadapter, we are going to create a web page to allow users to view and delete employees. But before that,We first need to create a new class in the business logic layer to handle the employees in the northwindwithsprocs dataset. In step 4, we will create an employeesbllwithsprocs class class. In step 5, we will use it in an asp.net page The class.

Step 4:Update the business logic layer

create Create a class file named employeesbllwithsprocs.cs in the ~/app_code/bll folder.This class file is similar to the existing employeesbll class file.It ’s just that there are fewer methods,And use the northwindwithsprocs dataset (not the northwind dataset). Add the following code to the employeesbllwithsprocs class:

using system;
using system.data;
using system.configuration;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.web.ui.htmlcontrols;
using northwindwithsprocstableadapters;
[system.componentmodel.dataobject]
public class employeesbllwithsprocs
{
 private employeestableadapter _employeesadapter=null;
 protected employeestableadapter adapter
 {
 get
 {
  if (_employeesadapter == null)
  _employeesadapter=new employeestableadapter ();
  return _employeesadapter;
 }
 }
 [system.componentmodel.dataobjectmethodattribute
 (system.componentmodel.dataobjectmethodtype.select, true)]
 public northwindwithsprocs.employeesdatatable getemployees ()
 {
 return adapter.getemployees ();
 }
 [system.componentmodel.dataobjectmethodattribute
 (system.componentmodel.dataobjectmethodtype.delete, true)]
 public bool deleteemployee (int employeeid)
 {
 int rowsaffected=adapter.delete (employeeid);
 //return true if precisely one row was deleted, otherwise false
 return rowsaffected == 1;
 }
}

The adapter property of the employeesbllwithsprocs class returns an instance of the employeeableadapter of the northwindwithsprocs dataset,The gettemployees and deletemployee methods in the class will use this property.Among them, the gemtemployees method calls the gemtemploye method corresponding to the employeeestableadapter, which in turn calls the employee_select stored procedure and passes the result to an employeeatatable;the deletemployee method only calls the delete method of the employeeableadapter, and the delete method calls the employee_delete stored procedure.

Step 5:Process the data at the presentation layer

After adding the employeesbllwithsprocs class, we will process the employee data in an asp.net page. Open the joins.aspx page in the advanceddal folder and drag a gridview control from the toolbox to the page.Set its id value to employees. Next, from its smart tag, bind to a new objectdatasource control named employeesdatasource. Set the objectdatasource control to use the employeesbllwithsprocs class class, and select the gemtemployees and deletemployee methods in the select and delete tags, respectively. Click finish to complete the setup.

Figure 12:Setting up the objectdatasource to use employeesbllwithsprocs class

Figure 13:Set the objectdatasource to call the gettemployees and deleteemployee methods

Visual studio will add a boundfield for each column in the employeesdatatable. Remove all columns except title, lastname, firstname, managerfirstname, and managerlastname.And rename the headertext attributes of these columns to "last name", "first name", "manager" s first name "," manager "s last name".

In order to allow users to delete employees on the page, we need to do 2 things. First, enable the delete function of the gridview,Then set the oldvaluesparameterformatstring property of the objectdatasource control to the default value of {0}. After doing this,The declaration code for the gridview and objectdatasource controls should look similar to the following:

<asp:gridview runat="server" autogeneratecolumns="false"
 datakeynames="employeeid" datasourceid="employeesdatasource">
 <columns>
 <asp:commandfield showdeletebutton="true" />
 <asp:boundfield datafield="title"
  headertext="title"
  sortexpression="title" />
 <asp:boundfield datafield="lastname"
  headertext="last name"
  sortexpression="lastname" />
 <asp:boundfield datafield="firstname"
  headertext="first name"
  sortexpression="firstname" />
 <asp:boundfield datafield="managerfirstname"
  headertext="manager" s first name "
  sortexpression="managerfirstname" />
 <asp:boundfield datafield="managerlastname"
  headertext="manager" s last name "
  sortexpression="managerlastname" />
 </columns>
</asp:gridview>
<asp:objectdatasource runat="server"
 deletemethod="deleteemployee" oldvaluesparameterformatstring="{0}"
 selectmethod="getemployees" typename="employeesbllwithsprocs">
 <deleteparameters>
 <asp:parameter name="employeeid" type="int32" />
 </deleteparameters>
</asp:objectdatasource>

test Test the page in your browser,As shown in Figure 14, this page lists each employee and the name of their manager.

Figure 14:The employees_select stored procedure returns the name of the manager using join

Clicking the delete button will trigger the delete process, which will not end until the employees_delete stored procedure is executedHowever, the execution of the delete statement in the stored procedure failed.The reason is that there are foreign key constraints (as shown in Figure 15). Because each employee has one or more records in the orders table,It caused the delete operation to fail.

Figure 15:Delete operation violates foreign key constraints

If the delete operation executes successfully,i want:

Update foreign key constraints

For the employee (s) i want to delete, delete the corresponding record in the orders table

. Update the employees_delete stored procedure,Before deleting the employees records,First delete the corresponding record in the orders table.We are in Chapter 66Use existing stored procedures in tableadaptersThis issue was discussed in ``.

I leave this as an exercise to the reader

to sum up:

deal with When working with relational databases,We usually get data from multiple different but related tables.Correlated subqueries and joins provide two ways to access data from relational tables.Correlated subqueries were used in previous articles, because if you use join, tableadapter will not be able to automatically generate insert, update, delete statements, but we can add them manually.If you use ad-hoc sql statements, any user customizations may be overwritten by the changes made by the tableadapter setup wizard.

Fortunately,Tableadapters built using stored procedures are not as vulnerable as tableadapters built using ad-hoc sql statements.Therefore, when using a stored procedure to build a tableadapter, it is possible to use join in the main query.In this article, we examined how to create such a tableadapter. Initially we used a select query without a join in the main query of the tableadapter to automatically generate the corresponding insert, update, delete stored procedures. We then augmented the selectcommand stored procedure to use a join and re-run the tableadapter setup wizard to update the columns of employeesdatatable.

Re-run the tableadapter setup wizard will automatically update the columns of employeesdatatable to map the columns returned by the employees_select stored procedure.Of course, we can also manually add these columns to the datatable,This is what we will examine in the next chapter.

Happy programming!

About the Author

  • Previous Sample code of redis cache related operation files in CI framework
  • Next Sixty-ninth of Manipulating Data in ASPNET 20: Handling Computed Columns