Enterprise Library20 企业库常用方法

1、Enterprise Library 2.0 - 企业库常用方法 下面我们重点来说一下 DataAccess Application Block 操作数据库 的方法。1、创建一个数据库实例 DataBase /创建一个 默认的数据库 实例Database defaultdb = DatabaseFactory.CreateDatabase(); /创建一个 名为 Connection String 的数据库 实例Database db = DatabaseFactory.CreateDatabase(Connection S tring);/上面两种创建数据库实例的方法的数据库可以是任何类型的

2、数据 库，取决于 Config 文件中的配置信息 /下面的这种方面展示了创建一个 SQL 数据库的实例 ,需引用 Micros oft.Practices.EnterpriseLibrary.Data.Sql 程序集SqlDatabase dbsql = DatabaseFactory.CreateDatabase(Connec tion String) as SqlDatabase;/我们同样也可以不通过配置文件来创建数据库实例，如下 string connString = server=.;database=EnterpriseLibrary;uid=sa; pwd=;SqlDatabas

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

4、 /创建一个存储过程的 DbCommand/存储过程名称为 GetAllPersonByNamestring procName = GetAllPersonByName;DbCommand Procdbcomm = db. GetStoredProcCommand (procN ame);3、存储过程中的参数处理DataBase 类中包含多种传递存储过程参数的方法，也包含了得到和 设置参数值的方法，如下：AddParameter. 为存储过程传递一个参数 (输入型或输出型 )AddInParameter. 为存储过程传递一个输入型参数AddOutParameter. 为存储过程传递一个输出型参

5、数 GetParameterValue. 获取某存储过程指定参数的值 SetParameterValue. 为存储过程的某个参数赋值，当你需要用同一 个方法来执行插入多条记录时，就可参数给参数赋值的方法来进行 /创建一个存储过程的 DbCommand/存储过程名称为 GetAllPersonByName string procName = GetAllPersonByName;DbCommand Procdbcomm = db.GetStoredProcCommand(procN ame);/添加一个输入型的参数db.AddInParameter(Procdbcomm, sex, DbType

6、.String); /添加一个输出型的参数db.AddOutParameter(Procdbcomm, name, DbType.String,20); /设置参数的值db.SetParameterValue (Procdbcomm, sex, 男 ); /执行存储过程db.ExecuteNonQuery(Procdbcomm);/得到输出参数的值 ,注意转化返回值类型string outvalue = (string) db. GetParameterValue (Procdbcomm, name);4、执行 ExecuteReader 方法返回一个 IDataReader 类型的数据集 因

7、为 ExecuteReader 方法在一开始执行时就打开了一个与数据库的 连接，所以我们必须注意在使用结束时关闭连接，而用 using() 语句 块就能确保 ExecuteReader 方法在执行完毕时关闭与数据库的连接 。 什么时候使用此方法： 返回的数据仅仅用来显示，并不对其进行修改，删除等操作； 绑定返回的数据到 WebForm Control 不需要缓存返回的数据，用完就释放using (IDataReader reader = db.ExecuteReader(CommandType.Text, select * from person)DataGrid1.DataSource =

8、reader;DataGrid1.DataBind();5、执行 ExecuteDataSet 方法返回一个 DataSetpublic DataSet GetPersonList()Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.GetSqlStringCommand(select * fro m person);return db.ExecuteDataSet(dbcomm);什么时候使用此方法： 需要返回多表数据； 如果你需要逐一访问数据库的每一条记录，这个时候如果使用 Data Reader 的话

9、，则会使与数据库的连接一直处于打开的状态，长此以 往，就会使应用程序的性能和可伸缩性大大降低； 需要和其他的应用程序交换数据；6、执行 ExecuteNonQuery该方法返回的是 SQL 语句执行影响的行数， 我们可以利用该方法来 执行一些没有返回值的操作 (Insert,Update,Delete)public void AddPerson()Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.GetSqlStringCommand(insert person values(1,shy, 女,123456

10、);db.ExecuteNonQuery(dbcomm);7、执行 ExecuteScalar 返回单值public string GetPersonName()Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.GetSqlStringCommand(select e from person);return (string)db.ExecuteScalar(dbcomm);8、事务处理public void UseTransaction()Database db = DatabaseFactory.Crea

11、teDatabase();DbCommand dbcomm1 = db.GetSqlStringCommand(update rson set name=pw);intonampefroDbCommand dbcomm2 = db.GetSqlStringCommand(delete m person where id=1);using (DbConnection conn = db.CreateConnection() ) /打开连接 conn.Open();/创建事务DbTransaction trans = conn.BeginTransaction(); try db.ExecuteN

12、onQuery(dbcomm1); db.ExecuteNonQuery(dbcomm2);/都执行成功则提交事务 trans.Commit();catch(Exception)/发生异常，事务回滚 trans.Rollback();/关闭连接 conn.Close();9、执行 ExecuteXmlReader 返回 XML 数据支持 Sql Server 2000 及以后版本的数据库，对微软以外的数据库 应该不支持。public void UseXMLReader()SqlDatabase dbSQL = DatabaseFactory.CreateDatabase(Conn ection

13、 String) as SqlDatabase;/ 要返回 XML 数据需要在 SQL 语句后加 FOR XML AUTO string sqlCommand = SELECT ProductID, ProductName FRO M Products FOR XML AUTO;DbCommand dbCommand = dbSQL.GetSqlStringCommand(sql Command);XmlReader personReader = null;StringBuilder personlist = new StringBuilder();trypersonReader = dbSQ

14、L. ExecuteXmlReader (dbCommand);/ 循环向 XML 中写入我们查询得到的数据while (!personReader.EOF)if (personReader.IsStartElement() personlist.Append(personReader.ReadOuterXml(); personlist.Append(Environment.NewLine);finally/ 关闭 Reader.if (personReader != null) personReader.Close();/ 关闭数据库连接if (dbCommand.Connection !

15、= null) dbCommand.Connection.Close(); 10 、用 DataSet 批量的添加，修改，删除数据public void UpdateDataBase()Database db = DatabaseFactory.CreateDatabase();DataSet personDataSet = new DataSet();string sqlCommand = Select * from person;DbCommand dbCommand = db.GetSqlStringCommand(sqlCom mand);string personTable = pe

16、rson;/ 得到初始化数据db.LoadDataSet(dbCommand, personDataSet, personTable);/ 得到未修改前的数据集DataTable table = personDataSet.TablespersonTable;/ 往 DataSet 中添加一行数据DataRow addedRow = table.Rows.Add(new object 18, New person, 男 , 654321 );/ 修改 table.Rows0ProductName = Modified product;/ 下面分别创建添加，修改，删除的操作DbCommand i

17、nsertCommand = db.GetStoredProcCommand(Ad dPerson);db.AddInParameter(insertCommand, Name, DbType.String, N ame, DataRowVersion.Current);db.AddInParameter(insertCommand, Sex, DbType.String, Sex, DataRowVersion.Current);db.AddInParameter(insertCommand, ID, DbType.Int32, ID, D ataRowVersion.Current);Db

18、Command deleteCommand = db.GetStoredProcCommand(D eletePerson);db.AddInParameter(deleteCommand, ID, DbType.Int32, ID, D ataRowVersion.Current);DbCommand updateCommand = db.GetStoredProcCommand(UpdatePerson) ; db.AddInParameter(updateCommand, Name, DataRowVersion.Current); db.AddInParameter(updateCom

19、mand, x, DataRowVersion.Current); db.AddInParameter(insertCommand, ataRowVersion.Current);Name, DbType.String, Sex, DbType.String, SeID, DbType.Int32, ID, D/ 提交对 DataSet 的修改，并返回影响的行数int rowsAffected = db.UpdateDataSet(productsDataSet, Product s, insertCommand, updateCommand, deleteCommand , Microso

20、ft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);/-Enterprise Library2.0数据库常用操作2 (不同版本的解释)今天学习了 Enterprise Library2.0 的 Data Access Application Block, Data Access Application Block提供了通用的数据访问的功 能，随着2.0版本的推出有了很大变化。俺就多写了对 SQL和ACC ESS数据库自由切换的一些代码出来共享。先看完原文再接俺的代 码吧。一. 改进在DAAB1.1里面我们知道 Databas

21、e方法返回或者创建一个 DB CommandWrapper 对象，而在 DAAB2.0 里面移除了 DBCommand Wrapper类，用ADO.NET2.0里面的DBCommand类代替实现类似 的功能，这样使得DAAB跟我们的.NET类库的结合更加紧密，回忆 一下我们在1.1里面用DBCommandWrapper 来访问数据时的代 码：二. 使用示例Database db = DatabaseFactory.CreateDatabase();DBComma ndWrapper dbComma nd = db.GetStoredProcComma ndWrapper(GetProducts

22、ByCategory);dbComma nd.Addl nParameter(CategorylD,DbType.l nt32,Category);DataSet productDataSet = db.ExecuteDataSet(dbCommand);而用了新的DBCommand类之后则变成了：Database db = DatabaseFactory.CreateDatabase();DbComma nd dbComma nd = db.GetStoredProcComma nd(”GetProductsByCategory);db.AddI nParameter(dbComma nd,

23、CategoryID, DbTypent32, Category);DataSet productDataSet = db.ExecuteDataSet(dbComman d);数据库连接字符串在我们基于数据库的开发永远是少不了的，但 是在DAAB1.1下，它所使用的字符串跟我们在.NET类库中使用的连 接字符串却是不能共享的，它们分别保存在不同的位置。而在2.0的Data Access Application Block 使用了 ADO.NET2.0 里面connect ionStrings配置区，这样带来的一个好处是连接字符串可以在 Appli cation Block和自定义的.NET类

24、之间共享使用该配置区，如：vconn ecti on Stri ngsvaddn ame二DataAccessQuickStartproviderName二System.Data.SqlClie nt co nn ectio nStri ng二server=(local)SQLEXPRESS;database二EntLibQuickStarts;lntegratedSecurity二true /v/conn ecti on Stri ngs在.NET2.0下，泛型编程已经成为了一个核心，而 2.0版的DAAB中也新增了一个GenericDatabase对象。DAAB中虽然已经包含 了 SqlD

25、atabase和OrcaleDatabase ，但是如果我们需要使用其他的 像DB2等数据库时，就需要用到 GenericDatabase ，它可以用于任 何.NET类库中的数据提供者，包括 OdbcProvider和OleDbProvide r。DAAB2.0的配置非常简单，主要有以下几方面的配置：配置连接字符串38Enterprise Libriary Confi griration*File Action Kelp曰4Q D:Frogrom FileVEnt Ent&rprist Library ConfiguralienQ D:Frogrom FileEnt erpr i se Lib

26、r ary 2. 0(3 Data Access Application BlockEntrpris LiIrary CnfixrticnerpriseLibrary 2.0(.-j 肝 D&ta Access Applieati+j Custom Frovidtr lppirigsBlockonCustom Prcvidr MingsJ二ini 凶日 General lameProvi derHain eD &t&A电c电EQui uk5t/r七 Syst e*. Data. SqlCli entGets or s电ts the name of the node.ConfiguiatiDn

27、Errorsidd a new ccnriecticxi etring param$t$r配置默认数据库添加相关的命名空间:using Microsoft.Practices.EnterpriseLibrary.Data;using System.Data;使用Data Access Application Block进行数据的读取和操作，般分为三步：1 .创建Database对象2.提供命令参数，如果需要的话3执行命令下面分别看一下 DataAccessQuickStart 中提供的一些例子：执行静态的 SQL 语句public string GetCustomerList()/ 创建 Da

28、tabase 对象Database db = DatabaseFactory.CreateDatabase();/ 使用 SQL 语句创建 DbCommand 对象string sqlCommand = Select CustomerID, Name, Address, City, Country, PostalCode +From Customers;DbCommand dbCommand = db.GetSqlStringCommand(sqlC ommand);StringBuilder readerData = new StringBuilder();/ 调用 ExecuteReade

29、r 方法using (IDataReader dataReader = db.ExecuteReader(dbCo mmand)while (dataReader.Read()/ Get the value of the Name column in the DataRe aderreaderData.Append(dataReaderName);readerData.Append(Environment.NewLine);return readerData.ToString();执行存储过程并传递参数，返回 DataSetpublic DataSet GetProductsInCategor

30、y(int Category)/ Create the Database object, using the default databas e service. The/ default database service is determined through config uration.Database db = DatabaseFactory.CreateDatabase();string sqlCommand = GetProductsByCategory;DbCommand dbCommand = db.GetStoredProcCommand (sqlCommand);/ R

31、etrieve products from the specified category.db.AddInParameter(dbCommand, CategoryID, DbType.I nt32, Category);/ DataSet that will hold the returned resultsDataSet productsDataSet = null;productsDataSet = db.ExecuteDataSet(dbCommand);/ Note: connection was closed by ExecuteDataSet method callreturn

32、productsDataSet;利用 DataSet 更新数据public int UpdateProducts()/ Create the Database object, using the default databas e service. The/ default database service is determined through config uration.Database db = DatabaseFactory.CreateDatabase();DataSet productsDataSet = new DataSet();string sqlCommand = S

33、elect ProductID, ProductName, CategoryID, UnitPrice, LastUpdate +From Products;DbCommand dbCommand = db.GetSqlStringCommand(s qlCommand);string productsTable = Products;/ Retrieve the initial datadb.LoadDataSet(dbCommand, productsDataSet, products Table);/ Get the table that will be modifiedDataTabl

34、e table = productsDataSet.TablesproductsTable;/ Add a new product to existing DataSetDataRow addedRow = table.Rows.Add(new object DB Null.Value, New product, 11, 25);/ Modify an existing producttable.Rows0ProductName = Modified product;/ Establish our Insert, Delete, and Update commandsDbCommand ins

35、ertCommand = db.GetStoredProcComma nd(AddProduct);db.AddInParameter(insertCommand, ProductName, DbT ype.String, ProductName, DataRowVersion.Current);db.AddInParameter(insertCommand, CategoryID, DbTyp e.Int32, CategoryID, DataRowVersion.Current);db.AddInParameter(insertCommand, UnitPrice, DbType. Cur

36、rency, UnitPrice, DataRowVersion.Current);DbCommand deleteCommand = db.GetStoredProcComm and(DeleteProduct);db.AddInParameter(deleteCommand, ProductID, DbType. Int32, ProductID, DataRowVersion.Current);DbCommand updateCommand = db.GetStoredProcComm and(UpdateProduct);db.AddInParameter(updateCommand,

37、 ProductID, DbTyp e.Int32, ProductID, DataRowVersion.Current);db.AddInParameter(updateCommand, ProductName, Db Type.String, ProductName, DataRowVersion.Current);db.AddInParameter(updateCommand, LastUpdate, DbTy pe.DateTime, LastUpdate, DataRowVersion.Current);/ Submit the DataSet, capturing the numb

38、er of rows th at were affectedint rowsAffected = db.UpdateDataSet(productsDataSet, Products, insertCommand, updateCommand,deleteCommand, UpdateBehavior.St andard);return rowsAffected;通过 ID 获取记录详细信息public string GetProductDetails(int productID)/ Create the Database object, using the default databas e

39、 service. The/ default database service is determined through config uration.Database db = DatabaseFactory.CreateDatabase();string sqlCommand = GetProductDetails;DbCommand dbCommand = db.GetStoredProcCommand (sqlCommand);/ Add paramters/ Input parameters can specify the input valuedb.AddInParameter(

40、dbCommand, ProductID, DbType.Int 32, productID);/ Output parameters specify the size of the return data db.AddOutParameter(dbCommand, ProductName, DbTy pe.String, 50);db.AddOutParameter(dbCommand, UnitPrice, DbType.C urrency, 8);db.ExecuteNonQuery(dbCommand);/ Row of data is captured via output para

41、metersstring 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

42、 refers to a SQL Server database.SqlDatabase dbSQL = DatabaseFactory.CreateDatabase() as SqlDatabase;/ Use FOR XML AUTO to have SQL return XML datastring sqlCommand = Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate +From Products FOR XML AUTO;DbCommand dbCommand = dbSQL.GetSqlString

43、Comma nd(sqlCommand);XmlReader productsReader = null;StringBuilder productList = new StringBuilder();tryproductsReader = dbSQL.ExecuteXmlReader(dbComm and);/ Iterate through the XmlReader and put the data into our results.while (!productsReader.EOF)if (productsReader.IsStartElement()productList.Appe

44、nd(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.Cl

45、ose();return productList.ToString();使用事务public bool Transfer(int transactionAmount, int sourceAccou nt, int destinationAccount)bool result = false;/ Create the Database object, using the default databas e service. The/ default database service is determined through config uration.Database db = Datab

46、aseFactory.CreateDatabase();/ Two operations, one to credit an account, and one to debit another/ account.string sqlCommand = CreditAccount;DbCommand creditCommand = db.GetStoredProcComma nd(sqlCommand);db.AddInParameter(creditCommand, AccountID, DbType.Int32, sourceAccount);db.AddInParameter(credit

47、Command, Amount, DbType.In t32, transactionAmount);sqlCommand = DebitAccount;DbCommand debitCommand = db.GetStoredProcComma nd(sqlCommand);db.AddInParameter(debitCommand, AccountID, DbType. Int32, destinationAccount);db.AddInParameter(debitCommand, Amount, DbType.Int 32, transactionAmount);using (Db

48、Connection connection = db.CreateConnection() connection.Open();DbTransaction transaction = connection.BeginTransacti on();try/ Credit the first accountdb.ExecuteNonQuery(creditCommand, transaction);/ Debit the second accounttransaction);db.ExecuteNonQuery(debitCommand,/ Commit the transactiontransa

49、ction.Commit();result = true;catch/ Rollback transactiontransaction.Rollback();connection.Close();return result;三常见功能1创建 Database 对象创建一个默认的 Database 对象Database dbSvc = DatabaseFactory.CreateDatabase();默认的数据库在配置文件中：创建一个实例 Database 对象/ Use a named database instance that refers to an arbitra ry databas

50、e type,/ which is determined by configuration information.Database myDb = DatabaseFactory.CreateDatabase(DataA ccessQuickStart);创建一个具体的类型的数据库对象/ Create a SQL database.SqlDatabase dbSQL = DatabaseFactory.CreateDatabase(D ataAccessQuickStart) as SqlDatabase;2创建 DbCommand 对象静态的 SQL 语句创建一个 DbCommandData

51、base db = DatabaseFactory.CreateDatabase();string sqlCommand = Select CustomerID, LastName, First Name From Customers;DbCommand dbCommand = db.GetSqlStringCommand(sqlC ommand);存储过程创建一个 DbCommandDatabase db = DatabaseFactory.CreateDatabase();DbCommand dbCommand = db.GetStoredProcCommand(G etProductsB

52、yCategory);3管理对象当连接对象打开后，不需要再次连接Database db = DatabaseFactory.CreateDatabase();string sqlCommand = Select ProductID, ProductName Fro m Products;DbCommand dbCommand = db.GetSqlStringCommand(sqlC ommand);/ No need to open the connection; just make the call.DataSet customerDataSet = db.ExecuteDataSet(dbComman d);使用 Using 及早释放对象Database db = DatabaseFactory.CreateDatabase();DbCommand dbCommand = db.GetSqlStringCommand(Sel ect Name, Address From Customers);using (IDataReader dataReader =