Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Data Access Application Block DAAB in SQL Server Part 2 of 2


By:   |   Last Updated: 2010-07-08   |   Comments (3)   |   Related Tips: More > Application Development

Problem

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.

Solution

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.

references

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[0].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

try 
{ 
    /*DatabaseFactory.CreateDatabase creates an instance of the database; 
     *first of the two overloaded methods takes no parameter and creates 
     *database instance taking default settings from the confifuration whereas 
     *second one takes configuration key and creates instance on that basis */ 
    Database _database = DatabaseFactory.CreateDatabase("AdventureWorksConnectionString"); 
    string sqlCommand = "SELECT TOP 5 FirstName, LastName, JobTitle FROM HumanResources.vEmployee"; 
    DbCommand dbCommand = _database.GetSqlStringCommand(sqlCommand); 
    DataSet _dataSet = _database.ExecuteDataSet(dbCommand); 

    /* Iterate through all the rows of the resultset (datatable)*/ 
    foreach (DataRow _dataRow in _dataSet.Tables[0].Rows) 
    { 
        /* Iterate through all the columns of each row*/ 
        foreach (DataColumn _dataColumn in _dataSet.Tables[0].Columns) 
        { 
            Console.Write(_dataRow[_dataColumn.ColumnName] + ", "); 
        } 
        Console.WriteLine(""); 
    } 
} 
catch (Exception exp) 
{ 
    Console.ForegroundColor = ConsoleColor.Red; 
    Console.WriteLine(exp.Message); 
} 

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

try 
{ 
    Database _database = DatabaseFactory.CreateDatabase(); 
    DbCommand _command = _database.GetStoredProcCommand("GetEmployeeDetail"); 
    /* Define the input parameter for SP with AddInParameter method */ 
    _database.AddInParameter(_command, "@EmployeeID", DbType.Int32, 10); 
    /* Define the output parameters for SP with AddOutParameter method */ 
    _database.AddOutParameter(_command, "@FirstName", DbType.string, 50); 
    _database.AddOutParameter(_command, "@MiddleName", DbType.string, 50); 
    _database.AddOutParameter(_command, "@LastName", DbType.string, 50); 

    /* Define the SP return value with AddParameter method & ParameterDirection.ReturnValue*/ 
    _database.AddParameter(_command, "@ReturnValue", DbType.Int32, 
        ParameterDirection.ReturnValue, "@ReturnValue", DataRowVersion.default, null); 

    /* Alternatively you can use these lines of code to get return value  */ 
    //IDbDataParameter myParam = _command.CreateParameter();  
    //myParam.DbType = DbType.Int32;  
    //myParam.ParameterName = "@RETURN_VALUE";  
    //myParam.Direction = ParameterDirection.ReturnValue;  
    //_command.Parameters.Add(myParam);  

    _database.ExecuteNonQuery(_command); 
    //int retvalue = (int)myParam.Value; 

    /* Getting return value after command execution*/ 
    int retvalue = (int)_database.GetParameterValue(_command, "@ReturnValue"); 

    if (retvalue == 0) 
    { 
        Console.WriteLine("Employe found:"); 
        /* Getting values of output parameters after command execution*/ 
        Console.WriteLine(string.Format("{0}, {1}, {2}", _database.GetParameterValue(_command, "@FirstName"), 
                    _database.GetParameterValue(_command, "MiddleName"), _database.GetParameterValue(_command, "@LastName"))); 
    } 
    else 
    { 
        Console.Write("Employe not found."); 
    } 
} 
catch (Exception exp) 
{ 
    Console.ForegroundColor = ConsoleColor.Red; 
    Console.WriteLine(exp.Message); 
} 

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

CREATE PROCEDURE [dbo].[GetEmployeeDetail] 
( 
   @EmployeeID INT, 
   @FirstName NVARCHAR(50) OUTPUT, 
   @MiddleName NVARCHAR(50) OUTPUT, 
   @LastName NVARCHAR(50) OUTPUT 
) 
AS 
   SELECT @FirstName = FirstName, @MiddleName = MiddleName, @LastName = LastName 
   FROM HumanResources.vEmployee WHERE EmployeeID = @EmployeeID 
   IF @@ROWCOUNT <> 0 
       RETURN 0 
   ELSE 
       RETURN 1 
GO 

--DROP PROCEDURE [dbo].[GetEmployeeDetail] 

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

Database _database = DatabaseFactory.CreateDatabase(); 
DbCommand _cmdAcceptOrder = 
    _database.GetSqlStringCommand("<Specify your command to insert or update order details>"); 
DbCommand _cmdConfirmOrder = 
    _database.GetSqlStringCommand("<Command to send a confirmation to Inventory as well as to customer>"); 

using (DbConnection _dbConnection = _database.CreateConnection()) 
{ 
    _dbConnection.Open(); 
    /*Start a transaction by calling BeginTransaction method on the 
     * connection object created above*/ 
    DbTransaction _dbTransaction = _dbConnection.BeginTransaction(); 
    try 
    { 
        _database.ExecuteNonQuery(_cmdAcceptOrder, _dbTransaction); 
        /*Once order is accepted successfully, send a confirmation  
         *to Inventory department as well as to customer */ 
        _database.ExecuteNonQuery(_cmdConfirmOrder, _dbTransaction); 

        /*Complete the transaction by commiting it*/ 
        _dbTransaction.Commit(); 
    } 
    catch (Exception exp) 
    { 
        /*Rollback the transaction in case of any exception*/ 
        _dbTransaction.Rollback(); 
        Console.ForegroundColor = ConsoleColor.Red; 
        Console.WriteLine(exp.Message); 
    } 
    _dbConnection.Close(); 
} 

The complete code listing for the above examples is provided below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace DAL_UsingDAAB
{
    class Program
    {
        static void Main(string[] args)
        {
            DatasetDemo();
        }
        private static void DatasetDemo()
        {
            try
            {
                /*DatabaseFactory.CreateDatabase creates an instance of the database;
                 *first of the two overloaded methods takes no parameter and creates
                 *database instance taking default settings from the confifuration whereas
                 *second one takes configuration key and creates instance on that basis */
                Database _database = DatabaseFactory.CreateDatabase("AdventureWorksConnectionString");
                string sqlCommand = "SELECT TOP 5 FirstName, LastName, JobTitle FROM HumanResources.vEmployee";
                DbCommand dbCommand = _database.GetSqlStringCommand(sqlCommand);
                DataSet _dataSet = _database.ExecuteDataSet(dbCommand);
                /* Iterate through all the rows of the resultset (datatable)*/
                foreach (DataRow _dataRow in _dataSet.Tables[0].Rows)
                {
                    /* Iterate through all the columns of each row*/
                    foreach (DataColumn _dataColumn in _dataSet.Tables[0].Columns)
                    {
                        Console.Write(_dataRow[_dataColumn.ColumnName] + ", ");
                    }
                    Console.WriteLine("");
                }
            }
            catch (Exception exp)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(exp.Message);
            }
        }
        private static void CallingStoredProcedureDemo()
        {
            try
            {
                Database _database = DatabaseFactory.CreateDatabase();
                DbCommand _command = _database.GetStoredProcCommand("GetEmployeeDetail");
                /* Define the input parameter for SP with AddInParameter method */
                _database.AddInParameter(_command, "@EmployeeID", DbType.Int32, 10);
                /* Define the output parameters for SP with AddOutParameter method */
                _database.AddOutParameter(_command, "@FirstName", DbType.String, 50);
                _database.AddOutParameter(_command, "@MiddleName", DbType.String, 50);
                _database.AddOutParameter(_command, "@LastName", DbType.String, 50);
                /* Define the SP return value with AddParameter method & ParameterDirection.ReturnValue*/
                _database.AddParameter(_command, "@ReturnValue", DbType.Int32,
                    ParameterDirection.ReturnValue, "@ReturnValue", DataRowVersion.Default, null);
                /* Alternatively you can use these lines of code to get return value  */
                //IDbDataParameter myParam = _command.CreateParameter(); 
                //myParam.DbType = DbType.Int32; 
                //myParam.ParameterName = "@RETURN_VALUE"; 
                //myParam.Direction = ParameterDirection.ReturnValue; 
                //_command.Parameters.Add(myParam); 
                _database.ExecuteNonQuery(_command);
                //int retvalue = (int)myParam.Value;
                /* Getting return value after command execution*/
                int retvalue = (int)_database.GetParameterValue(_command, "@ReturnValue");
                if (retvalue == 0)
                {
                    Console.WriteLine("Employe found:");
                    /* Getting values of output parameters after command execution*/
                    Console.WriteLine(String.Format("{0}, {1}, {2}", _database.GetParameterValue(_command, "@FirstName"),
                                _database.GetParameterValue(_command, "MiddleName"), _database.GetParameterValue(_command, "@LastName")));
                }
                else
                {
                    Console.Write("Employe not found.");
                }
            }
            catch (Exception exp)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(exp.Message);
            }
        }
        private static void LeveragingTransactionDemo()
        {
            Database _database = DatabaseFactory.CreateDatabase();
            DbCommand _cmdAcceptOrder =
                _database.GetSqlStringCommand("Specify your command to insert or update order details");
            DbCommand _cmdConfirmOrder =
                _database.GetSqlStringCommand("Command to send a confirmation to Inventory as well as to customer");
            using (DbConnection _dbConnection = _database.CreateConnection())
            {
                _dbConnection.Open();
                /*Start a transaction by calling BeginTransaction method on the
                 * connection object created above*/
                DbTransaction _dbTransaction = _dbConnection.BeginTransaction();
                try
                {
                    _database.ExecuteNonQuery(_cmdAcceptOrder, _dbTransaction);
                    /*Once order is accepted successfully, send a confirmation 
                     *to Inventory department as well as to customer */
                    _database.ExecuteNonQuery(_cmdConfirmOrder, _dbTransaction);
                    /*Complete the transaction by commiting it*/
                    _dbTransaction.Commit();
                }
                catch (Exception exp)
                {
                    /*Rollback the transaction in case of any exception*/
                    _dbTransaction.Rollback();
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine(exp.Message);
                }
                _dbConnection.Close();
            }
        }
    }
}

Note:

  • 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.
Next Steps


Last Updated: 2010-07-08


next webcast button


next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 11, 2013 - 5:15:29 AM - Arshath Back To Top

Thanks. excellent post.

 

Arshath


Wednesday, September 14, 2011 - 10:24:19 AM - Luis Fernando Back To Top

I'm working with EntLib 5, also I’m using the same code example, but I have a little problem with my code, the rollback is not working.
My code execute 2 stored Procedures, one to insert a row in a parent table which provides a parameter for the second insert that is in the child table.
I have deleted the second SP because I want that an error occurs so the rolleback executes.
But the problem is that the firs SP executes and makes an inserts and the rollback never occurs
Does Anyone have any suggestions?

My Code:

public void CrearJerarquia(string nombre, string descripcion)
        {
            Database db = DatabaseFactory.CreateDatabase();

            using (DbConnection dbConnection = db.CreateConnection())
            {
                dbConnection.Open();
                DbTransaction dbTransaction = dbConnection.BeginTransaction();
                try
                {
                   
                    int idJerarquia = (int)db.ExecuteScalar(dbJerarquiasCommand);

                    db.AddInParameter(dbElementosCommand, "Nombre", DbType.String, "Raiz" + nombre);
                    db.AddInParameter(dbElementosCommand, "Descripcion", DbType.String, "Nodo Raiz " + nombre);
                    db.AddInParameter(dbElementosCommand, "IdJerarquia", DbType.Int32, idJerarquia);
                    db.ExecuteNonQuery(dbElementosCommand);

                    dbTransaction.Commit();
                }
                catch (Exception ex)
                {
                    dbTransaction.Rollback();
                }
                dbConnection.Close();
            }
        }


Monday, October 25, 2010 - 1:05:50 PM - Simon Back To Top

Thanks for showing how to call a stored proc, but is there a code sample to get a result set back into a datatable and/or dataset? Do these methods still apply in Ent Lib 5?


Learn more about SQL Server tools