使用计算列70

简介

Microsoft SQL Server 允许使用 计算列 ，计算列的值是由引用同一个表的其他列的值的表达式计算得到的。做为一个例子，时间跟踪数据模型也许包含一个名称为ServiceLog 的表，其中包含 ServicePerformed 、EmployeeID 、Rate 和 Duration 列。虽然每个服务项目对应的数量（为持续时间乘以比率）可以通过网页或其他编程界面计算出来，但是也可以通过在ServiceLog 表中包含名称为 AmountDue 的一个列而方便地获得，该列报告该信息。可以将该列创建为常规列，但是一旦Rate 或 Duration 列的值发生变化，就需要随时更新该列。最好的方法是将AmountDue 定义为使用表达式 Rate * Duration 的计算列。这样做，一旦在查询中引用AmountDue 的值时，SQL Server 就可以自动计算AmountDue 列。

因为计算列的值是由表达式决定的，因此这些列是只读的，不能用INSERT 或 UPDATE 语句为这些列赋值。但是，当计算列为使用ad-hoc SQL 语句的 TableAdapter 主查询的一部分时，计算列会自动包含在自动生成的INSERT 和 UPDATE 语句中。因此，必须对 TableAdapter 的 INSERT 和 UPDATE 查询以及 InsertCommand 和 UpdateCommand 属性进行更新以删除对任何计算列的引用。

与使用ad-hoc SQL 语句的TableAdapter 一起应用计算列的一个挑战是：只要完成了TableAdapter 配置向导，就自动生成了TableAdapter 的INSERT 和 UPDATE 查询。因此，一旦向导重新运行，人工从INSERT 和 UPDATE 查询中删除的计算列又会重新出现。尽管使用存储过程的TableAdapter 不会存在这一脆弱性，但它们自身也存在我们在步骤3 要处理的特殊问题。

本教程中，我们将向 Northwind 的Suppliers 表添加一个计算列，然后创建相应的TableAdapter 来使用该表和它的计算列。我们将创建使用存储过程而非ad-hoc SQL 语句的TableAdapter ，这样使用 TableAdapter 配置向导时自定义的内容不会丢失。

让我们开始吧！

步骤 1：向 Suppliers 表添加一个计算列

Northwind 数据库目前还没有任何计算列，我们需要自己添加一个。本教程中，我们为Suppliers 表添加一个名为 FullContactName 的计算列，这一列的返回值为联系人的姓名、职位、供职的公司，其格式如下：“ContactName (ContactTitle, CompanyName)” 。在显示供应商信息的报表中可能会用到这一计算列。

首先，在 Server Explorer 中右键单击 Suppliers 表，然后从上下文菜单选择 Open Table Definition 来打开 Suppliers 表定义。这将显示表的各列及其属性，如数据类型、是否允许为空，等等。要添加一个计算列，首先将列名称键入表定义中。接着，将列的表达式输入Column Properties 窗口的 Computed Column Specification 部分下面的文本框（公式）中（见图 1 ）。将计算列命名为 FullContactName ， 并使用下列表达式：

ContactName + ' (' + CASE WHEN ContactTitle IS NOT NULL THEN  
    ContactTitle + ', ' ELSE '' END + CompanyName + ')'

注意：可以在SQL 中使用 + 运算符来合并这些字符串。可以像传统编程语言中一样有条件的使用CASE 语句。在上面的表达式中，CASE 语句可读作：如果 ContactTitle 不为 NULL ，则与豆号合并输出 ContactTitle 值，否则不输出任何内容。有关 CASE 语句使用的详细信息，参见 SQL CASE 语句的功能 。

注意 ： 如果不使用CASE 语句，也可以选择使用ISNULL(ContactTitle, '') 。 如果ISNULL(checkExpression, replacementValue)为非空，则 ISNULL(checkExpression, replacementValue) 返回 checkExpression ，否则返回 replacementValue 。在这个实例中，无论使用 ISNULL 或CASE ，都会存在 CASE 语句的灵活性与ISNULL 不匹配的复杂情况。

添加该计算列后，屏幕显示如图 1 的屏幕截图。

图1：向 Suppliers 表添加名为 FullContactName 的计算列

为计算列命名并输入其表达式以后，单击工具栏的Save 图标、按 Ctrl+S 键、或转到File 菜单选择 SaveSuppliers 来保存这一修改。

保存表的操作将刷新 Server Explorer ，可以看到在 Suppliers 表的字段列表中包含了刚添加的计算列。此外，输入到文本框（公式）中的表达式将自动调整为一个等效表达式，其中删除了不必要的空白，用括号([]) 将列名括了起来，还添加了能明显表示运算顺序的圆括弧：

(((([ContactName]+' (')+case when [ContactTitle] IS NOT NULL  
    then [ContactTitle]+', ' else '' end)+[CompanyName])+')')

有关Microsoft SQL Server 中计算列的详细信息 ，请参考 技术文档。也可以参考 如何：指定计算列来一步步练习创建计算列。

注意 ：默认情况下 ，计算列物理上并不存储在表中 ，而是在查询中引用它们时每次重新计算。然而，通过选中“Is Persisted” 复选框，可以指示SQL Server 将计算列物理存储在表中。这样做，将在计算列上创建一个索引，这可以改善 WHERE 子语句中使用计算列的查询性能。详细信息，参见 在计算列上创建索引。

步骤2：查看计算列的值

在开始使用数据访问层之前，我们花些时间查看一下FullContactName 的值。从Server Explorer ，右键单击Suppliers 表名，然后从上下文菜单中选择 New Query 。这将打开Query 窗口，其中提示我们选择要包含在查询中的那个表。添加Suppliers 表并单击Close 。接着，从 Suppliers 表选中 CompanyName 、ContactName 、ContactTitle 和FullContactName 列。最后，单击 Toolbar 中的红色标记图标来执行查询并查看查询结果。

如图 2 所示，查询结果包含了 FullContactName ，其中使用格式 "ContactName (ContactTitle,CompanyName)” 列出了 CompanyName 、ContactName 和ContactTitle 列。

图2 ：使用格式 “ContactName (ContactTitle,CompanyName)” 的FullContactName

步骤3：将 SuppliersTableAdapter 添加到数据访问层

为了使用我们应用程序中的供应商信息，需要首先在我们的DAL 中创建 TableAdapter 和DataTable 。理想情况下，使用前边教程中探讨的相同的简单步骤就可以实现。但是，“使用计算列”介绍几种值得探讨的技巧。

如果用户正在使用采用 ad-hoc SQL 语句的 TableAdapter ，则可以通过TableAdapter Configuration 向导简单地将计算列包含在 TableAdapter 的主查询中。这样，就会自动生成包含计算列的INSERT 和UPDATE 语句。如果尝试执行这些方法之一，将会启动带有“The column ‘ColumnName’ cannot be modified because it is either a computed column or is the result of a UNION operator” 消息的SqlException 。尽管可以通过 TableAdapter 的 InsertCommand 和UpdateCommand 属性人工调整 INSERT 和 UPDATE 语句，但 TableAdapter Configuration 向导一旦重新运行，这些定制都将丢失。

由于使用 ad-hoc SQL 语句的 TableAdapter 的这一脆弱性，建议使用计算列时最好使用存储过程。如果用户正在使用现有的存储过程，只需像对强类型 DataSet 的 TableAdapter 使用现有存储过程 教程中讨论的那样对 TableAdapter 进行简单配置。因此，如果用户已经有了TableAdapter 向导为其创建的存储过程，重要的就是要在最初从主查询中删除所有计算列。如果用户将计算列包含在主查询中，TableAdapter 向导刚一完成，它就会通知用户：无法创建相应的存储过程。总而言之，最初我们需要使用无计算列的主查询对TableAdapter 进行配置，然后人工更新相应的存储过程和TableAdapter 的SelectCommand 以便包含计算列。这个方法类似于更新 TableAdapter来使用 JOIN 教程中使用的方法。

针对本教程，我们需要添加新的 TableAdapter 并让它自动为我们创建存储过程。因此，最初我们需要从主查询中删除FullContactName 计算列。

首先，打开~/App_Code/DAL 文件夹中的 NorthwindWithSprocs DataSet 。右键单击 Designer ，然后从上下文菜单选择添加一个新TableAdapter 。这将启动 TableAdapter Configuration 向导。指定要查询数据的数据库（NORTHWNDConnectionString 来自 Web.config ）然后单击 Next 。因为我们还没有为查询和修改 Suppliers 表创建任何存储过程，因此选择 “Create new stored procedures” 选项，以便向导会创建存储过程，然后单击Next 。

图3 ：选择 “Create new stored procedures” 选项

向导的下一步提示我们指定主查询。输入下列查询，它会返回每个供应商的SupplierID 、CompanyName 、ContactName 和ContactTitle 列。注意：该查询有目的地删除了计算列(FullContactName) ；在步骤 4 中我们将更新相应的存储过程以包含该列。

SELECT SupplierID, CompanyName, ContactName, ContactTitle FROM Suppliers

输入主查询并单击 Next 后，向导让我们为将要生成的四个存储过程命名。将这些存储过程命名为Suppliers_Select 、Suppliers_Insert 、Suppliers_Update 和Suppliers_Delete ，如图 4 所示。

图4 ：定制自动生成的存储过程的名称

向导的下一步让我们为 TableAdapter 的方法命名，并指定访问和更新数据使用的模式。保留选中三个复选框，但将GetData 方法重命名为 GetSuppliers 。单击 Finish 完成向导。

图5 ：将 GetData 方法重命名为 GetSuppliers

单击Finish 后，向导将创建四个存储过程并将 TableAdapter 和相应的DataTable 添加到Typed DataSet 。

步骤4：在TableAdapter 的主查询中包含计算列

我们需要更新在步骤 3 中创建的 TableAdapter 和 DataTable ，以便包含 FullContactName 计算列。这需要两个步骤：

1. 更新 Suppliers_Select 存储过程以便返回 FullContactName 计算列，接着
2. 更新 DataTable 以便包含相应的 FullContactName 列。

首先，定位到 Server Explorer ，向下进入 Stored Procedures 文件夹。打开 Suppliers_Select 存储过程并更新SELECT 查询，以便包含FullContactName 计算列：

SELECT SupplierID, CompanyName, ContactName, ContactTitle, FullContactName FROM Suppliers

单击Toolbar 的Save 图标、按Ctrl+S 键、或从 File 菜单选择 SaveSuppliers_Select 选项来保存对存储过程的这个修改。

之后，返回 DataSet Designer ，右键单击 SuppliersTableAdapter ，并从上下文菜单选择 Configure 。注意：现在 Suppliers_Select 列的 Data Columns 收集中包含了FullContactName 列。

图6 ：运行 TableAdapter 的 Configuration Wizard 来更新 DataTable 的各列

单击Finish 完成向导。这将自动将相应的列添加到SuppliersDataTable 。TableAdapter 向导的智能化足以检测出FullContactName 列为计算列，因此是只读的。因此，它将该列的ReadOnly 属性设置为 true 。要验证这点，从SuppliersDataTable 选择该列然后转到 Properties 窗口（见图 7 ）。注意：FullContactName 列的 DataType 和 MaxLength 属性也都做了相应的设置。

图7 ：FullContactName 列被标记为只读

步骤 5：向TableAdapter 添加 GetSupplierBySupplierID 方法

本教程中，我们将创建在可更新网格中显示供应商的ASP.NET 页面。在前面的几个教程中，通过如下操作：从强类型DataTable 的 DAL 检索一个记录，更新其属性，然后将更新的DataTable 发送回 DAL 来向数据库传播这一变更，我们已经从业务逻辑层更新了该记录。为了实现第一步，即从DAL 检索更新的记录，首先需要向DAL 添加GetSupplierBySupplierID(supplierID) 方法。

在DataSet Design 中，右键单击SuppliersTableAdapter ，然后从上下文菜单选择 Add Query 选项。按照步骤3 的操作，通过选择 “Create new stored procedure” 选项使向导生成一个新存储过程（参考前面图3 的向导的这个步骤的屏幕截图）。因为该方法将返回一个含有多列的记录，这指示我们想要使用其为“SELECT which returns rows” 的一个 SQL 查询，单击 Next 。

图8 ：选择 “SELECT which returns rows” 选项

下一步提示我们指定用于该方法的查询。输入下列字段，这些字段与主查询返回的数据字段相同，但针对的是特定供应商。

SELECT SupplierID, CompanyName, ContactName, ContactTitle, FullContactName FROM Suppliers WHERE SupplierID = @SupplierID

下一个屏幕要求我们为将要自动生成的存储过程命名。将该存储过程命名为Suppliers_SelectBySupplierID 然后单击Next 。

图9 ：将存储过程命名为 Suppliers_SelectBySupplierID

最后，向导提示我们指定用于该TableAdapter 的数据访问模式和方法名称。保持选中两个复选框，但是分别将FillBy 和 GetDataBy 方法重命名为FillBySupplierID 和 GetSupplierBySupplierID 。

图10 ：将 TableAdapter 方法命名为 FillBySupplierID 和GetSupplierBySupplierID

单击Finish 完成向导。

步骤6：创建业务逻辑层

在创建使用步骤 1 中创建的计算列的 ASP.NET 页面之前，首先需要在BLL 中添加相应的方法。步骤 7 将要创建的 ASP.NET 页面允许用户查看和编辑供应商。因此，需要我们的BLL 至少提供一个获得所有供应商的方法和另一个更新特定供应商的方法。

在 ~/App_Code/BLL 文件夹中创建一个名称为 SuppliersBLLWithSprocs 的新类文件，并添加下列代码：

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 SuppliersBLLWithSprocs 
{ 
    private SuppliersTableAdapter _suppliersAdapter = null; 
    protected SuppliersTableAdapter Adapter 
    { 
        get 
        { 
            if (_suppliersAdapter == null) 
                _suppliersAdapter = new SuppliersTableAdapter(); 
 
            return _suppliersAdapter; 
        } 
    } 
 
    [System.ComponentModel.DataObjectMethodAttribute 
        (System.ComponentModel.DataObjectMethodType.Select, true)] 
    public NorthwindWithSprocs.SuppliersDataTable GetSuppliers() 
    { 
        return Adapter.GetSuppliers(); 
    } 
 
    [System.ComponentModel.DataObjectMethodAttribute 
        (System.ComponentModel.DataObjectMethodType.Update, true)] 
    public bool UpdateSupplier(string companyName, string contactName,  
        string contactTitle, int supplierID) 
    { 
        NorthwindWithSprocs.SuppliersDataTable suppliers =  
            Adapter.GetSupplierBySupplierID(supplierID); 
        if (suppliers.Count == 0) 
            // no matching record found, return false 
            return false; 
 
        NorthwindWithSprocs.SuppliersRow supplier = suppliers[0]; 
 
        supplier.CompanyName = companyName; 
        if (contactName == null)  
            supplier.SetContactNameNull();  
        else  
            supplier.ContactName = contactName; 
        if (contactTitle == null)  
            supplier.SetContactTitleNull();  
        else  
            supplier.ContactTitle = contactTitle; 
 
        // Update the product record 
        int rowsAffected = Adapter.Update(supplier); 
 
        // Return true if precisely one row was updated, otherwise false 
        return rowsAffected == 1; 
    } 
}

像其他BLL 类一样，SuppliersBLLWithSprocs 含有一个返回 SuppliersTableAdapter 实例的Protected Adapter 属性以及两个 Public 方法：GetSuppliers 和UpdateSupplier 。GetSuppliers 方法调用并返回由数据访问层中对应的GetSupplier 方法返回的SuppliersDataTable 。UpdateSupplier 方法检索通过调用 DAL 的GetSupplierBySupplierID(supplierID) 方法更新的特定供应商的有关信息。接着它还更新CategoryName 、ContactName 和ContactTitle 属性，并通过调用 DAL 的 Update 方法将这些变更提交给数据库，也就传递了修改的SuppliersRow 对象。

注意 ： 除 SupplierID 和 CompanyName 外，Suppliers 表中的其他列都允许为 NULL 值得。因此，如果传递的 contactName 或contactTitle 参数为空，需要使用 SetContactNameNull 和SetContactTitleNull 方法分别将相应的 ContactName 和ContactTitle 属性设置为 NULL 数据库值。

步骤7：从表示层使用计算列

在向Suppliers 表添加了计算列并对 DAL 和 BLL 进行了相应更新后，现在我们可以构建使用FullContactName 计算列的 ASP.NET 页面了。首先，打开 AdvancedDAL 文件夹的 ComputedColumns.aspx 页面，并将  GridView 从 Toolbox 拖放到 Designer 。将GridView 的ID 属性设置为 Suppliers ，从其智能标记将其绑定到名称为 SuppliersDataSource 的新 ObjectDataSource 。将ObjectDataSource 配置为使用我们在步骤 6 中添加的 SuppliersBLLWithSprocs 类，然后单击 Next 。

图11 ：将 ObjectDataSource 配置为使用 SuppliersBLLWithSprocs 类

SuppliersBLLWithSprocs 类中仅定义了两个方法：GetSuppliers 和 UpdateSupplier 。确保在 SELECT 和UPDATE 选项卡中分别指定了这两个方法，然后单击Finish 以完成 ObjectDataSource 的配置。

一旦完成 Data Source Configuration 向导，Visual Studio 将为返回的每个数据字段添加一个BoundField 。删除SupplierID BoundField ，将 CompanyName 、ContactName 、ContactTitle 和FullContactName BoundField 的 HeaderText 属性分别修改为 “Company” 、“Contact Name” 、“Title” 和 “Full Contact Name” 。从智能标记，选中“Enable Editing” 复选框来启动 GridView 的内置编辑功能。

除了向GridView 添加 BoundField 外，完成了 Data Source Wizard 还造成 Visual Studio 将 ObjectDataSource 的 OldValuesParameterFormatString 属性设置为 “original_{0}” 。将该设置还原为其默认值 “{0}” 。

对 GridView 和 ObjectDataSource 完成了这些编辑后，它们的声明标记应当与如下声明类似：

<asp:GridView ID="Suppliers" runat="server" AutoGenerateColumns="False"  
    DataKeyNames="SupplierID" DataSourceID="SuppliersDataSource"> 
    <Columns> 
        <asp:CommandField ShowEditButton="True" /> 
        <asp:BoundField DataField="CompanyName"  
            HeaderText="Company"  
            SortExpression="CompanyName" /> 
        <asp:BoundField DataField="ContactName"  
            HeaderText="Contact Name"  
            SortExpression="ContactName" /> 
        <asp:BoundField DataField="ContactTitle"  
            HeaderText="Title"  
            SortExpression="ContactTitle" /> 
        <asp:BoundField DataField="FullContactName"  
            HeaderText="Full Contact Name" 
            SortExpression="FullContactName"  
            ReadOnly="True" /> 
    </Columns> 
</asp:GridView> 
 
<asp:ObjectDataSource ID="SuppliersDataSource" runat="server" 
    SelectMethod="GetSuppliers" TypeName="SuppliersBLLWithSprocs"  
        UpdateMethod="UpdateSupplier"> 
    <UpdateParameters> 
        <asp:Parameter Name="companyName" Type="String" /> 
        <asp:Parameter Name="contactName" Type="String" /> 
        <asp:Parameter Name="contactTitle" Type="String" /> 
        <asp:Parameter Name="supplierID" Type="Int32" /> 
    </UpdateParameters> 
</asp:ObjectDataSource>

下一步，通过浏览器访问该页面。如图 12 所示，网格中列出了每个供应商，包括FullContactName 列，它的值是由格式为 “ContactName (ContactTitle, CompanyName)” 的其他三列简单合并的。

图12：网格中列出了每个供应商

针对特定供应商单击 Edit 按钮形成回发，同时将该行以可编辑界面方式显示（见图13 ）。前三列以默认的可编辑界面，即TextBox 控件显示，控件的Text 属性被设置为数据字段的值。而 FullContactName 列维持为文本。当完成Data Source Configuration 向导，将 BoundField 添加到GridView 后，FullContactName BoundField 的 ReadOnly 属性被设置为True ，这是因为 SuppliersDataTable 中相应的 FullContactName 列的 ReadOnly 属性已经设为 True 。步骤 4 中讲过，因为TableAdapter 检测到了FullContactName 列为计算列，所以将它的 ReadOnly 属性设置为 True 。

图13 ：FullContactName 列是不能编辑的

继续下列步骤，对可编辑列的一个或多个的值进行更新，然后单击Update 。注意是如何自动更新 FullContactName 的值来反映这一变更的。

注意：GridView 当前使用可编辑字段的 BoundField ，其结果是显示默认的编辑界面。因为请求了CompanyName 字段，应该将该字段转换为包含 RequiredFieldValidator 的TemplateField 。把这个工作留给有兴趣的读者的进行练习。有关将BoundField 转换为TemplateField 并添加验证控件的详细步骤说明，参考为编辑与插入界面添加验证控件 教程。

小结

定义表的 schema 时，Microsoft SQL Server 允许使用计算列。计算列的值是由引用同一记录的其他列的值的表达式计算得到的。因为计算列的值是由表达式决定的，因此这些列是只读的，不能用INSERT 或 UPDATE 语句为这些列赋值。在尝试自动生成相应的INSERT 、UPDATE 和DELETE 语句的 TableAdapter 的主查询中使用计算列时，会带来一些挑战。

本教程中，我们讨论了应对这些由计算列带来的挑战的一些技术方法。特别是，我们在TableAdapter 中使用了存储过程，这就克服了使用ad-hoc SQL 语句的 TableAdapter 所固有的脆弱性。如果已经有了 TableAdapter 向导创建的新存储过程，重要的就是在最初从主查询中删除所有计算列，因为它们的存在会阻碍生成数据修改存储过程。初次配置TableAdapter 后，就可以对它的 SelectCommand 存储过程重新组织使其包含任何计算列。

快乐编程！