在ASP.NET 2.0中操作數(shù)據(jù)之六十九：處理Computed Columns列

導(dǎo)言：

　　Microsoft SQL Server里有一種computed columns列.這種列的值是通過一個(gè)表達(dá)式來計(jì)算，而表達(dá)式引用的是同一張表的其它列的值.打個(gè)比方，有一張ServiceLog表，其包含了ServicePerformed, EmployeeID, Rate, Duration等列. 雖然我們可以在一個(gè)web頁面或其它什么界面里計(jì)算每筆服務(wù)的費(fèi)用(也就是 比率 rate乘以時(shí)間段duration)，不過我們也可以手動(dòng)向ServiceLog表添加一個(gè) AmountDue列以反映該信息.我們可以將該列創(chuàng)建為一個(gè)普通列，只是任何時(shí)候當(dāng)Rate 或 Duration列的值發(fā)生改變時(shí)需要更新AmountDue列的值.一個(gè)比較好的辦法是將AmountDue創(chuàng)建成一個(gè)computed column 列，其使用的表達(dá)式為 Rate * Duration. 這樣，當(dāng)在一個(gè)查詢里引用該列時(shí)SQL Server就可以自動(dòng)的計(jì)算AmountDue列的值.

　　由于computed column列的值是由表達(dá)式?jīng)Q定的，所以這種列是只讀的，并且不能在INSERT 或 UPDATE statements里對(duì)其賦值.然而，對(duì)使用ad-hoc SQL statements的TableAdapter來說，如果主查詢里引用了computed column列，那么自動(dòng)生成的INSERT 和 UPDATE statements也會(huì)自動(dòng)的引用computed column列.所以，我們必須更新TableAdapter的 INSERT 和 UPDATE 查詢，以及InsertCommand 和 UpdateCommand屬性，以刪除對(duì)任何computed column列的引用.

　　如果在使用 ad-hoc SQL statements的TableAdapter里使用computed columns的話，我們要面臨的挑戰(zhàn)之一便是，每當(dāng)完成TableAdapte設(shè)置向?qū)r(shí)，TableAdapter的 INSERT 和 UPDATE查詢都會(huì)自動(dòng)的生成，又再一次的自動(dòng)引用computed column列.不過如果TableAdapters使用存儲(chǔ)過程的話，就不會(huì)出現(xiàn)這個(gè)問題.

　　在本文，我們將向Northwind數(shù)據(jù)庫的Suppliers表添加一個(gè)computed column列，然后相應(yīng)地創(chuàng)建一個(gè)TableAdapter來處理該表以及該computed column列.我們將在TableAdapter里使用存儲(chǔ)過程而不是ad-hoc SQL statements.

第一步：向Suppliers表添加一個(gè)Computed Column

　　在本文，我們將向Suppliers表添加一個(gè)名為FullContactName的computed column列，它以“ContactName (ContactTitle, CompanyName)”的格式返回contact的name, title，以及所在的公司.

　　打開服務(wù)器資源管理器，在Suppliers表上單擊右鍵，選“Open Table Definition”，這將會(huì)顯示出表所包含的列以及列的屬性，比如數(shù)據(jù)類型、是否允許為NULL值等等.要添加一個(gè)computed column列，只需在表定義里鍵入表的名稱，接下來在Column屬性窗口的Computed Column Specification部分的(Formula)文本框里輸入表達(dá)式（如圖1所示）。將該computed column列命名為FullContactName，并使用下面的表達(dá)式：

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

　　請(qǐng)注意，在SQL里可以用操作符“+” 來連接字符串。而CASE聲明類似于傳統(tǒng)編程語言里的條件語句。上面代碼里的CASE 聲明可以這樣來理解：如果ContactTitle 不為NULL，那么輸出ContactTitle值，再緊接一個(gè)逗號(hào)；如果為NULL，則無操作。關(guān)于CASE 聲明的更多信息請(qǐng)參閱文章《The Power of SQL CASE Statements》（http://www.4guysfromrolla.com/webtech/102704-1.shtml）

　　注意：除了CASE聲明外，我們還可以使用ISNULL(ContactTitle, '')。語法ISNULL(checkExpression, replacementValue) returns是這樣工作的，如果checkExpression 不為NULL,則對(duì)其進(jìn)行返回；如果為NULL則返回replacementValue.雖然本文這2種語法都可以使用，但是在一些稍微復(fù)雜點(diǎn)的情況下，使用ISNULL的情況要多一些.添加完computed column列后，你的屏幕看起來應(yīng)該和圖1差不多：

圖1：向Suppliers表添加一個(gè)名為FullContactName的Computed Column列

　　添加完后點(diǎn)工具欄上的Save圖標(biāo)，或按Ctrl+S鍵,又或者在File菜單里選“保存Suppliers”.“保存”操作會(huì)自動(dòng)地刷新服務(wù)器資源管理器，將剛剛添加的的列展現(xiàn)在Suppliers表里.此外，鍵入到(Formula)文本框的表達(dá)式會(huì)自動(dòng)的進(jìn)行調(diào)整，剔除不必要的空白，將列名用[]括起來，并使用圓括號(hào)()來顯示操作的先后順序：

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

　　關(guān)于Microsoft SQL Server里computed columns列的更多信息請(qǐng)參考文章《technical documentation》（http://msdn2.microsoft.com/en-us/library/ms191250.aspx）；同時(shí)你也可以參考文章《How to: Specify Computed Columns》（http://msdn2.microsoft.com/en-us/library/ms188300.aspx），看如何一步步地創(chuàng)建computed columns列.

　　注意：默認(rèn)情況下，數(shù)據(jù)庫表并沒有“實(shí)際”(physically)的包含computed columns列，而是每次在一個(gè)查詢里引用它時(shí)重新計(jì)算其值.不過，我們可以選擇“Is Persisted”選項(xiàng)來讓SQL Server實(shí)實(shí)在在的在數(shù)據(jù)庫表里創(chuàng)建computed columns列.這樣的話我們可以為computed column列創(chuàng)建一個(gè)索引，當(dāng)在一個(gè)查詢的WHERE字句里使用computed column列的值時(shí)就可以提高執(zhí)行效率.更多的信息請(qǐng)參閱文章《Creating Indexes on Computed Columns》(http://msdn2.microsoft.com/en-us/library/ms189292.aspx)

第二步：查看Computed Column列的值

　　在處理數(shù)據(jù)訪問層前，讓我們花點(diǎn)時(shí)間查看FullContactName列的值.在服務(wù)器資源管理器里，在Suppliers表上右鍵單擊，選擇“New Query”，這將啟動(dòng)一個(gè)查詢窗口提示我們?cè)诓樵兝锇膫€(gè)表.添加Suppliers表，再點(diǎn)“Close”.接下來從Suppliers表里選擇CompanyName, ContactName, ContactTitle,以及FullContactName列.最后，點(diǎn)擊工具欄上的紅色感嘆號(hào)圖標(biāo)執(zhí)行查詢，查看結(jié)果.如圖2所示，結(jié)果里包含了FullContactName列，它以ContactName (ContactTitle, CompanyName)”的格式使用了CompanyName, ContactName,ContactTitle這3列.

圖2：FullContactName列的格式為“ContactName (ContactTitle, CompanyName)”

第三步：在數(shù)據(jù)訪問層添加一個(gè)SuppliersTableAdapter

　　為了在我們的應(yīng)用程序里處理supplier信息，我們首先需要在DAL層創(chuàng)建一個(gè)TableAdapter 和 DataTable.我們可以用前面的教程探討的方法來進(jìn)行創(chuàng)建，稍微不同的是我們將要與computed columns列打交道.

　　如果你用ad-hoc SQL statements來構(gòu)造一個(gè)TableAdapter的話，你可以很簡(jiǎn)單的通過TableAdapter設(shè)置向?qū)г赥ableAdapter的主查詢里引用computed column列，這樣，在自動(dòng)生成的INSERT 和 UPDATE statements就會(huì)引用computed column列。如果你執(zhí)行這2個(gè)方法的話，將會(huì)拋出這樣的一個(gè)SqlException：“The column ‘ColumnName' cannot be modified because it is either a computed column or is the result of a UNION operator”.雖然我們可以在InsertCommand和UpdateCommand屬性里手工改動(dòng)INSERT 和 UPDATE statement，但是一旦重新運(yùn)行TableAdapter設(shè)置向?qū)Ш?，我們所做的用戶定制就?huì)丟失掉.

　　由于使用ad-hoc SQL statements的TableAdapters的這種不穩(wěn)定性，我們傾向于使用存儲(chǔ)過程來處理computed columns列.如果你使用的是現(xiàn)有的存儲(chǔ)過程的話，你可以參閱第66章《在TableAdapters中使用現(xiàn)有的存儲(chǔ)過程》那樣來配置TableAdapter.如果你使用TableAdapter設(shè)置向?qū)韯?chuàng)建存儲(chǔ)過程的話，很重要的一點(diǎn)是最開始，你不要在主查詢里引用computed columns列，如果你在主查詢里引用了computed columns列的話，你剛完成設(shè)置，向?qū)Ь蜁?huì)提示你不能創(chuàng)建相應(yīng)的存儲(chǔ)過程.簡(jiǎn)而言之，在設(shè)置TableAdapter時(shí)，最開始不要在主查詢里引用computed column列，接下來再對(duì)相應(yīng)的存儲(chǔ)過程和TableAdapter的SelectCommand屬性進(jìn)行更改以引用computed column列.這種方法我們?cè)诘?7章《在TableAdapters中使用JOINs》里探討過.

　　本文我們將新添加一個(gè)TableAdapter并自動(dòng)創(chuàng)建存儲(chǔ)過程.當(dāng)然我們要在主查詢里忽略這個(gè)名為FullContactName的computed column列.打開~/App_Code/DAL文件夾里的NorthwindWithSprocs DataSet數(shù)據(jù)集，在設(shè)計(jì)器里右鍵單擊，選“add a new TableAdapter”，這將開啟TableAdapter設(shè)置向?qū)?，指定?shù)據(jù)庫連接信息(也就Web.config文件里的NORTHWNDConnectionString)，點(diǎn)Next。選“Create new stored procedures”項(xiàng)，再點(diǎn)Next.

圖3：選擇“Create new stored procedures”項(xiàng)

　　接下來我們要指定主查詢，鍵入如下的查詢，其返回每個(gè)supplier的SupplierID, CompanyName, ContactName, ContactTitle列。注意，我們有意忽略了computed column列(即FullContactName列)。不過我們將在第四步更新該存儲(chǔ)過程以引用該列：

SELECT SupplierID, CompanyName, ContactName, ContactTitle
FROM Suppliers

　　輸入完主查詢后點(diǎn)Next，向?qū)б覀優(yōu)閷⒁獎(jiǎng)?chuàng)建的4個(gè)存儲(chǔ)過程命名，分別命名為Suppliers_Select, Suppliers_Insert, Suppliers_Update,以及 Suppliers_Delete。如圖4所示：

圖4：對(duì)自動(dòng)生成的存儲(chǔ)過程命名

　　接下來要我們?yōu)門ableAdapter的方法命名并指定用于訪問和更新數(shù)據(jù)的模式.我們?nèi)窟x中這3項(xiàng)，不過將GetData方法重命名為GetSuppliers.點(diǎn)擊Finish完成配置.

圖5：將GetData方法重命名為GetSuppliers

　　完成后向?qū)?chuàng)建這4個(gè)存儲(chǔ)過程，并向類型化的DataSet添加ableAdapter以及對(duì)應(yīng)的DataTable.

第四步：在TableAdapter的主查詢里引用Computed Column列

接下來我們將對(duì)第三步創(chuàng)建的TableAdapter 和 DataTable進(jìn)行更新以引用FullContactName列，這要經(jīng)過2個(gè)步驟：

1.更新名為Suppliers_Select的存儲(chǔ)過程以返回FullContactName列

2.更新DataTable以包含相應(yīng)的FullContactName列

　　首先在服務(wù)器資源管理器里打開存儲(chǔ)過程文件夾，打開Suppliers_Select存儲(chǔ)過程，更新其SELECT查詢以引用FullContactName列：

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

　　保存所做的修改.接下來返回到DataSet Designer，在SuppliersTableAdapter上右鍵單擊，選“Configure”.我們可以注意到Suppliers_Select里的Data Columns集里已經(jīng)包含了FullContactName列.

圖6：返回到TableAdapter的設(shè)置向?qū)Ц翫ataTable的列

　　點(diǎn)擊Finish完成設(shè)置，這將自動(dòng)地為SuppliersDataTable添加相應(yīng)的列.TableAdapter發(fā)覺FullContactName列是一個(gè)computed column列，且是只讀的.因此將設(shè)置該列的ReadOnly屬性為true.我們可以進(jìn)行驗(yàn)證：在SuppliersDataTable里選擇該列，打開其屬性窗口(如圖7)，我們注意到FullContactName列的DataType 和 MaxLength屬性都作了相應(yīng)的設(shè)置.

圖7：FullContactName列標(biāo)記為Read-Only

第五步：向TableAdapter添加一個(gè)GetSupplierBySupplierID方法

　　在本文我們將在一個(gè)具有更新功能的ASP.NET頁面里展示suppliers信息.在前面的文章里，我們從DAL獲取指定的記錄并將其作為一個(gè)強(qiáng)類型的DataTable返回給BLL以做更新，然后將更新后的DataTable再傳遞給DAL，對(duì)數(shù)據(jù)庫做相應(yīng)的改動(dòng).為此，第一步——從DAL返回要更新的記錄——我們需要向DAL層添加一個(gè)名為GetSupplierBySupplierID(supplierID)的方法.

　　在DataSet Design設(shè)計(jì)器里右鍵單擊SuppliersTableAdapter，選“Add Query” ，再選“Create new stored procedure”(可參考3圖)。再選“SELECT which returns rows”再點(diǎn)Next.

圖8：選“SELECT which returns rows”項(xiàng)

　　接下來為該方法指定查詢，鍵入如下的代碼，它將檢索某個(gè)具體的supplier返回的列與主查詢一樣.

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

　　接下來我們將該存儲(chǔ)過程命名為Suppliers_SelectBySupplierID，點(diǎn)Next.

圖9：將存儲(chǔ)過程命名為Suppliers_SelectBySupplierID

　　在接下來的界面，全部選中圖里的2項(xiàng)，并將FillBy 和 GetDataBy方法分別命名為FillBySupplierID 和 GetSupplierBySupplierID.

圖10：將TableAdapter的方法命名為FillBySupplierID 和 GetSupplierBySupplierID

　　點(diǎn)Finish完成向?qū)?/p>

第六步：創(chuàng)建業(yè)務(wù)邏輯層Business Logic Layer

　　在創(chuàng)建ASP.NET頁面前，我們首先要在BLL添加相應(yīng)的方法.我們將在第7步創(chuàng)建頁面，其允許我們查看并編輯suppliers.因此我們?cè)贐LL至少要包含2個(gè)方法，一個(gè)獲取所有的suppliers，一個(gè)用于更新某個(gè)具體的supplier.

在~/App_Code/BLL文件夾里創(chuàng)建一個(gè)名為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有一個(gè)protected Adapter屬性，2個(gè)public方法：GetSuppliers 和 UpdateSupplier.其中，GetSuppliers方法調(diào)用Data Access Layer層對(duì)應(yīng)的GetSupplier方法，該方法將SuppliersDataTable返回給BLL層；而UpdateSupplier方法通過調(diào)用DAL層的GetSupplierBySupplierID(supplierID)方法來獲取某人具體supplier的信息，然后更新其CategoryName, ContactName,ContactTitle屬性，再將修改后的SuppliersRow對(duì)象傳遞給Data Access Layer層的 Update方法，以對(duì)數(shù)據(jù)庫做相應(yīng)的更新.

　　注意：除了SupplierID 和 CompanyName外，Suppliers表的所有列都允許為NULL值，所以如果傳遞的contactName 或 contactTitle參數(shù)為null的話，我們將分別調(diào)用SetContactNameNull 和 SetContactTitleNull方法來將ContactName 和 ContactTitle 屬性設(shè)置為NULL.

第七步： 在表現(xiàn)層處理Computed Column列

　　做完了所有的必要工作后， 我們將創(chuàng)建一個(gè)ASP.NET頁面來處理FullContactName列， 打開AdvancedDAL文件夾里的ComputedColumns.aspx 頁面，拖一個(gè)GridView控件到頁面，設(shè)其ID為Suppliers，在其智能標(biāo)簽里綁定到一個(gè)名為SuppliersDataSource的ObjectDataSource控件，設(shè)置其調(diào)用SuppliersBLLWithSprocs類，點(diǎn)Next.

圖11：設(shè)置ObjectDataSource調(diào)用SuppliersBLLWithSprocs Class類

　　在SuppliersBLLWithSprocs類里只有2個(gè)方法GetSuppliers 和 UpdateSupplier.確保在SELECT 和 UPDATE標(biāo)簽里分別選中這2個(gè)方法，點(diǎn)Finish完成設(shè)置.完成設(shè)置后，Visual Studio將添加相應(yīng)的BoundField，移除SupplierID列，并將CompanyName, ContactName, ContactTitle,和FullContactName列的HeaderText屬性分別設(shè)置為“Company”, “Contact Name”, “Title”,“Full Contact Name”,再啟用GridView的編輯功能.

　　Visual Studio將ObjectDataSource控件的OldValuesParameterFormatString屬性設(shè)置為“original_{0}”. 我們要將其改為默認(rèn)值“{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>

　　接下來我們?cè)跒g覽器里登錄該頁面，如圖12所示。每行都有一個(gè)FullContactName列，格式為“ContactName (ContactTitle, CompanyName)”.

圖12：每行展示一個(gè)Supplier

　　點(diǎn)擊某行的Edit按鈕將導(dǎo)致頁面回傳，且該行顯示為一個(gè)編輯界面(如圖13)，頭3行呈現(xiàn)為默認(rèn)的編輯界面——一個(gè)TextBox控件，且其Text屬性為該數(shù)據(jù)域(data field)的值.不過FullContactName列仍然呈現(xiàn)為一個(gè)文本框.在Data Source設(shè)置向?qū)瓿刹⑾騁ridView控件添加完相應(yīng)的BoundFields時(shí)，F(xiàn)ullContactName BoundField的ReadOnly屬性為true。我們?cè)诘谒牟阶⒁獾剑現(xiàn)ullContactName列的ReadOnly屬性為true，因?yàn)門ableAdapter意識(shí)到該列為一個(gè)computed column列.

圖13：FullContactName列為只讀

　　我們改動(dòng)這3個(gè)列中至少一個(gè)列的值，點(diǎn)Update按鈕.我們發(fā)現(xiàn)FullContactName列的值跟著發(fā)生改變.

　　注意：由于GridView當(dāng)前用的是BoundFields，導(dǎo)致編輯時(shí)用的是默認(rèn)的界面.又由于CompanyName列是必需的，我們應(yīng)將其轉(zhuǎn)化成一個(gè)TemplateField以包含一個(gè)RequiredFieldValidator控件.我將此作為一個(gè)練習(xí)留給讀者，你可以參考第19章《給編輯和新增界面增加驗(yàn)證控件》，看如何一步步的將BoundField轉(zhuǎn)換成 TemplateField，再添加一個(gè)確認(rèn)控件.

結(jié)語：

　　當(dāng)創(chuàng)建一個(gè)表時(shí)，Microsoft SQL Server允許我們創(chuàng)建一個(gè)computed columns列.這些computed columns列引用該條記錄的其它列，再通過一個(gè)表達(dá)式對(duì)其賦值.由于其值來源于一個(gè)表達(dá)式，因此這種列是只讀的，且不能通過INSERT 或 UPDATE statement對(duì)其賦值.正是如此，當(dāng)在一個(gè)TableAdapter的主查詢里引用computed column的話，要想自動(dòng)的生成對(duì)應(yīng)的INSERT, UPDATE,和DELETE statements有點(diǎn)麻煩.

　　在本文，我們探討了使用computed columns列面臨的挑戰(zhàn).具體來說，由于使用ad-hoc SQL statements的TableAdapters自身固有的不穩(wěn)定性，我們探討了使用存儲(chǔ)過程的情況.當(dāng)使用TableAdapter向?qū)?chuàng)建一個(gè)新的存儲(chǔ)過程的時(shí)候，很重要的一點(diǎn)是，最開始不要在主查詢里引用任何的computed columns，不然就不能自動(dòng)的生成對(duì)應(yīng)的存儲(chǔ)過程.完成向?qū)Ш?，我們要手?dòng)修改SelectCommand屬性以引用computed columns列.

　　祝編程快樂！

作者簡(jiǎn)介

　　本系列教程作者 Scott Mitchell，著有六本ASP/ASP.NET方面的書，是4GuysFromRolla.com的創(chuàng)始人，自1998年以來一直應(yīng)用 微軟Web技術(shù)。大家可以點(diǎn)擊查看全部教程《[翻譯]Scott Mitchell 的ASP.NET 2.0數(shù)據(jù)教程》，希望對(duì)大家的學(xué)習(xí)ASP.NET有所幫助。