今天在看了LinqDataSource控件后,试着想实现一个Demo,在Northwind数据库Customers表中,查找指定字母开始的CustomerID信息。
效果图:
实现思路:
1.下拉列表绑定CustomerID中的首字母
2.GridView中绑定对应的首字母的CustomerId的Customers信息
第一个看似简单的操作,却用了我一个多小时。
Customer ID:
<asp:DropDownList ID="ddlCustomerId" runat="server" DataSourceID="ldsCustomer1" DataTextField="CustomerID" />
<asp:LinqDataSource runat="server" ID="ldsCustomer1"
ContextTypeName="Entities.Northwind.NorthwindEntities" EntityTypeName=""
TableName="Customers" Select="new (CustomerID)" />
(其中的Entities.Northwind.NorthwindEntities是在另一个项目中Ado.net实体数据模型的名称)这样出来的是CustomerID的列表,需要首字母,就必须使用Substring,尝试
Customer ID:
<asp:DropDownList ID="ddlCustomerId" runat="server" DataSourceID="ldsCustomer1" DataTextField="Letter" />
<asp:LinqDataSource runat="server" ID="ldsCustomer1"
ContextTypeName="Entities.Northwind.NorthwindEntities" EntityTypeName=""
TableName="Customers" Select="new (CustomerID.Substring(0,1)as Letter)" />
居然成功了!但是还是不理想,有很多相同的字母,必须过滤相同项。思路两种Distinct和Group By。
但是LinqDataSource中好像没找到Distinct的内容,尝试Select="new (CustomerID as Letter).Distinct()"不成功,尝试了多种后,放弃Distinct!
然后试验Group By,GroupBy="CustomerID.Substring(0,1)",报错!尝试多种之后,用Sql Server Profile监视SQL,也没有发现什么有价值的东西,反而感觉生成SQL语句很冗余。看看网上人家的案例,很少。但是看到一个key关键字,多种尝试后发现应该这么写:
Customer ID:
<asp:DropDownList ID="ddlCustomerId" runat="server" DataSourceID="ldsCustomer1" DataTextField="Letter" />
<asp:LinqDataSource runat="server" ID="ldsCustomer1"
ContextTypeName="Entities.Northwind.NorthwindEntities" EntityTypeName=""
TableName="Customers" Select="new (Key as Letter)"
GroupBy="CustomerID.Substring(0,1)" OrderGroupsBy="Key ASC"
/>
走了不少弯路,贴出来给后来者借鉴。
完整aspx内容:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Second.aspx.cs" Inherits="CH02.DataSources.LinqDataSource.Second" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>
Customer ID:
<asp:DropDownList ID="ddlCustomerId" runat="server" DataSourceID="ldsCustomer1" DataTextField="Letter" />
<asp:LinqDataSource runat="server" ID="ldsCustomer1"
ContextTypeName="Entities.Northwind.NorthwindEntities" EntityTypeName=""
TableName="Customers" Select="new (Key as Letter)"
GroupBy="CustomerID.Substring(0,1)" OrderGroupsBy="Key ASC"
/>
</p>
<p>
<asp:Button Text="Query" runat="server" />
</p>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="LinqDataSource1" >
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True"
SortExpression="CustomerID" />
<asp:BoundField DataField="ContactName" HeaderText="ContactName"
ReadOnly="True" SortExpression="ContactName" />
<asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle"
ReadOnly="True" SortExpression="ContactTitle" />
<asp:BoundField DataField="Region" HeaderText="Region" ReadOnly="True"
SortExpression="Region" />
</Columns>
</asp:GridView>
<asp:LinqDataSource runat="server" ID="LinqDataSource1"
ContextTypeName="Entities.Northwind.NorthwindEntities" TableName="Customers"
Select="new (CustomerID, ContactName, ContactTitle, Region)"
Where="CustomerID.Substring(0,1)=@Letter">
<WhereParameters>
<asp:FormParameter DefaultValue="" Name="Letter"
FormField="ddlCustomerId" Type="String" />
</WhereParameters>
</asp:LinqDataSource>
</div>
</form>
</body>
</html>