By: Arshad Ali | Last Updated: 2010-07-08 | Comments (3) | Application Development
Microsoft Enterprise Library provides several reusable software components (application blocks) which help developers in simplifying common development tasks. These components are provided along with source code which developers can use "as-is" or customize as per their projects need. Data Access Application Block (DAAB) is one of these components which simplifies the data access mechanism. In this tip series I am going to explain in detail what DAAB is, how it works and the benefits it provides.
In my first tip, Using Data Access Application Block (DAAB) - Part 1, of this series I discussed Data Access Application Block (DAAB) which provides a consistent data access mechanism to execute both Stored Procedures and inline SQL statements across projects/enterprises.
In part 1, I showed how you can use the "Enterprise Library Configuration" tool to create a configuration file, how to execute a command and how to use a data reader. In this article I am going to demonstrate how you can use a dataset, how to call stored procedure and pass/get different parameters from it and also how to manage database transactions using DAAB.
As I said in my last article, before you actually use the DAAB classes you need to take reference of several of the DAAB assemblies in your project, as you can see below I have taken reference of two of these assemblies.
In my last article on this topic, I discussed about Microsoft.Practices.EnterpriseLibrary.Data namespace and DatabaseFactory class that it contains. I also demonstrated how you can use an ExecuteReader instance method of the Database class. Here I am going to demonstrate ExecuteDataSet instance method to execute a command and return the result set in the dataset.
A data set is collection of zero or more data tables, likewise a data table is collection of zero or more rows. In the example below, I am calling the ExecuteDataSet method and passing the command to be executed, the result of this execution is being stored in a dataset. As the command has only one SELECT statement, the result set is stored in a single data table of the dataset (though you can define multiple SELECT statements in your command and multiple data tables would be created in the dataset), this is why I am using zero indexer (_dataSet.Tables.Rows) to locate the only data table of the dataset while iterating through it.
Database class also has LoadDataSet method to add a data table to an existing dataset and UpdateDataSet method to update the database with whatever changes have been made in the dataset locally by calling the available insert, update and delete commands to make appropriate changes in the underlying database.
C# Code Block 1 - DAAB example with DataSet
So far you have seen, that I called the GetSqlStringCommand instance method to execute a command with a row query in a simple string format. If you want to execute a stored procedure, you would need to call GetStoredProcCommand method instead. This method takes the stored procedure name as its input parameter and returns the command object. You can then add different input (using AddInParameter method) or output (using AddOutParameter method) parameters to it. For a return value, you can use either of the two ways demonstrated in the code below.
Database class also has DiscoverParameters method to discover the parameters for a specified stored procedure; the downside of using it is that it takes a round trip to the database to grab this information. However you can use parameter caching to cache the discovered parameter list in the first call to use it in subsequent calls.
C# Code Block 2 - DAAB example to call stored procedure
Above I am calling the GetEmployeeDetail stored procedure, which you can create with the script provided below. This stored procedure takes EmployeeID as its input parameter and returns the FirstName, MiddleName and LastName as its output parameters. The return value would be 0 if the record is found for a given EmployeeID or else 1 will be returned.
Script Block 1 - Creating stored procedure with in/out parameters
Very often it is required to execute two or more commands in a transaction and hence DAAB allows you to create an instance of a DbTransaction class by calling BeginTransaction instance method of the DbConnection object. Then whichever command you want to execute in this transaction, you can specify the transaction object along with that command while calling the Execute* methods of the database object. To commit or rollback the transaction, you need to call the Commit or Rollback methods of the transaction object you have created.
C# Code Block 3 - DAAB example to manage transaction
The complete code listing for the above examples is provided below.
- All examples and demonstrations shown above use Enterprise Library 4.1 which you can download here. Recently Microsoft released Enterprise Library 5.0 which has several new features and bug fixes, you can download it from here and can find its documentation on MSDN here or download the documentation from here.
- DAAB sits on top of ADO.NET and uses its functionality, so it does not replace ADO.NET.
- Review my last tip on Writing Data Access Layer (DAL) in ADO.NET.
- Review my last tip of this series, Using Data Access Application Block (DAAB) - Part 1 to learn more about configuration and use of data reader.
- Review Enterprise Library on MSDN.
- Download Enterprise Library 4.1 from here.
- Review my previous tips.
Last Updated: 2010-07-08
About the author
View all my tips