1. 概述
本章介绍 数据库、Json和Xml、web services 三种介质上的数据操作。
2. 主要内容
2.1 数据库
① 建立连接
.net平台中的数据连接类都继承自DbConnection。DbConnection实现了IDisposable接口。
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Execute operations against the database
} // Connection is automatically closed.
使用ConnectionStringBuilder 可以构建连接字符串。但是把连接字符串放到配置文件中是更好的选择。
var sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = @”(localdb)v11.0”;
sqlConnectionStringBuilder.InitialCatalog = “ProgrammingInCSharp”;
string connectionString = sqlConnectionStringBuilder.ToString();
.net平台使用连接池来优化数据库连接操作。
② 读取数据 : 可以使用async/await来异步操作。
public async Task SelectDataFromTable()
{
string connectionString = ConfigurationManager.
ConnectionStrings[“ProgrammingInCSharpConnection”].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(“SELECT * FROM People”, connection);
await connection.OpenAsync();
SqlDataReader dataReader = await command.ExecuteReaderAsync();
while (await dataReader.ReadAsync())
{
string formatStringWithMiddleName = “Person ({0}) is named {1} {2} {3}”;
string formatStringWithoutMiddleName = “Person ({0}) is named {1} {3}”;
if ((dataReader[“middlename”] == null))
{
Console.WriteLine(formatStringWithoutMiddleName,
dataReader[“id”],
dataReader[“firstname”],
dataReader[“lastname”]);
}
else
{
Console.WriteLine(formatStringWithMiddleName,
dataReader[“id”],
dataReader[“firstname”],
dataReader[“middlename”],
dataReader[“lastname”]);
}
}
dataReader.Close();
}
}
使用DataReader的NextResultAsync，可以处理多个结果集。
public async Task SelectMultipleResultSets()
{
string connectionString = ConfigurationManager.
ConnectionStrings[“ProgrammingInCSharpConnection”].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(“SELECT * FROM People;
SELECT TOP 1 * FROM People ORDER BY LastName”, connection);
await connection.OpenAsync();
SqlDataReader dataReader = await command.ExecuteReaderAsync();
await ReadQueryResults(dataReader);
await dataReader.NextResultAsync(); // Move to the next result set
await ReadQueryResults(dataReader);
dataReader.Close();
}
}
private static async Task ReadQueryResults(SqlDataReader dataReader)
{
while (await dataReader.ReadAsync())
{
string formatStringWithMiddleName = “Person ({0}) is named {1} {2} {3}”;
string formatStringWithoutMiddleName = “Person ({0}) is named {1} {3}”;
if ((dataReader[“middlename”] == null))
{
Console.WriteLine(formatStringWithoutMiddleName,
dataReader[“id”],
dataReader[“firstname”],
dataReader[“lastname”]);
}
else
{
Console.WriteLine(formatStringWithMiddleName,
dataReader[“id”],
dataReader[“firstname”],
dataReader[“middlename”],
dataReader[“lastname”]);
}
}
}
③ 更新数据
public async Task UpdateRows()
{
string connectionString = ConfigurationManager.
ConnectionStrings[“ProgrammingInCSharpConnection”].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(
“UPDATE People SET FirstName=’John’”,
connection);
await connection.OpenAsync();
int numberOfUpdatedRows = await command.ExecuteNonQueryAsync();
Console.WriteLine(“Updated {0} rows”, numberOfUpdatedRows);
}
}
④ 使用参数
public async Task InsertRowWithParameterizedQuery()
{
string connectionString = ConfigurationManager.
ConnectionStrings[“ProgrammingInCSharpConnection”].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(
“INSERT INTO People([FirstName], [LastName], [MiddleName]) VALUES(@
firstName, @lastName, @middleName)”,
connection);
await connection.OpenAsync();
command.Parameters.AddWithValue(“@firstName”, “John”);
command.Parameters.AddWithValue(“@lastName”, “Doe”);
command.Parameters.AddWithValue(“@middleName”, “Little”);
int numberOfInsertedRows = await command.ExecuteNonQueryAsync();
Console.WriteLine(“Inserted {0} rows”, numberOfInsertedRows);
}
}
⑤ 使用事务
string connectionString = ConfigurationManager.
ConnectionStrings[“ProgrammingInCSharpConnection”].ConnectionString;
using (TransactionScope transactionScope = new TransactionScope())
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command1 = new SqlCommand(
“INSERT INTO People ([FirstName], [LastName], [MiddleInitial])
VALUES(‘John’, ‘Doe’, null)”,
connection);
SqlCommand command2 = new SqlCommand(
“INSERT INTO People ([FirstName], [LastName], [MiddleInitial])
VALUES(‘Jane’, ‘Doe’, null)”,
connection);
command1.ExecuteNonQuery();
command2.ExecuteNonQuery();
}
transactionScope.Complete();
}
*TransactionScope支持三种类型：Required, RequiresNew, Supress.
⑥ 使用 Object Relational Mapper(ORM)
数据记录和表不符合面向对象的结构，手动写数据对象，创建和维护成本都很高。为了解决这个问题，出现了ORM。
微软提供的ORM工具是 Entiry Framework(EF)。EF支持三种模式：数据库先行、模型先行、代码先行。
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
public class PeopleContext : DbContext
{
public IDbSet<Person> People { get; set; }
}
using (PeopleContext ctx = new PeopleContext())
{
ctx.People.Add(new Person() { Id = 1, Name = “John Doe” });
ctx.SaveChanges();
}
using (PeopleContext ctx = new PeopleContext())
{
Person person = ctx.People.SingleOrDefault(p => p.Id == 1);
Console.WriteLine(person.Name);
}
2.2 使用 web services
创建一个Windows Comunication Service(WCF)服务
WCF的ABC模型：
① Address：服务公开的终结点，实际调用的物理地址。
② Binding：配置了通信的协议和传输类型。
③ Contract：合约，定义了服务公开的操作。
2.3 使用XML
System.Xml下比较重要的类包括：
① XmlReader：速度较快
string xml = @”<?xml version=””1.0”” encoding=””utf-8”” ?>
<people>
<person firstname=””john”” lastname=””doe””>
<contactdetails>
<emailaddress>john@unknown.com</emailaddress>
</contactdetails>
</person>
<person firstname=””jane”” lastname=””doe””>
<contactdetails>
<emailaddress>jane@unknown.com</emailaddress>
<phonenumber>001122334455</phonenumber>
</contactdetails>
</person>
</people>”;
using (StringReader stringReader = new StringReader(xml))
{
using (XmlReader xmlReader = XmlReader.Create(stringReader,
new XmlReaderSettings() { IgnoreWhitespace = true }))
{
xmlReader.MoveToContent();
xmlReader.ReadStartElement(“People”);
string firstName = xmlReader.GetAttribute(“firstName”);
string lastName = xmlReader.GetAttribute(“lastName”);
Console.WriteLine(“Person: {0} {1}”, firstName, lastName);
xmlReader.ReadStartElement(“Person”);
Console.WriteLine(“ContactDetails”);
xmlReader.ReadStartElement(“ContactDetails”);
string emailAddress = xmlReader.ReadString();
Console.WriteLine(“Email address: {0}”, emailAddress);
}
}
② XmlWriter：速度较快
StringWriter stream = new StringWriter();
using (XmlWriter writer = XmlWriter.Create(
stream,
new XmlWriterSettings() { Indent = true }))
{
writer.WriteStartDocument();
writer.WriteStartElement(“People”);
writer.WriteStartElement(“Person”);
writer.WriteAttributeString(“firstName”, “John”);
writer.WriteAttributeString(“lastName”, “Doe”);
writer.WriteStartElement(“ContactDetails”);
writer.WriteElementString(“EmailAddress”, “john@unknown.com”);
writer.WriteEndElement();
writer.WriteEndElement();
writer.Flush();
}
Console.WriteLine(stream.ToString());
③ XmlDocument：灵活性高
XmlDocument doc = new XmlDocument();
doc.LoadXml(xml);
XmlNodeList nodes = doc.GetElementsByTagName(“Person”);
// Output the names of the people in the document
foreach (XmlNode node in nodes)
{
string firstName = node.Attributes[“firstName”].Value;
string lastName = node.Attributes[“lastName”].Value;
Console.WriteLine(“Name: {0} {1}”, firstName, lastName);
}
// Start creating a new node
XmlNode newNode = doc.CreateNode(XmlNodeType.Element, “Person”, “”);
XmlAttribute firstNameAttribute = doc.CreateAttribute(“firstName”);
firstNameAttribute.Value = “Foo”;
XmlAttribute lastNameAttribute = doc.CreateAttribute(“lastName”);
lastNameAttribute.Value = “Bar”;
newNode.Attributes.Append(firstNameAttribute);
newNode.Attributes.Append(lastNameAttribute);
doc.DocumentElement.AppendChild(newNode);
Console.WriteLine(“Modified xml...”);
doc.Save(Console.Out);
//Displays:
//Name: john doe
//Name: jane doe
//Modified xml...
//<?xml version=”1.0” encoding=”ibm850”?>
//<people>
// <person firstname=”john” lastname=”doe”>
// <contactdetails>
// <emailaddress>john@unknown.com</emailaddress>
// </contactdetails>
// </person>
// <person firstname=”jane” lastname=”doe”>
// <contactdetails>
// <emailaddress>jane@unknown.com</emailaddress>
// <phonenumber>001122334455</phonenumber>
// </contactdetails>
// </person>
// <person firstname=”Foo” lastname=”Bar” />
//</people>
*XmlDocument实现了IXPathNavigable接口，可以使用XPathNavigator 对象遍历xml.
XmlDocument doc = new XmlDocument();
doc.LoadXml(xml); // Can be found in Listing 4-43
XPathNavigator nav = doc.CreateNavigator();
string query = “//People/Person[@firstName=’Jane’]”;
XPathNodeIterator iterator = nav.Select(query);
Console.WriteLine(iterator.Count); // Displays 1
while(iterator.MoveNext())
{
string firstName = iterator.Current.GetAttribute(“firstName”,””);
string lastName = iterator.Current.GetAttribute(“lastName”,””);
Console.WriteLine(“Name: {0} {1}”, firstName, lastName);
}
2.4 使用Json
可以使用第三方组件Newtonsoft.Json来处理Json数据。
3. 总结
① ADO.NET提供了provider模型来实现连接不同类型的数据源。
② 使用DbConnection对象来创建数据库连接。
③ 使用参数化的查询方式，可以避免sql注入。
④ 通过创建代理，可以使你的程序对外发布 web service 接口。
⑤ .net平台提供了处理Xml的相关类(XmlReader, XmlWriter, XPathNavigator, XmlDocument)。
⑥ 处理Json数据可以使用第三方的Newtonsoft.Json。