EnterpriseLibrary20企业库常用方法

1、EnterpriseLibrary20企业库常用方法下面我们重点来说一下DataAccess Application Block操作数据 库的方法。1、创建一个数据库实例DataBase创建一个默认的数据库实例Database defaultdb = DatabaseFactory.CreateDatabase();创建一个名为Connection String的数据库实例Database db = DatabaseFactory.CreateDatabase(nConnectio n String");上面两种创建数据库实例的方法的数据库能够是任何类型的数据 库，取决于Config

2、文件中的配置信息下面的这种方面展现了创建一个SQL数据库的实例需引用Micros oft.Practices.EnterpriseLibrary.Data.Sql 程序集SqlDatabase dbsql = DatabaseFactory.CreateDatabase(HCon nection String") as SqlDatabase;我们同样也能够不通过配置文件来创建数据库实例，如下string connString = nserver=.;database=EnterpriseLibrary;uid =sa;pwd = "SqlDatabase nocofigd

3、b = new SqlDatabase(connString);2、创建 DbCommandDbCommand的创建方式有两种：一种是为了调用储备过程创建，一种是为了执行一样的SQL语句而创建,具体用哪一种方式，就要 看你需要执行的是储备过程依旧SQL语句了。创建一个SQL语句的DbCommandDatabase db = DatabaseFactory.CreateDatabase(); string sql = "Select * from person"DbCommand Sqldbcomm = db.GetSqlStringCommand(sql);创建一个储备过程

4、的DbCommand 储备过程名称为GetAIIPersonByName string procName = "GetAIIPersonByName"DbCommand Procdbcomm = db.GetStoredProcCommand (procName);3、储备过程中的参数处理DataBase类中包含多种传递储备过程参数的方法，也包含了得到和 设置参数值的方法，如下：AddParameter,为储备过程传递一个参数(输入型或输出型) AddlnParameter,为储备过程传递一个输入型参数 AddOutParameter.为储备过程传递一个输出型参数 GetP

5、arameterValue.猎取某储备过程指定参数的值 SetParameterValue,为储备过程的某个参数赋值，当你需要用同一 个方法来执行插入多条记录时，就可参数给参数赋值的方法来进行 创建一个储备过程的DbCommand 储备过程名称为GetAIIPersonByName string procName = "GetAIIPersonByName"DbCommand Procdbcomm = db.GetStoredProcCommand(p rocName);添加一个输入型的参数db.AddlnParameteProcdbcomm, "sex"

6、;, DbType.String);添加一个输出型的参数db.AddOutParameter(Procdbcomm/ "©name", DbType.Strin g,20)；设置参数的值db.SetPararrieterValue(Procdbcomm/ nsex", "男")；执行储备过程db.ExecuteNonQuery(Procdbcomm);得到输出参数的值，注意转化返回值类型string outvalue = (string) db.GetParameterValue(Procdbco mm, "name&quo

7、t;);4、执行ExecuteReader方法返回一个IDataReader类型的数据集 因为ExecuteReader方法在一开始执行时就打开了一个与数据库的 连接，因此我们必须注意在使用终止时关闭连接,而用using(用语 句块就能确保ExecuteReader方法在执行完毕时关闭与数据库的连 接。什么时候使用此方法：返回的数据仅仅用来显示，并不对其进行修改，删除等操作；绑定返回的数据到WebForm Control不需要缓存返回的数据，用完就开释using (IDataReader reader = db.ExecuteReader(CommandTy pe.Text, "se

8、lect * from person11)DataGridl.DataSource = reader;DataGridl.DataBind();)5、执行 ExecuteDataSet 方法返回一个 DataSetpublic DataSet GetPersonList() (Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.GetSqlStringCommand("select* from person");return db.ExecuteDataSet(dbcomm);)什么时候使

9、用此方法：需要返回多表数据；假如你需要逐一访问数据库的每一条记录，那个时候假如使用Data Reader的话,则会使与数据库的连接一直处于打开的状态，长此以 往,就会使应用程序的性育济口可伸缩性大大降低；需要和其他的应用程序交换数据；6、执行 ExecuteNonQuery该方法返回的是SQL语句执行阻碍的行数，我们能够利用该方法来执行一些没有返回i 的操作(Insert, U pdate, Delete) public void AddPersonQDatabase db = DatabaseFactory.CreateDatabaseQ;DbCommand dbcomm = db.GetS

10、qlStringCommand(Hinsert i nto person valuesQ'shy1,女1234561)db.ExecuteNonQuery(dbcomm);)7、执行ExecuteScalar返回单值public string GetPersonNameQDatabase db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.GetSqlStringCommand(Hselect name from person");return (string)db.ExecuteScalar(dbcomm)

11、; ) 8、事务处理public void UseTransactionQDatabase db = DatabaseFactory<reateDatabase();DbCommand dbcomml = db.GetSqlStringCommand(nupdat e person set name=,pw,n);DbCommand dbcomm2 = db.GetSqlStringCommand(ndelet e from person where id = lH);using (DbConnection conn = db.CreateConnectionQ)(打开连接conn.Op

12、en();创建事务DbTransaction trans = conn.BeginTransaction();try(db.ExecuteNonQuery(dbcomml);db.ExecuteNonQuery(dbcomm2);都执行成功则提交事务trans mit();)catch(Exception)(发生专门，事务回滚trans.Rollback();)关闭连接conn.Close();)9、执行 ExecuteXmlReader 返回 XML 数据支持Sql Server 2000及以后版本的数据库，对微软以外的数据库 应该不支持。public void UseXMLReaderQS

13、qlDatabase dbSQL = DatabaseFactory.CreateDatabase(nCo nnection String'1) as SqlDatabase;/要返回XML数据需要在SQL语句后加FOR XML AUTO string sqlCommand = "SELECT ProductID, ProductName FROM Products FOR XML AUTO11;DbCommand dbCommand = dbSQL.GetSqIStringCommand (sqlCommand);XmlReader personReader = null;

14、StringBuilder personlist = new StringBuilderQ;trypersonReader = dbSQL.ExecuteXmlReader(dbCommand);/循环向XML中写入我们查询得到的数据while (ipersonReader.EOF)if (personReader.IsStartElement() personlist.Append(personReader.ReadOuterXml();personlist.Append(Environment.NewLine);)finally(/ 关闭 Reader.if (personReader !

15、= null)(personReader.Close();)/关闭数据库连接if (dbCommand.Connection != null)(dbCommand.Connection.Close();)10、用DataSet批量的添加，修改,删除数据public void UpdateDataBase()(Database db = DatabaseFactory.CreateDatabase();DataSet personDataSet = new DataSet();string sqlCommand = "Select * from person11;DbCommand d

16、bCommand = db.GetSqlStringCommand(sql Command);string personTable = "person”；/得到初始化数据db.LoadDataSet(dbCommand, personDataSet, personTable);/得到未修改前的数据集DataTable table = personDataSet.TablespersonTable;/往DataSet中添加一行数据Data Row addedRow = table.Rows«Add(new object 18, "New person“，“男“，“6

17、54321” );/修改table.Rows0"ProductName" = "Modified product"/下面分别创建添加，修改，删除的操作DbCommand insertCommand = db«GetStoredProcComm andC'AddPerson1');db.AddInParameter(insertCommand, "Name", DbType.String, “Name”，DataRowVersion.Current);db.AddInParameter(insertComman

18、d/ “Sex”，DbType.String, ” Sext DataRowVersion.Current);db.AddInParameter(insertCommand, "ID", DbType.Int32, "ID ”, DataRowVersion.Current);DbCommand deleteCommand = db.GetStoredProcComm and(HDeletePerson");db.AddInParameter(deleteCommand, "ID"Z DbType.Int32, "I D”，

19、DataRowVersion.Current);DbCommand updateCommand = db.GetStoredProcCom mandCpdatePerson");db.AddlnParameteupdateCommand, “Name”，DbType.Strin g, "Name： DataRowVersion.Current);db.AddInParameter(updateCommand, "Sex”，DbType.String, “Sex"，DataRowVersion.Current);db.AddInParameter(inse

20、rtCommandf “ID”, DbType.Int32f "ID "，DataRowVersion.Current);/提交对DataSet的修改，并返回阻碍的行int rowsAffected = db<UpdateDataSet(productsDataSetf11 Productsinsertcommand, updateCommand, deleteCo mmand, Microsoft.Practices.EnterpriseLibrary.Data.Updat e Be havi o r.Standa rd）;G->Enterprise Libr

21、ary2.0数据库常用操作2 （不同版本的说明）今天学习了 Enterprise Library2.0 的 Data Access Applicatio n Block z Data Access Application Block 提供了通用的数据访问 的功能,随着2.0版本的推出有了专门大变化。俺就多写了对SQL 和ACCESS数据库自由切换的一些代码出来共享。先看完原文再接 俺的代码吧。一改进在DAAB1.1里面我们明白Database方法返回或者创建一个DB CommandWrapper 对象，而在 DAAB2.0 里面移除了 DBComma ndWrapper类，用ADO.NET2.

22、0里面的DBCommand类代替实现 类似的功能,如此使得DAAB跟我们的.NET类库的结合更加紧密， 回忆一下我们在1.1里面用DBCommandWrapper来访问数据时的 代码：二.使用示例Database db = DatabaseFactory.CreateDatabase();DBCommandWrapper dbCommand = db.GetStoredPro cCommandWrapper(,GetProductsByCategory,);dbCommand.AddInParameter(,CategoryIDn/ DbType.Int 32, Category);DataS

23、et productDataSet = db.ExecuteDataSet(dbComm and);而用了新的DBCommand类之后则变成了 ：Database db = DatabaseFactory.CreateDatabase();DbCommand dbCommand = db.GetStoredProcComma ndCGetProductsByCategory'1);db.AddInParameter(dbCommand/ "CategorylD", DbTyp e.Int32z Category);DataSet productDataSet = d

24、b.ExecuteDataSet(dbComm and);数据库连接字符串在我们基于数据库的开发永久是少不了的，然 而在DAAB1.1下，它所使用的字符串跟我们在.NET类库中使用的连 接字符串却是不能共享的，它们分别储存在不同的位置。而在2.0的 Data Access Application Block 使用了 ADO.NET2.0 里面connectionStrings配置区，如此带来的一个好处是连接字符串能够在 Application Block 和定义的.NET类之间共享使用该配置区,如：< connectionstrings ><addname = >lDa

25、taAccessQuickStart>lproviderName=vvSystem.Data.SqlClientlvconnectionstring = "server= (local)SQLEXPRES S;database = EntLibQuickStarts;Integrated Security二true” /></connectionStrings>在.NET2.0下,泛型编程差不多成为了一个核心,而2.0版的D AAB中也新增了一个GenericDatabase对象。DAAB中尽管差不多 包含了 SqlDatabase和OrcaleDatabas

26、e ,然而假如我们需要使用 其他的像DB2等数据库时，就需要用到GenericDatabase ,它能 够用于任HNET类库中的数据提供者，包括OdbcProvider和Ole DbProvideroDAAB2.0的配置专门简单，要紧有以下几方面的配置:配置连接字符串配置默认数据库添加相关的命名空间：using Microsoft.Practices.EnterpriseLibrary.Data;using System.Data;使用Data Access Application Block进行数据的读取和操作， 一样分为三步：1 .创建Database对象2 .提供命令参数，假如需要的话下

27、面分别看一下DataAccessQuickStart中提供的一些例子：执行静态的SQL语句public string GetCustomerList()(/创建Database对象Database db = DatabaseFactory.CreateDatabase();使用SQL语句创建DbCommand对象string sqlCommand = nSelect CustomerlD, Name, Addr ess, City, Country, PostalCode " +"From Customers1'DbCommand dbCommand = db.Ge

28、tSqIStringCommand (sqlCommand);StringBuilder readerData = new StringBuilder();/ 调用 ExecuteReader 方法using (IDataReader dataReader = db.ExecuteReader(dbC ommand)while (dataReader.ReadQ)/ Get the value of the 'Name' column in the Dat aReaderreaderData.Append(dataReader"Name");readerDa

29、ta.Append(Environment.NewLine);)return readerData.ToString();)执行储备过程并传递参数，返回DataSetpublic DataSet GetProductsInCategory(int Category)(/ Create the Database object, using the default database service. The/ default database service is determined through c onfiguration.Database db = DatabaseFactory.Cre

30、ateDatabase();string sqlCommand = "GetProductsByCategory11;DbCommand dbCommand = db.GetStoredProcComm and(sqlCommand);/ Retrieve products from the specified category.db.AddInParameter(dbCommand, "CategorylD", DbTy pe.Int32/ Category);/ DataSet that will hold the returned resultsDataSe

31、t productsDataSet = null;productsDataSet = db.ExecuteDataSet(dbCommand);/ Note: connection was closed by ExecuteDataSet method callreturn productsDataSet;)利用DataSet更新数据public int UpdateProducts()/ Create the Database object, using the default dat abase service. The/ default database service is deter

32、mined through c onfiguration.Database db = DatabaseFactory.CreateDatabase();DataSet productsDataSet = new DataSet();string sqlCommand = "Select ProductID, ProductNa me, CategorylD, UnitPrice, LastUpdate " +"From Products"DbCommand dbCommand = db.GetSqIStringComma nd(sqlCommand);s

33、tring productsTable = "Products1"/ Retrieve the initial datadb.LoadDataSet(dbCommand, productsDataSet, productsTable);/ Get the table that will be modifiedDataTable table = productsDataSet.TablesproductsTa ble;/ Add a new product to existing DataSetDataRow addedRow = table.Rows.Add(new obj

34、ect DBNulLValue, "New product； 11, 25);/ Modify an existing producttable.Rows0nProductName" = "Modified product"/ Establish our Insert, Delete, and Update command sDbCommand insertCommand = db.GetStoredProcCo mmand("AddProductn);db.AddInParameter(insertCommand/ "Product

35、Name", DbType.String, "ProductName DataRowVersion.Current);db.AddlnParameteinsertCommand, "CategorylD", DbType.Int32, "CategorylD", DataRowVersion.Current);db.AddInParameter(insertCommandz "UnitPrice11, DbTy pe.Currency, "UnitPrice", DataRowVersion.Curren

36、t);DbCommand deleteCommand = db.GetStoredProcCo mmand("DeleteProduct");db.AddInParameter(deleteCommand, "ProductID", Db Type.Int32, "ProductID", DataRowVersion.Current);DbCommand updateCommand = db.GetStoredProcC ommand(" UpdateProduct");db.AddInParameter(upda

37、teCommand/ "ProductID", Db Type.Int32z "ProductlD", DataRowVersion.Current);db.AddInParameter(updateCommandz TroductName", DbType.String, TroductName DataRowVersion.Current);db.AddInParameter(updateCommandz nLastUpdate'1, D bType.DateTime, "LastUpdate", DataRow

38、Version.Current);/ Submit the DataSet, capturing the number of row s that were affectedint rowsAffected = db.UpdateDataSet(productsDataSet, "Products'1, insertCommand, updateCommand,deleteCommand, UpdateBehavior. Standard);return rows Affected;)通过ID猎取记录详细信息public string GetProductDetails(in

39、t productID)(/ Create the Database object, using the default dat abase service. The/ default database service is determined through c onfiguration.Database db = DatabaseFactory.CreateDatabase();string sqlCommand = "GetProductDetails"DbCommand dbCommand = db.GetStoredProcComm and(sqlCommand

40、);/ Add paramters / Input parameters can specify the input valuedb.AddlnParameterCdbCommand, TroductlD", DbTyp e.Int32, productID);/ Output parameters specify the size of the return datadb.AddOutRarameter(dbCommand, "ProductName", D bType.String, 50);db.AddOutParametedbCommand, "

41、UnitPrice", DbTy pe.Currency, 8);db.ExecuteNonQuery(dbCommand);/ Row of data is captured via output parametersstring results = string.Format(CultureInfo.CurrentCultu re0, 1, 2:C ”，db.GetRarameterValue(dbCommandz "ProductlD"),db.GetParameterValue(dbCommandz "ProductName"),db.

42、GetParameterValue(dbCommand/nUnitPricen);return results;)以XML格式返回数据public string GetProductList()(/ Use a named database instance that refers to a S QL Server database.SqlDatabase dbSQL = DatabaseFactory.CreateDatabas e() as SqlDatabase;/ Use "FOR XML AUTO" to have SQL return XML d atastri

43、ng sqlCommand = "Select ProductID, ProductNa me, CategorylD, UnitPrice, LastUpdate " +"From Products FOR XML AUTO"DbCommand dbCommand = dbSQL.GetSqlStringCom mand(sqlCommand);XmlReader productsReader = null;StringBuilder productList = new StringBuilderQ;productsReader = dbSQL.Exe

44、cuteXmlReader(dbCommand);/ Iterate through the XmlReader and put the dat a into our results.while (iproductsReader.EOF)if (productsReader.IsStartElement()productList.Append (productsReader. ReadOuterXmlQ);productList.Append(Environment.NewLine);)finally(/ Close the Reader.if (productsReader != null)

45、(productsReader.Close();)/ Explicitly close the connection. The connection is not closed/ when the XmlReader is closed.if (dbCommand.Connection != null)dbCommand.Connection.CloseQ;)return productList.ToString();)使用事务public bool Transfer(int transaction Amount, int sourceA ccount, int destinationAcco

46、unt)(bool result = false;/ Create the Database object, using the default dat abase service. The/ default database service is determined through c onfiguration.Database db = DatabaseFactory.CreateDatabase();/ Two operations, one to credit an account, and on e to debit another/ account.string sqlComma

47、nd = "CreditAccount'1;DbCommand creditCommand = db.GetStoredProcCo mmand(sqlCommand);db.AddlnParametecreditCommand, "AccountID", Db Type.Int32, sourceAccount);db.AddInParameter(creditCommand, "Amount"，DbTy pe.Int32f transactionAmount);sqlCommand = nDebitAccountn;DbComman

48、d debitCommand = db.GetStoredProcCo mmand(sqlCommand);db.AddlnParametedebitCommand, "AccountID"z DbT ype.Int32, destinationAccount);db.AddInParameter(debitCommandz "Amount”, DbTyp e.Int32, transactionAmount);using (DbConnection connection = db.CreateConnect ionQ)connection.Open();DbTr

49、ansaction transaction = connection.BeginTransaction();try(/ Credit the first accountdb.ExecuteNonQuery(creditCommandz transactio n)；/ Debit the second accountdb.ExecuteNonQueryfdebitCommand, transaction);/ Commit the transactiontransaction mit();result = true;) catch Rollback transactiontransaction.

50、Rollback();)connection.Close();return result;)3 .常见功能1 .创建Database对象创建一个默认的Database对象Database dbSvc = DatabaseFactory.CreateDatabase();默认的数据库在配置文件中：<dataConfiguration defaultDatabase ="DataAccessQuickS tartn />创建一个实例Database对象/ Use a named database instance that refers to an ar bitrary da

51、tabase type,/ which is determined by configuration information.Database myDb = DatabaseFactory.CreateDatabase(HDat aAccessQuickStart");创建一个具体的类型的数据库对象/ Create a SQL database.SqlDatabase dbSQL = DatabaseFactory.CreateDatabase(" DataAccessQuickStartn) as SqlDatabase;2 .仓(J建 DbCommand 对象静态的SQ

52、L语句创建一个DbCommandDatabase db = DatabaseFactory.CreateDatabase();string sqlCommand = "Select CustomerlD, LastName, Fi rstName From Customers1,;DbCommand dbCommand = db.GetSqIStringCommand (sqlCommand);储备过程创建一个DbCommandDatabase db = DatabaseFactory.CreateDatabase();DbCommand dbCommand = db.GetStor

53、edProcComman d("GetProductsByCategory");3 .治理对象当连接对象打开后，不需要再次连接Database db = DatabaseFactory.CreateDatabase();string sqlCommand = "Select ProductID, ProductName From Products11;DbCommand dbCommand = db.GetSqIStringCommand (sqlCommand);/ No need to open the connection; just make the ca

54、ll.DataSet customerDataSet = db.ExecuteDataSet(dbCom mand);使用Using及早开释对象Database db = DatabaseFactory.CreateDatabaseQ;DbCommand dbCommand = db.GetSqIStringCommand ("Select Name, Address From Customers");using (IDataReader dataReader = db.ExecuteReader(dbC ommand)(/ Process results4 .参数处理Da

55、tabase类提供了如下的方法，用于参数的处理：AddParameter.传递参数给储备过程AddlnParameter.传递输入参数给储备过程AddOutParameter.传递输出参数给储备过程GetParameterValue.得到指定参数的值SetParameterValue.设定参数值使用示例如下：Database db = DatabaseFactory.CreateDatabaseQ;string sqlCommand = "GetProductDetails0;DbCommand dbCommand = db.GetStoredProcComman d(sqlComm

56、and);db.AddlnParametedbCommand, "ProductID", DbType.I nt32z 5);db.AddOutParameter(dbCommand, "ProductName", DbT ype.String, 50);db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8);Database db = DatabaseFactory.CreateDatabase();DbCommand insertCommand = db.Get

57、StoredProcCom mand(" Add Product");db.AddInParameter(insertCommand, nProductName,/ Db Type.String, TroductName11, DataRowVersion.Current);db.AddInParameter(insertCommand/ "CategorylD11, DbTy pe.Int32, "CategoryID"z DataRowVersion.Current);db.AddlnParameteinsertCommand, '

58、'UnitPrice", DbType.Currency, ,UnitPrice,/ DataRowVersion.Current);4 .使用场景DAAB2.0是对ADO.NET2.0的补充，它承诺你使用相同的数据 访问代码来支持不同的数据库，您通过改变配置文件就在不同的数据 库之间切换。目前尽管只提供SQLServer和Oracle的支持，然而能 够通过 GenericDatabase 和 ADO.NET 2.0 下的 DbProviderFact。 ry对象来增加对其他数据库的支持。假如想要编写出来的数据库访问 程序具有更好的移植性，则DAAB2.0是一个不错的选择，然

59、而假如 您想要针对特定数据库的特性进行编程，就要用ADO.NET 了。参考:Enterprise Libaray -January 2006 关心文档及 QuickS tart好，看到那个地点俺应该差不多明白得使用了，俺就动手试一下 SQL和ACCESS数据库自由切换的方法，因俺平常的适应是使用vb. net写东西，因此只写出的代码出来,有爱好的自己改成C# 好了，看以下html代码：<%. Page Language="VB" AutoEventWireup="false" CodeFile= "sql,aspx.vb" Inherits="sql" %><!DOCTYPE html PUBLIC "-/W3C/DTD XHTML 1.0 Tra nsitional/ENn ":/ /TR/xhtmll/DTD/xhtmll-tra nsitional.dtd"><html