ACCESSEnterprise Library2.0数据库常用操作

后天学习了Enterprise Library2.0的Data Access Application Block,Data
Access Application
Block提供了通用的数额访问的意义,随着2.0本子的生产有了相当的大变迁。我就多写了对SQL和ACCESS数据库自由切换的一些代码出来共享。先看完原文再接我的代码吧。

一.改进

在DAAB1.第11中学间我们驾驭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和奥莱DbProvider。

二.施用示例

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语句创设1个DbCommand

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

仓库储存进度成立3个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是2个不易的选料,不过假设您想要针对一定数据库的性状开始展览编制程序,就要用ADO.NET了。

参照:Enterprise Libaray –January 二零零六协理文书档案及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的数据库了,简单吗。那里只交给1个思路,就看我们封装起来成尤其简便易行易用的嘞。

相关文章