Using the Microsoft Enterprise Library Data Access Application Block – Part I

• Download source - 109 KB

Introduction

I have been using various Microsoft Enterprise Library application blocks in my application development. The most common tasks for any application are database transactions. I have used the Enterprise Library Data Access Application Block and found it extremely easy and simple to integrate and use.

The Enterprise Library Data Access Application Block simplifies development tasks that implement common data access functionality. Applications can use this application block in a variety of situations, such as reading data for display, passing data through application layers, and submitting changed data back to the database system. The application block includes support for both Stored Procedures and in-line SQL.

In this article, let me share how to integrate and use the Data Access Application Block for inserting data into a database.

Please note that I have used the Microsoft Enterprise Library 4.1 – October, 2008 release. It works on .NET framework 3.5 and Visual Studio 2008. You can download Microsoft Enterprise Library 4.1 from this URL: http://www.microsoft.com/downloads/details.aspx?FamilyId=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en.

Integration and Usage

• Code: In the attached source, I have put in the Data Access Layer class, the Entity class, the EventLog class, and the SQL script to create the table and the relevant Stored Procedure. And also the relevant assemblies from the Data Access Application block. The code is to save user information (Insert and Update) in the database. I have put in the classes in the same folder, but they are from a different namespace and assembly. It is better practice to put in a different namespace for different layers and in a different assembly.
• Explanation: First, we need to install Microsoft Enterprise Library 4.1 and get hold of the assemblies “Microsoft.Practices.EnterpriseLibrary.Common.dll” and “Microsoft.Practices.EnterpriseLibrary.Data.dll” and add the reference to the same in your project. In the attached sample, you will find the necessary DLL in case you don’t want to to download and install Microsoft Enterprise Library 4.1.
• User.cs: The Entity Layer class. This class is used to pass user data between various layers.
• UserDAC.cs: The Data Access Layer class. This class uses the Entity class to get the data members, and passes the same to be inserted into the database using the Data Access Application block. It executes a Stored Procedure to save user information (New User or Edit User). Once the database transaction is done, it responds back with the message ID and message string. For a successful transaction, the message ID would always be a positive numeric integer carrying the new user ID for the new user and the user ID for the modified user. The message string would carry the transaction message from the Stored Procedure. I have used static messages for the purpose of the demo, but it is configurable. This class should be called from the Business Layer class to save data into the database. The Business Layer will pass in the Entity object storing the user information and gets back the message ID and the message string. The User Data Access class will also log the error in the Application Event Log using the EventLog class.
C#

public void SaveUser(User vobjUser, out int vintMessageID, out string vstrMessage)
{
    //Use the database connection string here...
    Database objDB = new SqlDatabase(ConnectionString);
    using (DbCommand objCMD = objDB.GetStoredProcCommand("sprocSample_SaveUser"))
    {
        objDB.AddInParameter(objCMD, "@intUserID", 
                             DbType.Int32, vobjUser.UserID);
        objDB.AddInParameter(objCMD, "@strUserName", 
                             DbType.String, vobjUser.UserName);
        objDB.AddInParameter(objCMD, "@strUserPassword", 
                             DbType.String, vobjUser.UserPassword);
        objDB.AddInParameter(objCMD, "@strFirstName", 
                             DbType.String, vobjUser.FirstName);
        objDB.AddInParameter(objCMD, "@strLastName", 
                             DbType.String, vobjUser.LastName);
        objDB.AddOutParameter(objCMD, "@intErrID", 
                              DbType.Int32, Int32.MaxValue);
        objDB.AddOutParameter(objCMD, "@strMessage", DbType.String, 255);

        try{
        objDB.ExecuteNonQuery(objCMD);
        vintMessageID = 
          Convert.ToInt32(objDB.GetParameterValue(objCMD, "@intErrID"));
        vstrMessage = objDB.GetParameterValue(objCMD, "@strMessage").ToString();

        vobjUser.UserID = vintMessageID > 0 ? vintMessageID : 0;
        }
        catch (Exception ex)
        {
            EventLog objLog = new EventLog();
            objLog.LogError(ex);

            throw ex;
        }
    }
}

The above method saves the user into the database by calling the procedure “sprocSample_SaveUser”. The using keyword closes and disposes the relevant object being used for the database transaction once the transaction is complete, so no need to close the database connection explicitly.

• EventLog.cs: The Framework / Utility layer class. This class is used to log error information into the Application Event Log of the system. Refer my article Using Microsoft Enterprise Library Logging Application Block for details on the event logging mechanism using the Microsoft Enterprise Library Logging Application Block.
• SQLScripts.sql: The SQL scripts to create the database table and the Stored Procedure used in the example.

Conclusion

By now, you should be able to understand how simple it is to use the Enterprise Library 4.1 Application Blocks for Data Access to save data into a database. I will come up with some more articles on the Data Access Application block and the layered architecture.

To study more on the Microsoft Enterprise Library 4.1 Application Blocks, refer to this URL: http://msdn.microsoft.com/en-us/library/dd203099.aspx.