基於業務對象(列表)的篩選

文章目錄

• 拼裝SQL語句的典型實現方式
• 緩衝策略
• 對業務對象進行篩選
• 頁面實現

代碼下載：http://www.tracefact.net/sourcecode/filterSorting.rar

基於業務對象的篩選引言

可能大家對SQL語句太過熟悉了，也可能雖然已經從Asp過度到了Asp.Net時代，但是Asp的觀念沒有發生太大變化。結果就是我們將應用程式大部分的邏輯都交給了資料庫去完成，忘記了.Net Framework提供給我們靈活強大的資料操控能力。比如說，當我們需要對資料進行篩選的時候，我們想到的是“Where”子句，而不是List<T>.FindAll()；當我們需要對資料進行排序的時候，我們想到的是“Order By”子句，而不是List<T>.Sort()；當我們需要對資料進行分頁的時候我們想到的是預存程序，而不是List<T>.GetRange()。

當然，讓資料庫去完成這些工作在很多情況下效率會很高，尤其是在資料量較大的時候。然而在資料量不大的情況下，一次將所有資料取出，然後緩衝在伺服器上，對於以後的排序、篩選、分頁請求僅針對緩衝進行，則會使效率提高很多。

沒有哪種方法絕對好或者絕對不好，都有一個適用範圍。本文將介紹的也是一樣，當資料量非常大的時候，我們可能不僅希望資料庫先用“Where”子句進行篩選，進而再一次篩選只返回當前頁需要顯示的資料條目。

本文僅僅提出同一個問題的另一種解決思路，何時使用根據情況而定。

你是否還在拼裝SQL語句？篩選資料的傳統方式

對資料進行篩選應該是最常見不過的操作了，我們以NorthWind資料庫的Orders訂單表為例說明。如果我們需要對它按年、月、日的任意組合進行篩選，你大概會怎麼做呢？我想應該是這樣的：

1. 在頁面上建立三個下拉框，用於對年、月、日的選擇。
2. 使用者第一次訪問頁面，顯示所有資料。此時有一次對資料庫的訪問，返回全部資料，SQL語句諸如“Select * From Orders”。
3. 使用者選擇年、月、日中的任意一個，產生PostBack。
4. 根據使用者的選擇拼裝一個SQL語句，諸如“Where Year(OrderDate) = @Date and Month(OrderDate)= @Month”。
5. 將SQL語句發送到資料庫，資料庫返回查詢結果，顯示到使用者介面。
6. 如此反覆。

可以看到，在上面的模式中，為了根據使用者的選擇顯示不同的資料，每次使用者的操作都要進行對資料庫的一次訪問，我們看一下具體的實現是怎樣的。

拼裝SQL語句的典型實現方式

首先，建立一個頁面(SqlApproach.aspx)，在頁面上放置三個DropDownList控制項，一個GridView控制項，以及一個ObjectDataSource控制項，如同所示：

在 App_Code 檔案夾中根據Orders表建立業務對象Order(位於Order.cs)。

public class Order
{
    private int orderId;            // 訂單Id
    private string customerId;          // 使用者Id
    private DateTime orderDate;         // 下單日期
    private string country;             // 國家

    public int OrderId {
       get { return orderId; }
       set { orderId = value; }
    }

    public string CustomerId
    {
       get { return customerId; }
       set { customerId = value; }
    }

    public DateTime OrderDate
    {
       get { return orderDate; }
       set { orderDate = value; }
    }

    public string Country
    {
       get { return country; }
       set { country = value; }
    }
}

對於集合(列表、或者叫行集)資料，我們使用List<Order>來儲存。接下來在App_Code目錄下再建立一個檔案 OrderManager.cs 用於從資料庫返回結果集、填充列表，通常包含類似這樣的代碼：

public class OrderManager
{
    // 根據SQL語句擷取列表對象
    public static List<Order> GetList(string query)
    {
       List<Order> list = null;
       SqlDataReader reader = ExecuteReader(query);

       if (reader.HasRows)   {
           list = new List<Order>();
           while (reader.Read()){
              list.Add(GetItem(reader));
           }
       }

       reader.Close();

       return list;
    }

    // 擷取一個SqlDataReader對象
    private static SqlDataReader ExecuteReader(string query)
    {
       string connString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
       SqlConnection conn = new SqlConnection(connString);
       SqlCommand cmd = new SqlCommand(query, conn);
       conn.Open();
       SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

       return reader;
    }

    // 從一行擷取一個Order對象
    private static Order GetItem(IDataRecord record)
    {
       Order item = new Order();
       item.OrderId = Convert.ToInt32(record["orderId"]);
       item.CustomerId = record["CustomerId"].ToString();
       item.OrderDate = Convert.ToDateTime(record["OrderDate"]);
       item.Country = record["ShipCountry"].ToString();
       return item;
    }
}

上面的代碼很好理解：GetList()方法接受一個查詢語句，然後返回List<Order>列表。在GetList()方法內部，調用了 ExecuteReader()方法，ExecuteReader()方法根據傳遞進去的查詢語句query建立並返回了一個SqlDataReader對象，它用於讀取資料庫返回的資料。在While語句中，調用了GetItem()方法，它根據每一個資料行建立了一個Order對象。最後將Order對象添加到List<Order>列表中，然後返回了列表。

可見，我們需要在頁面上調用的方法就是GetList(query)方法了，我們看下分頁檔SqlFilter.aspx的主要代碼：

<asp:ObjectDataSource ID="objdsOrderList" runat="server" SelectMethod="GetList"
     TypeName="OrderManager" OnSelecting="objdsOrderList_Selecting">
     <SelectParameters>
       <asp:Parameter Name="query" Type="string" />
     </SelectParameters>
</asp:ObjectDataSource>

ObjectDataSource使用GetList作為SelectCommand， ObjectDataSource的ID將會用於GridView的DataSourceID。

現在我們繼續看SqlFilter.aspx的後置代碼通常是什麼樣的(我們設定當DropDownList的Text為“全部”的時候，它的Value為“0”)：

public partial class SqlApproach : System.Web.UI.Page
{
    public int Year{
       get { return Convert.ToInt32(ddlYear.SelectedValue); }
    }

    public int Month{
       get { return Convert.ToInt32(ddlMonth.SelectedValue); }
    }

    public int Day{
       get { return Convert.ToInt32(ddlDay.SelectedValue); }
    }
   
    // 擷取查詢語句
    public string QuerySql
    {
       get
       {
           int year = Year;
           int month = Month;
           int day = Day;

           List<string> sqlList = new List<string>();
           string subSql = string.Empty;
          
           if(year != 0)
              sqlList.Add(String.Format("Year(OrderDate) = {0}", year));

           if(month != 0)
              sqlList.Add(String.Format("Month(OrderDate) = {0}", month));

           if (day != 0)
              sqlList.Add(String.Format("Day(OrderDate) = {0}", day));

           if (sqlList.Count > 0)   // 如果選擇了任意一個下拉框，那麼就拼裝Sql語句
           {
              string[] list = sqlList.ToArray();
              subSql = "Where (" + String.Join(" and ", list) + ")";
           }
           // 返回拼裝好的SQL語句
           return "Select CustomerId, ShipCountry, OrderDate, OrderId From Orders " + subSql;          
       }
    }

    // 頁面載入的事件
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            AppendListItem(ddlMonth, 12);   // 共12個月
            AppendListItem(ddlDay, 31);     // 預設為31天       
        }
    }
   
    // 月份改變
    protected void ddlMonth_SelectedIndexChanged(object sender, EventArgs e)    {
       gvOrderList.DataBind();
    }

    // 年份改變
    protected void ddlYear_SelectedIndexChanged(object sender, EventArgs e)    {
       gvOrderList.DataBind();
    }

    // 天改變
    protected void ddlDay_SelectedIndexChanged(object sender, EventArgs e)    {
       gvOrderList.DataBind();
    }

    // 為列表添加項目
    protected void AppendListItem(ListControl list, int end) {
       for (int i = 1; i <= end; i++) {
           list.Items.Add(new ListItem(i.ToString()));
       }
    }

    // 每個列表的PostBack都會調用gvOrderList.DataBind()，然後觸發這裡
// 進而調用了OrderManager.GetList(query)方法，從資料庫返回資料。
    protected void objdsOrderList_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)   {
       e.InputParameters["query"] = QuerySql;
    }
}

這段代碼使用Year, Month, Day三個屬性分別擷取年、月、日DropDownList的值。主要的邏輯包含在了QuerySql屬性中，它根據三個列表的狀態進行SQL語句的拼裝。最後，在ObjectDataSource的Selecting事件中，傳遞QuerySql方法進去，獲得列表對象，進而顯示在頁面上。

NOTE：為了使代碼簡單，我沒有對類似1997-2-30這樣特殊日期的處理。即便使用這個日期作為查詢條件，僅僅會返回一個空列表，不會使程式出錯，由於這隻是個樣本程式，我覺得還可以接受。

基於業務對象的篩選

瞭解了傳統的基於拼裝SQL語句的篩選，現在我們看看基於對象的篩選是怎麼樣的，又是如何來提升效能的。

1. 在頁面上建立三個下拉框，用於對年、月、日的選擇。
2. 使用者第一次訪問頁面，顯示所有資料。此時有一次對資料庫的訪問，返回全部資料，SQL語句諸如“Select * From Orders”。
3. 將返回的資料(已經轉換成了List<Order>業務對象)，全部進行緩衝。
4. 根據使用者的選擇對緩衝中的 List<Order> 進行篩選，返回篩選結果，顯示在頁面上。
5. 如此反覆，以後每次使用者請求都只針對緩衝進行。

緩衝策略

因為這裡我們使用了緩衝，所以有必要討論一下緩衝。我們知道緩衝共有三種，一種是OutputCache，一種是基於資料來源控制項的資料緩衝，一種是基於 System.Web.Caching.Cache 類的對象緩衝。在這三種緩衝中，OutputCache和 資料緩衝 又可以應用SqlCacheDependency緩衝到期策略，SqlCacheDependency說簡單些就是當資料庫的資料發生改變的時候使依賴於此資料庫(表)的緩衝自動到期，SqlCacheDependency又分為Sql Server2000的基於輪詢(Polling)的拉機制，和Sql Server2005 基於通知(Notification)的推機制兩種不同策略。而應用System.Web.Caching.Cache時不能應用SqlCacheDependency到期策略，只能應用基於某個檔案改變或其他Cache項目改變的到期策略。

NOTE：Sql Server2000 的輪詢(Polling)機制意思是說Asp.Net進程每隔一段時間就對資料庫進程進行一此訪問，因為間隔時間是固定的，所以叫輪詢(訪問時間以毫秒為單位，可以在Web.Config裡設定)。當某次輪詢時發現與上一次訪問時的資料不一樣，那麼就立刻使依賴於此資料的緩衝到期。Sql Server2005 的通知(Notification)機制是說Asp.Net只管做自己的事情，不對資料庫進程進行詢問，而當資料庫的資料發生變動時，Sql Server 2005進程主動通知Asp.Net進程，告訴它資料發生了改變，然後Asp.Net讓緩衝到期。由此可見，使用Sql Server2005 的通知機制效率要高得多。
如何開啟SqlDependency本文不講述了，可以查閱相關書籍。

當我提到緩衝的時候你可能會覺得對於基於業務對象的篩選我使用了緩衝，而對於拼裝SQL的方式我沒有，這樣去比較它們顯得不公平，那麼我現在列張表，對於它們各自應用緩衝時的表現做一個對比(SqlDependency使用SqlServer 2000的輪詢機制)：

緩衝名稱	基於拼裝SQL篩選	基於業務對象篩選
OutputCache VaryByParam="*" Duration="600"	當下拉框的選項較少的時候比較有意義，在緩衝有效期間內，不會對資料庫進行訪問。但當選項較多時，會對較多頁進行緩衝,第一次訪問時仍要訪問資料庫，緩衝多個頁面結果,效果不夠好。資料庫資料改動時，緩衝不會到期。	沒有意義，因為業務對象已經是自緩衝讀出。資料庫資料改動時，緩衝不到期。
OutputCache VaryByParam="*" Duration="999999" SqlDependency="Northwind:Orders"	與上同，但是當資料變動時會使緩衝到期。	沒有意義，當資料庫資料改動時，會使頁面緩衝到期，頁面緩衝要求重新載入資料，但是重新載入的資料依然來自緩衝中的對象。結果是即使資料庫發生改變，頁面顯示結果依然沒有改變。
ObjectDataSource EnableCaching="true" CacheDuration="600"	在緩衝有效時間內，下拉式清單的功能失效。因為在Cache有效期間內，GridView的DataBind()方法不會使資料來源重新讀取資料(資料來源不會觸發Selecting事件)，換言之，資料來源不會調用GetList(query)方法，因此列表功能失效。	效果與拼裝Sql方法一樣，列表失效。
ObjectDataSource EnableCaching="true" CacheDuration=" infinite" SqlDependency="Northwind:Orders"	列表失效，與上面效果相同，區別僅僅是在資料庫改動時緩衝到期(在失效後的第一次訪問，列表有效)。	列表失效，與拼裝Sql方法一樣。區別是SqlDependency也失效，因為在資料庫資料變動時，資料緩衝到期，資料來源重新讀資料，但是資料依然來自於緩衝。
Catch Insert("fullList", List<Order>)	基本不可實施(對每次返回結果進行緩衝，效果基本等同於全部返回，且非常麻煩)	本文對象即是應用此方法緩衝。

很明顯，本文使用的方法的問題就是：當資料庫資料變動時，緩衝不能夠即時到期。解決方案有兩種：一個是使用Cache.Insert()的重載方法，設定緩衝的自動到期時間(時間設的短了緩衝優勢不明顯，時間設的長了資料變化不能即時反應)；還有一個是在對資料庫進行增刪改時使用Cache.Remove()手動移除緩衝(比較麻煩容易遺漏)。

本文不是講述如何使用緩衝的，上面是讓大家瞭解使用緩衝都會發生哪些可能，只要知道使用Cache.Insert(key,value)方法可以添加緩衝就可以了。最後再說一下當我們使用Cache.Insert(key,value)插入緩衝時，雖然沒有設定到期時間，但是當伺服器記憶體空間不足的時候，依然會將緩衝移除。

對業務對象進行篩選

基於業務對象篩選其實就是基於List<Order>進行篩選(當然你的業務對象也可能不是List<Order>)，思路似乎很簡單，我們先通過一個重載的GetList()方法擷取全部列表，在這個GetList()方法中應用緩衝。然後遍曆業務對象，選出它合格項目，然後將合格項目加入到新列表中，最後返回新列表。

// 擷取全部列表
public static List<Order> GetList() {
    List<Order> list = HttpContext.Current.Cache["fullList"] as List<Order>;

    if (list == null) {
       list = GetList("Select OrderId, CustomerId, ShipCountry, OrderDate From Orders");
       // 添加緩衝，永不到期(可以在刪除、更新操作時手動讓緩衝到期)
       HttpContext.Current.Cache.Insert("fullList", list);
    }

    return list;
}

// 根據一個全部項目的列表，以及年、月、日對列表進行篩選
public static List<Order> GetList(List<Order> fullList, int year, int month, int day)
{
    List<Order> list = null;
    bool canAdd;      // 標記變數，說明當前項目是否符合添加的條件

    if (fullList != null)
    {
       list = new List<Order>();

       foreach (Order item in fullList)
       {
           canAdd = true;

           if (year != 0 && year != item.Date.Year)
              canAdd = false;

           if (month != 0 && month != item.Date.Month)
              canAdd = false;

           if (day != 0 && day != item.Date.Day)
              canAdd = false;

           if (canAdd == true)      // 如果全部條件滿足，那麼加入列表
              list.Add(item);
       }
    }

    return list;
}

上面無參數的GetList()方法在沒有緩衝的情況下調用GetList(query)方法，返回全部列表，然後加入緩衝；有緩衝的情況下直接使用緩衝中的資料。在GetList(fullList, year, month, day)方法中，根據 年、月、日 對傳遞進去的列表(全部列表)進行了篩選。

使用List<T>.FindAll(Predicate<T> match)進行篩選

上面的方法雖然可以完成任務，但是不夠好，為什麼呢？

1. 我們將篩選的條件(年、月、日)緊耦合到了GetList()方法中，如果日後想添加對其他列，比如國家的篩選，那麼我們的方法簽名就需要改變(添加國家)，而所有調用GetList()方法的地方都需要修改。
2. 代碼沒有重用，針對年、月、日來進行篩選是一項很常見的任務，我們應該把這部分封裝起來，以後對其他的業務對象進行篩選時，使這些代碼可以重用。

實際上，這些問題.Net Framework已經為我們想到了，並在List<T>上提供了一個FindAll(Predicate<T> match)方法來進行篩選的工作，而Predicate<T>類型的參數，封裝了篩選的規則。Predicate<T>是一個泛型委派，這意味著match參數是一個返回bool類型的方法，在FindAll()內部，會調用我們傳遞進去的這個方法。

public delegate bool Predicate<T>(T obj);

NOTE：我看到過這樣的一句話，是問Librariy和Framework的區別是什嗎？回答是：我們調用Librariy的方法，但是Framework調用我們的方法(當然我們也會調用Framework)。可見Framework是一個擴充性和彈性很高的東西，在很多地方我們可以將自己的代碼融入到Framework中去。

現在我們來看下如何定義滿足 Predicate<T>委託的方法。如果我們將方法寫在OrderManager類的內部，那麼似乎可以這樣寫：

// 進行資料篩選的主要邏輯
public bool MatchRule(Order item)
{
    if (year != 0 && year != item.Date.Year)
       return false;

    if (month != 0 && month != item.Date.Month)
       return false;

    if (day != 0 && day != item.Date.Day)
       return false;

    return true;
}

可實際上，你發現沒有地方來傳遞year, month, day參數，因為Predicate<T>(T obj)要求只接受一個參數，在這裡是Order類型的item。所以，實際上我們要對這個方法進行一個簡單的封裝，讓我們可以傳遞year, month, day參數進去。在進行封裝之前，我們應該考慮：對於年、月、日的篩選是很常見的操作，我們要讓代碼重用。

我們先定義一個介面，這個介面僅要求返回一個DateTime類型的屬性Date，對於所有實現了這個介面的類，都應該可以使用我們的篩選方法(一個沒有日期的對象顯然不能按年、月、日篩選)。

public interface IDate
{
    DateTime Date { get; }
}

此時我們的Order類也應該進行修改，讓它來實現這個介面，我們只需要它返回orderDate欄位就可以了：

public class Order :IDate
{  
    // ... 略
    public DateTime Date
    {
       get { return orderDate; }
    }
}

接下來定義可以用於篩選的類，建立一個DateFilter.cs檔案：

// 用於按照年、月、日篩選列表的泛型類，基類
public class DateFilter<T> where T : IDate
{
    private int year;
    private int month;
    private int day;

    public DateFilter(int year, int month, int day)
    {
       this.year = year;
       this.month = month;
       this.day = day;
    }
    // 方便使用的一組建構函式
    public DateFilter(DateTime date) : this(date.Year, date.Month, date.Day) { }
    public DateFilter(int year, int month) : this(year, month, 0) { }
    public DateFilter(int year) : this(year, 0, 0) { }
    public DateFilter() : this(0, 0, 0) { }
   
    // 進行資料篩選的主要邏輯
    public virtual bool MatchRule(T item)
    {
       if (year != 0 && year != item.Date.Year)
           return false;

       if (month != 0 && month != item.Date.Month)
           return false;

       if (day != 0 && day != item.Date.Day)
           return false;

       return true;
    }
}

可以看到，Predicate<T>委託類型的方法MatchRule和前面幾乎沒有區別，唯一的不同是改成了虛擬方法，以便在子類中覆蓋它，以支援對更多列(屬性)的篩選。還有值得注意的地方是這個泛型類使用了約束，我們要求型別參數T必須實現IDate介面。

實際上這個類通常用作基類(也可以直接使用，非抽象類別)，現在來看下如果我們希望可以對Country也進行篩選，應該如何擴充它：

// 可以添加對國家的篩選
public class OrderFilter : DateFilter<Order>
{
    private string country;

    public OrderFilter(int year, int month, int day, string country)
       : base(year, month, day)     // 調用基類建構函式
    {
       this.country = country;
    }

    public override bool MatchRule(Order item)
    {
       // 先擷取基類關於日期的對比結果
       bool result = base.MatchRule(item);

       if (result == false)     // 如果日期都不滿足，直接返回false
           return false;

       // 繼續進行 country 的對比
       if (String.IsNullOrEmpty(country) || string.Compare(item.Country, country, true) == 0)
       {
           return true;     
       } else
       {
           return false;
       }
    }
}

頁面實現

我們現在為OrderManager類添加一個新方法，使用我們上面建立的OrderFilter，看看它是什麼樣的，它僅僅是在fullList上調用了FindAll()方法，傳遞了我們自訂的DateFilter，然後返回了結果：

// 擷取列表對象，使用 filter 作為篩選的條件
public static List<Order> GetList(List<Order> fullList, DateFilter<Order> filter)
{
    List<Order> list = null;
    if (fullList != null)
    {
       list = fullList.FindAll(new Predicate<Order>(filter.MatchRule));
    }
    return list;
}

在ObjFilter.aspx頁面上布局與使用拼裝SQL幾乎沒有區別，ObjectDataSource控制項的屬性有一些變化：

<asp:ObjectDataSource ID="objdsOrderList" runat="server" SelectMethod="GetList"
    TypeName="OrderManager" OnSelecting="objdsOrderList_Selecting">
    <SelectParameters>
       <asp:Parameter Name="fullList" Type="Object" />
       <asp:Parameter Name="filter" Type="Object" />
    </SelectParameters>
</asp:ObjectDataSource>

調用了新的重載了的GetList()方法。然後我們看一下CodeBehind檔案上如何進行設定ObjectDataSource的Selecting事件：

// 屬性，擷取用於篩選的對象
public DateFilter<Order> Filter {
    get {
       DateFilter<Order> filter = new OrderFilter(Year, Month, Day, Country);
       return filter;
    }
}

// 設定參數
protected void objdsOrderList_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
{
    e.InputParameters["fullList"] = OrderManager.GetList();// 擷取全部列表
    e.InputParameters["filter"] = Filter;
}

注意上面Year、Month、Day屬性的擷取代碼以及DropDownList的SelectedIndexChanged事件代碼我都省略了以節省篇幅。

事件探查器

OK，現在我們的所有工作都已經完成了，我們來測試一下通過這種方式對資料庫依賴的減小。大家可以開啟Sql Server2000的事件探查器(Sql Server2005下的Sql Server Profiler)。選擇“檔案” --> “建立” --> “跟蹤” --> 進行登入。之後應該如所示：

選擇“事件”選項卡，之後如所示：

從右側“選定的事件”中刪除“預存程序”、“安全審查”、“會話”，只保留“T-SQL”，我們只對它進行監視。然後可以看到類似，我們對資料庫的每次訪問都可以在這裡看到：

點擊上面的“橡皮擦”表徵圖，可以對列表進行清除。然後我們先開啟SqlFilter.aspx檔案，可以看到我們對列表的每次操作，不管是翻頁還是篩選，都會對資料庫進行一次查詢操作。然後我們點擊“橡皮擦”清除掉列表，然後開啟ObjFilter.aspx檔案，可以看到在對資料庫進行了第一次訪問以後，後繼的動作，無論是進行分頁還是篩選操作，都不再對資料庫構成依賴。

總結

在這篇文章中，我們主要討論了如何對業務對象進行篩選。我先提出了很多人的一個思維定勢：將操作交給資料庫。隨後列出了這種操作的典型流程，並在本文中將它稱為“基於拼裝SQL進行篩選”，然後給出了代碼示範。

後半部分，我們詳細討論了基於業務對象進行篩選的方法――將對象緩衝在伺服器上來對請求提供支援。與前半部分一樣，我們先瞭解了流程，學習了緩衝策略，然後進行了代碼實現。最後我們使用Sql Server提供的事件探查器對兩種情況下對資料庫請求的狀況進行了跟蹤。

感謝閱讀，希望這篇文章能給你帶來協助！