如何使用SqlDataSource数据源控件来选择记录
一、SqlDataSource数据源控件如何执行存储过程
首先创建两个存储过程:
第一个存储过程返回产品所属的子类别
CREATE PROCEDURE [dbo].[GetProductSubCategories]
AS
SELECT ProductSubcategoryID, Name FROM Production.ProductSubcategory
ORDER BY ProductSubcategoryID
第二个存储过程接收具体的子类别ID,返回属于该子类别ID的所有产品
CREATE PROCEDURE [dbo].[GetProductsByCategoryID]
@ProductSubcategoryID int
AS
SELECT ProductID, Name, ProductNumber FROM Production.Product
WHERE ProductSubcategoryID=@ProductSubcategoryID ORDER BY ProductID
具体代码如下:
![](/Images/OutliningIndicators/ContractedBlock.gif)
StoredProcedure代码
1![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlDataSourceDataBindingStoredProcedureChapter4.aspx.cs" Inherits="SqlDataSourceDataBindingStoredProcedureChapter4" %>
2![](/Images/OutliningIndicators/None.gif)
3
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4![](/Images/OutliningIndicators/None.gif)
5
<html xmlns="http://www.w3.org/1999/xhtml" >
6
<head runat="server">
7
<title>Executing a Stored Procedure using SqlDataSource Control</title>
8
</head>
9
<body>
10
<form id="form1" runat="server">
11
<div>
12
<asp:SqlDataSource ID="categoriesSource" runat="server" ProviderName="System.Data.SqlClient"
13
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
14
SelectCommand="GetProductSubCategories" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
15
Categories:
16
<asp:DropDownList ID="lstCategories" DataSourceID="categoriesSource" runat="server"
17
DataValueField="ProductSubcategoryID" DataTextField="Name" AutoPostBack="true"></asp:DropDownList>
18
19
<asp:SqlDataSource ID="productsSource" runat="server" ProviderName="System.Data.SqlClient"
20
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
21
SelectCommand="GetProductsByCategoryID" SelectCommandType="StoredProcedure">
22
<SelectParameters>
23
<asp:ControlParameter ControlID="lstCategories" Name="ProductSubcategoryID" PropertyName="SelectedValue"/>
24
</SelectParameters>
25
</asp:SqlDataSource>
26
<asp:GridView DataSourceID="productsSource" runat="server"
27
HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="true"
28
HeaderStyle-Backcolor="blue" HeaderStyle-ForeColor="white"></asp:GridView>
29
</div>
30
</form>
31
</body>
32
</html>
注意其中使用了SqlDataSource中的SelectCommandType属性。该属性默认设置为"Text",这里设置为"StoredProcedure",这样就能调用存储过程来完成查询。
二、使用DataReader来读取数据
SqlDataSource控件要么返回DataSet,要么返回DataReader。默认为DataSet,以前的例子就是使用DataSet。DataSet可以使GridView控件内置的排序和分页功能得到体现,也可以利用SqlDataSource的缓存功能。但是DataReader在当只需要向前访问数据时,它要比使用DataSet具有更高的性能,然而这种情况就无法使用排序功能了。
如何设置呢?需要在SqlDataSource控件的DataSourceMode属性中设置。
请看如下代码:
![](/Images/OutliningIndicators/ContractedBlock.gif)
DataReader代码
1![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlDataSourceDataBindingDataReaderChapter4.aspx.cs" Inherits="SqlDataSourceDataBindingDataReaderChapter4" %>
2![](/Images/OutliningIndicators/None.gif)
3
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4![](/Images/OutliningIndicators/None.gif)
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="categoriesSource" runat="server" ProviderName="System.Data.SqlClient"
13
DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
14
SelectCommand="SELECT ProductSubcategoryID, Name FROM Production.ProductSubcategory"></asp:SqlDataSource>
15
Categories:
16
<asp:DropDownList ID="lstCategories" DataSourceID="categoriesSource" runat="server"
17
DataValueField="ProductSubcategoryID" DataTextField="Name" AutoPostBack="true"></asp:DropDownList>
18
19
<asp:SqlDataSource ID="productsSource" runat="server" ProviderName="System.Data.SqlClient"
20
DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
21
SelectCommand="SELECT ProductID, Name, ProductNumber, StandardCost FROM Production.Product WHERE ProductSubcategoryID=@ProductSubcategoryID">
22
<SelectParameters>
23
<asp:ControlParameter ControlID="lstCategories" Name="ProductSubcategoryID" PropertyName="SelectedValue"/>
24
</SelectParameters>
25
</asp:SqlDataSource>
26
<asp:GridView ID="GridView1" DataSourceID="productsSource" runat="server"
27
HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="true"
28
HeaderStyle-Backcolor="blue" HeaderStyle-ForeColor="white"></asp:GridView>
29
</div>
30
</form>
31
</body>
32
</html>
注意:DataSourceMode属性设置为DataReader。
三、从QueryString中获取数据
具体代码如下:
![](/Images/OutliningIndicators/ContractedBlock.gif)
QueryString代码
1![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlDataSourceDataBindingQueryStringChapter4.aspx.cs" Inherits="SqlDataSourceDataBindingQueryStringChapter4" %>
2![](/Images/OutliningIndicators/None.gif)
3
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4![](/Images/OutliningIndicators/None.gif)
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="productsSource" runat="server" ProviderName="System.Data.SqlClient"
13
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
14
SelectCommand="GetProductsByCategoryID" SelectCommandType="StoredProcedure">
15
<SelectParameters>
16
<asp:QueryStringParameter Name="ProductSubcategoryID" QueryStringField="CategoryID"/>
17
</SelectParameters>
18
</asp:SqlDataSource>
19
<asp:GridView ID="GridView1" DataSourceID="productsSource" runat="server"
20
HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="true"
21
HeaderStyle-Backcolor="blue" HeaderStyle-ForeColor="white"></asp:GridView>
22
</div>
23
</form>
24
</body>
25
</html>
上面的代码将QueryString值作为参数传递给存储过程。
<asp:QueryStringParameter>控件中的Name和QueryStringField属性,分别设置存储过程参数和查询字符串名称。
如何触发呢?要在该页面运行后,浏览器地址栏中自行手动添加查询字符串"?CategoryID=N",N在这里是数字,1,2,3,4...
这个QueryStringParameter参数控件仍旧是SelectParameters集合中包含的一种参数控件。
四、利用SqlDataSource控件处理返回参数值
首先创建存储过程
1
CREATE PROCEDURE [dbo].[GetDepartments]
2
AS
3
SELECT DepartmentID, Name, GroupName, ModifiedDate FROM HumanResources.Department
4
DECLARE @Count smallint
5
SELECT @Count = Count(DepartmentID) FROM HumanResources.Department
6
RETURN @Count
具体代码如下:
![](/Images/OutliningIndicators/ContractedBlock.gif)
Return Parameters代码
1![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Page Language="C#" AutoEventWireup="true"%>
2![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Import Namespace="System.Text" %>
3![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<%
@ Import Namespace="System.Data.SqlClient" %>
4![](/Images/OutliningIndicators/None.gif)
5
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6![](/Images/OutliningIndicators/None.gif)
7![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<script runat="server">![](https://www.cnblogs.com/Images/dot.gif)
8![](/Images/OutliningIndicators/InBlock.gif)
9
protected void deptSource_Selected(object sender, SqlDataSourceStatusEventArgs e)
10![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
11
StringBuilder builder = new StringBuilder();
12
foreach( SqlParameter param in e.Command.Parameters)
13![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
14
builder.Append(Server.HtmlDecode(param.ParameterName) + "=");
15
builder.Append(Server.HtmlDecode(param.Value.ToString()) + "(");
16
builder.Append(Server.HtmlDecode(param.Value.GetType().ToString()) + ")<br>");
17
}
18
lblResult.Text = "Return Parameter :" + builder.ToString();
19
}
20
</script>
21![](/Images/OutliningIndicators/None.gif)
22
<html xmlns="http://www.w3.org/1999/xhtml" >
23
<head runat="server">
24
<title>Handling Return Parameters from a Stored Procedure</title>
25
</head>
26
<body>
27
<form id="form1" runat="server">
28
<div>
29
<asp:GridView ID="deptView" runat="server" AllowPaging="True" AllowSorting="True"
30
DataSourceID="deptSource" DataKeyNames="DepartmentID" AutoGenerateColumns="False"
31
HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="true" HeaderStyle-BackColor="blue" HeaderStyle-ForeColor="white">
32
<Columns>
33
<asp:BoundField ReadOnly="True" HeaderText="Department ID" DataField="DepartmentID" SortExpression="DepartmentID"/>
34
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
35
<asp:BoundField DataField="GroupName" HeaderText="Group Name" SortExpression="GroupName" />
36
<asp:BoundField DataField="ModifiedDate" HeaderText="ModifiedDate" SortExpression="ModifiedDate" />
37
</Columns>
38
</asp:GridView>
39
<asp:SqlDataSource ID="deptSource" runat="server" ProviderName="System.Data.SqlClient"
40
ConnectionString="<%$ ConnectionStrings:AdventureWorks %>"
41
SelectCommand="GetDepartments" SelectCommandType="StoredProcedure" OnSelected="deptSource_Selected">
42
<SelectParameters>
43
<asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" />
44
</SelectParameters>
45
</asp:SqlDataSource>
46
<asp:Label runat="server" Font-Bold="true" ID="lblResult"></asp:Label>
47
</div>
48
</form>
49
</body>
50
</html>
SelectParameter集合除了以前谈到的输入参数外,还可以实现双向的参数。通过Parameter类的Direction属性来设置,该属性值如下:Input、Output、InputOutput和ReturnValue。
为了在数据操作完成之后获取这些参数值,利用适当的发送操作事件,例如这里的Selected(还可以是Updated、Inserted、Deleted),从传递给这些事件的事件参数中获取参数值。上面的例子中通过SqlDataSourceStatusEventArgs的Command属性来获取参数值。
注意:<asp:Parameter>中的Direction属性设置为"ReturnValue",Name属性的值相当于给通过查询返回的参数重新命名,这里将返回参数@Count设置为@ReturnValue。