XML LINQ通用场景
本章包括：
n 从XML构建对象
n 从对象创建XML
n 从数据库数据创建XML
n 从CSV文件创建XML
11.1 从XML创建对象
11.1.1 目标
本节的目标是使用XML LINQ从列表11.1所示的XML文档创建一个包含这些数据的对象集合。
列表11.1 XML格式的Book数据
<?xml version="1.0" encoding="utf-8" ?>
<books>
<book>
<title>LINQ in Action</title>
<authors>
<author>
<firstName>Fabrice</firstName>
<lastName>Marguerie</lastName>
<website>http://linqinaction.net/</website>
</author>
<author>
<firstName>Steve</firstName>
<lastName>Eichert</lastName>
<webSite>http://iqueryable.com</webSite>
</author>
<author>
<firstName>Jim</firstName>
<lastName>Wooley</lastName>
<webSite> http://devauthority.com/blogs/jwooley/</webSite>
</author>
</authors>
<subject>
<name>LINQ</name>
<description>LINQ shall rule the world</description>
</subject>
<publisher>Manning</publisher>
<publicationDate>January 15, 2008</publicationDate>
<price>44.99</price>
<isbn>1933988169</isbn>
<notes>Great book!</notes>
<summary>LINQ in Action is great!</summary>
<reviews>
<review>
<user>Steve Eichert</user>
<rating>5</rating>
<comments>What can I say, I’m biased!</comments>
</review>
</reviews>
</book>
<book>
<title>Patterns of Enterprise Application Architecture</title>
…
</book>
</books>
上述的XML文档包含一系列的book信息，如果我们关注类图11.1，可以看到它们之间的关联。
图 11.1 类图
我们的目标是提取XML文件中包含的书籍数据，然后创建一个Book对象的列表。
11.1.2 实现
为了实现我们的目标，第一步要做的就是使用XElement类加载目标XML文件，创建XElement对象。如下：
XElement booksXml = XElement.Load("books.xml");
因为book的详细信息存储在<book>元素中，我们首先应该获取所有的book元素，如下：
IEnumerable<XElement> bookElements = booksXml.Elements("book");
为了创建Book对象，我们使用了对象初始化器。如列表11.2所示：
列表11.2 从XML元素创建Book对象
var books =
from bookElement in booksXml.Elements("book")
select new Book {
Title = (string)bookElement.Element("title"),
PublicationDate = (DateTime)bookElement.Element("publicationDate"),
Price = (decimal)bookElement.Element("price"),
Isbn = (string)bookElement.Element("isbn"),
Notes = (string)bookElement.Element("notes"),
Summary = (string)bookElement.Element("summary")
};
虽然提取了Book信息，但是以上代码没有提供嵌套的子节点的处理，如authors和reviews。为了提取这些信息，可以使用嵌套查询。如下所示：
... Authors =
from authorElement in bookElement.Descendants("author")
select new Author {
FirstName = (string)authorElement.Element("firstName"),
LastName = (string)authorElement.Element("lastName")
}
...
使用同样的方法可以处理review信息：
... Reviews =
from reviewElement in bookElement.Descendants("review")
select new Review {
User = new User { Name = (string)reviewElement.Element("user") },
Rating = (int)reviewElement.Element("rating"),
Comments = (string)reviewElement.Element("comments")
}
...
最终的代码如列表11.3所示，最后用ObjectDumper类往控制台输出了Book对象列表的内容。
列表11.3 从XML创建对象
using System;
using System.Linq;
using System.Xml.Linq;
using LinqInAction.LinqBooks.Common;
namespace Chapter11.CreateObjectsFromXml
{
class Program
{
static void Main(string[] args)
{
XElement booksXml = XElement.Load("books.xml");
var books =
from bookElement in booksXml.Elements("book")
select new Book {
Title = (string)bookElement.Element("title"),
Publisher = new Publisher {
Name = (string)bookElement.Element("publisher") },
PublicationDate = (DateTime)bookElement.Element("publicationDate"),
Price = (decimal)bookElement.Element("price"),
Isbn = (string)bookElement.Element("isbn"),
Notes = (string)bookElement.Element("notes"),
Summary = (string)bookElement.Element("summary"),
Authors =
from authorElement in bookElement.Descendants("author")
select new Author {
FirstName = (string)authorElement.Element("firstName"),
LastName = (string)authorElement.Element("lastName")},
Reviews =
from reviewElement in bookElement.Descendants("review")
select new Review {
User = new User {Name = (string)reviewElement.Element("user")},
Rating = (int)reviewElement.Element("rating"),
Comments = (string)reviewElement.Element("comments")
}
};
ObjectDumper.Write(books);
}
}
}
由此可见，使用XML LINQ，我们可以快速的将XML数据转换为对象列表。
11.2 从对象创建XML
(本节使用VB.NET介绍，所以没有翻译)
11.3 从数据库创建XML
11.3.1 目标
我们的目标是，使用数据库的book数据创建表示book信息的XML。列表11.8显示了我们创建的XML。
列表11.8 创建的目标XML格式
<books>
<book>
<title>LINQ in Action</title>
<authors>
<author>
<firstName>Steve</firstName>
<lastName>Eichert</lastName>
<webSite>http://iqueryable.com</webSite>
</author>
<author>
<firstName>Fabrice</firstName>
<lastName>Marguerie</lastName>
<webSite>http://linqinaction.net/</website>
</author>
<author>
<firstName>Jim</firstName>
<lastName>Wooley</lastName>
<webSite>http://devauthority.com/blogs/jwooley</website>
</author>
</authors>
<subject>
<name>LINQ</name>
<description>LINQ shall rule the world</description>
</subject>
<publisher>Manning</publisher>
<publicationDate>January, 2008</publicationDate>
<price>43.99</price>
<isbn>1933988169</isbn>
<notes>Great book!</notes>
<summary>LINQ in Action is great!</summary>
<reviews>
<review>
<user>Steve Eichert</user>
<rating>5</rating>
<comments>What can I say, I’m biased!</comments>
<review>
<reviews>
</book>
</books>
为了获得这种XML数据，我们需要从图11.3所示的数据库中获取数据。
图11.3 数据库模式
11.3.2 实现
你可以使用设计器或者SqlMetal工具生成映射信息，为了保持简单，我们使用SqlMetal.exe命令行来生成类映射文件。
CMD>Sqlmetal /server:localhost /database:LinqInAction /pluralize
/namespace:LinqInAction.LinqToSql /code:LinqInAction.cs
对SqlMetal工具的介绍超出了本章的范围，你可以通过直接输入sqlmetal.exe并执行来查看它所有的用法。
usage: sqlmetal [options] [<input file>]
options:
/server:<name> database server name
/database:<name> database catalog on server
/user:<name> login user id
/password:<name> login password
/views extract database views
/functions extract database functions
/sprocs extract stored procedures
/xml[:file] output as xml
/code[:file] output as source code
/map[:file] generate xml mapping file instead of attributes
/language:xxx language for source code (vb,csharp)
/namespace:<name> namespace used for source code
/pluralize auto-pluralize table names
/dataAttributes auto-generate DataObjectField and Precision attributes
/timeout:<seconds> timeout value in seconds to use for database commands
产生了SQL LINQ代码，我们就可以编写创建XML树的代码了。本章的示例中提供了一个插件，包含在项目PasteXmlAsLinq中（如何集成插件到VisualStudio中，请参阅MSDN）。安装之后在”编辑”菜单中有一个Paste XML as XElement的命令，复制要拷贝的XML，然后选择此命令，就会出现产生所选XML内容的XML LINQ代码。如列表11.9所示。
列表11.9 使用PasteXmlAsLinq插件产生的代码
XElement xml =
new XElement("books",
new XElement("book",
new XElement("title", "LINQ in Action"),
new XElement("authors",
new XElement("author",
new XElement("firstName", "Steve"),
new XElement("lastName", "Eichert"),
new XElement("webSite", "http://iqueryable.com")
),
new XElement("author",
new XElement("firstName", "Fabrice"),
new XElement("lastName", "Marguerie"),
new XElement("website", "http://linqinaction.net/")
),
new XElement("author",
new XElement("firstName", "Jim"),
new XElement("lastName", "Wooley"),
new XElement("website", "http://devauthority.com/blogs/jwooley/")
)
),
new XElement("subject",
new XElement("name", "LINQ"),
new XElement("description", "LINQ shall rule the world")
),
new XElement("publisher", "Manning"),
|
D |
new XElement("price", "43.99"),
new XElement("isbn", "1933988169"),
new XElement("notes", "Great book!"),
new XElement("summary", "LINQ in Action is great!"),
new XElement("reviews",
new XElement("review",
new XElement("user", "Steve Eichert"),
new XElement("rating", "5"),
new XElement("comments", "What can I say, I'm biased!")
)
)
|
E |
);
在列表中，有四个部分将被LINQ表达式取代：author元素，subject元素，publisher元素，reviews元素。第一个查询表达式返回了数据库中所有的book数据。返回的book数据按照title排序，如下：
LinqInActionContext ctx = new LinqInActionContext();
var books = from book in ctx.Books orderby book.Title
select book;
为了实现创建XML这个目标，我们需要仔细查看SqlMetal产生的类图11.4，观察我们有哪些关系可用。
图 11.4 SQLMetal工具从LinqBooks数据库产生类图
从图11.4中可以看出，我们可以使用的关系有book.BookAuthors, book.Subject, book.Reviews和book.Publisher。使用这些关系已经完全能够完成我们的任务了。列表11.10显示了对这些关系的查询。
列表 11.10 使用LINQ语句获取必要的数据
var authors =
from bookAuthor in book.BookAuthors
orderby bookAuthor.AuthorOrder
select bookAuthor.Author;
var subject = book.Subject;
var publisher = book.Publisher;
var reviews =
from review in book.Reviews orderby review.Rating
select review;
现在，我们拥有了所有用于构建XML的数据，下一步就是使用这些查询替换由插件自动生成的代码中需要替换的部分。如列表11.11所示：
列表11.11 创建XML树的完整
using System;
using System.Linq;
using System.Xml.Linq;
using System.Data.Linq;
using LinqInAction.LinqToSql;
namespace Chapter11.CreateXmlFromDatabase
{
class Program
{
static void Main(string[] args)
{
LinqInActionDataContext ctx = new LinqInActionDataContext();
XElement xml = new XElement("books", from book in ctx.Books
orderby book.Title
select new XElement("book",
new XElement("title", book.Title),
new XElement("authors",
from bookAuthor in book.BookAuthors
orderby bookAuthor.AuthorOrder
select new XElement("author",
new XElement("firstName", bookAuthor.Author.FirstName),
new XElement("lastName", bookAuthor.Author.LastName),
new XElement("webSite", bookAuthor.Author.WebSite)
)
),
new XElement("subject",
new XElement("name", book.Subject.Name),
new XElement("description", book.Subject.Description)
),
new XElement("publisher", book.Publisher.Name),
new XElement("publicationDate", book.PubDate),
new XElement("price", book.Price),
new XElement("isbn", book.Isbn),
new XElement("notes", book.Notes),
new XElement("summary", book.Summary),
new XElement("reviews",
from review in book.Reviews orderby review.Rating
select new XElement("review",
new XElement("user", review.User.Name),
new XElement("rating", review.Rating),
new XElement("comments", review.Comments)
)
)
)
);
Console.WriteLine(xml.ToString());
}
}
}
11.4 过滤和合并数据库数据及XML数据
11.4.1 目标
Amazon商业web服务提供了一系列用来从Amazon目录获取book数据的API，我们将使用它们提供的Lookup服务来查询这些数据，然后将查询结果与查询我们本地数据库的book信息混合，并将结果显示在屏幕上。
以下是本示例的输出：
Book: CLR via C#, Second Edition
-------------------------- Rating: 5
--------------------------
Jeffrey Richter is my hero, he really is. This guy is simply amazing. I just cant imagine how he pulls it off - the toughest topics explained in the clearest manner.Moreover, he has achieved this feat over and over again. Any book he has written is testimony for this.
<br />In his books, you would find information where you wouldnt find in any
other place. You would also find information you can find elsewhere, but
not as clear as his. He has the advantage of working closely with Microsoft
and consulting with the .NET team, but I would say he would be a great author and teacher even without that advantage.
<br />As about this book, it should not be your first C# book. I suggest you
get beginner's C# book first (if you dont know any C#), I suggest Jesse Liberty's book, and then come to this book. You would get a tremendous advantage over people who havent read this book and your understanding of the building blocks of .NET platform would be in depth. His chapters on Threading alone is worth the price of the book. This book is an absolute pleasure to read, just like any other book from Richter. Grab your copy today!
If there really is a 5 star book,this one is it.
<br />Nobody writes like Richter, nobody.
-------------------------- Rating: 5
--------------------------
I echo the positive reviews below. If you already know the .Net platform
fairly well and want to understand the internals of the CLR, this is the best place to start. This edition is as good or better than his 1.0/1.1 version, Applied .Net Framework Programming.
11.4.2 实现
LINQ提供了很多合并XML和关系数据的方法，我们要做的就是合并从Amazon web服务提供的数据和从本地数据库获取的数据进行合并。虽然，这两种数据有着完全不同的格式和来源，LINQ允许我们在这两种数据相同的地方进行连接，在这里，它们的连接点是ISBN。
首先需要从Amazon web服务加载XML数据。加载的方法在前面的章节中已经讲过。列表11.12显示了从Amazon web服务获取book数据的代码。
列表11.12 从Amazon’s web 服务加载XML
string requestUrl =
"http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&" +
"AWSAccessKeyId={AccessKey}&" +
"Operation=ItemLookup&" +
"ItemId=0735621632&" +
"ResponseGroup=Reviews";
XElement amazonReviews = XElement.Load(requestUrl);
图11.5显示了请求返回的数据格式。在图中，reviews信息包含在<CustomerReviews>元素中，我们需要的是<Rating> 和 <Content>元素。
图 11.5 Amazon.com 返回的XML
使用下面的代码可以获取本地数据库中的book数据：
LinqInActionDataContext ctx = new LinqInActionDataContext();
var books = ctx.Books;
接下来，使用join操作符可以将这两种数据连接起来。GroupJoin需要两个序列有匹配的键值，在本例中，来自amazon的键值为Reviews.Elements(ns + "Items").Elements(ns + "Item")，来自本地数据的键值为Book对象的Isbn属性。通过匹配这两个键值，我们可以将这两种数据匹配起来。如列表11.13所示：
列表11.13 使用一个查询合并XML和关系数据
using System;
using System.Linq;
using System.Xml.Linq;
using System.Data.Linq;
using LinqInAction.LinqToSql;
namespace Chapter11.MixXmlAndRelationalData
{
class Program
{
public const string AmazonAccessID = "15QN7X0P65HR0X975T02";
static void Main(string[] args)
{
string requestUrl =
"http://webservices.amazon.com/onca/xml?Service=AWSECommerceService" +
"&AWSAccessKeyId=" + AmazonAccessID +
"&Operation=ItemLookup&" +
"ItemId=0735621632&" +
"ResponseGroup=Reviews";
XNamespace ns =
"http://webservices.amazon.com/AWSECommerceService/2005-10-05";
LinqInActionDataContext ctx = new LinqInActionDataContext();
XElement amazonReviews = XElement.Load(requestUrl);
var results =
from bookElement in amazonReviews.Element(ns + "Items").Elements(ns + "Item")
join book in ctx.Books on
(string)bookElement.Element(ns + "ASIN") equals book.Isbn.Trim()
select new {
Title = book.Title,
Reviews =
from reviewElement in bookElement.Descendants(ns + "Review")
orderby (int)reviewElement.Element(ns + "Rating") descending
select new Review {
Rating = (int)reviewElement.Element(ns + "Rating"),
Comments = (string)reviewElement.Element(ns + "Content")
}
};
string seperator = "--------------------------";
foreach (var item in results)
{
Console.WriteLine("Book: " + item.Title);
foreach (var review in item.Reviews)
{
Console.WriteLine(seperator + "/r/nRating: " +
review.Rating + "/r/n" + seperator + "/r/n" + review.Comments);
}
}
}
}
}
通过使用LINQ提供了连接操作符支持，我们可以构建跨越多个数据源的连接操作。下一节我们要讲的是使用XML数据更新数据库。
11.5 读取XML和更新数据库
11.5.1 目标
本节，我们将为示例添加一个令人激动的特性。我们允许用户可以直接将从Amazon获取的数据添加到数据库中。图11.6显示了本特性的GUI。
图11.6 Amazon UI
在图中，用户点击搜索按钮就可以从amazon获取匹配搜索关键字的book数据。如果用户选择了合适的Subject然后点击Import按钮，那么book数据将会被导入到数据库中。
11.5.2 实现
这次，我们对amazon web服务查询的url如列表11.14所示。
列表 11.14 请求book数据的Amazon.com REST URL
http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&
AWSAccessKeyId={AccessKey}&
Operation=ItemSearch& SearchIndex=Books& Keywords={keywords}&
ResponseGroup=Large
以下代码获取了匹配关键字的从amazon web服务获取的book数据：
string requestUrl =
"http://webservices.amazon.com/onca/xml?Service=AWSECommerceService" +
"&AWSAccessKeyId=" + AmazonAccessKey +
"&Operation=ItemSearch" +
"&SearchIndex=Books" +
"&Keywords=" + keywords.Text +
"&ResponseGroup=Large";
XElement amazonXml = XElement.Load(requestUrl);
以上代码返回了如下数据，如列表11.15所示：
列表11.15 表示一本book 的Amazon XML
<?xml version="1.0" encoding="utf-8"?>
<ItemSearchResponse
xmlns="http://webservices.amazon.com/AWSECommerceService/2005-10-05">
<Items>
<TotalResults>1389</TotalResults>
<TotalPages>139</TotalPages>
<Item>
<ASIN>0977326403</ASIN>
<DetailPageURL>http://www.amazon.com/gp/redirect.html%3FASIN=0977326403%26tag=ws%26lcode=xm2%26cID=2025%26ccmID=165953%26location=/o/ASIN/0977326403%253FSubscriptionId=15QN7X0P65HR0X975T02</DetailPageURL>
<SalesRank>49</SalesRank>
<ItemAttributes>
<Author>Jim Collins</Author>
<Binding>Paperback</Binding>
<DeweyDecimalNumber>658.048</DeweyDecimalNumber>
<EAN>9780977326402</EAN>
<ISBN>0977326403</ISBN>
<Label>Collins</Label>
<ListPrice>
<Amount>1195</Amount>
<CurrencyCode>USD</CurrencyCode>
<FormattedPrice>$11.95</FormattedPrice>
</ListPrice>
<Manufacturer>Collins</Manufacturer>
<NumberOfItems>1</NumberOfItems>
<NumberOfPages>42</NumberOfPages>
<PackageDimensions>
<Height Units="hundredths-inches">13</Height>
<Length Units="hundredths-inches">916</Length>
<Weight Units="hundredths-pounds">21</Weight>
<Width Units="hundredths-inches">642</Width>
</PackageDimensions>
<ProductGroup>Book</ProductGroup>
<PublicationDate>2005-11-30</PublicationDate>
<Publisher>Collins</Publisher>
<Studio>Collins</Studio>
<Title>Good to Great and the Social Sectors:
A Monograph to Accompany Good to Great</Title>
</ItemAttributes>
</Item>
</Items>
</ItemSearchResponse>
之后，为search按钮添加点击事件：
private void searchButton_Click(object sender, EventArgs e)
{
}
在search按钮的点击事件中，我们读取了XML中的title和isbn元素的内容并显示的界面中，如列表11.16所示：
列表11.16 点击search按钮的时候，查询并显示book数据
private void searchButton_Click(object sender, EventArgs e)
{
string requestUrl = String.Format(Amazon.AmazonSearchRestUrl, keywords.Text);
amazonXml = XElement.Load(requestUrl);
var books =
from amazonItem in amazonXml.Descendants(ns + "Item")
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
};
bookBindingSource.DataSource = books;
}
现在用户可以通过复选框进行选择感兴趣的书籍，然后点击Import按钮，完成book的导入工作。当用户点击import按钮的时候，首先我们需要获取用户选择的book数据。可以通过检查DataGridViewCell. EditedFormattedValue属性来判断用户是否选择了复选框。如下所示：
var selectedBooks =
from row in bookDataGridView.Rows.OfType<DataGridViewRow>()
where ((bool) row.Cells[0].EditedFormattedValue) == true
select (Book) row.DataBoundItem;
上面的代码使用了OfType<>扩展方法，OfType<>方法允许我们将一个DataGridViewRowCollection对象转换为一个包含DataGridViewRow的IEnumerable对象。之后就可以使用LINQ对其进行查询。为了将XML内容存储到数据库，需要知道Book元素中的信息如何与数据库的Book信息对应。表11.2显示了它们之间的映射关系。
表 11.2 Book数据XML和数据库的映射
| Book attribute | Location in XML | Location in database |
| Title | /Items/Item/ItemAttributes/Title | Book.Title |
| ISBN | /Items/Item/ItemAttributes/ISBN | Book.ISBN |
| Publication Date | /Items/Item/ItemAttributes/PublicationDate | Book.PubDate |
| Price | /Items/Item/ItemAttributes/ListPrice/FormattedPrice | Book.Price |
| Publisher | /Items/Item/ItemAttributes/Publisher | Publisher.Name |
| Author(s) | /Items/Item/ItemAttributes/Author(repeated for each author) | Author.FirstName & Author.LastName |
有了以上元素，我们就可以将Book元素的信息转换为一个Book对象。如列表11.17所示：
列表11.17 读取XML并转换为Book对象
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice")
};
对于上述查询，还应该加入过滤功能，应该只选择那些用户选择的book。并且与book元素信息进行连接以获取完整的book信息。如列表11.18所示：
列表11.18 连接book对象和book元素
var selectedBooks =
from row in bookDataGridView.Rows
where ((bool)row.Cells[0].EditedFormattedValue) == true
select (Book)row.DataBoundItem;
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
join selectedBook in selectedBooks
on (string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "ISBN")
equals selectedBook.Isbn
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice"))
};
现在Book对象已经包含了那些简单属性，但是获取并更新Publishers 和Authors却不是一件简单的事情，如果这些Publisher和Author不存在，那么我们应该将它们加入数据库，如果它们已经存在于数据库中，那么就应该使用现有的数据，不应再添加。问题是，我们怎么样用一个查询解决这个问题呢？
首先需要检查包含在/Item/ItemAttributes/Publisher元素中的信息是否已经存在于Publisher表中（通过Name判断）。如果存在，那么我们会读取此Publisher信息并将其赋予当前Book对象。如果此Publisher不存在，那么我们将创建一个新的Publisher对象，然后赋予Book对象。为了完成这个工作，我们使用了GroupJoin和DefaultIfEmpty操作符。
from amazonItem in amazonXml.Descendants(ns + "Item")
join p in ctx.Publishers on
(string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "Publisher")
equals p.Name into publishers
from existingPublisher in publishers.DefaultIfEmpty()
...
通过将GroupJoin和DefaultIfEmpty操作符合并使用，我们可以实现一个左外连接。之后就可以使用数据库已有的Publisher数据了。
...
Publisher=(existingPublisher ??
new Publisher {
ID = Guid.Empty,
Name = (string)attributes.Element(ns + "Publisher")
}
)
...
完整的代码如列表11.19所示：
列表 11.19 连接Publisher 表
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
join selectedBook in selectedBooks on
(string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "ISBN")
equals selectedBook.Isbn
join p in ctx.Publishers on
(string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "Publisher")
equals p.Name into publishers
let existingPublisher = publishers.SingleOrDefault()
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
Publisher = (existingPublisher ??new Publisher {
ID = Guid.Empty,
Name = (string)attributes.Element(ns + "Publisher")
}),
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice"))
};
对于Author，我们采取与Publisher一样的方法，不过因为一个Book可以有多个Author，所以相关的查询表达式可能会比较复杂，所以我们将处理Author的逻辑隔离到一个方法中。XML中一本Book可能对应多个Author元素，我们需要确保不会重复创建那些已经在数据中的Author。如图11.7所示，我们还需要将每个Author元素映射为一个Author对象，Book和Author之间的关系信息包含在属性BookAuthor中。
图11.7 Author 和Book 类图
列表11.20显示了从XML中获取Author的代码。
列表11.20 在XML中查询Book的Author
var bookAuthors =
from authorElement in amazonItem.Elements(ns + “Author”)
join a in ctx.Authors on
(string)authorElement equals a.FirstName + " " + a.LastName into authors
let existingAuthor = authors.SingleOrDefault()
let nameParts = ((string)authorElement).Split(' ')
select new BookAuthor { Author = existingAuthor ??
new Author {
ID = Guid.Empty,
FirstName = nameParts[0],
LastName = nameParts[1]
}
};
列表11.21将以上查询封装到方法GetAuthors中，该方法创建并返回了一个EntitySet<BookAuthor>，这允许我们可以将返回结果赋予Book对象。
列表11.21 从XML获取Author信息，并将其转换为一个EntitySet
private EntitySet<BookAuthor> GetAuthors(IEnumerable<XElement>authorElements)
{
LinqInActionDataContext ctx = new LinqInActionDataContext();
var bookAuthors =
from authorElement in authorElements
join a in ctx.Authors on
(string)author equals a.FirstName + " " + a.LastName into authors
from existingAuthor in authors.DefaultIfEmpty()
let nameParts = ((string)authorElement).Split(' ')
select new BookAuthor {
Author = existingAuthor ?? new Author {
ID = Guid.Empty,
FirstName = nameParts[0],
LastName = nameParts[1]
}
};
EntitySet<BookAuthor>set = new EntitySet<BookAuthor>();
set.AddRange(bookAuthors);
return set;
}
将GetAuthors()方法集成到整个查询中，得到如下查询：
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
join selectedBook in selectedBooks on
(string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "ISBN")
equals selectedBook.Isbn join p in ctx.Publishers on
(string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "Publisher")
equals p.Name into publishers
from existingPublisher in publishers.DefaultIfEmpty()
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book {
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
Publisher = (existingPublisher ??
new Publisher {
ID = Guid.Empty,
Name = (string)attributes.Element(ns + "Publisher")
}),
Subject = (Subject)categoryComboBox.SelectedItem,
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice")),
BookAuthors = GetAuthors(attributes.Elements(ns + "Author"))
};
现在我们已经完成了需要导入的整个book列表。将book数据保存在数据库中只需要很少的工作。只需要调用ctx.Books.InsertAllOnSubmit(booksToImport)，然后再调用SubmitChanges()。
ctx.Books.InsertAllOnSubmit(booksToImport);
try
{
ctx.SubmitChanges();
MessageBox.Show(booksToImport.Count() + " books imported.");
}
catch(Exception ex)
{
MessageBox.Show("An error occurred while attempting to import the selected books. " + Environment.NewLine + Environment.NewLine + ex.Message);
}
现在我们已经完成了所有的工作，全部代码显示在列表11.22中。
列表11.22 导入来自Amazon.com的Book的完整代码
using System;
using System.Collections.Generic;
using System.Data.Linq; using System.Drawing; using System.Linq;
using System.Windows.Forms;
using System.Xml.Linq;
using Chapter11.Common;
using LinqInAction.LinqToSql;
namespace Chapter11.WinForms
{
public partial class ImportForm : Form
{
LinqInActionDataContext ctx;
XNamespace ns =
"http://webservices.amazon.com/AWSECommerceService/2005-10-05";
XElement amazonXml;
public ImportForm()
{
InitializeComponent();
this.Load += new EventHandler(ImportForm_Load);
ctx = new LinqInActionDataContext();
}
void ImportForm_Load(object sender, EventArgs e)
{
subjectComboBox.DataSource = ctx.Subjects.ToList();
}
void searchButton_Click(object sender, EventArgs e)
{
string requestUrl =
String.Format(Amazon.AmazonSearchRestUrl, keywords.Text);
amazonXml = XElement.Load(requestUrl);
var books =
from result in amazonXml.Descendants(ns + "Item")
let attributes = result.Element(ns + "ItemAttributes")
select new Book
{
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
};
bookBindingSource.DataSource = books;
var rows =
from row in bookDataGridView.Rows.OfType<DataGridViewRow>()
let dataBoundBook = ((Book)row.DataBoundItem)
join book in ctx.Books
on dataBoundBook.Isbn equals book.Isbn.Trim()
select row;
foreach (DataGridViewRow row in rows)
{
row.DefaultCellStyle.BackColor = Color.LightGray;
row.Cells[0].ReadOnly = true;
row.Cells[1].Value = "** Already Exists ** - " + row.Cells[1].Value;
}
}
void importButton_Click(object sender, EventArgs e)
{
var selectedBooks =
from row in bookDataGridView.Rows.OfType<DataGridViewRow>()
where ((bool)row.Cells[0].EditedFormattedValue) == true
select (Book)row.DataBoundItem;
using (var newContext = new LinqInActionDataContext())
{
var booksToImport =
from amazonItem in amazonXml.Descendants(ns + "Item")
join selectedBook in selectedBooks
on (string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "ISBN")
equals selectedBook.Isbn join p in newContext.Publishers
on ((string)amazonItem.Element(ns + "ItemAttributes").Element(ns + "Publisher")).ToUpperInvariant()
equals p.Name.Trim().ToUpperInvariant() into publishers
from existingPublisher in publishers.DefaultIfEmpty()
let attributes = amazonItem.Element(ns + "ItemAttributes")
select new Book
{
ID = Guid.NewGuid(),
Isbn = (string)attributes.Element(ns + "ISBN"),
Title = (string)attributes.Element(ns + "Title"),
Publisher = (existingPublisher ??new Publisher{
ID = Guid.NewGuid(),
Name = (string)attributes.Element(ns + "Publisher")
}),
Subject = (Subject)subjectComboBox.SelectedItem,
PubDate = (DateTime)attributes.Element(ns + "PublicationDate"),
Price = ParsePrice(attributes.Element(ns + "ListPrice")),
BookAuthors = GetAuthors(attributes.Elements(ns + "Author"))
};
newContext.Subjects.Attach((Subject)subjectComboBox.SelectedItem);
newContext.Books.InsertAllOnSubmit(booksToImport);
try
{
newContext.SubmitChanges();
MessageBox.Show(booksToImport.Count() + " books imported.");
}
catch (Exception ex)
{
MessageBox.Show("An error occurred while attempting to import the selected books." + Environment.NewLine + Environment.NewLine + ex.Message);
}
}
}
private EntitySet<BookAuthor> GetAuthors(IEnumerable<XElement>
authorElements)
{
var bookAuthors =
from authorElement in authorElements join author in ctx.Authors
on (string)authorElement
equals author.FirstName + " " + author.LastName into authors
from existingAuthor in authors.DefaultIfEmpty()
let nameParts = ((string)authorElement).Split(' ')
select new BookAuthor
{
ID = Guid.NewGuid(),
Author = existingAuthor ??
new Author
{
ID = Guid.NewGuid(),
FirstName = nameParts[0],
LastName = nameParts[1]
}
};
EntitySet<BookAuthor> set = new EntitySet<BookAuthor>();
set.AddRange(bookAuthors);
return set;
}
private decimal ParsePrice(XElement priceElement)
{
return Convert.ToDecimal( ((string)priceElement.Element(ns + "FormattedPrice")).Replace("$", String.Empty));
}
}
}
11.6 转换文本文件到XML
11.6.1 目标
在本节中，我们的目标是把一个文本文件转换为层次结构的XML文档。如列表11.23所示，文本文件包含以下的Book信息：ISBN，标题，作者，出版社，出版日期和价格。
列表11.23 包含Book信息的CSV文件
01 0735621632,CLR via C#,Jeffrey Richter,Microsoft Press,02-22-2006,59.99
02 0321127420, Patterns Of Enterprise Application Architecture, Martin Fowler, Addison-Wesley,11-05-2002,54.99
03 0321200683,Enterprise Integration Patterns,Gregor Hohpe,Addison-Wesley, 10-10-2003,54.99
04 0321125215,Domain-Driven Design,Eric Evans,Addison-Wesley,08-22-2003, 54.99
05 1932394613,Ajax In Action,Dave Crane;Eric Pascarello;Darren James, Manning Publications,10-01-2005,44.95
转换之后的XML文档格式如列表11.24所示：
列表11.24 转换后的XML输出
<?xml version="1.0" encoding="utf-8" ?>
<books>
<book>
<title>CLR via C#</title>
<authors>
<author>
<firstName>Jeffrey</firstName>
<lastName>Richter</lastName>
</author>
</authors>
<publisher>Microsoft Press</publisher>
<publicationDate>02-22-2006</publicationDate>
<price>59.99</price>
<isbn>0735621632</isbn>
</book>
<book>
<title>Patterns Of Enterprise Application Architecture</title>
<authors>
<author>
<firstName>Martin</firstName>
<lastName>Fowler</lastName>
</author>
</authors>
<publisher>Addison-Wesley Professional</publisher>
<publicationDate>11-05-2002</publicationDate>
<price>54.99</price>
<isbn>0321127420</isbn>
</book>
...
</books>
11.6.2 实现
要完成构建XML文档的工作，首先需要从文本文件中分离数据，如下所示：
from line in File.ReadAllLines("books.txt")
let items = line.Split(',')
一旦我们有了可以使用的数据，就可以用它们来构建XML元素，如列表11.25所示：
列表11.25 读取文本文件构建XElement 对象
var booksXml = new XElement("books",
from line in File.ReadAllLines("books.txt")
let items = line.Split(',')
select new XElement("book",
new XElement("title", items[1]),
new XElement("publisher", items[3]),
new XElement("publicationDate", items[4]),
new XElement("price", items[5]),
new XElement("isbn", items[0])
);
因为一个Book有多个作者，作者是用分号分割的，如下：
Dave Crane;Eric Pascarello;Darren James
所以可以使用Split方法将作者分离到一个数组中，再对每个author使用空格Split，就可以得到表示作者的XElement对象。如下：
...
new XElement("authors",
from authorFullName in items[2].Split(';')
let authorNameParts = authorFullName.Split(' ')
select new XElement("author",
new XElement("firstName", authorNameParts[0]),
new XElement("lastName", authorNameParts[1])
)
)
...
完整的代码如列表11.26所示：
列表11.26 平面文件转换为XML的实现
using System;
using System.Linq;
using System.Xml.Linq;
using System.IO;
namespace Chapter11.FlatFileToXml
{
class Program
{
static void Main(string[] args)
{
XElement xml = new XElement("books",
from line in File.ReadAllLines("books.txt")
where !line.StartsWith("#")
let items = line.Split(',')
select new XElement("book",
new XElement("title", items[1]),
new XElement("authors",
from authorFullName in items[2].Split(';')
let authorNameParts = authorFullName.Split(' ')
select new XElement("author",
new XElement("firstName", authorNameParts[0]),
new XElement("lastName", authorNameParts[1])
)
),
new XElement("publisher", items[3]),
new XElement("publicationDate", items[4]),
new XElement("price", items[5]),
new XElement("isbn", items[0])
)
);
Console.WriteLine(xml);
}
}
}
扫一扫
1883
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
