处理计算列 (VB)

  • 项目

作者 ：Scott Mitchell

下载 PDF

创建数据库表时，Microsoft SQL Server 允许定义计算列，其值是从通常引用同一数据库记录中其他值的表达式计算的。 此类值在数据库中是只读的，在使用 TableAdapter 时需要特殊注意事项。 本教程介绍如何应对计算列带来的挑战。

简介

Microsoft SQL Server 允许计算列，这些列的值是从通常引用同一表中其他列的值的表达式计算的。 例如，时间跟踪数据模型可能有一个名为 的表ServiceLog，其中包含 、ServicePerformedEmployeeID、 Rate和 Duration等列。 虽然每个服务项的应付金额 (比率乘以持续时间，) 可通过网页或其他编程界面计算，但在表中包含报告AmountDue此信息的ServiceLog列可能很方便。 此列可以创建为普通列，但每当 或 Duration 列值发生更改时，都需要对其进行更新Rate。 更好的方法是使用表达式 Rate * Duration将AmountDue列设置为计算列。 这样做会导致SQL Server在查询中引用列值时自动计算AmountDue列值。

由于计算列的值由表达式确定，因此此类列是只读的，因此不能在 或 UPDATE 语句中INSERT为其分配值。 但是，当计算列是使用临时 SQL 语句的 TableAdapter main查询的一部分时，它们会自动包含在自动生成INSERT的 和 UPDATE 语句中。 因此，必须更新 TableAdapter 和 INSERTUPDATE 查询和 InsertCommand 属性 UpdateCommand ，以删除对任何计算列的引用。

将计算列与使用临时 SQL 语句的 TableAdapter 配合使用的一个难题是，每当完成 TableAdapter INSERT 配置向导时，都会自动重新生成 TableAdapter 和 UPDATE 查询。 因此，如果重新运行向导，从 和 UPDATE 手动删除INSERT的计算列将重新出现查询。 虽然使用存储过程的 TableAdapter 不会受到这种脆性的影响，但它们确实有自己的怪癖，我们将在步骤 3 中解决。

在本教程中，我们将向 Northwind 数据库中的 Suppliers 表添加计算列，然后创建相应的 TableAdapter 来处理此表及其计算列。 我们将让 TableAdapter 使用存储过程而不是临时 SQL 语句，以便在使用 TableAdapter 配置向导时不会丢失自定义项。

让我们开始吧！

步骤 1：将计算列添加到Suppliers表

Northwind 数据库没有任何计算列，因此需要自行添加一个计算列。 在本教程中，让我们将计算列添加到 Suppliers 名为 FullContactName 的表中，该列以以下格式返回联系人的姓名、职务和公司： ContactName (ContactTitle、 CompanyName) 。 显示有关供应商的信息时，此计算列可能用于报表中。

首先打开表定义， Suppliers 方法是在服务器资源管理器中右键单击表， Suppliers 然后从上下文菜单中选择“打开表定义”。 这将显示表的列及其属性，例如其数据类型、是否允许 NULL 等。 若要添加计算列，请先在表定义中键入列的名称。 接下来，在“列属性窗口 (的”计算列规范“部分下的” (公式) “文本框中输入其表达式，请参阅图 1) 。 将计算列 FullContactName 命名为 ，并使用以下表达式：

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

请注意，可以使用 运算符在 SQL + 中串联字符串。 语句 CASE 可以像传统编程语言中的条件一样使用。 在上述表达式中，CASE语句可以读取为：如果没有ContactTitleNULL，则输出ContactTitle与逗号连接的值，否则不发出任何内容。 有关 语句有用性的详细信息 CASE ，请参阅 SQL CASE 语句。

注意

我们可以选择使用 ，而不是在此处使用 CASEISNULL(ContactTitle, '')语句。 ISNULL(checkExpression, replacementValue) 如果 checkExpression 为非 NULL，则返回 checkExpression;否则返回 replacementValue。 ISNULL虽然 或 CASE 在此实例中都适用，但存在更复杂的情况，CASE即 语句的灵活性无法与 ISNULL相匹配。

添加此计算列后，屏幕应类似于图 1 中的屏幕截图。

将名为 FullContactName 的计算列添加到 Suppliers 表

图 1：将名为 的 FullContactName 计算列添加到 Suppliers 表 (单击以查看全尺寸图像)

命名计算列并输入其表达式后，通过单击工具栏中的“保存”图标、按 Ctrl+S 或转到“文件”菜单并选择“保存” Suppliers，将更改保存到表中。

保存表应刷新服务器资源管理器，包括表 列列表中的刚添加的 Suppliers 列。 此外，输入到“公式) ”文本框中 (表达式将自动调整为等效表达式，该表达式去除不必要的空格，用方括号 ([]) 将列名括起来，并包含括号以更明确地显示操作顺序：

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

有关 Microsoft SQL Server 中计算列的详细信息，请参阅技术文档。 此外，检查如何：指定计算列，以逐步演练创建计算列。

注意

默认情况下，计算列不会以物理方式存储在表中，而是在每次在查询中引用计算列时重新计算。 但是，通过选中“持久化”复选框，可以指示SQL Server以物理方式将计算列存储在表中。 这样做可以在计算列上创建索引，这可以提高在其 WHERE 子句中使用计算列值的查询的性能。 有关详细信息 ，请参阅创建计算列的索引 。

步骤 2：查看计算列的值

在开始处理数据访问层之前，让我们花一点时间查看 FullContactName 值。 在服务器资源管理器中，右键单击表名称， Suppliers 然后从上下文菜单中选择“新建查询”。 此时会显示一个“查询”窗口，提示我们选择要包含在查询中的表。 添加表并单击 Suppliers “关闭”。 接下来，检查 CompanyNameSuppliers 表中的 、ContactName、 ContactTitle和 FullContactName 列。 最后，单击工具栏中的红色感叹号图标以执行查询并查看结果。

如图 2 所示，结果包括 FullContactName，其中列出了CompanyNameContactName使用 (格式ContactName的 ContactTitle、 和 ContactTitle 列， CompanyName) 。

FullContactName 使用 ContactName 格式 (ContactTitle，CompanyName)

图 2：使用 FullContactName 格式 ContactName (ContactTitle， CompanyName) (单击以查看全尺寸图像)

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

为了在应用程序中处理供应商信息，我们需要首先在 DAL 中创建 TableAdapter 和 DataTable。 理想情况下，可以使用前面教程中介绍的相同简单步骤完成此操作。 但是，使用计算列会引入一些值得讨论的皱纹。

如果使用使用即席 SQL 语句的 TableAdapter，只需通过 TableAdapter 配置向导将计算列包含在 TableAdapter main 查询中。 但是，这将自动生成 INSERT 包含计算列的 和 UPDATE 语句。 如果尝试执行其中一种方法，将引发带有 SqlException 消息 ColumnName 列的 ，因为它要么是计算列，要么是 UNION 运算符的结果。 INSERT虽然 可以通过 TableAdapter 和 InsertCommandUpdateCommand 属性手动调整 和 UPDATE 语句，但只要重新运行 TableAdapter 配置向导，这些自定义项就会丢失。

由于 TableAdapter 使用临时 SQL 语句的脆弱性，建议在处理计算列时使用存储过程。 如果使用现有存储过程，只需配置 TableAdapter，如 对类型化数据集使用现有存储过程 教程中所述。 但是，如果已使用 TableAdapter 向导创建存储过程，请务必首先省略main查询中的任何计算列。 如果在main查询中包含计算列，则 TableAdapter 配置向导将在完成后通知你它无法创建相应的存储过程。 简而言之，我们需要首先使用计算的无列main查询配置 TableAdapter，然后手动更新相应的存储过程和 TableAdapter 以SelectCommand包含计算列。 此方法类似于将 TableAdapter 更新为使用JOIN教程中使用的 方法。

在本教程中，让我们添加一个新的 TableAdapter，并让它自动为我们创建存储过程。 因此，我们最初需要省略FullContactNamemain查询中的计算列。

首先打开 NorthwindWithSprocs 文件夹中的 ~/App_Code/DAL DataSet。 右键单击Designer，然后从上下文菜单中选择添加新的 TableAdapter。 这将启动 TableAdapter 配置向导。 指定要从) 查询 (NORTHWNDConnectionStringWeb.config 数据的数据库，然后单击“下一步”。 由于我们尚未创建任何用于查询或修改表的 Suppliers 存储过程，因此请选择“创建新存储过程”选项，以便向导为我们创建存储过程，然后单击“下一步”。

选择“创建新存储过程”选项

图 3：选择“创建新存储过程”选项 (单击以查看全尺寸图像)

后续步骤会提示我们输入main查询。 输入以下查询，该查询返回每个供应商的 SupplierID、 CompanyName、 ContactName和 ContactTitle 列。 请注意，此查询有意省略计算列 (FullContactName) ;我们将更新相应的存储过程，以在步骤 4 中包含此列。

SELECT SupplierID, CompanyName, ContactName, ContactTitle
FROM Suppliers

输入main查询并单击“下一步”后，向导允许我们命名它将生成的四个存储过程。 将这些存储过程 Suppliers_Select命名为 、 Suppliers_Insert、 Suppliers_Update和 Suppliers_Delete，如图 4 所示。

自定义自动生成的存储过程的名称

图 4：自定义自动生成的存储过程的名称 (单击以查看全尺寸图像)

下一个向导步骤允许我们命名 TableAdapter 方法，并指定用于访问和更新数据的模式。 保留所有三个复选框处于选中状态，但将 GetData 方法重命名为 GetSuppliers。 单击“完成”以完成向导。

将 GetData 方法重命名为 GetSuppliers

图 5：重命名 GetData 方法以 GetSuppliers (单击以查看全尺寸图像)

单击“完成”后，向导将创建四个存储过程，并将 TableAdapter 和相应的 DataTable 添加到类型化数据集。

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

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

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

首先导航到服务器资源管理器并向下钻取到“存储过程”文件夹。 Suppliers_Select打开存储过程并更新SELECT查询以包含FullContactName计算列：

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

通过单击工具栏中的“保存”图标、按 Ctrl+S 或从“文件”菜单中选择“保存” Suppliers_Select 选项，保存对存储过程所做的更改。

接下来，返回到 DataSet Designer，右键单击 SuppliersTableAdapter，然后从上下文菜单中选择“配置”。 请注意，列 Suppliers_Select 现在在其 FullContactName Data Columns 集合中包含列。

运行 TableAdapter 配置向导以更新 DataTable 的列

图 6：运行 TableAdapter 配置向导以更新 DataTable 的列 (单击以查看全尺寸图像)

单击“完成”以完成向导。 这会自动将相应的列添加到 SuppliersDataTable。 TableAdapter 向导足够智能，可以检测到该 FullContactName 列是计算列，因此是只读的。 因此，它将列 的 ReadOnly 属性设置为 true。 若要对此进行验证，请从 SuppliersDataTable 中选择列，然后转到属性窗口 (请参阅图 7) 。 请注意， FullContactName 列 和 DataTypeMaxLength 属性也会相应地设置。

FullContactName 列标记为只读

图 7：将 FullContactName 列标记为 Read-Only (单击以查看全尺寸图像)

步骤 5：向GetSupplierBySupplierIDTableAdapter 添加方法

在本教程中，我们将创建一个 ASP.NET 页面，用于在可更新的网格中显示供应商。 在以前的教程中，我们更新了业务逻辑层中的单个记录，方法是将 DAL 中的特定记录检索为强类型 DataTable，更新其属性，然后将更新后的 DataTable 发送回 DAL 以将更改传播到数据库。 若要完成第一步（从 DAL 检索要更新的记录）时，需要先将 方法 GetSupplierBySupplierID(supplierID) 添加到 DAL。

右键单击 SuppliersTableAdapter “数据集设计”中的 ，然后从上下文菜单中选择“添加查询”选项。 正如我们在步骤 3 中所做的那样，通过选择“创建新存储过程”选项，让向导为我们生成新的存储过程 (请参阅图 3 获取此向导步骤) 的屏幕截图。 由于此方法将返回包含多个列的记录，因此指示我们想要使用 SQL 查询，该查询是 SELECT，该查询返回行，然后单击“下一步”。

选择返回行的 SELECT 选项

图 8：选择“返回行的 SELECT”选项 (单击以查看全尺寸图像)

后续步骤会提示我们输入要用于此方法的查询。 输入以下内容，这将返回与main查询相同的数据字段，但返回特定供应商的数据字段。

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

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

将存储过程命名为Suppliers_SelectBySupplierID

图 9：将存储过程 Suppliers_SelectBySupplierID 命名 (单击以查看全尺寸图像)

最后，向导会提示我们输入要用于 TableAdapter 的数据访问模式和方法名称。 让这两个复选框保持选中状态，但将 和 GetDataBy 方法分别重命名FillBy为 FillBySupplierID 和 GetSupplierBySupplierID。

将 TableAdapter 方法命名为 FillBySupplierID 和 GetSupplierBySupplierID

图 10：命名 TableAdapter 方法 FillBySupplierID 并 GetSupplierBySupplierID (单击以查看全尺寸图像)

单击“完成”以完成向导。

步骤 6：创建业务逻辑层

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

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

Imports NorthwindWithSprocsTableAdapters
<System.ComponentModel.DataObject()> _
Public Class SuppliersBLLWithSprocs
    Private _suppliersAdapter As SuppliersTableAdapter = Nothing
    Protected ReadOnly Property Adapter() As SuppliersTableAdapter
        Get
            If _suppliersAdapter Is Nothing Then
                _suppliersAdapter = New SuppliersTableAdapter()
            End If
            Return _suppliersAdapter
        End Get
    End Property
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, True)> _
    Public Function GetSuppliers() As NorthwindWithSprocs.SuppliersDataTable
        Return Adapter.GetSuppliers()
    End Function
    <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Update, True)> _
    Public Function UpdateSupplier(companyName As String, contactName As String, _
        contactTitle As String, supplierID As Integer) As Boolean
        Dim suppliers As NorthwindWithSprocs.SuppliersDataTable = _
            Adapter.GetSupplierBySupplierID(supplierID)
        If suppliers.Count = 0 Then
            ' no matching record found, return false
            Return False
        End If
        Dim supplier As NorthwindWithSprocs.SuppliersRow = suppliers(0)
        supplier.CompanyName = companyName
        If contactName Is Nothing Then 
            supplier.SetContactNameNull() 
        Else 
            supplier.ContactName = contactName
        End If
        If contactTitle Is Nothing Then 
            supplier.SetContactTitleNull() 
        Else 
            supplier.ContactTitle = contactTitle
        End If
        ' Update the product record
        Dim rowsAffected As Integer = Adapter.Update(supplier)
        ' Return true if precisely one row was updated, otherwise false
        Return rowsAffected = 1
    End Function
End Class

与其他 BLL 类一样， 具有一个 ProtectedAdapter 属性，SuppliersBLLWithSprocs该属性返回 类的SuppliersTableAdapter实例以及两Public种方法： GetSuppliers 和 UpdateSupplier。 方法 GetSuppliers 调用 并返回 SuppliersDataTable 数据访问层中相应 GetSupplier 方法返回的 。 方法 UpdateSupplier 通过调用 DAL s GetSupplierBySupplierID(supplierID) 方法检索有关正在更新的特定供应商的信息。 然后，它会更新 CategoryName、 ContactName和 ContactTitle 属性，并通过调用数据访问层 方法 Update （传入已 SuppliersRow 修改的对象）将这些更改提交到数据库。

注意

SupplierID除了 和 CompanyName之外，“供应商”表中的所有列都允许NULL值。 因此，如果传入的 contactName 或 参数是 Nothing ，则需要分别使用 SetContactNameNull 和 方法将相应的 ContactName 和 SetContactTitleNullContactTitle 属性设置为NULL数据库contactTitle值。

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

将计算列添加到 Suppliers 表中并相应地更新 DAL 和 BLL 后，我们便可以构建一个适用于 FullContactName 计算列的 ASP.NET 页。 首先打开 文件夹中的页面ComputedColumns.aspxAdvancedDAL，并将 GridView 从“工具箱”拖到Designer。 将 GridView 属性 ID 设置为 Suppliers ，并从其智能标记中将其绑定到名为 SuppliersDataSource的新 ObjectDataSource。 将 ObjectDataSource 配置为使用 SuppliersBLLWithSprocs 我们在步骤 6 中添加的类，然后单击“下一步”。

将 ObjectDataSource 配置为使用 SuppliersBLLWithSprocs 类

图 11：将 ObjectDataSource 配置为使用 SuppliersBLLWithSprocs 类 (单击以查看全尺寸图像)

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

完成数据源配置向导后，Visual Studio 将为返回的每个数据字段添加 BoundField。 SupplierID删除 BoundField，并将 、ContactName、 ContactTitle和 FullContactName BoundFields 的属性CompanyName分别更改为HeaderText“公司”、“联系人姓名”、“职务”和“完整联系人姓名”。 在智能标记中，检查“启用编辑”复选框以打开 GridView 的内置编辑功能。

除了将 BoundFields 添加到 GridView 之外，数据源向导的完成还会导致 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：每个供应商都列在网格 (单击以查看全尺寸图像)

单击特定供应商的“编辑”按钮会导致回发，并在其编辑界面中呈现该行 (见图 13) 。 前三列呈现在其默认编辑界面中 - 一个 TextBox 控件，其 Text 属性设置为数据字段的值。 但是，该 FullContactName 列仍保留为文本。 在完成数据源配置向导时，将 BoundFields 添加到 GridView 时，BoundField 的 ReadOnly 属性设置为 True ，因为 中的SuppliersDataTable相应FullContactName列的 ReadOnly 属性设置为 True。FullContactName 如步骤 4 中所述， FullContactName s ReadOnly 属性设置为 True ，因为 TableAdapter 检测到该列是计算列。

FullContactName 列不可编辑

图 13：列 FullContactName 不可编辑 (单击以查看全尺寸图像)

继续更新一个或多个可编辑列的值，然后单击“更新”。 请注意 s FullContactName 值如何自动更新以反映更改。

注意

GridView 当前将 BoundFields 用于可编辑字段，从而生成默认编辑界面。 由于字段 CompanyName 是必需的，因此应将其转换为包含 RequiredFieldValidator 的 TemplateField。 我留给感兴趣的读者做练习。 有关将 BoundField 转换为 TemplateField 和添加验证控件的分步说明，请参阅将 验证控件添加到编辑和插入接口 教程。

总结

定义表的架构时，Microsoft SQL Server 允许包含计算列。 这些列的值是从通常引用同一记录中其他列的值的表达式计算的。 由于计算列的值基于表达式，因此它们是只读的，不能在 或 UPDATE 语句中INSERT分配值。 在 tableAdapter 的main查询中使用计算列时，这会引入挑战，该查询会尝试自动生成相应的 INSERT、 UPDATE和 DELETE 语句。

在本教程中，我们讨论了规避计算列带来的挑战的技术。 具体而言，我们在 TableAdapter 中使用了存储过程来克服 TableAdapter 中使用临时 SQL 语句的固有的脆性。 让 TableAdapter 向导创建新的存储过程时，请务必让main查询最初省略任何计算列，因为它们的存在会阻止生成数据修改存储过程。 在最初配置 TableAdapter 后，可以重新调整其 SelectCommand 存储过程，以包含任何计算列。

编程愉快！

关于作者

Scott Mitchell 是七本 ASP/ASP.NET 书籍的作者， 4GuysFromRolla.com 的创始人，自 1998 年以来一直从事 Microsoft Web 技术工作。 Scott 担任独立顾问、培训师和作家。 他的最新书是 山姆斯在24小时内 ASP.NET 2.0自学。 可以在 上mitchell@4GuysFromRolla.com联系他，也可以通过他的博客（可在 中找到http://ScottOnWriting.NET）。

特别感谢

本教程系列由许多有用的审阅者审阅。 本教程的主要审阅者是希尔顿·吉塞诺和特蕾莎·墨菲。 有兴趣查看我即将发布的 MSDN 文章？ 如果是，请在 处mitchell@4GuysFromRolla.com放置一行。

上一页下一页