导航

Enterprise Library2.0数据库常用操作

Posted on 2011-04-12 15:43  毅无涯  阅读(265)  评论(0编辑  收藏  举报

今天学习了Enterprise Library2.0的Data Access Application Block,Data Access Application Block提供了通用的数据访问的功能,随着2.0版本的推出有了很大变化。俺就多写了对SQL和ACCESS数据库自由切换的一些代码出来共享。先看完原文再接俺的代码吧。

一.改进

在DAAB1.1里面我们知道Database方法返回或者创建一个DBCommandWrapper对象,而在DAAB2.0里面移除了DBCommandWrapper类,用ADO.NET2.0里面的DBCommand类代替实现类似的功能,这样使得DAAB跟我们的.NET类库的结合更加紧密,回忆一下我们在1.1里面用DBCommandWrapper来访问数据时的代码:

二.使用示例

Database db = DatabaseFactory.CreateDatabase();

DBCommandWrapper dbCommand = db.GetStoredProcCommandWrapper("GetProductsByCategory");

dbCommand.AddInParameter("CategoryID", DbType.Int32, Category);

DataSet productDataSet = db.ExecuteDataSet(dbCommand);

而用了新的DBCommand类之后则变成了:

Database db = DatabaseFactory.CreateDatabase();

DbCommand dbCommand = db.GetStoredProcCommand("GetProductsByCategory");

db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);

DataSet productDataSet = db.ExecuteDataSet(dbCommand);

数据库连接字符串在我们基于数据库的开发永远是少不了的,但是在DAAB1.1下,它所使用的字符串跟我们在.NET类库中使用的连接字符串却是不能共享的,它们分别保存在不同的位置。而在2.0的Data Access Application Block使用了ADO.NET2.0里面<connectionStrings>配置区,这样带来的一个好处是连接字符串可以在Application Block和自定义的.NET类之间共享使用该配置区,如:

<connectionStrings>
<add
name="DataAccessQuickStart"
             providerName="System.Data.SqlClient"
             connectionString="server=(local)\SQLEXPRESS;database=EntLibQuickStarts;Integrated Security=true" />
</connectionStrings>

在.NET2.0下,泛型编程已经成为了一个核心,而2.0版的DAAB中也新增了一个GenericDatabase对象。DAAB中虽然已经包含了SqlDatabase和OrcaleDatabase,但是如果我们需要使用其他的像DB2等数据库时,就需要用到GenericDatabase,它可以用于任何.NET类库中的数据提供者,包括OdbcProvider和OleDbProvider。

DAAB2.0的配置非常简单,主要有以下几方面的配置:

配置连接字符串

配置默认数据库

添加相关的命名空间:

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
使用Data Access Application Block进行数据的读取和操作,一般分为三步:

1.创建Database对象

2.提供命令参数,如果需要的话

3.执行命令

下面分别看一下DataAccessQuickStart中提供的一些例子:

执行静态的SQL语句

public string GetCustomerList()
{
// 创建Database对象
Database db = DatabaseFactory.CreateDatabase();
// 使用SQL语句创建DbCommand对象
string sqlCommand = "Select CustomerID, Name, Address, City, Country, PostalCode " +
    "From Customers";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
StringBuilder readerData = new StringBuilder();
// 调用ExecuteReader方法
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
    while (dataReader.Read())
    {
        // Get the value of the 'Name' column in the DataReader
        readerData.Append(dataReader["Name"]);
        readerData.Append(Environment.NewLine);
    }
}
return readerData.ToString();
}
执行存储过程并传递参数,返回DataSet

public DataSet GetProductsInCategory(int Category)
{
    // Create the Database object, using the default database service. The
    // default database service is determined through configuration.
    Database db = DatabaseFactory.CreateDatabase();
    string sqlCommand = "GetProductsByCategory";
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
    // Retrieve products from the specified category.
    db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);
    // DataSet that will hold the returned results        
    DataSet productsDataSet = null;
    productsDataSet = db.ExecuteDataSet(dbCommand);
    // Note: connection was closed by ExecuteDataSet method call
    return productsDataSet;
}
利用DataSet更新数据

public int UpdateProducts()
{
    // Create the Database object, using the default database service. The
    // default database service is determined through configuration.
    Database db = DatabaseFactory.CreateDatabase();
    DataSet productsDataSet = new DataSet();
    string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " +
        "From Products";
    DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
    string productsTable = "Products";
    // Retrieve the initial data
    db.LoadDataSet(dbCommand, productsDataSet, productsTable);
    // Get the table that will be modified
    DataTable table = productsDataSet.Tables[productsTable];
    // Add a new product to existing DataSet
    DataRow addedRow = table.Rows.Add(new object[] {DBNull.Value, "New product", 11, 25});
    // Modify an existing product
    table.Rows[0]["ProductName"] = "Modified product";
    // Establish our Insert, Delete, and Update commands
    DbCommand insertCommand = db.GetStoredProcCommand("AddProduct");
    db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
    db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
    db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);
    DbCommand deleteCommand = db.GetStoredProcCommand("DeleteProduct");
    db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
    DbCommand updateCommand = db.GetStoredProcCommand("UpdateProduct");
    db.AddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);
    db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
    db.AddInParameter(updateCommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current);
    // Submit the DataSet, capturing the number of rows that were affected
    int rowsAffected = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand,
                                        deleteCommand, UpdateBehavior.Standard);
    return rowsAffected;
}
通过ID获取记录详细信息

public string GetProductDetails(int productID)
{
    // Create the Database object, using the default database service. The
    // default database service is determined through configuration.
    Database db = DatabaseFactory.CreateDatabase();
    string sqlCommand = "GetProductDetails";
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
    // Add paramters
    // Input parameters can specify the input value
    db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productID);
    // Output parameters specify the size of the return data
    db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50);
    db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8);
    db.ExecuteNonQuery(dbCommand);
    // Row of data is captured via output parameters
    string results = string.Format(CultureInfo.CurrentCulture, "{0}, {1}, {2:C} ",
                                   db.GetParameterValue(dbCommand, "ProductID"),
                                   db.GetParameterValue(dbCommand, "ProductName"),
                                   db.GetParameterValue(dbCommand, "UnitPrice"));
    return results;
}
以XML格式返回数据

public string GetProductList()
{
    // Use a named database instance that refers to a SQL Server database.
    SqlDatabase dbSQL = DatabaseFactory.CreateDatabase() as SqlDatabase;
    // Use "FOR XML AUTO" to have SQL return XML data
    string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " +
        "From Products FOR XML AUTO";
    DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand);
    XmlReader productsReader = null;
    StringBuilder productList = new StringBuilder();
    try
    {
        productsReader = dbSQL.ExecuteXmlReader(dbCommand);
        // Iterate through the XmlReader and put the data into our results.
        while (!productsReader.EOF)
        {
            if (productsReader.IsStartElement())
            {
                productList.Append(productsReader.ReadOuterXml());
                productList.Append(Environment.NewLine);
            }
        }
    }
    finally
    {
      // Close the Reader.
      if (productsReader != null)
      {
          productsReader.Close();
      }
      // Explicitly close the connection. The connection is not closed
      // when the XmlReader is closed.
      if (dbCommand.Connection != null)
      {
        dbCommand.Connection.Close();
      }  
    }
    return productList.ToString();
}
使用事务

public bool Transfer(int transactionAmount, int sourceAccount, int destinationAccount)
{
    bool result = false;
    // Create the Database object, using the default database service. The
    // default database service is determined through configuration.
    Database db = DatabaseFactory.CreateDatabase();
    // Two operations, one to credit an account, and one to debit another
    // account.
    string sqlCommand = "CreditAccount";
    DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
    db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount);
    db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount);
    sqlCommand = "DebitAccount";
    DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
    db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount);
    db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount);
    using (DbConnection connection = db.CreateConnection())
    {
        connection.Open();
        DbTransaction transaction = connection.BeginTransaction();
        try
        {
            // Credit the first account
            db.ExecuteNonQuery(creditCommand, transaction);
            // Debit the second account
            db.ExecuteNonQuery(debitCommand, transaction);
            // Commit the transaction
            transaction.Commit();
            result = true;
        }
        catch
        {
            // Rollback transaction
            transaction.Rollback();
        }
        connection.Close();
        return result;
    }
}
三.常见功能

1.创建Database对象

创建一个默认的Database对象

Database dbSvc = DatabaseFactory.CreateDatabase();
默认的数据库在配置文件中:

<dataConfiguration defaultDatabase="DataAccessQuickStart" />
创建一个实例Database对象

// Use a named database instance that refers to an arbitrary database type,
// which is determined by configuration information.
Database myDb = DatabaseFactory.CreateDatabase("DataAccessQuickStart");
创建一个具体的类型的数据库对象

// Create a SQL database.
SqlDatabase dbSQL = DatabaseFactory.CreateDatabase("DataAccessQuickStart") as SqlDatabase;
2.创建DbCommand对象

静态的SQL语句创建一个DbCommand

Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "Select CustomerID, LastName, FirstName From Customers";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
存储过程创建一个DbCommand

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("GetProductsByCategory");
3.管理对象

当连接对象打开后,不需要再次连接

Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "Select ProductID, ProductName From Products";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
// No need to open the connection; just make the call.
DataSet customerDataSet = db.ExecuteDataSet(dbCommand);
使用Using及早释放对象

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetSqlStringCommand("Select Name, Address From Customers");
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
// Process results
}
4.参数处理

Database类提供了如下的方法,用于参数的处理:

AddParameter. 传递参数给存储过程
AddInParameter. 传递输入参数给存储过程
AddOutParameter. 传递输出参数给存储过程
GetParameterValue. 得到指定参数的值
SetParameterValue. 设定参数值

使用示例如下:

Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "GetProductDetails";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "ProductID", DbType.Int32, 5);
db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50);
db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8);

Database db = DatabaseFactory.CreateDatabase();
DbCommand insertCommand = db.GetStoredProcCommand("AddProduct");
db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);
db.AddInParameter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);
四.使用场景

DAAB2.0是对ADO.NET2.0的补充,它允许你使用相同的数据访问代码来支持不同的数据库,您通过改变配置文件就在不同的数据库之间切换。目前虽然只提供SQLServer和Oracle的支持,但是可以通过GenericDatabase和ADO.NET 2.0下的DbProviderFactory对象来增加对其他数据库的支持。如果想要编写出来的数据库访问程序具有更好的移植性,则DAAB2.0是一个不错的选择,但是如果您想要针对特定数据库的特性进行编程,就要用ADO.NET了。

参考:Enterprise Libaray –January 2006帮助文档及QuickStart

好,看到这里俺应该基本懂得使用了,俺就动手试一下SQL和ACCESS数据库自由切换的方法,因俺平时的习惯是使用vb.net写东西,所以只写出vb.net的代码出来,有兴趣的自己改成C#好了,看以下html代码:
<%...@ Page Language="VB" AutoEventWireup="false" CodeFile="sql.aspx.vb" Inherits="sql" %>

<!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>web3.cn——SQL、Access数据库自由切换</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" SortExpression="id" >
<HeaderStyle BackColor="Silver" />
</asp:BoundField>
<asp:BoundField DataField="provinceID" HeaderText="provinceID" SortExpression="provinceID" >
<HeaderStyle BackColor="Silver" />
</asp:BoundField>
<asp:BoundField DataField="province" HeaderText="provinceID" SortExpression="province" >
<HeaderStyle BackColor="Silver" />
</asp:BoundField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
vb.net代码:
Imports System.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports system.Data.Common
Imports System.Data.Odbc

Partial Class sql_accessClass sql_access
Inherits System.Web.UI.Page
Dim sys As New WebService
Protected Sub Page_Load()Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindGrid()
End If
End Sub

Sub BindGrid()Sub BindGrid()
Dim dv As DataView
dv = GetList_Access().DefaultView
GridView1.DataSource = dv
GridView1.DataBind()
End Sub

'列表
Public Function GetList_SQL()Function GetList_SQL() As DataTable
Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "select * FROM province ORDER BY id desc"

'要对数据源执行的 SQL 语句或存储过程。
Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

Return db.ExecuteDataSet(dbCommand).Tables(0)
End Function

'列表
Public Function GetList_Access()Function GetList_Access() As DataTable

Dim db As Database = New GenericDatabase("Driver={Microsoft Access Driver (*.mdb)};Dbq=D:vs2005dbdb.mdb;Uid=sa;Pwd=sa;", OdbcFactory.Instance)
Dim sqlCommand As String = "select * FROM province ORDER BY id desc"

'要对数据源执行的 SQL 语句或存储过程。
Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

Return db.ExecuteDataSet(dbCommand).Tables(0)
End Function

以上代码不多,应该明白了吧,呵呵,只要把“dv = GetList_Access().DefaultView”换成“dv = GetList_SQL().DefaultView”即可换成了SQL的数据库了,简单吧。这里只给出一个思路,就看大家封装起来成更加简单易用的咯。