Enterprise-Library 20--企业库常用方法.doc

Enterprise Library 2.0 -企业库常用方法下面我们重点来说一下DataAccess Application Block操作数据库的方法。1、创建一个数据库实例DataBase/创建一个默认的数据库实例Database defaultdb = DatabaseFactory.CreateDatabase();/创建一个名为Connection String的数据库实例Database db = DatabaseFactory.CreateDatabase(Connection String);/上面两种创建数据库实例的方法的数据库可以是任何类型的数据库，取决于Config文件中的配置信息/下面的这种方面展示了创建一个SQL数据库的实例,需引用Microsoft.Practices.EnterpriseLibrary.Data.Sql程序集SqlDatabase dbsql = DatabaseFactory.CreateDatabase(Connection String) as SqlDatabase;/我们同样也可以不通过配置文件来创建数据库实例，如下string connString = server=.;database=EnterpriseLibrary;uid=sa;pwd=;SqlDatabase nocofigdb = new SqlDatabase(connString);2、创建DbCommandDbCommand的创建方式有两种：一种是为了调用存储过程创建，一种是为了执行一般的SQL语句而创建，具体用哪一种方式，就要看你需要执行的是存储过程还是SQL语句了。/创建一个SQL语句的DbCommandDatabase db = DatabaseFactory.CreateDatabase();string sql = Select * from person;DbCommand Sqldbcomm = db.GetSqlStringCommand(sql);/创建一个存储过程的DbCommand/存储过程名称为GetAllPersonByNamestring procName = GetAllPersonByName;DbCommand Procdbcomm = db.GetStoredProcCommand(procName);3、存储过程中的参数处理DataBase类中包含多种传递存储过程参数的方法，也包含了得到和设置参数值的方法，如下：AddParameter. 为存储过程传递一个参数(输入型或输出型) AddInParameter. 为存储过程传递一个输入型参数AddOutParameter. 为存储过程传递一个输出型参数GetParameterValue. 获取某存储过程指定参数的值 SetParameterValue. 为存储过程的某个参数赋值，当你需要用同一个方法来执行插入多条记录时，就可参数给参数赋值的方法来进行/创建一个存储过程的DbCommand/存储过程名称为GetAllPersonByNamestring procName = GetAllPersonByName;DbCommand Procdbcomm = db.GetStoredProcCommand(procName);/添加一个输入型的参数db.AddInParameter(Procdbcomm, sex, DbType.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类型的数据集因为ExecuteReader方法在一开始执行时就打开了一个与数据库的连接，所以我们必须注意在使用结束时关闭连接，而用using()语句块就能确保ExecuteReader方法在执行完毕时关闭与数据库的连接。什么时候使用此方法：返回的数据仅仅用来显示，并不对其进行修改，删除等操作； 绑定返回的数据到WebForm Control不需要缓存返回的数据，用完就释放using (IDataReader reader = db.ExecuteReader(CommandType.Text, 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(dbcomm);什么时候使用此方法：需要返回多表数据；如果你需要逐一访问数据库的每一条记录，这个时候如果使用DataReader的话，则会使与数据库的连接一直处于打开的状态，长此以往，就会使应用程序的性能和可伸缩性大大降低；需要和其他的应用程序交换数据；6、执行ExecuteNonQuery该方法返回的是SQL语句执行影响的行数，我们可以利用该方法来执行一些没有返回值的操作(Insert,Update,Delete)public void AddPerson()Database db = DatabaseFactory.CreateDatabase();DbCommand dbcomm = db.GetSqlStringCommand(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);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);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 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 = 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();personlist.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 DataSet();string sqlCommand = Select * from person;DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);string personTable = person;/ 得到初始化数据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 insertCommand = db.GetStoredProcCommand(AddPerson);db.AddInParameter(insertCommand, 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 deleteCommand = db.GetStoredProcCommand(DeletePerson);db.AddInParameter(deleteCommand, ID, DbType.Int32, ID, DataRowVersion.Current);DbCommand updateCommand = db.GetStoredProcCommand(UpdatePerson);db.AddInParameter(updateCommand, Name, DbType.String, 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.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 Block提供了通用的数据访问的功能，随着2.0版本的推出有了很大变化。俺就多写了对SQL和ACCESS数据库自由切换的一些代码出来共享。先看完原文再接俺的代码吧。一改进在DAAB1.1里面我们知道Database方法返回或者创建一个DBCommandWrapper对象，而在DAAB2.0里面移除了DBCommandWrapper类，用ADO.NET2.0里面的DBCommand类代替实现类似的功能，这样使得DAAB跟我们的.NET类库的结合更加紧密，回忆一下我们在1.1里面用DBCommandWrapper来访问数据时的代码： 二使用示例Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper dbCommand = db.GetStoredProcCommandWrapper(GetProductsByCategory); dbCommand.AddInParameter(CategoryID, DbType.Int32, Category); DataSet productDataSet = db.ExecuteDataSet(dbCommand);而用了新的DBCommand类之后则变成了： Database db = DatabaseFactory.CreateDatabase(); DbCommand dbCommand = db.GetStoredProcCommand(GetProductsByCategory); db.AddInParameter(dbCommand, CategoryID, DbType.Int32, Category); DataSet productDataSet = db.ExecuteDataSet(dbCommand);数据库连接字符串在我们基于数据库的开发永远是少不了的，但是在DAAB1.1下，它所使用的字符串跟我们在.NET类库中使用的连接字符串却是不能共享的，它们分别保存在不同的位置。而在2.0的Data Access Application Block使用了ADO.NET2.0里面配置区，这样带来的一个好处是连接字符串可以在Application Block和自定义的.NET类之间共享使用该配置区，如： 在.NET2.0下，泛型编程已经成为了一个核心，而2.0版的DAAB中也新增了一个GenericDatabase对象。DAAB中虽然已经包含了SqlDatabase和OrcaleDatabase，但是如果我们需要使用其他的像DB2等数据库时，就需要用到GenericDatabase，它可以用于任何.NET类库中的数据提供者，包括OdbcProvider和OleDbProvider。DAAB2.0的配置非常简单，主要有以下几方面的配置：配置连接字符串配置默认数据库添加相关的命名空间： 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.CreateDatabase(); / 使用SQL语句创建DbCommand对象 string sqlCommand = Select CustomerID, Name, Address, City, Country, PostalCode + From Customers; DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); StringBuilder readerData = new StringBuilder(); / 调用ExecuteReader方法 using (IDataReader dataReader = db.ExecuteReader(dbCommand) while (dataReader.Read() / Get the value of the Name column in the DataReader readerData.Append(dataReaderName); readerData.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 configuration. Database db = DatabaseFactory.CreateDatabase(); string sqlCommand = GetProductsByCategory; DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand); / Retrieve products from the specified category. db.AddInParameter(dbCommand, CategoryID, DbType.Int32, Category); / DataSet that will hold the returned results DataSet productsDataSet = null; productsDataSet = db.ExecuteDataSet(dbCommand); / Note: connection was closed by ExecuteDataSet method call return productsDataSet; 利用DataSet更新数据public int UpdateProducts() / Create 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 sqlCommand = Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate + From Products; DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); string productsTable = Products; / 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 existing DataSet DataRow addedRow = table.Rows.Add(new object DBNull.Value, New product, 11, 25); / Modify an existing product table.Rows0ProductName = Modified product; / Establish our Insert, Delete, and Update commands DbCommand insertCommand = db.GetStoredProcCommand(AddProduct); db.AddInParameter(insertCommand, ProductName, DbType.String, ProductName, DataRowVersion.Current); db.AddInParameter(insertCommand, CategoryID, DbType.Int32, CategoryID, DataRowVersion.Current); db.AddInParameter(insertCommand, UnitPrice, DbType.Currency, UnitPrice, DataRowVersion.Current); DbCommand deleteCommand = db.GetStoredProcCommand(DeleteProduct); db.AddInParameter(deleteCommand, ProductID, DbType.Int32, ProductID, DataRowVersion.Current); DbCommand updateCommand = db.GetStoredProcCommand(UpdateProduct); db.AddInParameter(updateCommand, ProductID, DbType.Int32, ProductID, DataRowVersion.Current); db.AddInParameter(updateCommand, ProductName, DbType.String, ProductName, DataRowVersion.Current); db.AddInParameter(updateCommand, LastUpdate, DbType.DateTime, LastUpdate, DataRowVersion.Current); / Submit the DataSet, capturing the number of rows that were affected int rowsAffected = db.UpdateDataSet(productsDataSet, Products, insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard); 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 = DatabaseFactory.CreateDatabase(); string sqlCommand = GetProductDetails; DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand); / Add paramters / Input parameters can specify the input value db.AddInParameter(dbCommand, ProductID, DbType.Int32, productID); / Output parameters specify the size of the return data db.AddOutParameter(dbCommand, ProductName, DbType.String, 50); db.AddOutParameter(dbCommand, UnitPrice, DbType.Currency, 8); db.ExecuteNonQuery(dbCommand); / Row of data is captured via output parameters string 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 refers to a SQL Server database. SqlDatabase dbSQL = DatabaseFactory.CreateDatabase() as SqlDatabase; / Use FOR XML AUTO to have SQL return XML data string sqlCommand = Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate + From Products FOR XML AUTO; DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand); XmlReader productsReader = null; StringBuilder productList = new StringBuilder(); try productsReader = dbSQL.ExecuteXmlReader(dbCommand); / Iterate through the XmlReader and put the data into our results. while (!productsReader.EOF) if (productsReader.IsStartElement() productList.Append(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.Close(); return productList.ToString(); 使用事务public bool Transfer(int transactionAmount, int sourceAccount, int destinationAccount) bool result = false; / Create the Database object, using the default database service. The / default database service is determined through configuration. Database db = DatabaseFactory.CreateDatabase(); / Two operations, one to credit an account, and one to debit another / account. string sqlCommand = CreditAccount; DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand); db.AddInParameter(creditCommand, AccountID, DbType.Int32, sourceAccount); db.AddInParameter(creditCommand, Amount, DbType.Int32, transactionAmount); sqlCommand = DebitAccount; DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand); db.AddInParameter(debitCommand, AccountID, DbType.Int32, destinationAccount); db.AddInParameter(debitCommand, Amount, DbType.Int32, transactionAmount); using (DbConnection connection = db.CreateConnection() connection.Open(); DbTransaction transaction = connection.BeginTransaction(); try / Credit the first account db.ExecuteNonQuery(creditCommand, transaction); / Debit the second account db.ExecuteNonQuery(debitCommand, transaction); / Commit the transaction transaction.Commit(); result = true; catch / Rollback transaction transaction.Rollback(); connection.Close(); return result; 三常见功能1创建Database对象创建一个默认的Database对象Database dbSvc = DatabaseFactory.CreateDatabase(); 默认的数据库在配置文件中： 创建一个实例Database对象/ Use a named database instance that refers to an arbitrary database 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对象静态的SQL语句创建一个DbCommandDatabase db = DatabaseFactory.CreateDatabase(); string sqlCommand = Select CustomerID, LastName, FirstName From Customers; DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand); 存储过程创建一个DbCommandDatabase db = DatabaseFactory.CreateDatabase(); DbCommand dbCommand = 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; just make the call. DataSet customerDataSet = db.ExecuteDataSet(