学习SqlDataSource数据源控件（一）
SqlDataSource数据源控件常用参数学习
1. ProviderName: SqlDataSource控件连接底层数据库的提供程序名称
2 .ConnectionString: SqlDataSource控件可使用该参数连接到底层数据库
3. SelectCommand: SqlDataSource控件从底层数据库中选择数据所使用的SQL命令
1
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlDataSourceDataBinding.aspx.cs" Inherits="SqlDataSourceDataBinding" %>
2
3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5<html xmlns="http://www.w3.org/1999/xhtml" >
6<head runat="server">
7 <title>无标题页</title>
8</head>
9<body>
10 <form id="form1" runat="server">
11 <div>
12 <asp:SqlDataSource ID="productCategoriesSource" ProviderName="System.Data.SqlClient" runat="server"
13 ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
14 SelectCommand="SELECT ProductCategoryID, Name FROM Production.ProductCategory"></asp:SqlDataSource>
15
16 <asp:DropDownList ID="ddlProductCategoriesSource" DataSourceID="productCategoriesSource" runat="server"
17 DataTextField="Name" DataValueField="ProductCategoryID" AutoPostBack="True"></asp:DropDownList>
18 </div>
19 </form>
20</body>
21</html>
该代码对应的web.config文件如下：<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlDataSourceDataBinding.aspx.cs" Inherits="SqlDataSourceDataBinding" %>
2
3
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">4
5
<html xmlns="http://www.w3.org/1999/xhtml" >6
<head runat="server">7
<title>无标题页</title>8
</head>9
<body>10
<form id="form1" runat="server">11
<div>12
<asp:SqlDataSource ID="productCategoriesSource" ProviderName="System.Data.SqlClient" runat="server"13
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"14
SelectCommand="SELECT ProductCategoryID, Name FROM Production.ProductCategory"></asp:SqlDataSource>15
16
<asp:DropDownList ID="ddlProductCategoriesSource" DataSourceID="productCategoriesSource" runat="server"17
DataTextField="Name" DataValueField="ProductCategoryID" AutoPostBack="True"></asp:DropDownList> 18
</div>19
</form>20
</body>21
</html>1<connectionStrings>
2<add name="AdventureWorks" connectionString="server=IT02\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated security=True"/>
3</connectionStrings>
AdventureWorks数据库可以在如下链接中下载：（微软官方网站已经不提供这个版本的下载）<connectionStrings>2
<add name="AdventureWorks" connectionString="server=IT02\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated security=True"/>3
</connectionStrings>http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004
下面的这个例子涉及到两个SqlDataSource，最后的结果是一个DropDownList中会列出四个大的产品类别，每当选择一个大的产品类别时会在GridView中显示每个大的产品类别中的所有子类别。该例子使用了SelectParameters参数来过滤数据
1<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlDataSourceDataBindingSelectparametersChapter3.aspx.cs" Inherits="SqlDataSourceDataBindingSelectparametersChapter3" %>
2
3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5<html xmlns="http://www.w3.org/1999/xhtml" >
6<head runat="server">
7 <title>Data Binding using SqlDataSource control</title>
8</head>
9<body>
10 <form id="form1" runat="server">
11 <div>
12 <asp:SqlDataSource ID="productCategoriesSource" ProviderName="System.Data.SqlClient"
13 ConnectionString="<%$ ConnectionStrings:AdventureWorks %>" runat="server"
14 SelectCommand="SELECT * FROM Production.ProductCategory"></asp:SqlDataSource>
15 <asp:DropDownList ID="ddl1stCategories" DataSourceID="productCategoriesSource" runat="server"
16 DataTextField="Name" DataValueField="ProductCategoryID" AutoPostBack="true"></asp:DropDownList>
17
18 <asp:SqlDataSource ID="productSubCategoriesSource" runat="server" ProviderName="System.Data.SqlClient"
19 ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
20 SelectCommand="SELECT ProductSubcategoryID AS CategoryID, Name FROM Production.ProductSubcategory WHERE ProductCategoryID=@ProductCategoryID">
21 <SelectParameters>
22 <asp:ControlParameter ControlID="ddl1stCategories" Name="ProductCategoryID" PropertyName="SelectedValue"/>
23 </SelectParameters>
24 </asp:SqlDataSource>
25 <asp:GridView ID="gridCategories" DataSourceID="productSubCategoriesSource" runat="server">
26 </asp:GridView>
27 </div>
28 </form>
29</body>
30</html>
请注意SelectParameters参数如何使用<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlDataSourceDataBindingSelectparametersChapter3.aspx.cs" Inherits="SqlDataSourceDataBindingSelectparametersChapter3" %>2
3
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">4
5
<html xmlns="http://www.w3.org/1999/xhtml" >6
<head runat="server">7
<title>Data Binding using SqlDataSource control</title>8
</head>9
<body>10
<form id="form1" runat="server">11
<div>12
<asp:SqlDataSource ID="productCategoriesSource" ProviderName="System.Data.SqlClient"13
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>" runat="server"14
SelectCommand="SELECT * FROM Production.ProductCategory"></asp:SqlDataSource>15
<asp:DropDownList ID="ddl1stCategories" DataSourceID="productCategoriesSource" runat="server"16
DataTextField="Name" DataValueField="ProductCategoryID" AutoPostBack="true"></asp:DropDownList>17
18
<asp:SqlDataSource ID="productSubCategoriesSource" runat="server" ProviderName="System.Data.SqlClient"19
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"20
SelectCommand="SELECT ProductSubcategoryID AS CategoryID, Name FROM Production.ProductSubcategory WHERE ProductCategoryID=@ProductCategoryID">21
<SelectParameters>22
<asp:ControlParameter ControlID="ddl1stCategories" Name="ProductCategoryID" PropertyName="SelectedValue"/>23
</SelectParameters>24
</asp:SqlDataSource>25
<asp:GridView ID="gridCategories" DataSourceID="productSubCategoriesSource" runat="server">26
</asp:GridView> 27
</div>28
</form>29
</body>30
</html>4. SelectParameters: 它本身是个集合，SqlDataSource控件的SelectCommand属性所包含的SQL命令中有可能会有一些参数，这些参数要从SelectParamenters中获取实际的值。
SelectParameters集合包括一些继承自Parameters类的参数控件，可以将任意多的参数控件添加到该集合中。然后，SqlDataSource控件就能够使用这些控件来创建动态SQL查询。
下面指出在上面代码中用到的参数控件
5. ControlParameter: 它实际是个控件，在代码中应改写成<asp:ControlParameter>，使用特定控件的值。
可以看到这个控件的ControlID指向了DropDownList控件，说明参数要从DropDownList控件中来获取，Name属性的值指代要替换SQL命令中的相应参数，PropertyName属性的值说明DropDownList当前已经选择的值。
