处理计算列 (VB)

项目
07/16/2024

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

简介

Microsoft SQL Server 允许 计算列，这些列是从通常引用同一表中其他列的值的表达式计算的列。例如，时间跟踪数据模型可能具有一个包含列的ServiceLog表，其中包括ServicePerformed、EmployeeIDRate和Duration等列。虽然每个服务项（即费率乘以持续时间）可以通过网页或其他编程界面计算到期金额，但可以方便地在表中包括一个名为AmountDue报告此信息的ServiceLog列。此列可以创建为普通列，但需要随时 Rate 更新或 Duration 更改列值。更好的方法是使用表达式Rate * Duration使AmountDue列成为计算列。这样做会导致 SQL Server 在查询中引用列值时自动计算 AmountDue 列值。

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

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

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

让我们开始吧！

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

Northwind 数据库没有任何计算列，因此我们需要自己添加一个列。对于本教程，让我们将一个计算列添加到调用FullContactName的Suppliers表中，该表返回联系人的姓名、标题及其工作的公司，格式如下： ContactName （ContactTitle， CompanyName）。显示有关供应商的信息时，可以在报表中使用此计算列。

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

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

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

注意

我们可以替代ISNULL(ContactTitle, '')使用此处的CASE语句。 ISNULL(checkExpression, replacementValue)如果 checkExpression 为非 NULL，则返回 checkExpression，否则返回 replacementValue。虽然这一实例中要么ISNULLCASE工作，但也有更复杂的方案，即语句的灵活性CASE无法匹配ISNULL。

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

图 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添加表，然后单击“关闭”。接下来，检查“供应商”表中的“供应商”表中的CompanyName列ContactNameContactTitle和FullContactName列。最后，单击工具栏中的红色感叹号图标以执行查询并查看结果。

如图 2 所示，结果包括FullContactName：使用格式 ContactName （ContactTitle， CompanyName）列出CompanyNameContactName和ContactTitle列。

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

步骤 3：向数据访问层添加`SuppliersTableAdapter`

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

如果使用使用临时 SQL 语句的 TableAdapter，则只需通过 TableAdapter 配置向导将计算列包含在 TableAdapter 主查询中。但是，这将自动生成 INSERT 和 UPDATE 包含计算列的语句。如果尝试执行这些方法之一，SqlException则无法修改列列名称，因为列名称是计算列，或者是 UNION 运算符的结果将引发。 INSERT虽然可以通过 TableAdapter s InsertCommand 和UpdateCommand属性手动调整 and UPDATE 语句，但只要重新运行 TableAdapter 配置向导，这些自定义项就会丢失。

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

在本教程中，让我们添加新的 TableAdapter，并自动为我们创建存储过程。因此，我们需要首先省略 FullContactName 主查询中的计算列。

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

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

后续步骤会提示我们进行主查询。输入以下查询，该查询返回每个供应商的SupplierID列ContactNameCompanyName和ContactTitle列。请注意，此查询故意省略计算列（FullContactName）;我们将更新相应的存储过程，以在步骤 4 中包含此列。

SELECT SupplierID, CompanyName, ContactName, ContactTitle
FROM Suppliers

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

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

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

图 5：将 GetData 方法重命名为 GetSuppliers （单击以查看全尺寸图像）

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

步骤 4：在 TableAdapter s Main Query 中包含计算列

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

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

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

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

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

接下来，返回到数据集设计器，右键单击 SuppliersTableAdapter，然后从上下文菜单中选择“配置”。请注意，该 Suppliers_Select 列现在在其 FullContactName “数据列”集合中包含该列。

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

单击“完成”，完成向导。这会自动向 <a0/> 添加相应的列。 TableAdapter 向导足够智能，可以 FullContactName 检测列是否为计算列，因此是只读的。因此，它将列 ReadOnly 的属性设置为 true。若要验证这一点，请从SuppliersDataTable中选择列，然后转到属性窗口（请参阅图 7）。请注意， FullContactName 列 DataType 和 MaxLength 属性也相应地设置。

图 7：列 FullContactName 标记为只读（单击以查看全尺寸图像）

步骤 5：向`GetSupplierBySupplierID`TableAdapter 添加方法

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

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

图 8：选择返回行选项的 SELECT（单击以查看全尺寸图像）

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

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

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

图 9：为存储过程 Suppliers_SelectBySupplierID 命名（单击以查看全尺寸图像）

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

图 10：命名 TableAdapter 方法FillBySupplierID（GetSupplierBySupplierID单击以查看全尺寸图像）

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

步骤 6：创建业务逻辑层

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

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

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 方法 GetSupplierBySupplierID(supplierID) 检索有关要更新的特定供应商的信息。然后，它通过调用数据访问层Update的方法（传入修改SuppliersRow的对象）来更新CategoryNameContactName和ContactTitle属性，并将这些更改提交到数据库。

注意

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

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

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

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

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

数据源配置向导完成后，Visual Studio 将为返回的每个数据字段添加 BoundField。 SupplierID删除 BoundField 并将 BoundFields 的属性CompanyNameFullContactNameContactTitleContactName分别更改为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 时，FullContactNameBoundField s ReadOnly 属性被设置为True因为其属性已设置为True相应的FullContactName列SuppliersDataTableReadOnly。如步骤 4 中所述， FullContactName s ReadOnly 属性设置为 True 因为 TableAdapter 检测到该列是计算列。

图 13： FullContactName 列不可编辑（单击可查看全尺寸图像）

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

注意

GridView 当前对可编辑字段使用 BoundFields，从而导致默认编辑界面。由于字段 CompanyName 是必需的，因此应将其转换为包含 RequiredFieldValidator 的 TemplateField。我离开这个作为一个练习，为感兴趣的读者。有关将 BoundField 转换为 TemplateField 和添加验证控件以及添加验证控件的分步说明，请参阅“将验证控件添加到编辑和插入接口”教程。

总结

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

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

快乐编程！

关于作者

斯科特·米切尔，七本 ASP/ASP.NET 书籍的作者和 4GuysFromRolla.com 的创始人，自1998年以来一直在与Microsoft Web 技术合作。斯科特担任独立顾问、教练和作家。他的最新书是 山姆斯在24小时内 ASP.NET 2.0。他可以通过他的博客联系到mitchell@4GuysFromRolla.com他，可以在该博客中找到 http://ScottOnWriting.NET。

特别感谢

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

通过

处理计算列 (VB)

简介

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

步骤 2：查看计算列的值

步骤 3：向数据访问层添加`SuppliersTableAdapter`

步骤 4：在 TableAdapter s Main Query 中包含计算列

步骤 5：向`GetSupplierBySupplierID`TableAdapter 添加方法

步骤 6：创建业务逻辑层

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

总结

关于作者

特别感谢

反馈

其他资源

通过

处理计算列 (VB)

简介

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

步骤 2：查看计算列的值

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

步骤 4：在 TableAdapter s Main Query 中包含计算列

步骤 5：向GetSupplierBySupplierIDTableAdapter 添加方法

步骤 6：创建业务逻辑层

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

总结

关于作者

特别感谢

反馈

其他资源

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

步骤 3：向数据访问层添加`SuppliersTableAdapter`

步骤 5：向`GetSupplierBySupplierID`TableAdapter 添加方法