在ASP.NET 2.0中操作数据之六十九：处理Computed Columns列

导言：

　　microsoft sql server里有一种computed columns列.这种列的值是通过一个表达式来计算，而表达式引用的是同一张表的其它列的值.打个比方，有一张servicelog表，其包含了serviceperformed, employeeid, rate, duration等列. 虽然我们可以在一个web页面或其它什么界面里计算每笔服务的费用(也就是 比率 rate乘以时间段duration)，不过我们也可以手动向servicelog表添加一个 amountdue列以反映该信息.我们可以将该列创建为一个普通列，只是任何时候当rate 或 duration列的值发生改变时需要更新amountdue列的值.一个比较好的办法是将amountdue创建成一个computed column 列，其使用的表达式为 rate * duration. 这样，当在一个查询里引用该列时sql server就可以自动的计算amountdue列的值.

　　由于computed column列的值是由表达式决定的，所以这种列是只读的，并且不能在insert 或 update statements里对其赋值.然而，对使用ad-hoc sql statements的tableadapter来说，如果主查询里引用了computed column列，那么自动生成的insert 和 update statements也会自动的引用computed column列.所以，我们必须更新tableadapter的 insert 和 update 查询，以及insertcommand 和 updatecommand属性，以删除对任何computed column列的引用.

　　如果在使用 ad-hoc sql statements的tableadapter里使用computed columns的话，我们要面临的挑战之一便是，每当完成tableadapte设置向导时，tableadapter的 insert 和 update查询都会自动的生成，又再一次的自动引用computed column列.不过如果tableadapters使用存储过程的话，就不会出现这个问题.

　　在本文，我们将向northwind数据库的suppliers表添加一个computed column列，然后相应地创建一个tableadapter来处理该表以及该computed column列.我们将在tableadapter里使用存储过程而不是ad-hoc sql statements.

第一步：向suppliers表添加一个computed column

　　在本文，我们将向suppliers表添加一个名为fullcontactname的computed column列，它以“contactname (contacttitle, companyname)”的格式返回contact的name, title，以及所在的公司.

　　打开服务器资源管理器，在suppliers表上单击右键，选“open table definition”，这将会显示出表所包含的列以及列的属性，比如数据类型、是否允许为null值等等.要添加一个computed column列，只需在表定义里键入表的名称，接下来在column属性窗口的computed column specification部分的(formula)文本框里输入表达式（如图1所示）。将该computed column列命名为fullcontactname，并使用下面的表达式：

contactname + ' (' + case when contacttitle is not null then
 contacttitle + ', ' else '' end + companyname + ')'

　　请注意，在sql里可以用操作符“+” 来连接字符串。而case声明类似于传统编程语言里的条件语句。上面代码里的case 声明可以这样来理解：如果contacttitle 不为null，那么输出contacttitle值，再紧接一个逗号；如果为null，则无操作。关于case 声明的更多信息请参阅文章《the power of sql case statements》（http://www.4guysfromrolla.com/webtech/102704-1.shtml）

　　注意：除了case声明外，我们还可以使用isnull(contacttitle, '')。语法isnull(checkexpression, replacementvalue) returns是这样工作的，如果checkexpression 不为null,则对其进行返回；如果为null则返回replacementvalue.虽然本文这2种语法都可以使用，但是在一些稍微复杂点的情况下，使用isnull的情况要多一些.添加完computed column列后，你的屏幕看起来应该和图1差不多：

图1：向suppliers表添加一个名为fullcontactname的computed column列

　　添加完后点工具栏上的save图标，或按ctrl+s键,又或者在file菜单里选“保存suppliers”.“保存”操作会自动地刷新服务器资源管理器，将刚刚添加的的列展现在suppliers表里.此外，键入到(formula)文本框的表达式会自动的进行调整，剔除不必要的空白，将列名用[]括起来，并使用圆括号()来显示操作的先后顺序：

(((([contactname]+' (')+case when [contacttitle] is not null
 then [contacttitle]+', ' else '' end)+[companyname])+')')

　　关于microsoft sql server里computed columns列的更多信息请参考文章《technical documentation》（http://msdn2.microsoft.com/en-us/library/ms191250.aspx）；同时你也可以参考文章《how to: specify computed columns》（http://msdn2.microsoft.com/en-us/library/ms188300.aspx），看如何一步步地创建computed columns列.

　　注意：默认情况下，数据库表并没有“实际”(physically)的包含computed columns列，而是每次在一个查询里引用它时重新计算其值.不过，我们可以选择“is persisted”选项来让sql server实实在在的在数据库表里创建computed columns列.这样的话我们可以为computed column列创建一个索引，当在一个查询的where字句里使用computed column列的值时就可以提高执行效率.更多的信息请参阅文章《creating indexes on computed columns》(http://msdn2.microsoft.com/en-us/library/ms189292.aspx)

第二步：查看computed column列的值

　　在处理数据访问层前，让我们花点时间查看fullcontactname列的值.在服务器资源管理器里，在suppliers表上右键单击，选择“new query”，这将启动一个查询窗口提示我们在查询里包含哪个表.添加suppliers表，再点“close”.接下来从suppliers表里选择companyname, contactname, contacttitle,以及fullcontactname列.最后，点击工具栏上的红色感叹号图标执行查询，查看结果.如图2所示，结果里包含了fullcontactname列，它以contactname (contacttitle, companyname)”的格式使用了companyname, contactname,contacttitle这3列.

图2：fullcontactname列的格式为“contactname (contacttitle, companyname)”

第三步：在数据访问层添加一个supplierstableadapter

　　为了在我们的应用程序里处理supplier信息，我们首先需要在dal层创建一个tableadapter 和 datatable.我们可以用前面的教程探讨的方法来进行创建，稍微不同的是我们将要与computed columns列打交道.

　　如果你用ad-hoc sql statements来构造一个tableadapter的话，你可以很简单的通过tableadapter设置向导在tableadapter的主查询里引用computed column列，这样，在自动生成的insert 和 update statements就会引用computed column列。如果你执行这2个方法的话，将会抛出这样的一个sqlexception：“the column ‘columnname' cannot be modified because it is either a computed column or is the result of a union operator”.虽然我们可以在insertcommand和updatecommand属性里手工改动insert 和 update statement，但是一旦重新运行tableadapter设置向导后，我们所做的用户定制就会丢失掉.

　　由于使用ad-hoc sql statements的tableadapters的这种不稳定性，我们倾向于使用存储过程来处理computed columns列.如果你使用的是现有的存储过程的话，你可以参阅第66章《在tableadapters中使用现有的存储过程》那样来配置tableadapter.如果你使用tableadapter设置向导来创建存储过程的话，很重要的一点是最开始，你不要在主查询里引用computed columns列，如果你在主查询里引用了computed columns列的话，你刚完成设置，向导就会提示你不能创建相应的存储过程.简而言之，在设置tableadapter时，最开始不要在主查询里引用computed column列，接下来再对相应的存储过程和tableadapter的selectcommand属性进行更改以引用computed column列.这种方法我们在第67章《在tableadapters中使用joins》里探讨过.

　　本文我们将新添加一个tableadapter并自动创建存储过程.当然我们要在主查询里忽略这个名为fullcontactname的computed column列.打开~/app_code/dal文件夹里的northwindwithsprocs dataset数据集，在设计器里右键单击，选“add a new tableadapter”，这将开启tableadapter设置向导，指定数据库连接信息(也就web.config文件里的northwndconnectionstring)，点next。选“create new stored procedures”项，再点next.

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

　　接下来我们要指定主查询，键入如下的查询，其返回每个supplier的supplierid, companyname, contactname, contacttitle列。注意，我们有意忽略了computed column列(即fullcontactname列)。不过我们将在第四步更新该存储过程以引用该列：

select supplierid, companyname, contactname, contacttitle
from suppliers

　　输入完主查询后点next，向导要我们为将要创建的4个存储过程命名，分别命名为suppliers_select, suppliers_insert, suppliers_update,以及 suppliers_delete。如图4所示：

图4：对自动生成的存储过程命名

　　接下来要我们为tableadapter的方法命名并指定用于访问和更新数据的模式.我们全部选中这3项，不过将getdata方法重命名为getsuppliers.点击finish完成配置.

图5：将getdata方法重命名为getsuppliers

　　完成后向导将创建这4个存储过程，并向类型化的dataset添加ableadapter以及对应的datatable.

第四步：在tableadapter的主查询里引用computed column列

接下来我们将对第三步创建的tableadapter 和 datatable进行更新以引用fullcontactname列，这要经过2个步骤：

1.更新名为suppliers_select的存储过程以返回fullcontactname列

2.更新datatable以包含相应的fullcontactname列

　　首先在服务器资源管理器里打开存储过程文件夹，打开suppliers_select存储过程，更新其select查询以引用fullcontactname列：

select supplierid, companyname, contactname, contacttitle, fullcontactname
from suppliers

　　保存所做的修改.接下来返回到dataset designer，在supplierstableadapter上右键单击，选“configure”.我们可以注意到suppliers_select里的data columns集里已经包含了fullcontactname列.

图6：返回到tableadapter的设置向导更新datatable的列

　　点击finish完成设置，这将自动地为suppliersdatatable添加相应的列.tableadapter发觉fullcontactname列是一个computed column列，且是只读的.因此将设置该列的readonly属性为true.我们可以进行验证：在suppliersdatatable里选择该列，打开其属性窗口(如图7)，我们注意到fullcontactname列的datatype 和 maxlength属性都作了相应的设置.

图7：fullcontactname列标记为read-only

第五步：向tableadapter添加一个getsupplierbysupplierid方法

　　在本文我们将在一个具有更新功能的asp.net页面里展示suppliers信息.在前面的文章里，我们从dal获取指定的记录并将其作为一个强类型的datatable返回给bll以做更新，然后将更新后的datatable再传递给dal，对数据库做相应的改动.为此，第一步——从dal返回要更新的记录——我们需要向dal层添加一个名为getsupplierbysupplierid(supplierid)的方法.

　　在dataset design设计器里右键单击supplierstableadapter，选“add query” ，再选“create new stored procedure”(可参考3图)。再选“select which returns rows”再点next.

图8：选“select which returns rows”项

　　接下来为该方法指定查询，键入如下的代码，它将检索某个具体的supplier返回的列与主查询一样.

select supplierid, companyname, contactname, contacttitle, fullcontactname
from suppliers
where supplierid = @supplierid

　　接下来我们将该存储过程命名为suppliers_selectbysupplierid，点next.

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

　　在接下来的界面，全部选中图里的2项，并将fillby 和 getdataby方法分别命名为fillbysupplierid 和 getsupplierbysupplierid.

图10：将tableadapter的方法命名为fillbysupplierid 和 getsupplierbysupplierid

　　点finish完成向导

第六步：创建业务逻辑层business logic layer

　　在创建asp.net页面前，我们首先要在bll添加相应的方法.我们将在第7步创建页面，其允许我们查看并编辑suppliers.因此我们在bll至少要包含2个方法，一个获取所有的suppliers，一个用于更新某个具体的supplier.

在~/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 class类一样，suppliersbllwithsprocs有一个protected adapter属性，2个public方法：getsuppliers 和 updatesupplier.其中，getsuppliers方法调用data access layer层对应的getsupplier方法，该方法将suppliersdatatable返回给bll层；而updatesupplier方法通过调用dal层的getsupplierbysupplierid(supplierid)方法来获取某人具体supplier的信息，然后更新其categoryname, contactname,contacttitle属性，再将修改后的suppliersrow对象传递给data access layer层的 update方法，以对数据库做相应的更新.

　　注意：除了supplierid 和 companyname外，suppliers表的所有列都允许为null值，所以如果传递的contactname 或 contacttitle参数为null的话，我们将分别调用setcontactnamenull 和 setcontacttitlenull方法来将contactname 和 contacttitle 属性设置为null.

第七步： 在表现层处理computed column列

　　做完了所有的必要工作后， 我们将创建一个asp.net页面来处理fullcontactname列， 打开advanceddal文件夹里的computedcolumns.aspx 页面，拖一个gridview控件到页面，设其id为suppliers，在其智能标签里绑定到一个名为suppliersdatasource的objectdatasource控件，设置其调用suppliersbllwithsprocs类，点next.

图11：设置objectdatasource调用suppliersbllwithsprocs class类

　　在suppliersbllwithsprocs类里只有2个方法getsuppliers 和 updatesupplier.确保在select 和 update标签里分别选中这2个方法，点finish完成设置.完成设置后，visual studio将添加相应的boundfield，移除supplierid列，并将companyname, contactname, contacttitle,和fullcontactname列的headertext属性分别设置为“company”, “contact name”, “title”,“full contact name”,再启用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：每行展示一个supplier

　　点击某行的edit按钮将导致页面回传，且该行显示为一个编辑界面(如图13)，头3行呈现为默认的编辑界面——一个textbox控件，且其text属性为该数据域(data field)的值.不过fullcontactname列仍然呈现为一个文本框.在data source设置向导完成并向gridview控件添加完相应的boundfields时，fullcontactname boundfield的readonly属性为true。我们在第四步注意到，fullcontactname列的readonly属性为true，因为tableadapter意识到该列为一个computed column列.

图13：fullcontactname列为只读

　　我们改动这3个列中至少一个列的值，点update按钮.我们发现fullcontactname列的值跟着发生改变.

　　注意：由于gridview当前用的是boundfields，导致编辑时用的是默认的界面.又由于companyname列是必需的，我们应将其转化成一个templatefield以包含一个requiredfieldvalidator控件.我将此作为一个练习留给读者，你可以参考第19章《》，看如何一步步的将boundfield转换成 templatefield，再添加一个确认控件.

结语：

　　当创建一个表时，microsoft sql server允许我们创建一个computed columns列.这些computed columns列引用该条记录的其它列，再通过一个表达式对其赋值.由于其值来源于一个表达式，因此这种列是只读的，且不能通过insert 或 update statement对其赋值.正是如此，当在一个tableadapter的主查询里引用computed column的话，要想自动的生成对应的insert, update,和delete statements有点麻烦.

　　在本文，我们探讨了使用computed columns列面临的挑战.具体来说，由于使用ad-hoc sql statements的tableadapters自身固有的不稳定性，我们探讨了使用存储过程的情况.当使用tableadapter向导创建一个新的存储过程的时候，很重要的一点是，最开始不要在主查询里引用任何的computed columns，不然就不能自动的生成对应的存储过程.完成向导后，我们要手动修改selectcommand属性以引用computed columns列.

　　祝编程快乐！

作者简介

　　本系列教程作者 scott mitchell，著有六本asp/asp.net方面的书，是4guysfromrolla.com的创始人，自1998年以来一直应用 微软web技术。大家可以点击查看全部教程《[翻译]scott mitchell 的asp.net 2.0数据教程》，希望对大家的学习asp.net有所帮助。