48-使用SqlDataSource控件查詢數(shù)據(jù)

1、導(dǎo)言：在上一章里，我們探討了怎樣用SqlDataSource控件直接從數(shù)據(jù)庫檢索數(shù)據(jù)。通過設(shè)置數(shù)據(jù)源向?qū)В覀兛梢赃x擇要訪問的數(shù)據(jù)庫，然后要么從指定的表或視圖，要么從自定義的SQL語句或存儲過程來返回需要的記錄。不管用那種方式，SqlDataSource控件的SelectCommand屬性都被賦值為一個ad-hocSQLSELECT命令（SELECTstatement）。當(dāng)SqlDataSource控件的Select()方法被調(diào)用時（無論數(shù)據(jù)Web控件是自動還是通過編程設(shè)置來調(diào)用Select()方法），就將執(zhí)行這個SELECT命令。上一篇教程里使用的SQLSELECT命令里缺少WHERE字句，

2、在SELECT命令里WHERE字句用來限制查詢結(jié)果范圍。比如，我們顯示那些價格高于50的產(chǎn)品名稱時，可以用如下所示的查詢語句：SELECTProductNameFROMProductsWHEREUnitPrice50.00特別的，WHERE字句中的參數(shù)值來源于外部（someexternalsource），比如：查詢字符串的值,一個session變量，或用戶從Web控件輸入的值。一般來說，通過使用參數(shù)的方式來傳遞這些值。在MicrosoftSQLServer里，我們這樣表示參數(shù)parameterName,例如：SELECTProductNameFROMProductsWHEREUnitPrice

3、PriceSqlDataSource控件支持帶參數(shù)的查詢，包括SELECT，INSERT,UPDATE和DELETE命令而且，參數(shù)的值可以來源于一個查詢字符串，session狀態(tài)，頁面上的控件等等，甚至可以通過編程來賦值。這一章我們探討怎樣通過顯式地賦值和通過編程來賦值2種方式來構(gòu)造帶參數(shù)的查詢。注意：在上一章我們將SqlDataSource控件和ObjectDataSource控件作了比較。除了在概念上類似外，它們在參數(shù)的使用上也很相像。不過SqlDataSource控件的參數(shù)與位于業(yè)務(wù)邏輯層的相關(guān)方法的對應(yīng)參數(shù)相匹配，而ObjectDataSource控件的參數(shù)由SQL查詢直接定義。此2種

4、控件的Select(),Insert(),Update()和Delete()方法都有對應(yīng)的參數(shù)集，此外這2種控件還可以包含通過編程傳入的，或由查詢字符串，session變量等預(yù)定義源（pre-definedsources）傳入的參數(shù)值。構(gòu)造一個帶參數(shù)的查詢SqlDataSource控件的數(shù)據(jù)源設(shè)置向?qū)峁┝巳N檢索數(shù)據(jù)的方式：通過返回表或視圖的相關(guān)列通過使用自定義SQL查詢通過使用存儲過程1當(dāng)使用通過返回表或視圖的相關(guān)列的方法的時候，需要在“添加WHERE字句”對話框里為WHERE字句設(shè)置參數(shù)。當(dāng)使用通過使用自定義SQL查詢方法時，需要直接在WHERE字句設(shè)置參數(shù)（每個參數(shù)的形式為：param

5、eterName）。對第三中方法，因?yàn)橐粋€存儲過程由一個或幾個SQL語句組成，而且在SQL語句中可以設(shè)置參數(shù)，所以那些SQL語句中設(shè)置的參數(shù)對存儲過程來說是輸入?yún)?shù)。一個帶參數(shù)查詢的構(gòu)造取決于SqlDataSource控件的SelectCommand命令是如何設(shè)置的，我們接下來使用上面三種方法來構(gòu)造帶參數(shù)的查詢。打開SqlDataSource文件夾里的ParameterizedQueries.aspx頁面，進(jìn)入設(shè)計模式，從工具箱拖一個SqlDataSource控件到頁面上，設(shè)置其ID為Products25BucksAndUnderDataSource，然后在智能標(biāo)簽中點(diǎn)“配置數(shù)據(jù)源”鏈接，選擇

6、NORTHWINDConnectionString，點(diǎn)“下一步”。第一步：在“指定來自表或視圖的列”模式里添加WHERE字句當(dāng)選擇使用SqlDataSource控件從數(shù)據(jù)庫返回數(shù)據(jù)時，其數(shù)據(jù)源設(shè)置向?qū)г试S我們使用最簡單的“指定來自表或視圖的列”模式（如圖1）。此模式會自動生成Select()方法調(diào)用時使用的SQLSELECT命名。象上一章探討的一樣，在Products表中選擇ProductID，ProductName和UnitPrice三列。圖1：選擇“指定來自表或視圖的列”模式為給SELECT命令添加WHERE字句，請點(diǎn)擊“WHERE”按鈕，進(jìn)入“添加WHERE字句”界面（如圖2），首選選擇

7、用來篩選記錄的列，再選擇操作符（比如=,等），最后選擇參數(shù)值來源，比如來自查詢字符串，視圖狀態(tài)等。完成設(shè)置后點(diǎn)“添加”按鈕。2本例中，我們僅僅返回那些價格小于或等于25的記錄。所以我們選“UnitPrice”列，在操作符中選“=”。當(dāng)使用“硬編碼”參數(shù)值（hard-coded），或使用參數(shù)傳遞值時，在“源”下拉列表里選擇“None”。本節(jié)我們使用“硬編碼”方式，所以在“源”下拉列表里選擇“None”，同時在右邊“參數(shù)屬性”里輸入25.00，點(diǎn)“添加”完成設(shè)置。圖2：在“添加WHERE字句”對話框中添加WHERE字句完成設(shè)置后，點(diǎn)“OK”，返回數(shù)據(jù)源設(shè)置向?qū)В诘撞康腟ELECT語句里應(yīng)該包含了

8、一個名為“UnitPrice”的參數(shù)：SELECTProductID,ProductName,UnitPriceFROMProductsWHERE(UnitPrice=UnitPrice)注意：如果你在“添加WHERE字句”對話框中指定了多條WHERE字句，那么向?qū)米址鸄ND把它們連接起來。當(dāng)你需要在WHERE字句里使用字符OR時，(比如：WHEREUnitPrice=UnitPriceORDiscontinued=1)，就只能在“自定義SQL語句”模式里構(gòu)建了。完成設(shè)置（點(diǎn)下一步，再點(diǎn)完成）后，查看其聲明代碼，代碼里現(xiàn)在包含了項(xiàng)。如下：3asp:SqlDataSourceID=Produ

9、cts25BucksAndUnderDataSourcerunat=serverConnectionString=SelectCommand=SELECTProductID,ProductName,UnitPriceFROMProductsWHERE(UnitPrice當(dāng)SqlDataSource控件的Select()方法被調(diào)用的時候，在訪問數(shù)據(jù)庫之前，UnitPrice參數(shù)值（25.00）就與SelectCommand里的UnitPriceparameter掛鉤。結(jié)果是表Products里只有那些UnitPrice等于或小于25.00的記錄被返回。為驗(yàn)證起見，在頁面上添加一個GridView

10、控件，并綁定到這個數(shù)據(jù)源，在瀏覽器里瀏覽本頁，你將看到那些價格小于等于25.00的產(chǎn)品顯示出來了，如圖3所示：圖3：只有那些價格小于等于25的產(chǎn)品顯示出來了。4第2步在自定義SQL語句里添加參數(shù)當(dāng)添加一個自定義SQL查詢時，你可以直接在WHERE字句里輸入?yún)?shù)，或者在查詢生成器里指定一個值。我們來做個實(shí)例，返回那些價格低于某個值的產(chǎn)品。在ParameterizedQueries.aspx頁面添加一個TextBox，設(shè)置其ID為MaxPrice，供用戶輸入某個值。再添加一個Button控件，設(shè)置其Text屬性為“DisplayMatchingProducts”下一步，在頁面添加一個GridVie

11、w控件，在其智能標(biāo)簽中配置一個名為ProductsFilteredByPriceDataSource的SqlDataSource數(shù)據(jù)源，在數(shù)據(jù)源配置向?qū)е械摹爸付ㄒ粋€自定義SQL語句或存儲過程”界面中（見圖4），鍵入如下查詢：SELECTProductName,UnitPriceFROMProductsWHEREUnitPrice=MaximumPrice完成鍵入后（不管是手工輸入還是用查詢生成器），點(diǎn)下一步。圖4：返回那些價格低于指定值的產(chǎn)品。然后，向?qū)崾疚覀冎付▍?shù)值來源，在“參數(shù)來源”下拉列表里選“Control”，在“ControlID”下拉列表里選“MaxPrice”（即TextB

12、ox控件的ID）。你也可以在默認(rèn)值”里隨意鍵入一個值，以防用戶未在TextBox控件輸入值的情況。不過在本教程里，無需指定一個默認(rèn)值。5圖5：MaxPriceTextBox控件的Text屬性指定為參數(shù)值來源。點(diǎn)下一步，點(diǎn)完成，完成設(shè)置?，F(xiàn)在這幾個控件的聲明代碼看起來應(yīng)跟下面差不多：Maximumprice:$6asp:SqlDataSourceID=ProductsFilteredByPriceDataSourcerunat=serverConnectionString=SelectCommand=SELECTProductName,UnitPriceFROMProductsWHEREUnit

13、Price我們注意到SqlDataSource控件的項(xiàng)的參數(shù)現(xiàn)在變成了一個ControlParameter，并且還多出了諸如ControlID和PropertyName的屬性。當(dāng)SqlDataSource控件的Select()方法被調(diào)用時，ControlParameter將會從頁面上指定的Web控件屬性獲取值，并賦給SelectCommand里的相應(yīng)參數(shù)。具體到本例，MaxPrice的Text屬性的值被賦給了參數(shù)MaxPrice?；◣追昼姇r間預(yù)覽頁面，當(dāng)?shù)谝淮卧L問該頁面，或者M(jìn)axPriceTextBox里沒輸入值時，GridView里沒有記錄顯示。圖6：當(dāng)MaxPriceTextBox未賦值

14、時，沒有記錄顯示。沒有產(chǎn)品顯示的原因是，默認(rèn)情況下，傳遞給參數(shù)的空字符串會被轉(zhuǎn)換為NULL，而UnitPrice=NULL的值總是為False，導(dǎo)致無法從數(shù)據(jù)庫檢索出記錄。7按”在textbox里輸入一個值，比如5.00，點(diǎn)擊“DisplayMatchingProducts鈕，頁面回傳，SqlDataSource控件提示GridView它的參數(shù)來源發(fā)生了改變，因此，GridView重新綁定到SqlDataSource，顯示那些價格低于5.00的產(chǎn)品。圖7：價格低于5的產(chǎn)品被顯示出來設(shè)置頁面初次登錄時顯示所有產(chǎn)品頁面初次登錄時，與其不顯示數(shù)據(jù)不如設(shè)置成顯示所有產(chǎn)品。當(dāng)MaxPriceTextBo

15、x的輸入值為空時，我們可以為參數(shù)設(shè)置一個高的離譜的默認(rèn)價格，比如1000000，因?yàn)椴淮罂梢杂袃r格超過1000000的庫存產(chǎn)品。然而在其它情況下這個辦法可能并不管用。在以前的教程DeclarativeParameters和Master/DetailFilteringWithaDropDownList中，我們都遇到了相類似的問題。我們的解決辦法是在放在業(yè)務(wù)邏輯層處理，當(dāng)業(yè)務(wù)邏輯層判斷出輸入值是NULL或其它的什么保留值（reservedvalue），則調(diào)用數(shù)據(jù)訪問層的返回所有記錄的方法，當(dāng)輸入值是正常的過濾值（filteringvalue）時，則執(zhí)行數(shù)據(jù)訪問層中WHERE字句包含相應(yīng)參數(shù)的SQL

16、語句。不幸的是，當(dāng)使用SqlDataSource時，這種方法并適用。因此當(dāng)參數(shù)MaximumPrice是NULL或其它的什么保留值時，我們必須自定義SQL語句來返回所有的產(chǎn)品。比如，讓我們把參數(shù)MaximumPrice設(shè)置為-1，然后返回所有記錄（-1就是一種保留值，因?yàn)闆]有產(chǎn)品的價格為負(fù)數(shù)），為達(dá)到目的，我們可以用下面的SQL語句：SELECTProductName,UnitPriceFROMProductsWHEREUnitPrice=MaximumPriceORMaximumPrice=-1.0在WHERE字句中，當(dāng)MaximumPrice剛好等于-1時，返回所有產(chǎn)品，如果不為-1，那么

17、只返回那些價格等于或小于MaximumPrice值的產(chǎn)品。當(dāng)把參數(shù)MaximumPrice8的默認(rèn)值設(shè)置為-1時，第一此顯示頁面時（或每當(dāng)MaxPriceTextBox中沒有鍵入值時）參數(shù)MaximumPrice的值都默認(rèn)為-1，因此將返回所有記錄。圖8：當(dāng)MaxPriceTextBox是空的時，顯示所有的產(chǎn)品。這種方法有2處容易出錯。第一，因?yàn)閰?shù)的數(shù)據(jù)類型是在SQL查詢中決定的，當(dāng)你把WHERE字句由“MaximumPrice=-1.0”改為“MaximumPrice=-1”后，運(yùn)行時參數(shù)就被當(dāng)作整數(shù)處理。如果你試圖在MaxPriceTextBox中輸入一個小數(shù)（比如5.00），這時錯誤發(fā)

18、生了，因?yàn)樗荒軐ⅰ?.00”轉(zhuǎn)換為一個整數(shù)。要解決這個問題，一個辦法是確保在WHERE字句使用“MaximumPrice=-1.0”，而更好的辦法是把ControlParameter的Type屬性設(shè)置為“Decimal”（即小數(shù)型）。第二個容易錯的是，當(dāng)把“ORMaximumPrice=-1.0”加入到WHERE字句后，將會使檢索引擎遍歷整個表，當(dāng)表Products包含的記錄數(shù)很龐大的時候?qū)?yán)重的削弱性能。比較好的解決辦法是用一個存儲過程中來處理，可以在存儲過程中使用IF語句來處理“UnitPrice=MaximumPriceORMaximumPrice=-1.0”這個邏輯關(guān)系。第3步：創(chuàng)建

19、并使用參數(shù)化的存儲過程存儲過程可以包含一個輸入?yún)?shù)，用于存儲過程里定義的SQL語句。當(dāng)設(shè)置SqlDataSource使用帶參數(shù)的存儲過程時，其用法與ad-hocSQL語句一樣。9來看個實(shí)例，我們在Northwind數(shù)據(jù)庫里創(chuàng)建一個名為GetProductsByCategory的存儲過程，這個存儲過程接受一個參數(shù)CategoryID，并返回products表中所有CategoryID列的值與CategoryID相匹配的記錄。讓我們開始創(chuàng)建吧，在服務(wù)器資源管理器中選定NORTHWND.MDF數(shù)據(jù)庫。（如果你找不到服務(wù)器資源管理器，在視圖菜單里選擇服務(wù)器資源管理器項(xiàng)即可）在NORTHWND.MDF數(shù)

20、據(jù)庫里，在存儲過程項(xiàng)上點(diǎn)右鍵，選擇“添加新存儲過程”，輸入如下語句：CREATEPROCEDUREdbo.GetProductsByCategory(CategoryIDint)ASSELECT*FROMProductsWHERECategoryID=CategoryID點(diǎn)保存圖標(biāo)（或Ctrl+S）保存新建的存儲過程。你可以測試你創(chuàng)建的存儲過程，方法是選中某個存儲過程，在右鍵選“執(zhí)行”。接下來將提示是輸入?yún)?shù)（就本例而言，是CategoryID），最后在輸出窗口顯示結(jié)果。圖9：當(dāng)在GetProductsByCategory中給參數(shù)CategoryID賦值1時的執(zhí)行情況10讓我們在GridVie

21、w中顯示那些種類為Beverages的產(chǎn)品。在頁面新添加一個GridView，并綁定到一個ID為BeverageProductsDataSource的SqlDataSource控件。啟用SqlDataSource控件的數(shù)據(jù)源設(shè)置向?qū)?，在“指定自定義SQL語句或存儲過程”界面中，在“存儲過程”下拉列表中，選GetProductsByCategory。圖10：在下拉列表中，選GetProductsByCategory存儲過程因?yàn)檫@個存儲過程接受一個輸入?yún)?shù)(CategoryID),點(diǎn)下一步后，向?qū)崾疚覀冎付▍?shù)值來源。因?yàn)槲覀兿敕祷厮蓄愋蜑锽everages的產(chǎn)品，且Beverages的Cat

22、egoryID為1，所以我們在“參數(shù)源”下拉列表里選“None”，在默認(rèn)值文本框里輸入111圖11：通過將默認(rèn)值設(shè)為1這種硬編碼方式返回所有種類為Beverages的產(chǎn)品就像下面的編碼所展示的那樣，當(dāng)使用存儲過程的時候，SqlDataSource控件的SelectCommand屬性被賦值為存儲過程的名稱，相應(yīng)的，SelectCommandType屬性被設(shè)置為StoredProcedure。這就指明了SelectCommand執(zhí)行的是存儲過程而不是一個ad-hocSQL語句。asp:SqlDataSourceID=BeverageProductsDataSourcerunat=serverCon

23、nectionString=SelectCommand=GetProductsByCategorySelectCommandType=StoredProcedure在瀏覽器里查看該頁面，只有那些類型為Beverages的產(chǎn)品顯示出來了。因?yàn)榇鎯^程GetProductsByCategory從表Products里檢索所有的列，所以在頁面中顯示了記錄的所有列。當(dāng)然，你可以在GridView控件的“編輯列”對話框中自定義要顯示的列。12圖12：所有Beverages類的產(chǎn)品被顯示出來。第四步：參數(shù)化的調(diào)用SqlDataSource控件的Select()命令本教程到目前為止直接將SqlDataSour

24、ce控件綁定到GridView控件。然而，可以通過在代碼里使用參數(shù)的方法來訪問SqlDataSource的數(shù)據(jù)。在我們需要訪問但又用不著把這些數(shù)據(jù)顯示出來的情況下，這種方法很有用。與其自己動手寫諸如連接數(shù)據(jù)庫、指定具體的訪問命令、返回結(jié)果等典型的ADO.NET代碼，不如讓SqlDataSource控件代勞。舉一個參數(shù)化使用SqlDataSource控件數(shù)據(jù)的例子。想象一下，你的老板讓你編寫這樣的一個頁面，根據(jù)隨機(jī)選擇的類，在頁面上顯示這個類的名稱，及其相關(guān)聯(lián)的產(chǎn)品。那意味著，當(dāng)我們訪問這個頁面時，隨機(jī)從表Categories中抽取一個類，頁面將該類的名字，以及屬于該類的所有產(chǎn)品顯示出來。為了達(dá)

25、到這樣目的，我們需要2個SqlDataSource控件。一個從表Categories中隨機(jī)的選擇一個類，一個返回該類的所有產(chǎn)品。在第4步我們探討如何構(gòu)建一SqlDataSource控件，使其返回一個隨機(jī)抽取的類記錄；在第5步我們探討如何構(gòu)建一個返回所有該類型的產(chǎn)品的SqlDataSource控件。在ParameterizedQueries.aspx頁面上添加一個SqlDataSource控件，設(shè)置其ID為RandomCategoryDataSource，對其使用如下的SQL查詢：SELECTTOP1CategoryID,CategoryNameFROMCategories13ORDERBYNE

26、WID()其中“ORDERBYNEWID()”意味著對返回的記錄隨機(jī)(randomorder)排序(詳細(xì)內(nèi)容看seeUsingNEWID()toRandomlySortRecords)，“SELECTTOP1”則意味著從結(jié)果里返回第一條記錄。合起來，此查詢將對查詢結(jié)果隨機(jī)排序，并返回第一條記錄的CategoryID和CategoryName列。在頁面上添加一個Label控件用于顯示CategoryName，設(shè)置其ID為CategoryNameLabel，并將Text屬性清空。為了參數(shù)化地從SqlDataSource控件獲取數(shù)據(jù)，我們需要調(diào)用它的Select()方法。Select()方法使用的是

27、類型為DataSourceSelectArguments的輸入?yún)?shù)。當(dāng)某個數(shù)據(jù)Web控件對SqlDataSource控件傳入的數(shù)據(jù)企用分頁或排序功能時，該類型的參數(shù)在Select()方法返回數(shù)據(jù)前，指出應(yīng)該對數(shù)據(jù)進(jìn)行篩選和排序。在本例中，我們并不需要對結(jié)果做排序等任何的改變，所以我們只需要參數(shù)的DataSourceSelectArguments.Empty形式。Select()方法返回什么類型的對象(object)，取決于SqlDataSource控件的DataSourceMode屬性。就像上篇教程所說的那樣，DataSourceMode屬性可以被設(shè)置為DataSet或DataReader，D

28、ataSourceMode屬性設(shè)置為哪種類型，Select()方法就返回那種類型的對象。因?yàn)镾qlDataSource控件RandomCategoryDataSource的DataSourceMode屬性被設(shè)置為DataSet（默認(rèn)的），所有我們將與DataView對象打交道。下面的代碼揭示了SqlDataSource控件RandomCategoryDataSource怎樣將返回的結(jié)果轉(zhuǎn)換成DataView類型，以及怎樣從DataView的第一行記錄讀取CategoryName列的值。protectedvoidPage_Load(objectsender,EventArgse)/Getthed

29、atafromtheSqlDataSourceasaDataViewDataViewrandomCategoryView=(DataView)RandomCategoryDataSource.Select(DataSourceSelectArguments.Empty);if(randomCategoryView.Count0)/AssigntheCategoryNamevaluetotheLabelCategoryNameLabel.Text=string.Format(HereareProductsinthe0Category.,randomCategoryView0CategoryNam

30、e.ToString();14randomCategoryView(0)意味著返回DataView的第一個DataRowView，randomCategoryView(0)(CategoryName)返回這個第一行的CategoryName列的值。注意，DataView是一種“泛型”(loosely-typed)對象，當(dāng)我們引用某個具體列時，需要使用列名的字符串形式(本例為“CategoryName”)。圖13顯示了當(dāng)訪問該頁面時，CategoryNameLabel文本框顯示的內(nèi)容。當(dāng)然，每次訪問該頁面(包括回傳)時，文本框根據(jù)SqlDataSource控件CategoryDataSource

31、里隨機(jī)選擇的種類，顯示該種類的名稱。圖13：顯示隨機(jī)選擇的種類的名稱注意:當(dāng)SqlDataSource控件的DataSourceMode屬性設(shè)置為DataReader時,從Select()方法返回的值需要轉(zhuǎn)換為DataReader,我們用如下的代碼來從第一行讀取列CategoryName的值:if(randomCategoryReader.Read()stringcategoryName=randomCategoryReaderCategoryName.ToString();.通過SqlDataSource,我們隨機(jī)的選擇了一種類型,然后我們將在頁面上添加一個GridView控件,顯示該類型的

32、產(chǎn)品.注意:一般來講,雖然用Label控件來展示類名,不如在頁面使用一個綁定到SqlDataSource控件的FormView或DetailsView來顯示.但用Label控件的好處在于,它便于我們調(diào)用SqlDataSource控件的帶參數(shù)Select()命令.第5步:編程為參數(shù)賦值15”到目前為止，本教程例子中的參數(shù)值要么通過“硬編碼（hard-coded）的方式設(shè)置，要么來自于查詢字符串、Web控件等預(yù)定義參數(shù)源（pre-defined然而，SqlDataSource的參數(shù)還可以通過編程的方式設(shè)置。為了完成這個實(shí)例，我們需要一個返回屬于某個特定種類的產(chǎn)品的SqlDataSource控件，該控件包含一個參數(shù)“CategoryID”。在Page_Load事件中，ID為RandomCategor