在ASP.NET 2.0中操作数据之六十七：在TableAdapters中

TableAdapters中如何使用包含JOIN的存储过程

引言

在关系型数据库中，我们经常需要处理跨越多个数据表的数据。当展示产品信息时，我们可能希望列出每个产品的类别以及供应商名称。虽然Products表中包含CategoryID和SupplierID值，但实际的类别和供应商名称却分别存储在Categories表和Suppliers表中。为了从其他相关表中获取信息，我们可以使用关联子查询或JOINs。

TableAdapters是ASP.NET中的一种数据访问层组件，用于简化与数据库的交互。TableAdapter向导在创建存储过程时有一定的局限性，只能生成不包含JOIN的存储过程。这导致很多开发者在TableAdapters中避免使用JOIN。实际上，这并非必要，我们依然可以在TableAdapters中使用包含JOIN的存储过程。

一、关联子查询与JOINs的比较

在之前的数据集和TableAdapter创建过程中，我们可能使用了关联子查询来返回每个产品对应的类别和供应商名称。关联子查询是一种嵌套查询，它可以引用外部查询的列。虽然这种方法可以工作，但JOINs是SQL中更为强大和灵活的工具，可以将两个不同表的相关行进行合并。

二、如何在TableAdapters中使用包含JOIN的存储过程

尽管TableAdapter向导的局限性，我们仍然可以通过手动创建存储过程并在TableAdapters中使用它们来绕过这些限制。以下是如何在TableAdapters中使用包含JOIN的存储过程的步骤：

1. 创建包含JOIN的存储过程：在数据库中创建一个存储过程，该过程将使用JOIN来联接多个表并返回所需的数据。

2. 在TableAdapter中调用存储过程：在TableAdapter中，将查询类型更改为“存储过程”，并从下拉列表中选择您刚刚创建的存储过程。

3. 配置TableAdapter：配置TableAdapter以识别返回的数据集，并为其属性（如InsertCommand、UpdateCommand和DeleteCommand）提供适当的SQL语句或存储过程。尽管主查询可能包含JOINs，但这不会影响TableAdapter为这些属性创建存储过程的能力。

通过这种方式，我们可以在TableAdapters中使用包含JOIN的存储过程，充分利用数据库查询的能力，提高数据访问的性能和效率。尽管TableAdapter向导有其局限性，但通过手动配置和使用存储过程，我们可以克服这些限制并实现更高级的数据库操作。产品数据查询：ProductsTableAdapter的主查询

在数据库查询中，我们经常会遇到各种各样的查询需求，其中涉及到的ProductsTableAdapter的主查询便是一个典型的例子。它的查询语句设计精巧，旨在从产品中获取详尽的信息，同时关联了供应商与类别数据。

该主查询的SQL语句如下：

```sql

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

```

这个查询中，特别引人注意的是两个相关的子查询（correlated subqueries）。它们分别用于获取产品的类别名称和供应商名称，这两个子查询根据产品表中的CategoryID和SupplierID与对应的类别表和供应商表中的记录进行匹配，返回相应的类别名称和供应商名称。这种查询方式虽然有效，但在处理大量数据时可能效率较低。

相比之下，使用JOIN语句进行查询更为高效和简洁。例如：

```sql

SELECT

ProductID, ProductName, Products.SupplierID, Products.CategoryID,

QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,

ReorderLevel, Discontinued,

Categories.CategoryName,

SupplierspanyName as SupplierName

FROM Products

LEFT JOIN Categories ON Categories.CategoryID = Products.CategoryID

LEFT JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID

```

在这个使用JOIN的查询中，我们通过左连接（LEFT JOIN）将产品表与类别表和供应商表连接起来，基于共同的字段（如CategoryID和SupplierID）进行匹配。这种方式能够一次性地从多个表中获取所需的数据，避免了子查询可能带来的性能问题。JOIN语句也更容易理解和维护。

在实际应用中，当使用类型化的数据集（Typed DataSets）构建数据访问层时，使用相关的子查询在某些情况下可能更为合适。对于大型和复杂的数据集，使用JOIN语句通常更为高效和推荐。为了更好地理解和掌握SQL查询语言，建议阅读W3Schools论坛的《SQL Join tutorial》以及SQL Books Online的《JOIN Fundamentals》和《Subquery Fundamentals》等相关资源。当涉及到在查询中使用 JOIN 操作时，TableAdapter 的设置向导在默认情况下并不会自动生成相应的 INSERT、UPDATE 以及 DELETE 语句。这一现象特别值得我们注意。如果我们使用相关的子查询，那么情况会有所不同。

为了验证这一点，我们在 ~/App_Code/DAL 文件夹中创建了一个临时类型化的数据集。在 TableAdapter 设置向导中，我们选择使用 ad-hoc SQL 语句，并输入了包含 JOIN 操作的主查询语句（如图1所示）。

图1：输入包含 JOIN 的主查询

我们的查询语句如下：

```sql

SELECT

ProductID, ProductName, Products.SupplierID, Products.CategoryID,

QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,

ReorderLevel, Discontinued,

Categories.CategoryName,

SupplierspanyName as SupplierName

FROM Products

LEFT JOIN Categories ON Categories.CategoryID = Products.CategoryID

LEFT JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID

```

在数据管理的世界中，TableAdapter无疑是一个强大的工具。当你在设计器中选中它，查看属性窗口时，会注意到InsertCommand、UpdateCommand和DeleteCommand属性都设置为“(None)”。如图4所示，这些属性为我们提供了自定义SQL语句的机会。

为了验证这些属性的功能，我们可以通过属性窗口为InsertCommand、UpdateCommand和DeleteCommand手动写入SQL语句及参数。起初，我们可以设置TableAdapter的主查询不包含任何JOIN操作，这样向导可以自动生成INSERT、UPDATE和DELETE语句。当我们完成向导设置后，依然可以通过属性窗口手动修改TableAdapter的SelectCommand，使其包含JOIN语法以获取更多相关数据。

虽然这种方法能够正常工作，但它较为脆弱。因为向导设置可以随时重置主查询，导致我们刚刚进行的自定义修改可能会丢失。TableAdapter自动生成的INSERT、UPDATE和DELETE语句的脆弱性仅限于ad-hoc SQL语句。如果你的TableAdapter使用的是存储过程，那么你可以自定义SelectCommand、InsertCommand、UpdateCommand或DeleteCommand存储过程。重新运行TableAdapter设置向导时，存储过程不会受到任何影响。

接下来，我们逐步创建一个TableAdapter。使用一个不包含JOIN的主查询来自动生成相应的insert、update和delete存储过程。然后，我们将更新该SelectCommand，通过JOIN从相关表中返回更多列。在ASP.NET页面上，我们将使用这个TableAdapter创建一个对应的Business Logic Layer class类。

在第1步中，我们将为NorthwindWithSprocs数据集的Employees表添加一个TableAdapter和强类型的DataTable。这个Employees表有一个有趣的ReportsTo列，它表示每个雇员的经理的EmployeeID值。例如，Anne Dodsworth的ReportsTo值为5，这意味着她的经理是Steven Buchanan。除了获取每个雇员的ReportsTo值外，我们还希望能够返回他们经理的名字。为了实现这一点，我们可以使用JOIN操作。但是要注意，如果在最初创建TableAdapter时使用了JOIN，向导可能无法自动生成相应的insert、update和delete属性。在最初创建TableAdapter时，我们避免在主查询中使用JOIN。在第二步中，我们将更新主查询存储过程，通过JOIN操作获取经理的名字。

现在让我们进入实操阶段。在~/App_Code/DAL文件夹里的NorthwindWithSprocs数据集中，我们打开设计器并点击右键。选择“Add”项后，再选择“TableAdapter”以启动设置向导。如图5所示，我们选择“创建新的存储过程”并点击“Next”。向导将引导我们创建一个新的存储过程作为TableAdapter的主查询。这个查询的SELECT语句非常简单，只选择了Employees表中的一些列。由于这个查询没有包含任何JOIN操作，TableAdapter向导将自动生成相应的INSERT、UPDATE和DELETE存储过程。接下来向导会要求我们为存储过程命名并继续设置。具体的细节将在第65章中详细阐述。

图6中的TableAdapter存储过程命名

在设计向导的要求下，我们为TableAdapter的方法进行了命名，分别选择了“Fill”和“GetEmployees”。在选择配置选项时，我们选中了“Create methods to send updates directly to the database”的选项，也就是生成直接对数据库进行更新的方法（GenerateDBDirectMethods）。这就是图7中的操作。

完成这些设置后，我们深入数据库内部，仔细检查了存储过程。你会发现四个新的存储过程：Employees_Select、Employees_Insert、Employees_Update和Employees_Delete。紧接着，我们审视刚刚创建的EmployeesDataTable和EmployeesTableAdapter。这个DataTable包含了主查询返回的每一列数据。当你选择TableAdapter并进入其属性窗口时，你会看到InsertCommand、UpdateCommand和DeleteCommand属性，它们分别对应调用相应的存储过程。这就是图8所揭示的。

接下来，我们进入定制阶段。当自动生成insert、update、delete存储过程并正确设置InsertCommand、UpdateCommand和DeleteCommand属性后，我们可以专注于对SelectCommand的存储过程进行个性化定制。以返回雇员的经理信息为例，我们需要更新Employees_Select存储过程，使用JOIN操作来返回经理的FirstName和LastName值。完成这一步后，我们要更新DataTable，以使其包含这些新增的列。我们将在接下来的第2步和第3步中逐步实现这些操作。

第2步：定制存储过程使用JOIN操作

在服务器资源管理器中，我们展开Northwind数据库的存储过程文件夹，并打开存储过程Employees_Select。如果没有找到该存储过程，可以通过右击存储过程文件夹并选择“刷新”来更新视图。现在，我们需要更新这个存储过程，通过LEFT JOIN操作来返回经理的FirstName和LastName。更新的SELECT语句如下：

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

完成SELECT语句的更新后，我们通过“文件”菜单选择“Save Employees_Select”来保存所做的修改。你也可以点击工具栏的保存图标或按下Ctrl+S键进行保存。保存成功后，在服务器资源管理器中右击存储过程Employees_Select，并选择“执行”。这样，就会执行存储过程并在输出窗口中显示结果，如图9所示。

第3步：更新DataTable的列

在员工数据表（EmployeesDataTable）中，原本并没有包含两列特定的信息——ManagerFirstName和ManagerLastName。通过采用狼蚁网站的搜索引擎优化（SEO）策略，我们可以轻松地为这个数据表添加这两列。

手动操作方法是：在设计器中，右键单击数据表（DataTable），然后在“添加”菜单中选择“列”。接着，为新的列命名并设置其相关属性。这一步骤相对直观，使用者可以根据界面指引逐步完成。

自动操作则更为便捷：通过TableAdapter设置向导，DataTable的列会自动更新，以映射SelectCommand存储过程返回的列。如果你的SQL语句是即席的（ad-hoc），向导会调整InsertCommand、UpdateCommand和DeleteCommand属性。但现在，由于SelectCommand中包含了JOIN操作，这些命令属性可能会被移除。反之，如果你使用的是存储过程，这些命令属性仍然会保留。

在前面的章节中，我们已经详细过手动添加列的过程，并在后续章节中会更深入地该过程的细节。但在此文中，我们将通过TableAdapter设置向导实现自动添加。只需右键单击EmployeesTableAdapter并选择“配置”，TableAdapter设置向导就会启动。在这里，你可以看到用于select、insert、update和delete的存储过程列表，以及它们返回的值和参数（如果有的话）。如图10所示，Employees_Select存储过程已经更新了列信息，包含了ManagerFirstName和ManagerLastName两列。

完成设置后，返回DataSet设计器界面。这时你会发现，EmployeesDataTable已经包含了两个新添加的列——ManagerFirstName和ManagerLastName。如图11所示，数据表的新面貌一目了然。

在EmployeesBLLWithSprocs的世界里，数据流动如同精心编织的交响乐，每一个音符都代表着一种操作，每一个操作都代表着数据的诞生、流动和消逝。让我们深入到这个类的心脏地带，看看它是如何与数据共舞的。

这个EmployeesBLLWithSprocs类，就像一个数据交互的桥梁，连接着表现层与数据层。它的核心就是Adapter属性，这个属性提供了通往NorthwindWithSprocs数据集的大门。在这个数据集中，EmployeesTableAdapter就像是一个向导，引领我们进行数据的获取和删除。

当你想要获取所有的员工信息时，GetEmployees方法就会闪亮登场。它如同一个舞台上的指挥家，优雅地挥舞着指挥棒，指挥着EmployeesTableAdapter上演一场精彩的演出。实际上，它只是在调用EmployeesTableAdapter对应的GetEmployees方法，这个方法进一步调用存储过程Employees_Select，将员工信息像一束束璀璨的烟花一样喷薄而出，然后打包成一个EmployeeDataTable传递给表现层。

而在你需要删除某个员工信息时，DeleteEmployee方法就会挺身而出。它像一名武士，挥舞着手中的剑，精准地刺向目标。它仅仅需要调用EmployeesTableAdapter的Delete方法，这个方法背后则是Employees_Delete存储过程。当存储过程成功删除一行数据后，DeleteEmployee方法就会带着胜利的喜悦返回true，否则返回false。这就像是在告诉你：“我已经完成了你的请求，是否成功，请自行判断。”

现在，我们已经为EmployeesBLLWithSprocs类添加了两个重要的方法：GetEmployees和DeleteEmployee。这两个方法如同两只强有力的翅膀，让该类在数据的世界里自由翱翔。接下来，我们将在ASP.NET页面上安装这对翅膀，让它在用户与数据的交互中展现出无比的力量和魅力。在表现层中，我们将利用这两个方法处理员工数据，展示、更新、删除，让数据的流动如同优美的舞蹈，带给用户流畅的体验。走进Visual Studio的AdvancedDAL文件夹，我们打开JOINs.aspx页面，就像开启一个数据交互的大门。我们从工具箱中精心挑选一个GridView控件，将它安置在页面中，赋予其独特的标识ID——Employees。这个控件将成为展示和交互数据的重要窗口。

接下来，我们要为这个GridView控件绑定一个强大的数据源，那就是名为EmployeesDataSource的ObjectDataSource控件。这个控件不仅承载了数据的获取和删除功能，更使用了EmployeesBLLWithSprocs这一强大的类，以提供业务逻辑和存储过程支持。在SELECT和DELETE操作中，我们分别选择了GetEmployees和DeleteEmployee这两个强大的方法，它们分别用于获取员工数据和删除员工信息。设置完成后，一切井然有序，只需轻轻一点，“Finish”，设置就算大功告成。

（图12：一目了然地展示了如何设置ObjectDataSource使用EmployeesBLLWithSprocs Class类。图13：清晰地说明了如何为ObjectDataSource选择GetEmployees 和 DeleteEmployee方法。）

Visual Studio会自动为EmployeesDataTable里的每一列添加一个BoundField。此刻，我们需要精心挑选并保留Title、LastName、FirstName、ManagerFirstName和ManagerLastName这几列，而将其他列尽数删除。我们还要对这些保留的列进行美化处理，将它们的HeaderText属性分别重命名为“Last Name”、“First Name”、“Manager's First Name”和“Manager's Last Name”，以提供更加直观的用户体验。

GridView与ObjectDataSource控件：狼蚁网站SEO优化的灵感之源

对于关系型数据库中的数据展示和管理，GridView与ObjectDataSource控件堪称强大而灵活的武器。让我们深入了解这两个控件，看看它们如何在狼蚁网站的SEO优化过程中发挥作用。

我们有一个GridView控件，用于在Web服务器上展示数据。下面是一个名为“Employees”的GridView声明示例：

```asp

```

此GridView展示了员工及其经理的姓名信息，并提供了删除按钮功能。数据来源于ObjectDataSource控件，它作为数据源为GridView提供数据。下面是ObjectDataSource控件的声明示例：

```asp

```

这个ObjectDataSource控件与名为“EmployeesBLLWithSprocs”的业务逻辑层类交互，通过调用GetEmployees方法来获取员工数据，并提供了DeleteEmployee方法来处理删除操作。在浏览器中呈现时，页面会展示一个如图14所示的界面，列出了每个员工的详细信息以及他们的经理姓名。点击删除按钮会启动删除流程，但最终执行删除操作之前会调用Employees_Delete存储过程。如果因为存在外键约束而试图删除员工记录，操作可能会失败（如图15所示）。这是因为每个员工在订单表中都有与之关联的记录。为了成功执行删除操作，需要采取某些措施来解除这种约束。比如更新外键约束、删除与要删除的员工相关的订单记录或更新存储过程以在删除员工记录之前先删除相关的订单记录。这个问题我们在后续的第66章中将深入。这里作为一个练习留给读者自行解决。GridView和ObjectDataSource控件在处理关系型数据库数据时非常有用，它们能够帮助我们高效地从多个相关的表中获取数据并在Web应用程序中进行展示和管理。数据库查询的两种策略：Correlated Subqueries与JOIN操作

在数据世界，我们时常需要从关系表中提取信息，这时有两种主要策略可供我们采用：Correlated Subqueries和JOIN操作。在之前的文章中，我们主要聚焦于correlated subqueries的使用，因为在使用JOIN时，TableAdapter无法自动生成INSERT、UPDATE和DELETE语句。通过手工添加这些语句，我们仍然可以在ad-hoc SQL环境中灵活地使用JOIN操作。但请注意，如果采用这种方式，用户进行的任何自定义修改都有可能会被TableAdapter设置向导的改动所覆盖。不过对于那些希望使用存储过程来构建TableAdapters的人来说，这一难题似乎得以解决。因为存储过程不受此限制，我们可以在主查询中使用JOIN操作。我们将如何创建这种TableAdapter。

我们从简单的开始。在TableAdapter的主查询中使用不带JOIN的SELECT查询，这样我们可以轻松地自动生成相应的insert、update和delete存储过程。当我们熟悉这个过程后，我们可以进一步扩充我们的SelectCommand存储过程以使用JOIN操作。这时，我们需要重新运行TableAdapter设置向导来更新我们的数据表（如EmployeesDataTable）的列映射。重新运行向导将自动更新数据表的列以匹配由带有JOIN的存储过程返回的列。我们也可以手动向DataTable添加这些列，这将是我们在下一章节中深入的内容。

编程的世界充满了无尽的可能和挑战，让我们继续，享受编程的快乐！让我们一起从数据表中获取所需的信息，构建强大的应用程序，为用户带来更好的体验。无论我们选择使用correlated subqueries还是JOIN操作，关键在于理解这两种策略的优势和局限性，并根据具体情况做出最佳选择。在学习的过程中，不要害怕尝试新的方法和策略，因为只有这样，我们才能不断进步，不断提升自己的技能和能力。祝大家在编程的道路上越走越远！