Enterprise Library2.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来访问数常常的代码:

ACCESS 1Database db = DatabaseFactory.CreateDatabase();
ACCESS 2
ACCESS 3DBCommandWrapper dbCommand = db.GetStoredProcCommandWrapper(“GetProductsByCategory”);
ACCESS 4
ACCESS 5dbCommand.AddInParameter(“CategoryID”, DbType.Int32, Category);
ACCESS 6
ACCESS 7DataSet productDataSet = db.ExecuteDataSet(dbCommand);

比方因此了初的DBCommand类之后则成了:

ACCESS 8Database db = DatabaseFactory.CreateDatabase();
ACCESS 9
ACCESS 10DbCommand dbCommand = db.GetStoredProcCommand(“GetProductsByCategory”); 
ACCESS 11
ACCESS 12db.AddInParameter(dbCommand, “CategoryID”, DbType.Int32, Category);
ACCESS 13
ACCESS 14DataSet productDataSet = db.ExecuteDataSet(dbCommand);

数据库连接字符串在我们根据数据库的支出永远是必不可少的,但是当DAAB1.1产,它所利用的字符串跟咱们以.NET类库中使用的连天字符串却是无克共享的,它们各自保存在不同的职。而当2.0的Data
Access Application
Block使用了ADO.NET2.0里面<connectionStrings>配置区,这样带来的一个功利是接二连三字符串可以于Application
Block和自定义的.NET类中共享利用该配置区,如:

ACCESS 15<connectionStrings>
ACCESS 16        <add
ACCESS 17            name=”DataAccessQuickStart” 
ACCESS 18            providerName=”System.Data.SqlClient”
ACCESS 19            connectionString=”server=(local)\SQLEXPRESS;database=EntLibQuickStarts;Integrated Security=true” />
ACCESS 20</connectionStrings>

在.NET2.0下蛋,泛型编程已经改成了一个基本,而2.0版本的DAAB中吗陡增了一个GenericDatabase对象。DAAB中则早已包含了SqlDatabase和OrcaleDatabase,但是要是我们得运用其他的像DB2等数据库时,就需用到GenericDatabase,它可用来任何.NET类库中的多寡提供者,包括OdbcProvider和OleDbProvider。

二.施用示例

DAAB2.0底部署非常简单,主要发生以下几上面的布局:

布置连接字符串

ACCESS 21

布置默认数据库

ACCESS 22

添加相关的命名空间:

ACCESS 23using Microsoft.Practices.EnterpriseLibrary.Data;
ACCESS 24using System.Data;

行使Data Access Application Block进行多少的读取和操作,一般分为三步:

 1.创建Database对象

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

3.执行命令

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

推行静态的SQL语句

ACCESS 25public string GetCustomerList()
ACCESS 26ACCESS 27ACCESS 28{
ACCESS 29// 创建Database对象
ACCESS 30Database db = DatabaseFactory.CreateDatabase();
ACCESS 31// 用SQL语句创建DbCommand对象
ACCESS 32string sqlCommand = “Select CustomerID, Name, Address, City, Country, PostalCode ” +
ACCESS 33    “From Customers”;
ACCESS 34DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
ACCESS 35
ACCESS 36StringBuilder readerData = new StringBuilder();
ACCESS 37
ACCESS 38// 调用ExecuteReader方法
ACCESS 39using (IDataReader dataReader = db.ExecuteReader(dbCommand))
ACCESS 40ACCESS 41ACCESS 42{
ACCESS 43    while (dataReader.Read())
ACCESS 44ACCESS 45    ACCESS 46{
ACCESS 47        // Get the value of the ‘Name’ column in the DataReader
ACCESS 48        readerData.Append(dataReader[“Name”]);
ACCESS 49        readerData.Append(Environment.NewLine);
ACCESS 50    }
ACCESS 51}
ACCESS 52
ACCESS 53return readerData.ToString();
ACCESS 54}

执行存储过程并传递参数,返回DataSet

ACCESS 55public DataSet GetProductsInCategory(int Category)
ACCESS 56ACCESS 57ACCESS 58{
ACCESS 59    // Create the Database object, using the default database service. The
ACCESS 60    // default database service is determined through configuration.
ACCESS 61    Database db = DatabaseFactory.CreateDatabase();
ACCESS 62
ACCESS 63    string sqlCommand = “GetProductsByCategory”;
ACCESS 64    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
ACCESS 65
ACCESS 66    // Retrieve products from the specified category.
ACCESS 67    db.AddInParameter(dbCommand, “CategoryID”, DbType.Int32, Category);
ACCESS 68
ACCESS 69    // DataSet that will hold the returned results        
ACCESS 70    DataSet productsDataSet = null;
ACCESS 71
ACCESS 72    productsDataSet = db.ExecuteDataSet(dbCommand);
ACCESS 73
ACCESS 74    // Note: connection was closed by ExecuteDataSet method call 
ACCESS 75
ACCESS 76    return productsDataSet;
ACCESS 77}

应用DataSet更新数据

ACCESS 78public int UpdateProducts()
ACCESS 79ACCESS 80ACCESS 81{
ACCESS 82    // Create the Database object, using the default database service. The
ACCESS 83    // default database service is determined through configuration.
ACCESS 84    Database db = DatabaseFactory.CreateDatabase();
ACCESS 85
ACCESS 86    DataSet productsDataSet = new DataSet();
ACCESS 87
ACCESS 88    string sqlCommand = “Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate ” +
ACCESS 89        “From Products”;
ACCESS 90    DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
ACCESS 91
ACCESS 92    string productsTable = “Products”;
ACCESS 93
ACCESS 94    // Retrieve the initial data
ACCESS 95    db.LoadDataSet(dbCommand, productsDataSet, productsTable);
ACCESS 96
ACCESS 97    // Get the table that will be modified
ACCESS 98    DataTable table = productsDataSet.Tables[productsTable];
ACCESS 99
ACCESS 100    // Add a new product to existing DataSet
ACCESS 101ACCESS 102    DataRow addedRow = table.Rows.Add(new object[] ACCESS 103{DBNull.Value, “New product”, 11, 25});
ACCESS 104
ACCESS 105    // Modify an existing product
ACCESS 106    table.Rows[0][“ProductName”] = “Modified product”;
ACCESS 107
ACCESS 108    // Establish our Insert, Delete, and Update commands
ACCESS 109    DbCommand insertCommand = db.GetStoredProcCommand(“AddProduct”);
ACCESS 110    db.AddInParameter(insertCommand, “ProductName”, DbType.String, “ProductName”, DataRowVersion.Current);
ACCESS 111    db.AddInParameter(insertCommand, “CategoryID”, DbType.Int32, “CategoryID”, DataRowVersion.Current);
ACCESS 112    db.AddInParameter(insertCommand, “UnitPrice”, DbType.Currency, “UnitPrice”, DataRowVersion.Current);
ACCESS 113
ACCESS 114    DbCommand deleteCommand = db.GetStoredProcCommand(“DeleteProduct”);
ACCESS 115    db.AddInParameter(deleteCommand, “ProductID”, DbType.Int32, “ProductID”, DataRowVersion.Current);
ACCESS 116
ACCESS 117    DbCommand updateCommand = db.GetStoredProcCommand(“UpdateProduct”);
ACCESS 118    db.AddInParameter(updateCommand, “ProductID”, DbType.Int32, “ProductID”, DataRowVersion.Current);
ACCESS 119    db.AddInParameter(updateCommand, “ProductName”, DbType.String, “ProductName”, DataRowVersion.Current);
ACCESS 120    db.AddInParameter(updateCommand, “LastUpdate”, DbType.DateTime, “LastUpdate”, DataRowVersion.Current);
ACCESS 121
ACCESS 122    // Submit the DataSet, capturing the number of rows that were affected
ACCESS 123    int rowsAffected = db.UpdateDataSet(productsDataSet, “Products”, insertCommand, updateCommand,
ACCESS 124                                        deleteCommand, UpdateBehavior.Standard);
ACCESS 125
ACCESS 126    return rowsAffected;
ACCESS 127
ACCESS 128}

经过ID获取记录详细信息

ACCESS 129public string GetProductDetails(int productID)
ACCESS 130ACCESS 131ACCESS 132{
ACCESS 133    // Create the Database object, using the default database service. The
ACCESS 134    // default database service is determined through configuration.
ACCESS 135    Database db = DatabaseFactory.CreateDatabase();
ACCESS 136
ACCESS 137    string sqlCommand = “GetProductDetails”;
ACCESS 138    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
ACCESS 139
ACCESS 140    // Add paramters
ACCESS 141    // Input parameters can specify the input value
ACCESS 142    db.AddInParameter(dbCommand, “ProductID”, DbType.Int32, productID);
ACCESS 143    // Output parameters specify the size of the return data
ACCESS 144    db.AddOutParameter(dbCommand, “ProductName”, DbType.String, 50);
ACCESS 145    db.AddOutParameter(dbCommand, “UnitPrice”, DbType.Currency, 8);
ACCESS 146
ACCESS 147    db.ExecuteNonQuery(dbCommand);
ACCESS 148
ACCESS 149    // Row of data is captured via output parameters
ACCESS 150    string results = string.Format(CultureInfo.CurrentCulture, “{0}, {1}, {2:C} “,
ACCESS 151                                   db.GetParameterValue(dbCommand, “ProductID”),
ACCESS 152                                   db.GetParameterValue(dbCommand, “ProductName”),
ACCESS 153                                   db.GetParameterValue(dbCommand, “UnitPrice”));
ACCESS 154
ACCESS 155    return results;
ACCESS 156}

因为XML格式返回数据

ACCESS 157public string GetProductList()
ACCESS 158ACCESS 159ACCESS 160{
ACCESS 161    // Use a named database instance that refers to a SQL Server database.
ACCESS 162    SqlDatabase dbSQL = DatabaseFactory.CreateDatabase() as SqlDatabase;
ACCESS 163
ACCESS 164    // Use “FOR XML AUTO” to have SQL return XML data
ACCESS 165    string sqlCommand = “Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate ” +
ACCESS 166        “From Products FOR XML AUTO”;
ACCESS 167    DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand);
ACCESS 168
ACCESS 169    XmlReader productsReader = null;
ACCESS 170    StringBuilder productList = new StringBuilder();
ACCESS 171
ACCESS 172    try
ACCESS 173ACCESS 174    ACCESS 175{
ACCESS 176        productsReader = dbSQL.ExecuteXmlReader(dbCommand);
ACCESS 177
ACCESS 178        // Iterate through the XmlReader and put the data into our results.
ACCESS 179        while (!productsReader.EOF)
ACCESS 180ACCESS 181        ACCESS 182{
ACCESS 183            if (productsReader.IsStartElement())
ACCESS 184ACCESS 185            ACCESS 186{
ACCESS 187                productList.Append(productsReader.ReadOuterXml());
ACCESS 188                productList.Append(Environment.NewLine);
ACCESS 189            }
ACCESS 190        }
ACCESS 191    }
ACCESS 192    finally
ACCESS 193ACCESS 194    ACCESS 195{
ACCESS 196      // Close the Reader.
ACCESS 197      if (productsReader != null)
ACCESS 198ACCESS 199      ACCESS 200{
ACCESS 201          productsReader.Close();
ACCESS 202      }
ACCESS 203      
ACCESS 204      // Explicitly close the connection. The connection is not closed
ACCESS 205      // when the XmlReader is closed.
ACCESS 206      if (dbCommand.Connection != null)
ACCESS 207ACCESS 208      ACCESS 209{
ACCESS 210        dbCommand.Connection.Close();
ACCESS 211      }  
ACCESS 212    }
ACCESS 213
ACCESS 214    return productList.ToString();
ACCESS 215}

采用工作

ACCESS 216public bool Transfer(int transactionAmount, int sourceAccount, int destinationAccount)
ACCESS 217ACCESS 218ACCESS 219{
ACCESS 220    bool result = false;
ACCESS 221    
ACCESS 222    // Create the Database object, using the default database service. The
ACCESS 223    // default database service is determined through configuration.
ACCESS 224    Database db = DatabaseFactory.CreateDatabase();
ACCESS 225
ACCESS 226    // Two operations, one to credit an account, and one to debit another
ACCESS 227    // account.
ACCESS 228    string sqlCommand = “CreditAccount”;
ACCESS 229    DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
ACCESS 230
ACCESS 231    db.AddInParameter(creditCommand, “AccountID”, DbType.Int32, sourceAccount);
ACCESS 232    db.AddInParameter(creditCommand, “Amount”, DbType.Int32, transactionAmount);
ACCESS 233
ACCESS 234    sqlCommand = “DebitAccount”;
ACCESS 235    DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
ACCESS 236
ACCESS 237    db.AddInParameter(debitCommand, “AccountID”, DbType.Int32, destinationAccount);
ACCESS 238    db.AddInParameter(debitCommand, “Amount”, DbType.Int32, transactionAmount);
ACCESS 239
ACCESS 240    using (DbConnection connection = db.CreateConnection())
ACCESS 241ACCESS 242    ACCESS 243{
ACCESS 244        connection.Open();
ACCESS 245        DbTransaction transaction = connection.BeginTransaction();
ACCESS 246
ACCESS 247        try
ACCESS 248ACCESS 249        ACCESS 250{
ACCESS 251            // Credit the first account
ACCESS 252            db.ExecuteNonQuery(creditCommand, transaction);
ACCESS 253            // Debit the second account
ACCESS 254            db.ExecuteNonQuery(debitCommand, transaction);
ACCESS 255
ACCESS 256            // Commit the transaction
ACCESS 257            transaction.Commit();
ACCESS 258            
ACCESS 259            result = true;
ACCESS 260        }
ACCESS 261        catch
ACCESS 262ACCESS 263        ACCESS 264{
ACCESS 265            // Rollback transaction 
ACCESS 266            transaction.Rollback();
ACCESS 267        }
ACCESS 268        connection.Close();
ACCESS 269        
ACCESS 270        return result;
ACCESS 271    }
ACCESS 272}

三.大功能

1.创建Database对象

创建一个默认的Database对象

ACCESS 273Database dbSvc = DatabaseFactory.CreateDatabase();

默认的数据库在配备文件被:

ACCESS 274<dataConfiguration defaultDatabase=”DataAccessQuickStart” />

缔造一个实例Database对象

ACCESS 275// Use a named database instance that refers to an arbitrary database type, 
ACCESS 276// which is determined by configuration information.
ACCESS 277Database myDb = DatabaseFactory.CreateDatabase(“DataAccessQuickStart”);

创一个实际的种的数据库对象

ACCESS 278// Create a SQL database.
ACCESS 279SqlDatabase dbSQL = DatabaseFactory.CreateDatabase(“DataAccessQuickStart”) as SqlDatabase;

2.创建DbCommand对象

静态的SQL语句创建一个DbCommand

ACCESS 280Database db = DatabaseFactory.CreateDatabase();
ACCESS 281string sqlCommand = “Select CustomerID, LastName, FirstName From Customers”;
ACCESS 282DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

储存过程创建一个DbCommand

ACCESS 283Database db = DatabaseFactory.CreateDatabase();
ACCESS 284DbCommand dbCommand = db.GetStoredProcCommand(“GetProductsByCategory”);

3.管理对象

当连接对象打开后,不待再行连

ACCESS 285Database db = DatabaseFactory.CreateDatabase();
ACCESS 286string sqlCommand = “Select ProductID, ProductName From Products”;
ACCESS 287DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); 
ACCESS 288// No need to open the connection; just make the call.
ACCESS 289DataSet customerDataSet = db.ExecuteDataSet(dbCommand);

使用Using及早释放对象

ACCESS 290Database db = DatabaseFactory.CreateDatabase();
ACCESS 291DbCommand dbCommand = db.GetSqlStringCommand(“Select Name, Address From Customers”);
ACCESS 292using (IDataReader dataReader = db.ExecuteReader(dbCommand))
ACCESS 293ACCESS 294ACCESS 295{
ACCESS 296// Process results
ACCESS 297}

4.参数处理

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

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

以示例如下:

ACCESS 298Database db = DatabaseFactory.CreateDatabase();
ACCESS 299string sqlCommand = “GetProductDetails”;
ACCESS 300DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
ACCESS 301db.AddInParameter(dbCommand, “ProductID”, DbType.Int32, 5);
ACCESS 302db.AddOutParameter(dbCommand, “ProductName”, DbType.String, 50);
ACCESS 303db.AddOutParameter(dbCommand, “UnitPrice”, DbType.Currency, 8);

 

ACCESS 304Database db = DatabaseFactory.CreateDatabase();
ACCESS 305DbCommand insertCommand = db.GetStoredProcCommand(“AddProduct”);
ACCESS 306db.AddInParameter(insertCommand, “ProductName”, DbType.String, “ProductName”, DataRowVersion.Current);
ACCESS 307db.AddInParameter(insertCommand, “CategoryID”, DbType.Int32, “CategoryID”, DataRowVersion.Current);
ACCESS 308db.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代码:

ACCESS 309ACCESS 310<%…@ Page Language=”VB”
AutoEventWireup=”false” CodeFile=”sql.aspx.vb” Inherits=”sql”
%>
ACCESS 311
ACCESS 312<!DOCTYPE html PUBLIC
“-//W3C//DTD XHTML 1.0 Transitional//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"&gt;
ACCESS 313
ACCESS 314<html
xmlns=”http://www.w3.org/1999/xhtml” >
ACCESS 315<head
runat=”server”>
ACCESS 316
<title>web3.cn——SQL、Access数据库自由切换</title>
ACCESS 317</head>
ACCESS 318<body>
ACCESS 319 <form id=”form1″
runat=”server”>
ACCESS 320 <div>
ACCESS 321 <asp:GridView
ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”>
ACCESS 322 <Columns>
ACCESS 323 <asp:BoundField
DataField=”id” HeaderText=”id” SortExpression=”id” >
ACCESS 324 <HeaderStyle
BackColor=”Silver” />
ACCESS 325 </asp:BoundField>
ACCESS 326 <asp:BoundField
DataField=”provinceID” HeaderText=”provinceID”
SortExpression=”provinceID” >
ACCESS 327 <HeaderStyle
BackColor=”Silver” />
ACCESS 328 </asp:BoundField>
ACCESS 329 <asp:BoundField
DataField=”province” HeaderText=”provinceID” SortExpression=”province”
>
ACCESS 330 <HeaderStyle
BackColor=”Silver” />
ACCESS 331 </asp:BoundField>
ACCESS 332 </Columns>
ACCESS 333 </asp:GridView>
ACCESS 334 </div>
ACCESS 335 </form>
ACCESS 336</body>
ACCESS 337</html>

vb.net代码:

ACCESS 338Imports System.Data
ACCESS 339Imports
Microsoft.Practices.EnterpriseLibrary.Data
ACCESS 340Imports
system.Data.Common
ACCESS 341Imports System.Data.Odbc
ACCESS 342
ACCESS 343ACCESS 344Partial
Class
sql_accessClass
sql_access
ACCESS 345 Inherits
System.Web.UI.Page
ACCESS 346 Dim sys As New
WebService
ACCESS 347ACCESS 348
Protected Sub
Page_Load()Sub
Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles
Me.Load
ACCESS 349 If Not Page.IsPostBack
Then
ACCESS 350 BindGrid()
ACCESS 351 End If
ACCESS 352 End Sub
ACCESS 353
ACCESS 354ACCESS 355
Sub
BindGrid()Sub
BindGrid()
ACCESS 356 Dim dv As DataView
ACCESS 357 dv =
GetList_Access().DefaultView
ACCESS 358 GridView1.DataSource =
dv
ACCESS 359 GridView1.DataBind()
ACCESS 360 End Sub
ACCESS 361
ACCESS 362 ‘列表
ACCESS 363ACCESS 364
Public Function
GetList_SQL()Function GetList_SQL() As
DataTable
ACCESS 365 Dim db As Database =
DatabaseFactory.CreateDatabase()
ACCESS 366
ACCESS 367 Dim sqlCommand As String =
“select * FROM province ORDER BY id desc”
ACCESS 368
ACCESS 369 ‘要指向数据源执行之 SQL
语句或存储过程。
ACCESS 370 Dim dbCommand As DbCommand
= db.GetSqlStringCommand(sqlCommand)
ACCESS 371
ACCESS 372 Return
db.ExecuteDataSet(dbCommand).Tables(0)
ACCESS 373 End Function
ACCESS 374
ACCESS 375 ‘列表
ACCESS 376ACCESS 377
Public Function
GetList_Access()Function GetList_Access() As
DataTable
ACCESS 378
ACCESS 379 Dim db As Database = New
GenericDatabase(“Driver={Microsoft Access Driver
(*.mdb)};Dbq=D:vs2005dbdb.mdb;Uid=sa;Pwd=sa;”, OdbcFactory.Instance)
ACCESS 380 Dim sqlCommand As String =
“select * FROM province ORDER BY id desc”
ACCESS 381
ACCESS 382 ‘要对准数据源执行之 SQL
语句或存储过程。
ACCESS 383 Dim dbCommand As DbCommand
= db.GetSqlStringCommand(sqlCommand)
ACCESS 384
ACCESS 385 Return
db.ExecuteDataSet(dbCommand).Tables(0)
ACCESS 386 End Function

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

相关文章