publicstring 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(dataReader["Name"]); readerData.Append(Environment.NewLine); } } return readerData.ToString(); }
执行存储过程并传递参数,返回DataSet
public 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更新数据
publicint 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.Tables[productsTable]; // Add a new product to existing DataSet DataRow addedRow = table.Rows.Add(newobject[] ...{DBNull.Value, "New product", 11, 25}); // Modify an existing product table.Rows[0]["ProductName"] ="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获取记录详细信息
publicstring 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格式返回数据
publicstring 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(); }
使用事务
publicbool 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; } }
// 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语句创建一个DbCommand
Database db = DatabaseFactory.CreateDatabase(); string sqlCommand ="Select CustomerID, LastName, FirstName From Customers"; DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
存储过程创建一个DbCommand
Database 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(dbCommand);
使用Using及早释放对象
Database db = DatabaseFactory.CreateDatabase(); DbCommand dbCommand = db.GetSqlStringCommand("Select Name, Address From Customers"); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) ...{ // Process results }
Data Access Application Block提供了通用的数据访问的功能,随着2.0版本的推出有了很大变化。一.改进在DAAB1.1里面我们知道Database方法返回或者创建一个DBCommandWrapper对象,而在DAAB2.0里面移除了DBCommandWrapper类,用ADO.NET2.0里面的DBCommand类代替实现类似的功能,这样使得DAAB跟我们的.NET类库的