简介:
优势
无须重复代码
无须考虑数据库实现细节
无须修改代码仅需修改配置文件即实现数据库迁移,特殊用法除外
实现了数据库的简单访问
常用方法
ExecuteDataSet
LoadDataSet
UpdateDataSet
ExecuteReader
ExecuteNonQuery
ExecuteScalar
ExecuteSprocAccessor
ExecuteSqlStringAccessor
ExecuteXmlReader(sql server 专用)
CreateConnection
如何使用:
通过NuGet添加EnterpriseLibrary.Data至项目
支持sql server 数据库需要添加System.Data.SqlClient
支持Oracle 数据库需要添加System.Data.OracleClient.dll,官方不推荐使用,请至http://entlibcontrib.codeplex.com/查询相应可用驱动
添加配置文件:
<?xml version="1.0"?>
<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration .DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data" requirePermission="true"/>
</configSections>
<dataConfiguration defaultDatabase="ExampleDatabase"/>
<connectionStrings>
<add name="ExampleDatabase" connectionString="..." providerName="System.Data.SqlClient" />
<add name="AsyncExampleDatabase" connectionString="..." providerName="System.Data.SqlClient" />
</connectionStrings>
...</configuration>
创建数据库实例
DatabaseProviderFactory factory = new DatabaseProviderFactory()
Database defaultDB = factory.CreateDefault();
Database namedDB = factory.Create("ExampleDatabase");
转换为sql database实例
SqlDatabase sqlServerDB = factory.CreateDefault() as SqlDatabase;
无参获取行记录
using (IDataReader reader = namedDB.ExecuteReader("MyStoredProcName"))
{
DisplayRowValues(reader);
}
using (IDataReader reader = namedDB.ExecuteReader(CommandType.Text, "SELECT TOP 1 * FROM OrderList")){
DisplayRowValues(reader);
}
void DisplayRowValues(IDataReader reader)
{
while (reader.Read()) {
for (int i = 0; i < reader.FieldCount; i++) {
Console.WriteLine("{0} = {1}", reader.GetName(i), reader[i].ToString());
}
Console.WriteLine();
}
}
通过数组参数获取行记录
using (IDataReader reader = defaultDB.ExecuteReader("ListOrdersByState", "Colorado"))
{
DisplayRowValues(reader);
}
此时注意参数顺序必须与预期一致
通过命名参数获取行记录
string sqlStatement = "SELECT TOP 1 * FROM OrderList WHERE State LIKE @state";
using (DbCommand sqlCmd = defaultDB.GetSqlStringCommand(sqlStatement))
{
defaultDB.AddInParameter(sqlCmd, "state", DbType.String, "New York");
using (IDataReader sqlReader = defaultDB.ExecuteReader(sqlCmd))
{
DisplayRowValues(sqlReader);
}
}
string storedProcName = "ListOrdersByState";
using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand(storedProcName))
{
defaultDB.AddInParameter(sprocCmd, "state", DbType.String, "New York");
using (IDataReader sprocReader = defaultDB.ExecuteReader(sprocCmd))
{
DisplayRowValues(sprocReader);
}
}
获取对象数据
通过访问器
var productData = defaultDB.ExecuteSprocAccessor<Product>("GetProductList",
"%bike%");
var results = from productItem
in productData
where productItem.Description != null
orderby productItem.Name
select new { productItem.Name, productItem.Description };
foreach (var item in results)
{
Console.WriteLine("Product Name: {0}", item.Name);
Console.WriteLine("Description: {0}", item.Description);
Console.WriteLine();
}
创建和使用映射器(待补充实例)
获取xml数据(sql server专用)
SqlDatabase sqlServerDB = DatabaseFactory.CreateDatabase() as SqlDatabase;
string xmlQuery = "SELECT * FROM OrderList WHERE State = @state FOR XML AUTO";
using (DbCommand xmlCmd = sqlServerDB.GetSqlStringCommand(xmlQuery))
{
xmlCmd.Parameters.Add(new SqlParameter("state", "Colorado"));
using (XmlReader reader = sqlServerDB.ExecuteXmlReader(xmlCmd))
{
while (!reader.EOF) {
if (reader.IsStartElement()) {
Console.WriteLine(reader.ReadOuterXml());
}
}
}
获取单行数据
using (DbCommand sqlCmd =
defaultDB.GetSqlStringCommand("SELECT [Name] FROM States"))
{
Console.WriteLine("Result using a SQL statement: {0}", defaultDB.ExecuteScalar(sqlCmd).ToString());
}
using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand("GetStatesList"))
{
Console.WriteLine("Result using a stored procedure: {0}", defaultDB.ExecuteScalar(sprocCmd).ToString());
}
异步获取数据
通过begin和end方法异步获取数据集
DbCommand cmd = asyncDB.GetStoredProcCommand("ListOrdersSlowly");
asyncDB.AddInParameter(cmd, "state", DbType.String, "Colorado");
asyncDB.AddInParameter(cmd, "status", DbType.String, "DRAFT");
asyncDB.BeginExecuteReader(cmd, asyncResult => {
try {
using (IDataReader reader = asyncDB.EndExecuteReader(asyncResult)) {
DisplayRowValues(reader);
}
}
catch (Exception ex) {
Console.WriteLine("Error after data access completed: {0}", ex.Message);
}
},
null);
通过task异步获取数据集
static void ReadDataAsynchronouslyTask()
{
if (!SupportsAsync(asyncDB)) return;
DoReadDataAsynchronouslyTask().Wait();
}
private static async Task DoReadDataAsynchronouslyTask(){
try {
DbCommand cmd = asyncDB.GetStoredProcCommand("ListOrdersSlowly");
asyncDB.AddInParameter(cmd, "state", DbType.String, "Colorado");
asyncDB.AddInParameter(cmd, "status", DbType.String, "DRAFT");
using (var timer = new Timer(_ => Console.Write("Waiting... "))) {
timer.Change(0, 1000); using (var reader = await Task<IDataReader>.Factory .FromAsync<DbCommand>(asyncDB.BeginExecuteReader, asyncDB.EndExecuteReader, cmd, null)) {
timer.Change(Timeout.Infinite, Timeout.Infinite);
Console.WriteLine();
Console.WriteLine();
DisplayRowValues(reader); } } }
catch (Exception ex) { Console.WriteLine("Error while starting data access: {0}", ex.Message); }}
异步获取对象数据(待补充示例)
更新数据
通过更新语句
string oldDescription = "Carries 4 bikes securely; steel construction, fits 2\" receiver hitch.";
string newDescription = "Bikes tend to fall off after a few miles.";
DbCommand cmd = defaultDB.GetStoredProcCommand("UpdateProductsTable");
defaultDB.AddInParameter(cmd, "productID", DbType.Int32, 84);
defaultDB.AddInParameter(cmd, "description", DbType.String, newDescription);
if (defaultDB.ExecuteNonQuery(cmd) == 1)
{
// Update succeeded
.}
else{
Console.WriteLine("ERROR: Could not update just one row.");
}
defaultDB.SetParameterValue(cmd, "description", oldDescription);
if (defaultDB.ExecuteNonQuery(cmd) == 1)
{
// Update succeeded.
}
else{ Console.WriteLine("ERROR: Could not update just one row.");}
通过DataSet
DataSet productDataSet;
string sql = "SELECT CustomerName, CustomerPhone FROM Customers";
productDataSet = db.ExecuteDataSet(CommandType.Text, sql);
productDataSet = db.ExecuteDataSet("GetProductsByCategory", "%bike%");
DbCommand cmd = db.GetStoredProcCommand("GetProductsByCategory");db.AddInParameter(cmd, "CategoryID", DbType.Int32, 7);
productDataSet = db.ExecuteDataSet(cmd);
string selectSQL = "SELECT Id, Name, Description FROM Products WHERE Id > 90";
DataSet simpleDS = defaultDB.ExecuteDataSet(CommandType.Text, selectSQL);
DisplayTableNames(simpleDS, "ExecuteDataSet");
DataSet loadedDS = new DataSet("ProductsDataSet");
defaultDB.LoadDataSet(CommandType.Text, selectSQL, loadedDS, new string[] { "Products" });
DisplayTableNames(loadedDS, "LoadDataSet");
DataTable dt = loadedDS.Tables["Products"];
dt.Rows[0].Delete();
object[] rowData = new object[] { -1, "A New Row", "Added to the table at " + DateTime.Now.ToShortTimeString() };
rowData = dt.Rows[1].ItemArray;
rowData[2] = "A new description at " + DateTime.Now.ToShortTimeString();
dt.Rows[1].ItemArray = rowData;
DisplayRowValues(dt);
string addSQL = "INSERT INTO Products (Name, Description) " + VALUES (@name, @description)";
string updateSQL = "UPDATE Products SET Name = @name, " + "Description = @description WHERE Id = @id";
string deleteSQL = "DELETE FROM Products WHERE Id = @id";
DbCommand insertCommand = defaultDB.GetSqlStringCommand(addSQL);
defaultDB.AddInParameter(insertCommand, "name", DbType.String, "Name", DataRowVersion.Current);
defaultDB.AddInParameter(insertCommand, "description", DbType.String, "Description", DataRowVersion.Current);
DbCommand updateCommand = defaultDB.GetSqlStringCommand(updateSQL);
defaultDB.AddInParameter(updateCommand, "name", DbType.String, "Name", DataRowVersion.Current);
defaultDB.AddInParameter(updateCommand, "description", DbType.String, "Description", DataRowVersion.Current);
defaultDB.AddInParameter(updateCommand, "id", DbType.String, "Id", DataRowVersion.Original);
DbCommand deleteCommand = defaultDB.GetSqlStringCommand(deleteSQL);
defaultDB.AddInParameter(deleteCommand, "id", DbType.Int32, "Id", DataRowVersion.Original);
int rowsAffected = defaultDB.UpdateDataSet(loadedDS, "Products", insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard);
Console.WriteLine("Updated a total of {0} rows in the database.", rowsAffected);
管理数据库连接
ADO.NET提供了数据库连接池的管理办法;Data Access同样提供了管理数据库连接的方案
通过using构造reader,以确保disposed被调用;
using (IDataReader reader = db.ExecuteReader(cmd))
{
}
主动创建connection,通过using构造,实现关闭和释放
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
try {
// perform data access here }
catch { // handle any errors here }
}
使用基于事务的connection
ACID:原子性(事务中所有操作要么执行要么不执行),一致性(事务完成之后数据库必须保持一致的状态),隔离线(处于事务中间状态的数据不可访问)、持久性(事务成功之后数据是持久的)
using (DbConnection conn = db.CreateConnection()){
conn.Open(); DbTransaction trans = conn.BeginTransaction();
try { // execute commands, passing in the current transaction to each one
db.ExecuteNonQuery(cmdA, trans);
db.ExecuteNonQuery(cmdB, trans);
trans.Commit(); // commit the transaction }
catch {
trans.Rollback(); // rollback the transaction
}
}
使用分布式事务
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{ // perform data access here}