48-使用SqlDataSource控件查询数据

1、导言：在上一章里，我们探讨了怎样用SqlDataSource控件直接从数据库检索数据。通过设置数据源向导，我们可以选择要访问的数据库，然后要么从指定的表或视图，要么从自定义的SQL语句或存储过程来返回需要的记录。不管用那种方式，SqlDataSource控件的SelectCommand属性都被赋值为一个ad-hocSQLSELECT命令（SELECTstatement）。当SqlDataSource控件的Select()方法被调用时（无论数据Web控件是自动还是通过编程设置来调用Select()方法），就将执行这个SELECT命令。上一篇教程里使用的SQLSELECT命令里缺少WHERE字句，

2、在SELECT命令里WHERE字句用来限制查询结果范围。比如，我们显示那些价格高于50的产品名称时，可以用如下所示的查询语句：SELECTProductNameFROMProductsWHEREUnitPrice50.00特别的，WHERE字句中的参数值来源于外部（someexternalsource），比如：查询字符串的值,一个session变量，或用户从Web控件输入的值。一般来说，通过使用参数的方式来传递这些值。在MicrosoftSQLServer里，我们这样表示参数parameterName,例如：SELECTProductNameFROMProductsWHEREUnitPrice

3、PriceSqlDataSource控件支持带参数的查询，包括SELECT，INSERT,UPDATE和DELETE命令而且，参数的值可以来源于一个查询字符串，session状态，页面上的控件等等，甚至可以通过编程来赋值。这一章我们探讨怎样通过显式地赋值和通过编程来赋值2种方式来构造带参数的查询。注意：在上一章我们将SqlDataSource控件和ObjectDataSource控件作了比较。除了在概念上类似外，它们在参数的使用上也很相像。不过SqlDataSource控件的参数与位于业务逻辑层的相关方法的对应参数相匹配，而ObjectDataSource控件的参数由SQL查询直接定义。此2种

4、控件的Select(),Insert(),Update()和Delete()方法都有对应的参数集，此外这2种控件还可以包含通过编程传入的，或由查询字符串，session变量等预定义源（pre-definedsources）传入的参数值。构造一个带参数的查询SqlDataSource控件的数据源设置向导提供了三种检索数据的方式：通过返回表或视图的相关列通过使用自定义SQL查询通过使用存储过程1当使用通过返回表或视图的相关列的方法的时候，需要在“添加WHERE字句”对话框里为WHERE字句设置参数。当使用通过使用自定义SQL查询方法时，需要直接在WHERE字句设置参数（每个参数的形式为：param

5、eterName）。对第三中方法，因为一个存储过程由一个或几个SQL语句组成，而且在SQL语句中可以设置参数，所以那些SQL语句中设置的参数对存储过程来说是输入参数。一个带参数查询的构造取决于SqlDataSource控件的SelectCommand命令是如何设置的，我们接下来使用上面三种方法来构造带参数的查询。打开SqlDataSource文件夹里的ParameterizedQueries.aspx页面，进入设计模式，从工具箱拖一个SqlDataSource控件到页面上，设置其ID为Products25BucksAndUnderDataSource，然后在智能标签中点“配置数据源”链接，选择

6、NORTHWINDConnectionString，点“下一步”。第一步：在“指定来自表或视图的列”模式里添加WHERE字句当选择使用SqlDataSource控件从数据库返回数据时，其数据源设置向导允许我们使用最简单的“指定来自表或视图的列”模式（如图1）。此模式会自动生成Select()方法调用时使用的SQLSELECT命名。象上一章探讨的一样，在Products表中选择ProductID，ProductName和UnitPrice三列。图1：选择“指定来自表或视图的列”模式为给SELECT命令添加WHERE字句，请点击“WHERE”按钮，进入“添加WHERE字句”界面（如图2），首选选择

7、用来筛选记录的列，再选择操作符（比如=,等），最后选择参数值来源，比如来自查询字符串，视图状态等。完成设置后点“添加”按钮。2本例中，我们仅仅返回那些价格小于或等于25的记录。所以我们选“UnitPrice”列，在操作符中选“=”。当使用“硬编码”参数值（hard-coded），或使用参数传递值时，在“源”下拉列表里选择“None”。本节我们使用“硬编码”方式，所以在“源”下拉列表里选择“None”，同时在右边“参数属性”里输入25.00，点“添加”完成设置。图2：在“添加WHERE字句”对话框中添加WHERE字句完成设置后，点“OK”，返回数据源设置向导，在底部的SELECT语句里应该包含了

8、一个名为“UnitPrice”的参数：SELECTProductID,ProductName,UnitPriceFROMProductsWHERE(UnitPrice=UnitPrice)注意：如果你在“添加WHERE字句”对话框中指定了多条WHERE字句，那么向导会用字符AND把它们连接起来。当你需要在WHERE字句里使用字符OR时，(比如：WHEREUnitPrice=UnitPriceORDiscontinued=1)，就只能在“自定义SQL语句”模式里构建了。完成设置（点下一步，再点完成）后，查看其声明代码，代码里现在包含了项。如下：3asp:SqlDataSourceID=Produ

9、cts25BucksAndUnderDataSourcerunat=serverConnectionString=SelectCommand=SELECTProductID,ProductName,UnitPriceFROMProductsWHERE(UnitPrice当SqlDataSource控件的Select()方法被调用的时候，在访问数据库之前，UnitPrice参数值（25.00）就与SelectCommand里的UnitPriceparameter挂钩。结果是表Products里只有那些UnitPrice等于或小于25.00的记录被返回。为验证起见，在页面上添加一个GridView

10、控件，并绑定到这个数据源，在浏览器里浏览本页，你将看到那些价格小于等于25.00的产品显示出来了，如图3所示：图3：只有那些价格小于等于25的产品显示出来了。4第2步在自定义SQL语句里添加参数当添加一个自定义SQL查询时，你可以直接在WHERE字句里输入参数，或者在查询生成器里指定一个值。我们来做个实例，返回那些价格低于某个值的产品。在ParameterizedQueries.aspx页面添加一个TextBox，设置其ID为MaxPrice，供用户输入某个值。再添加一个Button控件，设置其Text属性为“DisplayMatchingProducts”下一步，在页面添加一个GridVie

11、w控件，在其智能标签中配置一个名为ProductsFilteredByPriceDataSource的SqlDataSource数据源，在数据源配置向导中的“指定一个自定义SQL语句或存储过程”界面中（见图4），键入如下查询：SELECTProductName,UnitPriceFROMProductsWHEREUnitPrice=MaximumPrice完成键入后（不管是手工输入还是用查询生成器），点下一步。图4：返回那些价格低于指定值的产品。然后，向导提示我们指定参数值来源，在“参数来源”下拉列表里选“Control”，在“ControlID”下拉列表里选“MaxPrice”（即TextB

12、ox控件的ID）。你也可以在默认值”里随意键入一个值，以防用户未在TextBox控件输入值的情况。不过在本教程里，无需指定一个默认值。5图5：MaxPriceTextBox控件的Text属性指定为参数值来源。点下一步，点完成，完成设置。现在这几个控件的声明代码看起来应跟下面差不多：Maximumprice:$6asp:SqlDataSourceID=ProductsFilteredByPriceDataSourcerunat=serverConnectionString=SelectCommand=SELECTProductName,UnitPriceFROMProductsWHEREUnit

13、Price我们注意到SqlDataSource控件的项的参数现在变成了一个ControlParameter，并且还多出了诸如ControlID和PropertyName的属性。当SqlDataSource控件的Select()方法被调用时，ControlParameter将会从页面上指定的Web控件属性获取值，并赋给SelectCommand里的相应参数。具体到本例，MaxPrice的Text属性的值被赋给了参数MaxPrice。花几分钟时间预览页面，当第一次访问该页面，或者MaxPriceTextBox里没输入值时，GridView里没有记录显示。图6：当MaxPriceTextBox未赋值

14、时，没有记录显示。没有产品显示的原因是，默认情况下，传递给参数的空字符串会被转换为NULL，而UnitPrice=NULL的值总是为False，导致无法从数据库检索出记录。7按”在textbox里输入一个值，比如5.00，点击“DisplayMatchingProducts钮，页面回传，SqlDataSource控件提示GridView它的参数来源发生了改变，因此，GridView重新绑定到SqlDataSource，显示那些价格低于5.00的产品。图7：价格低于5的产品被显示出来设置页面初次登录时显示所有产品页面初次登录时，与其不显示数据不如设置成显示所有产品。当MaxPriceTextBo

15、x的输入值为空时，我们可以为参数设置一个高的离谱的默认价格，比如1000000，因为不大可以有价格超过1000000的库存产品。然而在其它情况下这个办法可能并不管用。在以前的教程DeclarativeParameters和Master/DetailFilteringWithaDropDownList中，我们都遇到了相类似的问题。我们的解决办法是在放在业务逻辑层处理，当业务逻辑层判断出输入值是NULL或其它的什么保留值（reservedvalue），则调用数据访问层的返回所有记录的方法，当输入值是正常的过滤值（filteringvalue）时，则执行数据访问层中WHERE字句包含相应参数的SQL

16、语句。不幸的是，当使用SqlDataSource时，这种方法并适用。因此当参数MaximumPrice是NULL或其它的什么保留值时，我们必须自定义SQL语句来返回所有的产品。比如，让我们把参数MaximumPrice设置为-1，然后返回所有记录（-1就是一种保留值，因为没有产品的价格为负数），为达到目的，我们可以用下面的SQL语句：SELECTProductName,UnitPriceFROMProductsWHEREUnitPrice=MaximumPriceORMaximumPrice=-1.0在WHERE字句中，当MaximumPrice刚好等于-1时，返回所有产品，如果不为-1，那么

17、只返回那些价格等于或小于MaximumPrice值的产品。当把参数MaximumPrice8的默认值设置为-1时，第一此显示页面时（或每当MaxPriceTextBox中没有键入值时）参数MaximumPrice的值都默认为-1，因此将返回所有记录。图8：当MaxPriceTextBox是空的时，显示所有的产品。这种方法有2处容易出错。第一，因为参数的数据类型是在SQL查询中决定的，当你把WHERE字句由“MaximumPrice=-1.0”改为“MaximumPrice=-1”后，运行时参数就被当作整数处理。如果你试图在MaxPriceTextBox中输入一个小数（比如5.00），这时错误发

18、生了，因为它不能将“5.00”转换为一个整数。要解决这个问题，一个办法是确保在WHERE字句使用“MaximumPrice=-1.0”，而更好的办法是把ControlParameter的Type属性设置为“Decimal”（即小数型）。第二个容易错的是，当把“ORMaximumPrice=-1.0”加入到WHERE字句后，将会使检索引擎遍历整个表，当表Products包含的记录数很庞大的时候将严重的削弱性能。比较好的解决办法是用一个存储过程中来处理，可以在存储过程中使用IF语句来处理“UnitPrice=MaximumPriceORMaximumPrice=-1.0”这个逻辑关系。第3步：创建

19、并使用参数化的存储过程存储过程可以包含一个输入参数，用于存储过程里定义的SQL语句。当设置SqlDataSource使用带参数的存储过程时，其用法与ad-hocSQL语句一样。9来看个实例，我们在Northwind数据库里创建一个名为GetProductsByCategory的存储过程，这个存储过程接受一个参数CategoryID，并返回products表中所有CategoryID列的值与CategoryID相匹配的记录。让我们开始创建吧，在服务器资源管理器中选定NORTHWND.MDF数据库。（如果你找不到服务器资源管理器，在视图菜单里选择服务器资源管理器项即可）在NORTHWND.MDF数

20、据库里，在存储过程项上点右键，选择“添加新存储过程”，输入如下语句：CREATEPROCEDUREdbo.GetProductsByCategory(CategoryIDint)ASSELECT*FROMProductsWHERECategoryID=CategoryID点保存图标（或Ctrl+S）保存新建的存储过程。你可以测试你创建的存储过程，方法是选中某个存储过程，在右键选“执行”。接下来将提示是输入参数（就本例而言，是CategoryID），最后在输出窗口显示结果。图9：当在GetProductsByCategory中给参数CategoryID赋值1时的执行情况10让我们在GridVie

21、w中显示那些种类为Beverages的产品。在页面新添加一个GridView，并绑定到一个ID为BeverageProductsDataSource的SqlDataSource控件。启用SqlDataSource控件的数据源设置向导，在“指定自定义SQL语句或存储过程”界面中，在“存储过程”下拉列表中，选GetProductsByCategory。图10：在下拉列表中，选GetProductsByCategory存储过程因为这个存储过程接受一个输入参数(CategoryID),点下一步后，向导提示我们指定参数值来源。因为我们想返回所有类型为Beverages的产品，且Beverages的Cat

22、egoryID为1，所以我们在“参数源”下拉列表里选“None”，在默认值文本框里输入111图11：通过将默认值设为1这种硬编码方式返回所有种类为Beverages的产品就像下面的编码所展示的那样，当使用存储过程的时候，SqlDataSource控件的SelectCommand属性被赋值为存储过程的名称，相应的，SelectCommandType属性被设置为StoredProcedure。这就指明了SelectCommand执行的是存储过程而不是一个ad-hocSQL语句。asp:SqlDataSourceID=BeverageProductsDataSourcerunat=serverCon

23、nectionString=SelectCommand=GetProductsByCategorySelectCommandType=StoredProcedure在浏览器里查看该页面，只有那些类型为Beverages的产品显示出来了。因为存储过程GetProductsByCategory从表Products里检索所有的列，所以在页面中显示了记录的所有列。当然，你可以在GridView控件的“编辑列”对话框中自定义要显示的列。12图12：所有Beverages类的产品被显示出来。第四步：参数化的调用SqlDataSource控件的Select()命令本教程到目前为止直接将SqlDataSour

24、ce控件绑定到GridView控件。然而，可以通过在代码里使用参数的方法来访问SqlDataSource的数据。在我们需要访问但又用不着把这些数据显示出来的情况下，这种方法很有用。与其自己动手写诸如连接数据库、指定具体的访问命令、返回结果等典型的ADO.NET代码，不如让SqlDataSource控件代劳。举一个参数化使用SqlDataSource控件数据的例子。想象一下，你的老板让你编写这样的一个页面，根据随机选择的类，在页面上显示这个类的名称，及其相关联的产品。那意味着，当我们访问这个页面时，随机从表Categories中抽取一个类，页面将该类的名字，以及属于该类的所有产品显示出来。为了达

25、到这样目的，我们需要2个SqlDataSource控件。一个从表Categories中随机的选择一个类，一个返回该类的所有产品。在第4步我们探讨如何构建一SqlDataSource控件，使其返回一个随机抽取的类记录；在第5步我们探讨如何构建一个返回所有该类型的产品的SqlDataSource控件。在ParameterizedQueries.aspx页面上添加一个SqlDataSource控件，设置其ID为RandomCategoryDataSource，对其使用如下的SQL查询：SELECTTOP1CategoryID,CategoryNameFROMCategories13ORDERBYNE

26、WID()其中“ORDERBYNEWID()”意味着对返回的记录随机(randomorder)排序(详细内容看seeUsingNEWID()toRandomlySortRecords)，“SELECTTOP1”则意味着从结果里返回第一条记录。合起来，此查询将对查询结果随机排序，并返回第一条记录的CategoryID和CategoryName列。在页面上添加一个Label控件用于显示CategoryName，设置其ID为CategoryNameLabel，并将Text属性清空。为了参数化地从SqlDataSource控件获取数据，我们需要调用它的Select()方法。Select()方法使用的是

27、类型为DataSourceSelectArguments的输入参数。当某个数据Web控件对SqlDataSource控件传入的数据企用分页或排序功能时，该类型的参数在Select()方法返回数据前，指出应该对数据进行筛选和排序。在本例中，我们并不需要对结果做排序等任何的改变，所以我们只需要参数的DataSourceSelectArguments.Empty形式。Select()方法返回什么类型的对象(object)，取决于SqlDataSource控件的DataSourceMode属性。就像上篇教程所说的那样，DataSourceMode属性可以被设置为DataSet或DataReader，D

28、ataSourceMode属性设置为哪种类型，Select()方法就返回那种类型的对象。因为SqlDataSource控件RandomCategoryDataSource的DataSourceMode属性被设置为DataSet（默认的），所有我们将与DataView对象打交道。下面的代码揭示了SqlDataSource控件RandomCategoryDataSource怎样将返回的结果转换成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)对象，当我们引用某个具体列时，需要使用列名的字符串形式(本例为“CategoryName”)。图13显示了当访问该页面时，CategoryNameLabel文本框显示的内容。当然，每次访问该页面(包括回传)时，文本框根据SqlDataSource控件CategoryDataSource

31、里随机选择的种类，显示该种类的名称。图13：显示随机选择的种类的名称注意:当SqlDataSource控件的DataSourceMode属性设置为DataReader时,从Select()方法返回的值需要转换为DataReader,我们用如下的代码来从第一行读取列CategoryName的值:if(randomCategoryReader.Read()stringcategoryName=randomCategoryReaderCategoryName.ToString();.通过SqlDataSource,我们随机的选择了一种类型,然后我们将在页面上添加一个GridView控件,显示该类型的

32、产品.注意:一般来讲,虽然用Label控件来展示类名,不如在页面使用一个绑定到SqlDataSource控件的FormView或DetailsView来显示.但用Label控件的好处在于,它便于我们调用SqlDataSource控件的带参数Select()命令.第5步:编程为参数赋值15”到目前为止，本教程例子中的参数值要么通过“硬编码（hard-coded）的方式设置，要么来自于查询字符串、Web控件等预定义参数源（pre-defined然而，SqlDataSource的参数还可以通过编程的方式设置。为了完成这个实例，我们需要一个返回属于某个特定种类的产品的SqlDataSource控件，该控件包含一个参数“CategoryID”。在Page_Load事件中，ID为RandomCategor