Data Access Application Block

Introduction

Each database has its connection information stored in a configuration file. It reduces difficulty in changing the database type.
1. Create the database object.
2. Suppy the parameters for the command, if they are needed.
3. Call the appropriate method(s). These methods are optimized for performance. They are also portable.

Data Access Application Block Dependencies
-Core library functionality - Microsoft.Practices.EnterpriseLibrary.Common.dll
-The ObjectBulder subsystem - Microsoft.Practices.ObjectBuilder - performs all the repetitive and necessary tasks for creating and disposing object instances.

Developing Applications Using the Data Access Application Block

Entering Configuration Information

To add the Data Access Application Block
1. Open the configuration file.
2. Right-click Application configuration, New, Data Access Application Block.

To configure the default database
3. expand DefaultDatabse property.
4. Enter a connection string name.
5. Change the provider name if you want to.

Adding Application Code
1. add reference
Microsoft.Practices.EnterpriseLibrary.Data.dll
Microsoft.Practices.EnterpriseLibrary.Common.dll

2. In source code,
Using Microsoft.Practices.EnterpriseLibrary.Data

3. Creating a Database Object
Dim db As Database = DatabaseFactory.CreateDatabase()
SqlDatabase, OracleDatabase, SqlCeDatabase are specific to a particular database type. They can be used instead of Database

Creating a Dbcommand Object

DbCommand objects are separated into two types
1. GetStoredProcCommand. This method is for stored procedures commands.
2. GetSqlStringCommand. This method is for SQL text commands.

example
for GetStoredProcCommand.
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim sqlCommand As String = "Select CustomerID, LastName, FirstName From Customers"
Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

for GetSqlStringCommand.
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim dbCommand As DbCommand
dbCommand = db.GetStoredProcCommand("GetProductsByCategory")

TransactionScope Class
It assumes that you will use a single connection for all the database calls that occur within the transaction.

Handling Parameters
Attributes can include direction, data types, and length.
The Database class includes a variety of methods for passing parameters to stored procedures.
1. AddParameter- this method passes a parameter (input or output) to a stored procedure.
2. AddInParameter - This method passes an input parameter to a stored procedure.
3. AddOutParameter - This method adds an output parameter.
4. GetparameterValue - This method finds the value of the specified parameter.
5. SetParameterValue - This method sets the value of the specified parameter.


Dim db As Database = DatabaseFactory.CreateDatabase()
Dim sqlCommand As String = "GetProductDetails"
Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

db.AddInParameter(dbCommand, "ProductID", DbType.Int32, 5)
db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50)
db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8)


Using a DbDataReader to Retrieve Multiple Rows

Used to retrieve data for read-only purposes, which in most cases means that you want to display the data.
The Database class implementation of ExecuteReader returns a DbDataReader object. The DbDataReader supports data binding and can act as a data source for a number of ASP.NET server controls.

Using ExecuteDataReader

Dim db As Database = DatabaseFactory.CreateDatabase()

Using dataReader As IDataReader = db.ExecuteReader(CommandType.Text, "Select Name, Address, City From Customers")

customerGrid.DataSource = dataReader
customerGrid.DataBind()

End Using

The ExecuteReader method opens a connection to the dtabase and uses the CommandBehavior.CloseConnection method to couple the lifetime of the connection to that of the reader. So closing the reader causes the connection to be closed and returned to the connection pool.


Using a DataSet to Retrieve Multiple rows

Retrieve multiple tables or tables from different data sources.
for exchange data with another application.
access interdependent records for data processing.

Using ExecuteDataSet


Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "GetProductsByCategory"
Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

' Retrieve products from the category 7.
Dim category As Integer = 7
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, category)

' DataSet that will hold the returned results
Dim customerproductDataSet As DataSet = Nothing

customerproductDataSet = db.ExecuteDataSet(dbCommand)


If you want to reuse an existing DataSet instead of creating a new one to hold the results of your query, use the LoadDataSet method in the Database class.


Executing a Command and Accessing Output Parameters.

Retrieve specific items of data either from one row in a particular table or from multiple related rows in different tables.
Use a stored procedure's output parameters.

Using ExecuteNonQuery

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "GetProductDetails"
Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

db.AddInParameter(dbCommand, "ProductID", DbType.Int32, 3)
db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50)
db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8)

db.ExecuteNonQuery(dbCommand)

Dim results As String = String.Format(CultureInfo.CurrentCulture, "{0}, {1}, {2:C} ", _
db.GetParameterValue(dbCommand, "ProductID"), _
db.GetParameterValue(dbCommand, "ProductName"), _
db.GetParameterValue(dbCommand, "UnitPrice"))


Executing a Command and Accessing a Single-Item Result

The goal in this scenario is to return a single value as the result of a query.

Using ExecuteScalar

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "GetProductName"
Dim productId As Integer = 7
Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand, productId)

Dim productName As String = DirectCast(db.ExecuteScalar(dbCommand), String)



Performing Multiple Updates within a Transaction

Transactions ensure the integrity of a database system's state. When an application executes multiple operations against a database, a common requirement is that all of the operations must succeed or the database must roll back to its original state.

Using ExecuteNonQuery in a Transaction

Public Function Transfer(ByRef transactionAmount As Integer, ByRef sourceAccount As Integer, ByRef destinationAccount As Integer) As Boolean

Dim result As Boolean = False

' Create the database object, using the default database service. The
' default database service is determined through configuration.
Dim db As Database = DatabaseFactory.CreateDatabase()

' Two operations, one to credit an account, and one to debit another
' account.
Dim sqlCommand As String = "CreditAccount"
Dim creditCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount)
db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount)

sqlCommand = "DebitAccount"
Dim debitCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount)
db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount)

Using connection As DbConnection = db.CreateConnection()
connection.Open()
Dim transaction As DbTransaction = 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
' Roll back the transaction.
transaction.Rollback()
End Try

connection.Close()
Return result
End Using
End Function

Using a DataSet to Update a Database


Using UpdateDataSet

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim productsDataSet As DataSet = new DataSet()

Dim sqlCommand As String = "Select Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products"
Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)

Dim productsTable As String = "Products"

' Retrieve the initial data.
db.LoadDataSet(dbCommand, productsDataSet, productsTable)

' Get the table that will be modified.
Dim table As DataTable = productsDataSet.Tables(productsTable)

' Add a new product to existing DataSet.
Dim addedRow As DataRow = table.Rows.Add(New Object() {DBNull.Value, "New product", 11, 25})

' Modify an existing product.
table.Rows(0)("ProductName") = "Modified product"

' Establish the Insert, Delete, and Update commands.
Dim insertCommand As DbCommand = 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)

Dim deleteCommand As DbCommand = db.GetStoredProcCommand("DeleteProduct")
db.AddInParameter(deleteCommand, "@ProductID", DbType.Int32, "ProductID", DataRowVersion.Current)

Dim updateCommand As DbCommand = 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.
Dim rowsAffected As Integer = db.UpdateDataSet(productsDataSet, "Products", insertCommand, updateCommand, _
deleteCommand, Microsoft.Practices.EnterpriseLibrary.Data.UpdateBehavior.Standard)

The UpdateDataSet method can specify the behavior that should occur when it encounters an error.
1. Standard - If the Update command encounters an error, the updates stops. Additional rows in the Datatable are unaffected.
2. Continue - The update command will try to update the remaining rows.
3. Transactional - All updated rows will be rolled back.

Retriving Multiple Rows as XML


Using ExecuteXmlReader

Dim dbSQL As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("EntLibQuickStartsSql"), SqlDatabase)

' Use "FOR XML AUTO" to have SQL return XML data.
Dim sqlCommand As String = "SELECT ProductID, ProductName FROM Products FOR XML AUTO"
Dim dbCommand As DbCommand = dbSQL.GetSqlStringCommand(sqlCommand)

Dim productsReader As XmlReader = Nothing
Dim productList As StringBuilder = New StringBuilder()

Try

productsReader = dbSQL.ExecuteXmlReader(dbCommand)

' Iterate through the XmlReader and put the data into our results.
While (Not productsReader.EOF)
If (productsReader.IsStartElement()) Then
productList.Append(productsReader.ReadOuterXml())
productList.Append(Environment.NewLine)
End If
End While

Finally
' Close the Reader if there is no active transaction.
If (Not productsReader Is Nothing And Transaction.Current Is Nothing) Then
productsReader.Close()
End If
End Try

The ExecuteXmlReader method of the SqlCommand object currently does not support the CommandBehavior.CloseConnection. So you must explicitly close the connection when you finish with the reader.