登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

学无止境

一点积累,与大家分享

 
 
 

日志

 
 

LINQ to DataSet  

2010-07-09 13:24:13|  分类: C#技术 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

LINQ to DataSet

The .NET native System.Data.DataSet is an in-memory representation of a set of data. It is useful to get a disconnected copy of data that comes from an external data source. Regardless of the data source, the internal representation of a DataSet follows the relational model, including tables, constraints, and relationships among the tables. In other words, you can consider the DataSet as a sort of in-memory relational database. This makes it a good target for a LINQ implementation.

Using LINQ to Load a DataSet

A DataSet can be loaded by querying a relational database. One possible way to do this is through a DataAdapter, as shown in Listing 5-27.

Listing 5-27: Loading DataSet using a DataAdapter
Image from book

DataSet ds = new DataSet("CustomerOrders");
SqlDataAdapter da = new SqlDataAdapter( QueryOrders, ConnectionString );
da.SelectCommand.Parameters.AddWithValue( "@CustomerID", "QUICK" );
da.TableMappings.Add( "Table", "Orders" );
da.TableMappings.Add( "Table1", "OrderDetails" );
da.Fill( ds );

const string ConnectionString = "Database=Northwind;Trusted_Connection=yes";
const string QueryOrders = @"
SET @CustomerID = 'QUICK'
SELECT OrderID, OrderDate, Freight, ShipName,
ShipAddress, ShipCity, ShipCountry
FROM Orders
WHERE CustomerID = @CustomerID

SELECT od.OrderID, od.UnitPrice, od.Quantity, od.Discount,
p.[ProductName]
FROM [Order Details] od
INNER JOIN Orders o
ON o.[OrderID] = od.[OrderID]
LEFT JOIN Products p
ON p.[ProductID] = od.[ProductID]
WHERE o.CustomerID = @CustomerID";


Image from book

The previous code combines two DataTable instances into one DataSet, which corresponds to the orders placed by a specific customer.

Using LINQ to Query a DataSet

A DataTable can be queried with LINQ, just as any other IEnumerable<T> list.


Note 

DataTable does not implement IEnumerable<T>. You have to call AsEnumerable, which is an extension method for DataTable, to obtain a wrapper that implements that interface.

The list is made of DataRow objects; thus, you must access DataRow member properties to get a field value. This arrangement allows the call of any DataRow member instead of using a query expression over a DataTable. You can use the Field<T> accessor method instead of using a direct cast on the result of the standard DataRow accessor (such as o["OrderDate"]). The query shown in Listing 5-28 gets the orders that show a date of 1998 or later.

Listing 5-28: Querying a DataTable with LINQ
Image from book

DataSet ds = LoadDataSetUsingDataAdapter();
DataTable orders = ds.Tables["Orders"];
DataTable orderDetails = ds.Tables["OrderDetails"];

var query =
from o in orders.AsEnumerable()
where o.Field<DateTime>( "OrderDate" ).Year >= 1998
orderby o.Field<DateTime>( "OrderDate" ) descending
select o;


Image from book


Note 

AsEnumerable and Field<T> are two custom extension methods for DataTable and DataRow types. They are defined in System.Data.DataTableExtensions and System.Data.DataRowExtensions, respectively.

When you have several DataTable objects in a DataSet, you might want to use some type of join. The query shown in Listing 5-29 calculates the total order amount for each order from 1998 to the present.

Listing 5-29: Joining two DataTable objects with LINQ
Image from book

DataSet ds = LoadDataSetUsingDataAdapter();
DataTable orders = ds.Tables["Orders"];
DataTable orderDetails = ds.Tables["OrderDetails"];

var query =
from o in orders.AsEnumerable()
join od in orderDetails.AsEnumerable()
on o.Field<int>( "OrderID" ) equals od.Field<int>( "OrderID" )

into orderLines
where o.Field<DateTime>( "OrderDate" ).Year >= 1998
orderby o.Field<DateTime>( "OrderDate" ) descending
select new { OrderID = o.Field<int>( "OrderID" ),
OrderDate = o.Field<DateTime>( "OrderDate" ),
Amount = orderLines.Sum(
od => od.Field<decimal>( "UnitPrice" )
* od.Field<short>( "Quantity" ) ) };


Image from book

In the previous examples, you specified the relationship between orders and orderDetails through the join syntax. If the DataSet contains information about existing relationships between entities, a LINQ query can take advantage of this. In Listing 5-30, we use GetChildRows to get the lines for the order details instead of explicitly joining the two tables.

Listing 5-30: Leveraging DataSet relationships in LINQ queries
Image from book

DataSet ds = LoadDataSetUsingDataAdapter();
DataTable orders = ds.Tables["Orders"];
DataTable orderDetails = ds.Tables["OrderDetails"];
ds.Relations.Add( "OrderDetails",
orders.Columns["OrderID"],
orderDetails.Columns["OrderID"]);

var query =
from o in orders.AsEnumerable()
where o.Field<DateTime>( "OrderDate" ).Year >= 1998
orderby o.Field<DateTime>( "OrderDate" ) descending
select new { OrderID = o.Field<int>( "OrderID" ),
OrderDate = o.Field<DateTime>( "OrderDate" ),
Amount = o.GetChildRows( "OrderDetails" ).Sum(
od => od.Field<decimal>( "UnitPrice" )
* od.Field<short>( "Quantity" ) ) };


Image from book

Using LINQ to Query a Typed DataSet

A typed DataSet can be queried with a simpler syntax because it is not necessary to use the Field<T> accessor and the AsEnumerable method.


Note 

If you create the typed DataSet with Visual Studio, your typed DataTable classes will be derived from the TypedTableBase<T> class, which implements the IEnumerable<T> interface. For this reason, it is not required to call AsEnumerable to get a wrapper.

The previous query, which we also used to leverage the existing DataSet relationships, can be written as shown in Listing 5-31, which uses a typed DataSet.

Listing 5-31: Querying a typed DataSet with LINQ
Image from book

var query =
from o in ds.Orders
where o.OrderDate.Year >= 1998
orderby o.OrderDate descending
select new { o.OrderID, o.OrderDate,
Amount = o.GetOrder_DetailsRows().Sum(
od => od.UnitPrice * od.Quantity ) };

Image from book

As you can see, the query syntax is much simpler and similar to the one we used earlier to query other type of entities. However, you must use a predefined schema (the typed DataSet) to query DataSet in such a way, and this prevents the use of this syntax with DataSet containing a flexible schema defined at execution time. This does not mean that you should use an untyped DataSet; it only emphasizes that untyped DataSets can be queried only with the Field<T> accessor.

Accessing Untyped DataSet Data

Accessing data in an untyped DataSet requires the use of the Field<T> and SetField<T> accessors to get and set field values, respectively. These accessors are important because a null value in a DataSet is represented by the IsNull method returning true. You should check this condition each time you access a column just to avoid potential cast errors. The use of these accessors is allowed in any DataTable or DataRow access, even outside a query expression, as you can see in Listing 5-32.

Listing 5-32: Querying an untyped DataSet with LINQ
Image from book

foreach( DataRow r in orderDetails.Rows ) {
if (r.Field<decimal>( "UnitPrice" ) < 10 ){
r.SetField<decimal>( "UnitPrice", 10 );
}
}


  评论这张
 
阅读(1560)| 评论(0)

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018