DataAccessApplicationBlock provides a common data access function, which is greatly changed with the release of MySQL 2.0. 1. Improvements in DAAB1.1 we know that the Database method returns or creates a DBCommandWrapper object, and removed the DBCommandWrapper class in DAAB2.0, using ADO. NET2.0
The Data Access Application Block provides a common Data Access function, which is greatly changed with the release of MySQL 2.0. 1. Improvements in DAAB1.1 we know that the Database method returns or creates a DBCommandWrapper object, and removed the DBCommandWrapper class in DAAB2.0, using ADO. NET2.0
The Data Access Application Block provides a common Data Access function, which is greatly changed with the release of MySQL 2.0.
I. Improvement
In DAAB1.1, we know that the Database method returns or creates a DBCommandWrapper object. In DAAB2.0, The DBCommandWrapper class is removed and ADO is used. the DBCommand class in NET2.0 replaces the implementation of similar functions, so that DAAB and ours. NET class library is more closely integrated. Let's recall the code we used DBCommandWrapper in 1.1 to access data:
Database db = DatabaseFactory. CreateDatabase ();
DBCommandWrapper dbCommand = db. GetStoredProcCommandWrapper ("GetProductsByCategory ");
DbCommand. AddInParameter ("CategoryID", DbType. Int32, Category );
DataSet productDataSet = db. ExecuteDataSet (dbCommand );
After using the new DBCommand class, it becomes:
Database db = DatabaseFactory. CreateDatabase ();
DbCommand dbCommand = db. GetStoredProcCommand ("GetProductsByCategory ");
Db. AddInParameter (dbCommand, "CategoryID", DbType. Int32, Category );
DataSet productDataSet = db. ExecuteDataSet (dbCommand );
Database connection strings are always indispensable in our database-based development. However, in DAAB1.1, the strings used by the database are the same as those used by us. the connection strings used in the. NET class library cannot be shared. They are stored in different locations. In the Data Access Application Block of 2.0, ADO. NET2.0 is used. Configuration area. The advantage of this is that the connection string can be shared between the Application Block and the custom. NET class. For example:
Name = "DataAccessQuickStart"
ProviderName = "System. Data. SqlClient"
ConnectionString = "server = (local) \ SQLEXPRESS; database = EntLibQuickStarts; Integrated Security = true"/>
Under. NET2.0, generic programming has become a core, and a GenericDatabase object has also been added to DAAB of version 2.0. Although the DAAB already contains SqlDatabase and OrcaleDatabase, if we need to use other databases such as DB2, we need to use GenericDatabase, which can be used for any.. NET class library, including OdbcProvider and OleDbProvider.
Ii. Example
The configuration of DAAB2.0 is very simple, mainly including the following Configuration:
Configure the connection string
Configure the default database
Add related namespaces:
Using Microsoft. Practices. EnterpriseLibrary. Data;
Using System. Data;
Data Access Application Block is generally divided into three steps:
1. Create a Database object
2. Provides command parameters, if needed
3. execute commands
The following are examples of DataAccessQuickStart:
Execute static SQL statements
Public string GetCustomerList ()
{
// Create a Database object
Database db = DatabaseFactory. CreateDatabase ();
// Create a DbCommand object using SQL statements
String sqlCommand = "Select CustomerID, Name, Address, City, Country, PostalCode" +
"From MERs ";
DbCommand dbCommand = db. GetSqlStringCommand (sqlCommand );
StringBuilder readerData = new StringBuilder ();
// Call the ExecuteReader Method
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 ();
}
Execute the stored procedure and pass parameters, and return DataSet
Public DataSet GetProductsInCategory (int Category)
{
// Create the Database object, using the default database service.
// 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;
}
Update Data Using DataSet
Public int UpdateProducts ()
{
// Create the Database object, using the default database service.
// 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 (new object [] {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;
}
Obtain Record details by ID
Public string GetProductDetails (int productID)
{
// Create the Database object, using the default database service.
// 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;
}
Return data in XML format
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 ();