[ADO.NET] 動態 SQL 指令與 SqlDataSource 的 QueryStringParameter 配合查詢

我在「[SQL] 使用 Stored Procedure 動態組成 SQL 查詢指令 」一文中介紹了如何在 Stored Procedure 中動態的組合出 SQL 指令並進行查詢。然而, 如果我們要配合 SqlDataSource 中以進行查詢, 該怎麼做呢?

我們可以產生一個 SqlDataSource 如下, 其主要目的在於配合上述的 Stored Procedure 以進行動態的查詢, 各參數的來源是網址列的 Query String:

<asp:SqlDataSource ID="ds" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
    ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>"
    SelectCommand="QueryProduct" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:QueryStringParameter Name="ProductID" QueryStringField="ProductID" Type="Int16"  />
        <asp:QueryStringParameter Name="Time" QueryStringField="Time" Type="DateTime" />
        <asp:QueryStringParameter Name="Span" QueryStringField="Span" Type="Int16" />
        <asp:QueryStringParameter Name="Deleted" QueryStringField="Deleted" Type="Boolean" />
        <asp:QueryStringParameter Name="Status" QueryStringField="Status" Type="Byte" />
        <asp:QueryStringParameter Name="Title" QueryStringField="Title" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

很可惜的, 即使上述程式看起來很正確, 實際執行時, 偏偏是行不通的, 不是查不到任何東西, 就是發生錯誤。

如果仔細觀察與分析, 我們可以歸納出幾個現象或原因:

1. 假設網頁名稱是 QueryProduct.aspx, 我們如果不帶入任何參數, 則根本查詢不到什麼資料
2. 如果我們要帶入參數, 你可以發現, 你必須輸入所有參數(一個都不能省, 例如 QueryProduct.aspx?ProductID=1&Time=2008/12/12&Span=1&Deleted=False&Status=0&Title="ABC"), 才可能查到資料 - 這跟原來的目的背道而馳
3. 在預設狀況中, 如果你沒有在網址中提供 Query 項目, ASP.NET 會自動假設你並未提供該項參數; 它不會自動幫你填入 NULL。然而, 如果你要執行一個 Stored Procedure, 你根本不能省掉任何一個參數。所以其結果就是該 Stored Procedure 根本不會被呼叫, 所以自然查詢不到任何資料

幸好, SqlDataSource 提供了 CancelSelectOnNullParameter 這個屬性。把它設定為 false, 它就不會因為你沒有輸入哪個參數而把你擋下來了。我把上面的寫法改為如下:

<asp:SqlDataSource ID="ds" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
    ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>" CancelSelectOnNullParameter="false"
    SelectCommand="QueryProduct" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:QueryStringParameter Name="ProductID" QueryStringField="ProductID" Type="Int16"  />
        <asp:QueryStringParameter Name="Time" QueryStringField="Time" Type="DateTime" />
        <asp:QueryStringParameter Name="Span" QueryStringField="Span" Type="Int16" />
        <asp:QueryStringParameter Name="Deleted" QueryStringField="Deleted" Type="Boolean" />
        <asp:QueryStringParameter Name="Status" QueryStringField="Status" Type="Byte" />
        <asp:QueryStringParameter Name="Title" QueryStringField="Title" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

這樣就行了。經由如此設定之後，再配合「[SQL] 使用 Stored Procedure 動態組成 SQL 查詢指令 」一文中所使用的 Stored Procedure，我們就可以方便的使用如 ABC.aspx?ProductID=1&Time=2008/12/31 之類的方法進行資料庫內容的查詢。其中所有或部份的參數可以給，也可以不給。例如，如果你只輸入 ABC.aspx (不提供任何參數)，那麼查詢的結果就是不設定任何查詢條件，也就是整個資料表全部列出。如果你輸入 ABC.axpc?ProductID=1 則查詢該項產品，其它條件不加入查詢，依此類推。如此，我們就可以架構一個萬用的查詢系統。