導言：
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，并使用下面的表達式：
|
1
2
|
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)文本框的表達式會自動的進行調整，剔除不必要的空白，將列名用[]括起來，并使用圓括號()來顯示操作的先后順序：
|
1
2
|
(((([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列)。不過我們將在第四步更新該存儲過程以引用該列：
|
1
2
|
SELECT SupplierID, CompanyName, ContactName, ContactTitleFROM 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列：
|
1
2
|
SELECT SupplierID, CompanyName, ContactName, ContactTitle, FullContactNameFROM 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返回的列與主查詢一樣.
|
1
2
3
|
SELECT SupplierID, CompanyName, ContactName, ContactTitle, FullContactNameFROM SuppliersWHERE 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的新類，添加代碼如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
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控件的聲明代碼看起來和下面的差不多：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
<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技術。希望對大家的學習ASP.NET有所幫助。
