Shulou(Shulou.com)06/03 Report--

This article is about how ASP.NET 2.0 uses JOINs in TableAdapters. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Introduction:

In a relational database, the data we deal with usually spans several tables. Example: when displaying product information, we may want to list the corresponding category of each product and the name of the supplier. True, the Products table contains CategoryID and SupplierID values, but the actual category and supplier names are defined in the Categories table and the Suppliers table, respectively. To get information from other related tables, we can use correlated subqueries or JOINs. A correlated subquerie is a nested SELECT that references the columns of an external query (outer query). For example, in the first chapter, "creating a data access layer", we use 2 correlated subqueries in the main query of ProductsTableAdapter to return the category and supplier names of each product. JOIN is a SQL construct that merges the associated rows of two different tables. In Chapter 46, retrieving data using SqlDataSource controls, we use JOIN to display category information for each product.

We avoid using JOIN in TableAdapters because of the limitations of INSERT, UPDATE, and DELETE statements automatically generated by the TableAdapter wizard. Specifically, if TableAdapter's main query contains any JOIN, then TableAdapter cannot automatically create ad-hoc SQL statements or stored procedures for its InsertCommand, UpdateCommand, and DeleteCommand properties. Before we begin, let's briefly compare correlated subqueries and JOIN.

Compare Correlated Subqueries and JOINs

We know that the ProductsTableAdapter created in the Northwind DataSet dataset in Chapter 1 uses correlated subqueries to return the category and supplier name for each product. The main query for 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 SupplierNameFROM Products

We notice that the two correlated 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 serve as additional columns for the external SELECT statement.

In addition, we can use JOIN to return the supplier and category name of each product, and the following query has the same effect as the code above, but with JOIN:

SELECT ProductID, ProductName, Products.SupplierID, Products.CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, Categories.CategoryName, Suppliers.CompanyName as SupplierNameFROM Products LEFT JOIN Categories ON Categories.CategoryID = Products.CategoryID LEFT JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID

JOIN combines the records of one table with the records of another table based on some standard. For example, in the above code, "LEFT JOIN Categories ON Categories.CategoryID = Products.CategoryID" instructs SQL Server to merge each product record with the category record. The standard is that the CategoryID value of the category record matches the CategoryID value of the product record. In the result of the merger, we can process the corresponding category fields for each product (such as CategoryName).

Note: JOIN is usually used to query data from related databases. If you are new to JOIN grammar or are familiar with its usage review, I recommend that you read the article "SQL Join tutorial" (http://www.w3schools.com/sql/sql_join.asp) on the W3 Schools forum; you can also read the "JOIN Fundamentals" and "Subquery Fundamentals" sections of SQL Books Online.

It is better to use correlated subqueries when building a data access layer using typed datasets (Typed DataSets). Specifically, if the main query contains any JOIN, the TableAdapter setup wizard will not automatically generate the corresponding INSERT, UPDATE, and DELETE statements. On the contrary, using correlated subqueries is fine.

To verify this, we create a temporarily typed dataset in the ~ / App_Code/DAL folder. Select to use ad-hoc SQL statements in the TableAdapter setup wizard, and type the following SELECT query (figure 1):

SELECT ProductID, ProductName, Products.SupplierID, Products.CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, Categories.CategoryName, Suppliers.CompanyName as SupplierNameFROM Products LEFT JOIN Categories ON Categories.CategoryID = Products.CategoryID LEFT JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID

Figure 1: type a main query containing 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 active. Ignoring these settings, TableAdapter will not be able to create INSERT, UPDATE, and DELETE statements because the main query contains JOIN.

Figure 2: type a main query containing JOIN

Click Finish to complete the wizard. At this point, the DataSet designer will contain only one TableAdapter, which contains a DataTable that lists the columns returned by the SELECT query. Includes CategoryName and SupplierName, as shown in figure 3.

Figure 3:DataTable contains the returned columns

In addition, the InsertCommand, UpdateCommand, and DeleteCommand properties of TableAdapter are empty. You can select TableAdapter in the designer to view the properties window. You will see that the InsertCommand, UpdateCommand, and DeleteCommand properties are set to (None).

Figure 4: InsertCommand, the UpdateCommand,DeleteCommand attribute is "(None)"

To verify this shortcoming, we can manually write SQL statements and parameters through the property window for InsertCommand, UpdateCommand, and DeleteCommand properties. At first, we can set up the main query of TableAdapter without any JOIN, which will allow automatic generation of INSERT, UPDATE, and DELETE statements. After completing the wizard settings, we can manually modify TableAdapter's SelectCommand to include JOIN syntax through the properties window.

Although this method works well, it is fragile. Because we can reset the main query and automatically generate INSERT, UPDATE, and DELETE statements at any time through wizard settings. This means that the user customization we just made can be easily lost.

Fortunately, the vulnerabilities of INSERT, UPDATE, and DELETE statements automatically generated by TableAdapter are only for ad-hoc SQL statements. If your TableAdapter uses stored procedures, you can customize SelectCommand, InsertCommand, UpdateCommand, or DeleteCommand stored procedures. Do not worry that the stored procedure will be modified when rerunning the TableAdapter setup wizard.

In the next few steps we will create a TableAdapter, initially using a main query without JOIN 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 the related table. Finally, we will create a corresponding Business Logic Layer class class that uses the TableAdapter. Exe 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. Exe to the Employees table of the NorthwindWithSprocs DataSet dataset. The Employees table contains a ReportsTo column that specifies the employee ID value of the employee's manager. For example, the Reportto value of employee Anne Dodsworth is 5, which is the employee ID 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. To do this, we can use JOIN. However, we know that the wizard will not be able to automatically generate the corresponding insert, update,delete properties if you use JOIN when you initially create an insert. Therefore, when we initially created TableAdapter, we did not include any JOIN in its main query. In step 2, we will update the main query stored procedure to get the manager's name by using JOIN.

We open the NorthwindWithSprocs DataSet dataset in the ~ / App_Code/DAL folder. Right-click in the designer, select Add, and then select TableAdapter, which will open the TableAdapter setup wizard. As shown in figure 5, let the wizard create a new stored procedure, and then click Next. For more details, see Chapter 65, "creating new stored procedures in TableAdapters".

Figure 5: select the "Create new stored procedures" item

The SELECT statement of the main query for the TableAdapter is as follows:

SELECT EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, CountryFROM Employees

Since the query does not contain any JOIN, the TableAdapter wizard will use the corresponding INSERT, UPDATE, and 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: naming the stored procedure for TableAdapter

Finally, the wizard asked us to name the method of TableAdapter, which we named Fill and GetEmployees. Also select the "Create methods to send updates directly to the database (GenerateDBDirectMethods)" option.

Figure 7: naming TableAdapter's methods Fill and GetEmployees

After setting up, take a moment to check the stored procedures in the database, and you can see four new stored procedures: Employees_Select, Employees_Insert, Employees_Update,Employees_Delete. Next, take a look at the EmployeesDataTable and EmployeesTableAdapter that we just created. The DataTable contains each column returned by the main query. Select TableAdapter and enter the properties window, you will see the InsertCommand, UpdateCommand,DeleteCommand property calls the corresponding stored procedure.

Figure 8:TableAdapter contains Insert and Update,Delete attributes

When the insert, update,delete stored procedures are automatically generated, and the InsertCommand and UpdateCommand,DeleteCommand properties are properly set, we can customize the SelectCommand stored procedures to return the employee's manager information. Specifically, we need to update the Employees_Select stored procedure and use JOIN to return the manager's FirstName and LastName values. When we are done, we will update the DataTable to include these additional columns. We will achieve it in steps 2 and 3.

Step 2: customize stored procedures with JOIN

In Server Explorer, expand the stored procedures folder of the Northwind database and open the stored procedure Employees_Select. If you do not find the stored procedure, right-click the stored procedure folder and select Refresh. Update the stored procedure to return the manager's first and last name with a LEFT JOIN:

SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.Title, Employees.HireDate, Employees.ReportsTo, Employees.Country, Manager.FirstName as ManagerFirstName, Manager.LastName as ManagerLastNameFROM Employees LEFT JOIN Employees AS Manager ON Employees.ReportsTo = Manager.EmployeeID

When you have finished updating SELECT statement, select Save Employees_Select from the File menu to save your changes. Of course, you can also click the save icon on the toolbar or press the Ctrl+S key. After saving, right-click the stored procedure Employees_Select in Server Explorer and select execute. This executes the stored procedure and displays the results in the output window, as shown in figure 9.

Figure 9: the result of the stored procedure is displayed in the output window

Step 3: update the column of DataTable

At this point, the Employees_Select stored procedure returns the ManagerFirstName and ManagerLastName values. But these two columns are not included in EmployeesDataTable. You can add it in the following ways:

. Manual-right-click DataTable in the designer and choose Column from the Add menu. Then name the column and set its properties.

. Automatic-- the TableAdapter setup wizard updates the columns of DataTable to map the columns returned by the SelectCommand stored procedure (field). If you are using ad-hoc SQL statements, the wizard removes the InsertCommand, UpdateCommand, and DeleteCommand properties, because SelectCommand now contains a JOIN. However, if you use stored procedures, these command properties will still exist.

We examined the manual addition of columns in Chapter 35, "using Repeater and DataList single Page to implement Master / Slave reports" and Chapter 52, "uploading Files using FileUpload". We will see more details about this process in future articles, but in this article, we automatically add columns by using the TableAdapter setup Wizard.

Right-click EmployeesTableAdapter and select configure. This opens the TableAdapter setup wizard, which lists the stored procedures for select, insert, and updat,delet, along with the values and parameters (if any) they return. As shown in figure 10, we can see that the Employees_Select stored procedure now returns the ManagerFirstName and ManagerLastName columns

Figure 10: the wizard shows the columns after the Employees_Select stored procedure update

Click Finish to complete the setup, and back to the DataSet designer, the EmployeesDataTable now contains two new columns ManagerFirstName and ManagerLastName.

Figure 11: the EmployeesDataTable now contains 2 new columns

To verify that the updated Employees_Select stored procedure works, as well as the insert and update,delete features of the TableAdapter, we create a web page that allows the user to view and delete the employees. Before we do that, however, we need to create a new class in the business logic layer to handle the employees in the NorthwindWithSprocs DataSet dataset. In step 4, we will create an EmployeesBLLWithSprocs class class, and in step 5, we will use this class in an ASP.NET page.

Step 4: update Business Logic Layer

Create a class file called EmployeesBLLWithSprocs.cs in the ~ / App_Code/BLL folder. This type of file is similar to the existing EmployeesBLL class class file, except that it has fewer methods and uses the NorthwindWithSprocs DataSet dataset (rather than the Northwind DataSet 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 class returns an instance of the EmployeesTableAdapter of the NorthwindWithSprocs DataSet dataset, which will be used by the GetEmployees and DeleteEmployee methods in the class. Among them, the GetEmployees method calls the GetEmploye method corresponding to EmployeesTableAdapter, which then calls the Employees_Select stored procedure and passes the result to an EmployeeDataTable;, while the DeleteEmployee method only calls the Delete method of EmployeesTableAdapter, which calls the Employees_Delete stored procedure.

Step 5: process data at the presentation layer

After adding the EmployeesBLLWithSprocs class class, we will process the employee data in an ASP.NET page. Open the JOINs.aspx page in the AdvancedDAL folder, drag a GridView control from the toolbox to the page, and set its ID to Employees. Next, bind to a new ObjectDataSource control called EmployeesDataSource from its smart tag. Set the ObjectDataSource control to use the EmployeesBLLWithSprocs class class, select the GetEmployees and DeleteEmployee methods in the SELECT and DELETE tags, respectively. Click Finish to complete the setup.

Figure 12: setting up the ObjectDataSource to use the EmployeesBLLWithSprocs Class class

Figure 13: setting up the ObjectDataSource to call the GetEmployees and DeleteEmployee methods

Visual Studio adds a BoundField for each column in the EmployeesDataTable. Delete all columns except Title, LastName, FirstName, ManagerFirstName, and ManagerLastName. Rename the HeaderText properties of these columns to "Last Name", "First Name", "Manager's First Name" and "Manager's Last Name" respectively.

In order for users to delete employees from the page, we need to do two things. First enable the delete function of GridView, and then set the OldValuesParameterFormatString property of the ObjectDataSource control to the default value {0}. When this is done, the declaration code for the GridView and ObjectDataSource controls should look similar to the following:

Test the page in a browser, as shown in figure 14, which lists the names of each employee and their manager.

Figure 14:Employees_Select stored procedure uses JOIN to return the name of the manager

Clicking the Delete button triggers the deleting process until the Employees_Delete stored procedure is executed, but the DELETE statement execution in the stored procedure fails because of foreign key constraints (see figure 15). Because each employee has one or more records in the Orders table, the delete operation fails.

Figure 15: deletion violates foreign key constraints

If you want to delete the operation successfully, you need to:

. Update foreign key constraints

. Delete the corresponding record in the Orders table for the employee (s) you want to delete

. Update the Employees_Delete stored procedure to delete the corresponding record in the Orders table before deleting the Employees record

Thank you for reading! This is the end of this article on "how to use JOINs in TableAdapters". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.