本文英文原版及代碼下載：
http://www.asp.net/learn/data-access/tutorial-71-cs.aspx

Scott Mitchell 的ASP.NET 2.0數(shù)據(jù)教程之71:處理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里對其賦值.然而，對使用ad-hoc SQL statements的TableAdapter來說，如果主查詢里引用了computed column列，那么自動(dòng)生成的INSERT 和 UPDATE statements也會(huì)自動(dòng)的引用computed column列.所以，我們必須更新TableAdapter的 INSERT 和 UPDATE 查詢，以及InsertCommand 和 UpdateCommand屬性，以刪除對任何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使用存儲過程的話，就不會(huì)出現(xiàn)這個(gè)問題.

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

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

注意：除了CASE聲明外，我們還可以使用ISNULL(ContactTitle, '')。語法ISNULL(checkExpression, replacementValue) returns是這樣工作的，如果checkExpression 不為NULL,則對其進(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)整，剔除不必要的空白，將列名用[]括起來，并使用圓括號()來顯示操作的先后順序：

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

關(guān)于Microsoft SQL Server里computed columns列的更多信息請參考文章《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í)行效率.更多的信息請參閱文章《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è)查詢窗口提示我們在查詢里包含哪個(gè)表.添加Suppliers表，再點(diǎn)“Close”.接下來從Suppliers表里選擇CompanyName, ContactName, ContactTitle,以及FullContactName列.最后，點(diǎn)擊工具欄上的紅色感嘆號圖標(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的話，你可以很簡單的通過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)定性，我們傾向于使用存儲過程來處理computed columns列.如果你使用的是現(xiàn)有的存儲過程的話，你可以參閱第68章《Using Existing Stored Procedures for the Typed DataSet’s TableAdapters》那樣來配置TableAdapter.如果你使用TableAdapter設(shè)置向?qū)韯?chuàng)建存儲過程的話，很重要的一點(diǎn)是最開始，你不要在主查詢里引用computed columns列，如果你在主查詢里引用了computed columns列的話，你剛完成設(shè)置，向?qū)Ь蜁?huì)提示你不能創(chuàng)建相應(yīng)的存儲過程.簡而言之，在設(shè)置TableAdapter時(shí)，最開始不要在主查詢里引用computed column列，接下來再對相應(yīng)的存儲過程和TableAdapter的SelectCommand屬性進(jìn)行更改以引用computed column列.這種方法我們在第69章《Updating the TableAdapter to Use JOINs》里探討過.

本文我們將新添加一個(gè)TableAdapter并自動(dòng)創(chuàng)建存儲過程.當(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列)。不過我們將在第四步更新該存儲過程以引用該列：

SELECT SupplierID, CompanyName, ContactName, ContactTitle
FROM Suppliers

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

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

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

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

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

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

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

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

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

首先在服務(wù)器資源管理器里打開存儲過程文件夾，打開Suppliers_Select存儲過程，更新其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，對數(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

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

圖9：將存儲過程命名為Suppliers_SelectBySupplierID

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

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

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

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

在創(chuàng)建ASP.NET頁面前，我們首先要在BLL添加相應(yīng)的方法.我們將在第7步創(chuàng)建頁面，其允許我們查看并編輯suppliers.因此我們在BLL至少要包含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層對應(yīng)的GetSupplier方法，該方法將SuppliersDataTable返回給BLL層；而UpdateSupplier方法通過調(diào)用DAL層的GetSupplierBySupplierID(supplierID)方法來獲取某人具體supplier的信息，然后更新其CategoryName, ContactName,ContactTitle屬性，再將修改后的SuppliersRow對象傳遞給Data Access Layer層的 Update方法，以對數(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>

接下來我們在瀏覽器里登錄該頁面，如圖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。我們在第四步注意到，F(xiàn)ullContactName列的ReadOnly屬性為true，因?yàn)門ableAdapter意識到該列為一個(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章《Adding Validation Controls to the Editing and Inserting Interfaces》，看如何一步步的將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á)式對其賦值.由于其值來源于一個(gè)表達(dá)式，因此這種列是只讀的，且不能通過INSERT 或 UPDATE statement對其賦值.正是如此，當(dāng)在一個(gè)TableAdapter的主查詢里引用computed column的話，要想自動(dòng)的生成對應(yīng)的INSERT, UPDATE,和DELETE statements有點(diǎn)麻煩.

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

祝編程快樂！

作者簡介

Scott Mitchell，著有六本ASP/ASP.NET方面的書，是4GuysFromRolla.com的創(chuàng)始人，自1998年以來一直應(yīng)用 微軟Web技術(shù)。Scott是個(gè)獨(dú)立的技術(shù)咨詢顧問，培訓(xùn)師，作家，最近完成了將由Sams出版社出版的新作，24小時(shí)內(nèi)精通ASP.NET 2.0。他的聯(lián)系電郵為 mitchell@4guysfromrolla.com ，也可以通過他的博客 http://ScottOnWriting.NET 與他聯(lián)系。

Scott Mitchell 的ASP.NET 2.0數(shù)據(jù)教程之71:處理Computed Columns列