Manipulating data in ASP.net 2.0 67: Using Joins_ self-study process in TableAdapters

Introduction:

In a relational database, the data we work with usually spans several data tables. For example: When presenting product information, we probably want to list the corresponding category of each product and the name of the supplier. Admittedly, the products contain CategoryID and SupplierID values, but in fact category and supplier Names are defined in the categories and suppliers tables respectively. To get information from other related lists, we can use correlated subqueries or joins. A correlated Subquerie is a set of SELECT, referencing 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 ProductsTableAdapter main query to return each product's category and supplier Names. The join is a SQL construct that merges the associated rows of 2 different tables. In the 46th chapter, "Using SqlDataSource control to retrieve data," We use join to display category information for each product.

We avoid using joins in TableAdapters because the TableAdapter Wizard automatically generates insert, UPDATE, and delete Statements has its limitations. Specifically, if TableAdapter's main query contains any joins, then TableAdapter cannot InsertCommand, UpdateCommand, and the DeleteCommand property to automatically create Ad-hoc SQL statements or stored procedures. Before we start, we'll briefly compare correlated subqueries with joins.

Compare correlated subqueries and joins

We know that the productstableadapter created in the first chapter of the Northwind DataSet DataSet uses correlated subqueries to return the corresponding 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 SupplierName from

We note that these 2 correlated subqueries--"(SELECT CategoryName from Categories WHERE Categories.CategoryID = Products.CategoryID) "and" (select CompanyName from suppliers WHERE Suppliers.supplierid = Products.supplierid) is a select query that returns a single value, And as an extra column for the external SELECT statement.

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

SELECT ProductID, ProductName, Products.supplierid, Products.CategoryID,
 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
 ReorderLevel, discontinued,
 Categories.categoryname,
 Suppliers.companyname as SupplierName from the 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 a standard. For example, in the above code, "left JOIN Categories on Categories.CategoryID = Products.CategoryID" indicates the SQL The server merges each product record with the category record, The standard is that the CategoryID value of the category record matches the value of the product record CategoryID. In the result of the merge, we can process the corresponding category fields for each product (such as CategoryName).

　　Note: Join is often used to query data from the relevant database. If you are unfamiliar with the Join grammar or have improved on its usage, I recommend that you read the article "SQL Join Tutorial" on the W3 Schools Forum (http://www.w3schools.com/sql/sql_ join.asp) and you can also read the section "JOIN Fundamentals" and "Subquery Fundamentals" in SQL Books Online.

When using a typed dataset (Typed datasets) to build a data access layer, it is better to use correlated subqueries. Specifically, if the main query contains any joins, the TableAdapter Setup Wizard does not automatically generate the corresponding insert, UPDATE, and DELETE statements. Instead, use the correlated If you subqueries, you can.

To verify this, we create a temporary typed dataset in the ~/app_code/dal folder. In the TableAdapter Setup Wizard, choose to use Ad-hoc SQL statements 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 SupplierName from the products left
 JOIN Categories on
 Categories.CategoryID = Products.CategoryID left
 JOIN suppliers on
 Suppliers.supplierid = Products.supplierid

Figure 1: Type a main query that contains joins

By default, TableAdapter automatically creates insert, UPDATE, and DELETE statements on the basis of 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 inserts, UPDATE, and DELETE statements because the main query contains joins.

Figure 2: Type a main query that contains joins

Click Finish to complete the wizard. The DataSet Designer will now contain only a TableAdapter containing 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, TableAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties are empty. You can select TableAdapter in the designer to view the Properties window. You will see the InsertCommand, UpdateCommand, and DeleteCommand properties set to (None).

Figure 4:insertcommand, Updatecommand,deletecommand property is "(None)"

To verify this shortcoming, we can manually write SQL through the Properties window for InsertCommand, UpdateCommand, and DeleteCommand properties Statements and parameters. At first we can set the main query of the TableAdapter does not contain any joins, which will allow automatic generation of INSERT, UPDATE, and delete Statements. After completing the wizard setup, we can manually modify the TableAdapter SelectCommand to include the join syntax through the Properties window.

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

Fortunately TableAdapter automatically generated insert, UPDATE, and DELETE statements vulnerabilities only for Ad-hoc SQL Statements. If your TableAdapter is using a stored procedure, you can customize SelectCommand, InsertCommand, UpdateCommand, or DeleteCommand stored procedures. When you rerun the TableAdapter Setup Wizard, you do not have to worry about the stored procedure being modified.

In the next few steps we'll create a TableAdapter that initially uses a main query without joins 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 the related table. Finally, we will create a corresponding business Logic Layer class class, Use the TableAdapter on the ASP.net page.

Step 1th: Create a TableAdapter using a simple main query

In this article, we will be for Northwindwithsprocs The Employees table of the dataset DataSet adds a TableAdapter and a strongly typed DataTable. The Employees table contains a ReportsTo column that specifies the EmployeeID value of the employee's manager. For example: Employee Anne The Reportto value of Dodsworth is 5, which is the EmployeeID value of Steven Buchanan. So the manager of the employee Anne Dodsworth is Steven. Buchanan. In addition to returning the ReportsTo value of each employee, we would like to return the name of their manager. To do so, we can use joins. But we know that the wizard will not be able to automatically generate the corresponding inserts when the join is originally created for the TableAdapter. Update,delete property. Therefore, we did not include any join in its main query when we initially created the TableAdapter. In step 2nd, we will update the main query stored procedure by using join to get the manager's name.

We open the Northwindwithsprocs DataSet dataset in the ~/app_code/dal folder. Right-click in the designer, select "Add", and choose "TableAdapter", This opens the TableAdapter Setup Wizard. As shown in Figure 5, let the wizard create a new stored procedure, and then click Next. For specific details, see Chapter 65th, "Creating a new stored procedure in TableAdapters"

Figure 5: Select the "Create New stored Procedures" Item

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

SELECT EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country from
Employees

Because the query does not contain any joins, the TableAdapter Wizard creates the stored procedure with the appropriate insert, UPDATE, and DELETE statements.

The wizard then wants us to name the stored procedure. Name them with Employees_select, Employees_insert, Employees_update, and Employees_delete, as shown in Figure 6.

Figure 6: Naming stored procedures for TableAdapter

The last wizard asked us to name the TableAdapter method, we named Fill and GetEmployees. Also select the Create methods to send updates directly to the database (Generate dbdirectmethods) "option.

Figure 7: The TableAdapter method is named fill and GetEmployees

After you finish setting up, take a moment to check the stored procedures in your database, and you can see 4 new stored procedures: Employees_select, Employees_insert, Employees_update,employees_ Delete. Next, examine the employeesdatatable and employeestableadapter we just created. The DataTable contains each column returned by the main query. Select TableAdapter and enter the Properties window, and you will see the InsertCommand, Updatecommand,deletecommand property to invoke the corresponding stored procedure.

Figure 8:tableadapter contains insert, Update,delete property

When automatically generate inserts, update,delete stored procedures, and properly set up InsertCommand, UpdateCommand, The DeleteCommand property allows us to customize the SelectCommand stored procedure to return the employee's manager. Specifically, we need to update the employees_select stored procedures, Use join to return the manager's FirstName and LastName values. When we're done, we want to update the DataTable to include these extra columns. We will be in the 2nd and 3 steps to achieve.

Step 2nd: Customize the stored procedure with the join

In Server Explorer, expand the stored Procedures folder for the Northwind database and open the stored procedure employees_select. If you do not find the stored procedure, right-click the Stored Procedures folder and select Refresh. Update the stored procedure so that it returns the manager's top 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 Managerlastname from

Employees left
 JOIN Employees as Manager on
 Employees.reportsto = Manager.employeeid

After you finish updating the Select statement, select Save Employees_select on the File menu to keep your changes. Of course, you can also click on the toolbar's save icon 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 results of the stored procedure are displayed in the Output window

Step 3rd: Update the columns of the DataTable

At this point, the Employees_select stored procedure returns managerfirstname and Managerlastname values. But the 2 columns are not included in the employeesdatatable. You can add this in the following ways:

. Manually--In the designer, right-click the datatable and select Column in the Add menu. Then name the column and set its properties.

. The Automatic--tableadapter Setup Wizard updates the columns of the DataTable to map the columns (field) returned by the SelectCommand stored procedure. 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.

In the previous 35th chapter, "Using the Repeater and DataList single page to implement master/from report" and the 52nd chapter "using FileUpload upload file" to investigate the situation of manually adding columns, we will also see the process in future articles more details, but in this article, We add them automatically 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 that they return, if any. As shown in Figure 10, we can see the Employees_ The Select stored procedure now returns the Managerfirstname and Managerlastname columns

Figure 10: The wizard shows the column after the update of the employees_select stored procedure

Finish setting, back to the DataSet Designer, the Employeesdatatable now contains 2 new columns Managerfirstname and Managerlastname.

Figure 11: The employeesdatatable now contains 2 new columns

To verify that the updated Employees_select stored procedure works, and that the TableAdapter insert, Update,delete function, We want to create a Web page that allows users to view and delete employees. But before we do, we need to create a new class in the business logic layer to handle the Northwindwithsprocs The employees in the dataset DataSet. In step 4th, we'll create a Employeesbllwithsprocs class, and in step 5th, we'll use that class in a ASP.net page.

4th Step: Update Business Logic Layer

Create a class file named EmployeesBLLWithSprocs.cs in the ~/app_code/bll folder. The class file is about the same as the existing EMPLOYEESBLL class file, but it's less of a method The Northwindwithsprocs DataSet DataSet (not the Northwind DataSet dataset) is used. 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 Employeestab

  Leadapter ();
 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 is deleted, otherwise false return rowsaffected = = 1;

 }
}

The Employeesbllwithsprocs Class adapter property returns an instance of the EmployeesTableAdapter of the Northwindwithsprocs dataset DataSet. The GetEmployees inside the class And the Deleteemployee method will use this property. Where the GetEmployees method invokes the EmployeesTableAdapter corresponding Getemploye method and then invokes the Employees_ Select the stored procedure and pass the result to a employeedatatable, while the Deleteemployee method invokes only the EmployeesTableAdapter Delete method, which calls the Employees_ The delete stored procedure.

Step 5th: Process data in the presentation layer

After adding the Employeesbllwithsprocs class classes, we will process the employee data in a 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 value to employees. Next, bind to a new Objectdataso named Employeesdatasource from its smart tag Urce control. Set the ObjectDataSource control to use the Employeesbllwithsprocs class class, select the GetEmployees and Deleteemployee methods separately in the select and delete labels. Click Finish to complete the setup.

Figure 12: Setting the ObjectDataSource using the Employeesbllwithsprocs class

Figure 13: Setting the ObjectDataSource call GetEmployees and Deleteemployee method

Visual Studio adds a BoundField for each column in the employeesdatatable. Delete all columns except title, LastName, FirstName, Managerfirstname, and Managerlastname. and rename the HeaderText property of these columns to "Last Name", "The Name of", "manager ' s name", "manager's" last name.

In order for users to be able to delete employees on the page, we have to do 2 things. First enable the removal of the GridView, and then set the ObjectDataSource control's OldValuesParameterFormatString property to the default value { 0}. Once this is done, the declaration code for the GridView and ObjectDataSource controls should look similar to the following:

<asp:gridview id= "Employees" 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" Headertex t= "Last Name" sortexpression= "LastName"/> <asp:boundfield datafield= "FirstName" headertext= "name" Sort expression= "FirstName"/> <asp:boundfield datafield= "managerfirstname" headertext= "Manager ' s Name" Sortex pression= "Managerfirstname"/> <asp:boundfield datafield= "managerlastname" headertext= "Manager ' s last Name" Sor texpression= "Managerlastname"/> </Columns> </asp:GridView> <asp:objectdatasource id= "
 Employeesdatasource "runat=" Server "deletemethod=" Deleteemployee "oldvaluesparameterformatstring=" {0} " Selectmethod= "GetEmployees" typename= "Employeesbllwithsprocs" >;D eleteparameters> <asp:parameter name= "EmployeeID" type= "Int32"/> </DeleteParameters> </asp:o

 Bjectdatasource>

Test the page in the browser, as shown in Figure 14, which lists each employee and their manager's name.

figure 14:employees_select Stored procedure use join to return manager's name

Clicking the Delete button triggers the deleting process until the employees_delete stored procedure is executed, but the DELETE statement execution fails in the stored procedure because of a foreign key constraint (as shown in Figure 15). Because each employee has one or more records in the Orders table, the delete operation fails.

Figure 15: Delete operation violates FOREIGN KEY constraint

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

. Updating FOREIGN KEY constraints
. For the employee (s) you want to delete, delete the corresponding record in the Orders table
Update the employees_delete stored procedure so that it deletes the records corresponding to the Orders table before deleting the employees record. We have discussed this issue in the 66th chapter, "Using existing stored procedures in TableAdapters".

I leave this as an exercise for the reader

Summarize:

When working with relational databases, we typically get data from several different but related tables. correlated subqueries and join provide two ways to access data from a relational table. In previous articles, the correlated subqueries was used, Because if you use join, TableAdapter will not automatically generate inserts, Update,delete statements, but we can add them by hand. If you use Ad-hoc SQL statements, Any user customizations may be overwritten by changes made by the TableAdapter Setup Wizard.

Luckily, the TableAdapters built with stored procedures is not like using Ad-hoc SQL Statements built TableAdapters is as susceptible as it can be. Therefore, when you build TableAdapter with stored procedures, it is possible to use joins in the main query. In this article, we look at how to create this TableAdapter. We started in TableAdapter. A select query with no join is used in the main query to automatically generate the corresponding insert, update,delete stored procedure. We then extended the SelectCommand stored procedure to use a join and rerun the TableAdapter Setup Wizard to update the employeesdatatable columns.

Rerun the TableAdapter Setup Wizard to automatically update employeesdatatable columns to map Employees_ The column returned by the select stored procedure. Of course, we can also add these columns manually to the DataTable, which is what we'll look at in the next chapter.

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.