Enterprise-Library20--企业库常用方法

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

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

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

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

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

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

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

8、t, "select * from person")DataGrid1.DataSource = reader;DataGrid1.DataBind();5、执行ExecuteDataSet方法返回一个DataSetpublic DataSet GetPersonList()Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.GetSqlStringCommand("select * from person");return db.ExecuteDataSet(dbco

9、mm);什么时候使用此方法：需要返回多表数据；如果你需要逐一访问数据库的每一条记录，这个时候如果使用DataReader的话，则会使与数据库的连接一直处于打开的状态，长此以往，就会使应用程序的性能和可伸缩性大大降低；需要和其他的应用程序交换数据；6、执行ExecuteNonQuery该方法返回的是SQL语句执行影响的行数，我们可以利用该方法来执行一些没有返回值的操作(Insert,Update,Delete)public void AddPerson()Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.Ge

10、tSqlStringCommand("insert into person values(1,'shy','女','123456')");db.ExecuteNonQuery(dbcomm);7、执行ExecuteScalar返回单值public string GetPersonName()Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.GetSqlStringCommand("select name from person&q

11、uot;);return (string)db.ExecuteScalar(dbcomm);8、事务处理public void UseTransaction()Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm1 = db.GetSqlStringCommand("update person set name='pw'");DbCommand dbcomm2 = db.GetSqlStringCommand("delete from person where id=1&qu

12、ot;);using (DbConnection conn = db.CreateConnection()/打开连接conn.Open();/创建事务DbTransaction trans = conn.BeginTransaction();trydb.ExecuteNonQuery(dbcomm1);db.ExecuteNonQuery(dbcomm2);/都执行成功则提交事务trans.Commit();catch(Exception)/发生异常，事务回滚trans.Rollback();/关闭连接conn.Close();9、执行ExecuteXmlReader返回XML数据支持Sql

13、Server 2000 及以后版本的数据库，对微软以外的数据库应该不支持。public void UseXMLReader()SqlDatabase dbSQL = DatabaseFactory.CreateDatabase("Connection String") as SqlDatabase;/ 要返回XML数据需要在SQL语句后加 FOR XML AUTOstring sqlCommand = "SELECT ProductID, ProductName FROM Products FOR XML AUTO"DbCommand dbCommand

14、 = dbSQL.GetSqlStringCommand(sqlCommand);XmlReader personReader = null;StringBuilder personlist = new StringBuilder();trypersonReader = dbSQL.ExecuteXmlReader(dbCommand);/ 循环向XML中写入我们查询得到的数据while (!personReader.EOF)if (personReader.IsStartElement()personlist.Append(personReader.ReadOuterXml();person

15、list.Append(Environment.NewLine);finally/ 关闭 Reader.if (personReader != null)personReader.Close();/ 关闭数据库连接if (dbCommand.Connection != null)dbCommand.Connection.Close();10、用DataSet批量的添加，修改，删除数据public void UpdateDataBase()Database db = DatabaseFactory.CreateDatabase();DataSet personDataSet = new Data

16、Set();string sqlCommand = "Select * from person"DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);string personTable = "person"/ 得到初始化数据db.LoadDataSet(dbCommand, personDataSet, personTable);/ 得到未修改前的数据集DataTable table = personDataSet.TablespersonTable;/ 往DataSet中添加一行数据Data

17、Row addedRow = table.Rows.Add(new object 18, "New person", "男", "654321" );/ 修改table.Rows0"ProductName" = "Modified product"/ 下面分别创建添加，修改，删除的操作DbCommand insertCommand = db.GetStoredProcCommand("AddPerson");db.AddInParameter(insertCommand, &

18、quot;Name", DbType.String, "Name", DataRowVersion.Current);db.AddInParameter(insertCommand, "Sex", DbType.String, "Sex", DataRowVersion.Current);db.AddInParameter(insertCommand, "ID", DbType.Int32, "ID", DataRowVersion.Current);DbCommand deleteC

19、ommand = db.GetStoredProcCommand("DeletePerson");db.AddInParameter(deleteCommand, "ID", DbType.Int32, "ID", DataRowVersion.Current);DbCommand updateCommand = db.GetStoredProcCommand("UpdatePerson");db.AddInParameter(updateCommand, "Name", DbType.Stri

20、ng, "Name", DataRowVersion.Current);db.AddInParameter(updateCommand, "Sex", DbType.String, "Sex", DataRowVersion.Current);db.AddInParameter(insertCommand, "ID", DbType.Int32, "ID", DataRowVersion.Current);/ 提交对DataSet的修改，并返回影响的行数int rowsAffected = db

21、.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, deleteCommand, Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard);ß/->Enterprise Library2.0数据库常用操作2（不同版本的解释）今天学习了Enterprise Library2.0的Data Access Application Block，Data Access Application Bl

22、ock提供了通用的数据访问的功能，随着2.0版本的推出有了很大变化。俺就多写了对SQL和ACCESS数据库自由切换的一些代码出来共享。先看完原文再接俺的代码吧。一改进在DAAB1.1里面我们知道Database方法返回或者创建一个DBCommandWrapper对象，而在DAAB2.0里面移除了DBCommandWrapper类，用ADO.NET2.0里面的DBCommand类代替实现类似的功能，这样使得DAAB跟我们的.NET类库的结合更加紧密，回忆一下我们在1.1里面用DBCommandWrapper来访问数据时的代码： 二使用示例Database db = DatabaseFactory

23、.CreateDatabase(); DBCommandWrapper dbCommand = db.GetStoredProcCommandWrapper("GetProductsByCategory"); dbCommand.AddInParameter("CategoryID", DbType.Int32, Category); DataSet productDataSet = db.ExecuteDataSet(dbCommand);而用了新的DBCommand类之后则变成了： Database db = DatabaseFactory.Crea

24、teDatabase(); DbCommand dbCommand = db.GetStoredProcCommand("GetProductsByCategory"); db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category); DataSet productDataSet = db.ExecuteDataSet(dbCommand);数据库连接字符串在我们基于数据库的开发永远是少不了的，但是在DAAB1.1下，它所使用的字符串跟我们在.NET类库中使用的连接字符串却是不能共享

25、的，它们分别保存在不同的位置。而在2.0的Data Access Application Block使用了ADO.NET2.0里面<connectionStrings>配置区，这样带来的一个好处是连接字符串可以在Application Block和自定义的.NET类之间共享使用该配置区，如： <connectionStrings>         <add            

26、; name="DataAccessQuickStart"              providerName="System.Data.SqlClient"              connectionString="server=(local)SQLEXPRESS;database=EntLi

27、bQuickStarts;Integrated Security=true" /> </connectionStrings>在.NET2.0下，泛型编程已经成为了一个核心，而2.0版的DAAB中也新增了一个GenericDatabase对象。DAAB中虽然已经包含了SqlDatabase和OrcaleDatabase，但是如果我们需要使用其他的像DB2等数据库时，就需要用到GenericDatabase，它可以用于任何.NET类库中的数据提供者，包括OdbcProvider和OleDbProvider。DAAB2.0的配置非常简单，主要有以下几方面的配置：配置连接字

28、符串配置默认数据库添加相关的命名空间： 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.CreateD

29、atabase(); / 使用SQL语句创建DbCommand对象 string sqlCommand = "Select CustomerID, Name, Address, City, Country, PostalCode " +     "From Customers" DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); StringBuilder readerData = new StringBuilder(); / 调用ExecuteReader方法

30、 using (IDataReader dataReader = db.ExecuteReader(dbCommand)     while (dataReader.Read()             / Get the value of the 'Name' column in the DataReader         readerData.Append(dataReade

31、r"Name");         readerData.Append(Environment.NewLine);     return readerData.ToString(); 执行存储过程并传递参数，返回DataSetpublic DataSet GetProductsInCategory(int Category)     / Create the Database object, using the default database

32、service. The     / default database service is determined through configuration.     Database db = DatabaseFactory.CreateDatabase();     string sqlCommand = "GetProductsByCategory"     DbCommand dbCommand = db.GetStoredProcCommand

33、(sqlCommand);     / Retrieve products from the specified category.     db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);     / DataSet that will hold the returned results          &#

34、160;  DataSet productsDataSet = null;     productsDataSet = db.ExecuteDataSet(dbCommand);     / Note: connection was closed by ExecuteDataSet method call     return productsDataSet; 利用DataSet更新数据public int UpdateProducts()     / Create

35、 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 sq

36、lCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " +         "From Products"     DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);     string productsTable = "Products"

37、    / Retrieve the initial data     db.LoadDataSet(dbCommand, productsDataSet, productsTable);     / Get the table that will be modified     DataTable table = productsDataSet.TablesproductsTable;     / Add a new product to ex

38、isting DataSet     DataRow addedRow = table.Rows.Add(new object DBNull.Value, "New product", 11, 25);     / Modify an existing product     table.Rows0"ProductName" = "Modified product"     / Establish our Inser

39、t, Delete, and Update commands     DbCommand insertCommand = db.GetStoredProcCommand("AddProduct");     db.AddInParameter(insertCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);     db.AddInParame

40、ter(insertCommand, "CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);     db.AddInParameter(insertCommand, "UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);     DbCommand deleteCommand = db.GetSto

41、redProcCommand("DeleteProduct");     db.AddInParameter(deleteCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);     DbCommand updateCommand = db.GetStoredProcCommand("UpdateProduct");     db.A

42、ddInParameter(updateCommand, "ProductID", DbType.Int32, "ProductID", DataRowVersion.Current);     db.AddInParameter(updateCommand, "ProductName", DbType.String, "ProductName", DataRowVersion.Current);     db.AddInParameter(updateC

43、ommand, "LastUpdate", DbType.DateTime, "LastUpdate", DataRowVersion.Current);     / Submit the DataSet, capturing the number of rows that were affected     int rowsAffected = db.UpdateDataSet(productsDataSet, "Products", insertCommand, upda

44、teCommand,                                         deleteCommand, UpdateBehavior.Standard);   &

45、#160; 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 = DatabaseF

46、actory.CreateDatabase();     string sqlCommand = "GetProductDetails"     DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);     / Add paramters     / Input parameters can specify the input value     db.AddIn

47、Parameter(dbCommand, "ProductID", DbType.Int32, productID);     / Output parameters specify the size of the return data     db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50);     db.AddOutParameter(dbCommand, "UnitPr

48、ice", DbType.Currency, 8);     db.ExecuteNonQuery(dbCommand);     / Row of data is captured via output parameters     string results = string.Format(CultureInfo.CurrentCulture, "0, 1, 2:C ",         &

49、#160;                          db.GetParameterValue(dbCommand, "ProductID"),               

50、0;                    db.GetParameterValue(dbCommand, "ProductName"),                      

51、;              db.GetParameterValue(dbCommand, "UnitPrice");     return results; 以XML格式返回数据public string GetProductList()     / Use a named database instance that refers to a SQL Server database.

52、     SqlDatabase dbSQL = DatabaseFactory.CreateDatabase() as SqlDatabase;     / Use "FOR XML AUTO" to have SQL return XML data     string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate " +   &#

53、160;     "From Products FOR XML AUTO"     DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand);     XmlReader productsReader = null;     StringBuilder productList = new StringBuilder();     try  

54、60;          productsReader = dbSQL.ExecuteXmlReader(dbCommand);         / Iterate through the XmlReader and put the data into our results.         while (!productsReader.EOF)   &#

55、160;                 if (productsReader.IsStartElement()                             productLis

56、t.Append(productsReader.ReadOuterXml();                 productList.Append(Environment.NewLine);                        

57、     finally           / Close the Reader.       if (productsReader != null)                 productsReader.Close();       &#

58、160;            / Explicitly close the connection. The connection is not closed       / when the XmlReader is closed.       if (dbCommand.Connection != null)         

59、;      dbCommand.Connection.Close();                  return productList.ToString(); 使用事务public bool Transfer(int transactionAmount, int sourceAccount, int destinationAccount)     bool result = f

60、alse;          / Create the Database object, using the default database service. The     / default database service is determined through configuration.     Database db = DatabaseFactory.CreateDatabase();     / Two opera

61、tions, one to credit an account, and one to debit another     / account.     string sqlCommand = "CreditAccount"     DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);     db.AddInParameter(creditCommand, "Account

62、ID", DbType.Int32, sourceAccount);     db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount);     sqlCommand = "DebitAccount"     DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);   

63、0; db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount);     db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount);     using (DbConnection connection = db.CreateConnection()      

64、60;      connection.Open();         DbTransaction transaction = connection.BeginTransaction();         try                  &

65、#160;  / Credit the first account             db.ExecuteNonQuery(creditCommand, transaction);             / Debit the second account         

66、0;   db.ExecuteNonQuery(debitCommand, transaction);             / Commit the transaction             transaction.Commit();          

67、60;               result = true;                 catch                    

68、/ Rollback transaction             transaction.Rollback();                 connection.Close();               &

69、#160;  return result;     三常见功能1创建Database对象创建一个默认的Database对象Database dbSvc = DatabaseFactory.CreateDatabase(); 默认的数据库在配置文件中：<dataConfiguration defaultDatabase="DataAccessQuickStart" /> 创建一个实例Database对象/ Use a named database instance that refers to an arbitrary da

70、tabase 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

71、对象静态的SQL语句创建一个DbCommandDatabase db = DatabaseFactory.CreateDatabase(); string sqlCommand = "Select CustomerID, LastName, FirstName From Customers" DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); 存储过程创建一个DbCommandDatabase db = DatabaseFactory.CreateDatabase(); DbCommand dbCommand

72、= 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; j

73、ust 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

74、 results 4参数处理Database类提供了如下的方法，用于参数的处理：AddParameter. 传递参数给存储过程 AddInParameter. 传递输入参数给存储过程 AddOutParameter. 传递输出参数给存储过程 GetParameterValue. 得到指定参数的值 SetParameterValue. 设定参数值使用示例如下：Database db = DatabaseFactory.CreateDatabase(); string sqlCommand = "GetProductDetails" DbCommand dbCommand = db.GetStoredProcCommand(sqlCom