用SQL完成條件式查詢
因為覺得每次要用條件式查詢很煩(如範例一)，就想有沒有更方便的用法，試出一種方法還不錯，分享給大家。
測試環境
DBMS:Sql Server 2008 Express
DB:AdventureWorks
Table:Sales.SalesOrderDetail(Count:121,317)
情境
對Sales.SalesOrderDetail的OrderId與ProduceId與SaleDate這三個欄位做查詢(如圖一)，如果沒有輸入條件為顯示全部。
圖一 查詢畫面
範例一 傳統的條件式
1: StringBuilder sb = new StringBuilder();
2:
3: if (!string.IsNullOrEmpty(Order.Text))
4: sb.AppendFormat("SalesOrderID='{0}'", Order.Text);
5:
6: if (!string.IsNullOrEmpty(Produce.Text))
7: {
8: if (sb.Length > 0)
9: sb.Append(" AND ");
10:
11: sb.AppendFormat("ProductID='{0}'", Produce.Text);
12: }
13:
14: if (!string.IsNullOrEmpty(StartDate.Text))
15: {
16: if (sb.Length > 0)
17: sb.Append(" AND ");
18:
19: sb.AppendFormat("ModifiedDate > '{0}'", StartDate.Text);
20: }
21:
22: if (!string.IsNullOrEmpty(EndDate.Text))
23: {
24: if (sb.Length > 0)
25: sb.Append(" AND ");
26:
27: sb.AppendFormat("ModifiedDate < '{0}'", EndDate.Text);
28: }
29:
30: if (sb.Length > 0)
31: sb.Insert(0, " WHERE ");
32:
33: sb.Insert(0,"SELECT * FROM Sales.SalesOrderDetail");
如果是以前的我，我會這樣寫，落落長看的都累了，下一個範例展表直接在SQL中作掉。
範例二 更方便的寫法
1: string sql = string.Format("SELECT * FROM Sales.SalesOrderDetail WHERE" +
2: "('{0}' = '' OR SalesOrderID = '{0}') AND " +
3: "('{1}' = '' OR ProductID = '{1}') AND " +
4: "('{2}' = '' OR ModifiedDate > '{2}') AND " +
5: "('{3}' = '' OR ModifiedDate < '{3}')",
6: Order.Text, Produce.Text, StartDate.Text, EndDate.Text);
這個方法是不是寫起來簡單，看起來乾淨，其作用為('' = '' OR SalesOrderID = '')參數是空的不查詢，('12345' = '' OR SalesOrderID = '12345')參數不是空的執行查詢。
應用一 SqlDataSource中使用
1: <asp:SqlDataSource ID="SqlDataSource" runat="server" CancelSelectOnNullParameter="false"
2: SelectCommand="SELECT * FROM Sales.SalesOrderDetail WHERE
3: (@Order IS NULL OR SalesOrderID = @Order) AND
4: (@Produce IS NULL OR ProductID = @Produce) AND
5: (@StartDate IS NULL OR ModifiedDate > @StartDate) AND
6: (@EndDate IS NULL OR ModifiedDate < @EndDate)">
7: <SelectParameters>
8: <asp:ControlParameter ControlID="Order" Name="Order" ConvertEmptyStringToNull="true" />
9: <asp:ControlParameter ControlID="Produce" Name="Produce" ConvertEmptyStringToNull="true"/>
10: <asp:ControlParameter ControlID="StartDate" Name="StartDate" ConvertEmptyStringToNull="true"/>
11: <asp:ControlParameter ControlID="EndDate" Name="EndDate" ConvertEmptyStringToNull="true"/>
12: </SelectParameters>
13: </asp:SqlDataSource>
用這個方式，在DataSource中也可以做到DataSource條件式查詢，使用CancelSelectOnNullParameter=”false”，不管有沒有結果都要執行，因為設定ConvertEmptyStringToNull="true"，所以SQL中也可以使用(@Order IS NULL OR SalesOrderID = @Order)
應用二 StoreProcedure中使用
1: CREATE PROCEDURE SearchSalesOrderDetail
2: @Order varchar(10) = null ,
3: @Produce varchar(10) = null,
4: @StartDate datetime = null,
5: @EndDate datetime = null
6: AS
7: BEGIN
8: SELECT * FROM Sales.SalesOrderDetail WHERE
9: (@Order IS NULL OR SalesOrderID = @Order) AND
10: (@Produce IS NULL OR ProductID = @Produce) AND
11: (@StartDate IS NULL OR ModifiedDate > @StartDate) AND
12: (@EndDate IS NULL OR ModifiedDate < @EndDate)
13: END
14: GO
當然也可以寫成Store Procedure直接在資料庫操作。
效能測試
各位最擔心的還是效能，就測試結果來說效能比較差，不過這樣物件導向是一樣的，為了方便多少會犧生一些效能，能不能接受，就看各位了。
EXEC dbo.SearchSalesOrderDetail NULL,NULL,NULL,NULL
SELECT * FROM Sales.SalesOrderDetail
EXEC dbo.SearchSalesOrderDetail '43659','776'
SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = '43659' AND ProductID = '776'
