Click here to Skip to main content
Click here to Skip to main content
Go to top

Using Microsoft Enterprise Library Data Access Application Block – Part II

, 7 Aug 2009
Rate this:
Please Sign up or sign in to vote.
Using Microsoft Enterprise Library Data Access Application Block to Retrieve Data from Database.

Introduction

In my previous article, Using Microsoft Enterprise Library Data Access Application Block – Part I, I have shown how to use the Data Access Application Block to insert and update data in a database using a Stored Procedure, following a layered architecture. In this article, let me show how to use the Data Access Block to retrieve data.

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 classes, the Entity class, the EventLog class, and the SQL script to create the table and relevant Stored Procedures. Also the relevant assemblies from the Data Access Application block. The code is to get user information (User Details based on UserID and All Active Users) from the database. I have put in the classes in the same folder, but they are in different namespaces and assemblies. It is a better practice to put in different namespaces for different layers and in different assemblies.
  • 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 references to them in your project. In the attached sample, you will find the necessary DLLs in case you don’t want 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 has methods to get User Details by ID and get All Active Users. The methods execute the relevant procedures to get the user info. This class should be called from the Business Layer class to get data from the database. The Business Layer will call the relevant methods of the Data Layer class, which in turn responds back with the User Entity object for User Details and a generic list for All Active Users.
  • To note, this class inherits the DataAccessComponent class. The DataAccessComponent has methods to get data from the result-sets returned by the procedure in a generic fashion. The ParseUser method of the UserDAC class is used to populate the User entity object by iterating through the DataReader. The GetUserDetailsById returns the User entity object and also the message ID and message string. In case the user is not existent for the user ID that is passed to this method, the message ID would return a negative integer and message string would return an error string. I have kept this simple for the demo purpose; however, this is configurable as per the application needs and architecture.

    The User Data Access class will also log the error in the Application Event Log using the EventLog class.

    public User GetUserDetailsById(int vintUserId, 
           out int vintMessageId, out string vstrMessage)
    {
        Database objDB = new SqlDatabase(ConnectionString);
        using (DbCommand objCMD = 
               objDB.GetStoredProcCommand("sprocSample_GetUserDetails"))
        {
            objDB.AddInParameter(objCMD, "@intUserID", DbType.Int32, vintUserId);
            objDB.AddOutParameter(objCMD, "@intErrID", 
                                  DbType.Int32, Int32.MaxValue);
            objDB.AddOutParameter(objCMD, "@strMessage", DbType.String, 255);
    
            User objUser = null;
            try{
            using (IDataReader objDataReader = objDB.ExecuteReader(objCMD))
            {
                if (objDataReader.Read())
                {
                    objUser = ParseUser(objDataReader);
                    vintMessageId = vintUserId;
                    vstrMessage = null;
                }
                else
                {
                    vintMessageId = Convert.ToInt32(objDB.GetParameterValue(objCMD, 
                                                    "@intErrID"));
                    vstrMessage = objDB.GetParameterValue(objCMD, 
                                  "@strMessage").ToString();
                }
            }
            }
            catch (Exception ex)
            {
                EventLog objLog = new EventLog();
                objLog.LogError(ex);
    
                throw ex;
            }
    
            return objUser;
        }
    }
    
    public List<user> GetAllActiveUsers()
    {
        Database objDB = new SqlDatabase(ConnectionString);
        using (DbCommand objCMD = 
          objDB.GetStoredProcCommand("sprocSample_GetActiveUsers"))
        {
            List<user> objUserList = new List<user>();
            try{
                using (IDataReader objDataReader = objDB.ExecuteReader(objCMD))
                {
                    while (objDataReader.Read())
                    {
                        User objUser = ParseUser(objDataReader);
                        objUserList.Add(objUser);
                    }
                }
            }
            catch (Exception ex)
            {
                EventLog objLog = new EventLog();
                objLog.LogError(ex);
    
                throw ex;
            }
    
            return objUserList;
        }
    }
    
    
    private User ParseUser(IDataReader vobjDataReader)
    {
        User objUser = new User();
        objUser.UserID = base.GetDataValue<int>(vobjDataReader, "UserID");
        objUser.UserName = base.GetDataValue<string>(vobjDataReader, "UserName");
        objUser.FirstName = base.GetDataValue<string>(vobjDataReader, "FirstName");
        objUser.LastName = base.GetDataValue<string>(vobjDataReader, "LastName");
        objUser.UserPassword = base.GetDataValue<string>(vobjDataReader, "UserPassword");
    
        return objUser;
    }

    The above methods are from the UserDAC class to get the User Info from the database and return the user entity object or a generic List of User Entity objects to the caller.

  • 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 event logging mechanisms using the Microsoft Enterprise Library Logging Application Block.
  • SQLScripts.sql: The SQL scripts to create the database table and the Stored Procedures used in the example.

Conclusion

By now, you can understand how simple it is to use the Enterprise Library 4.1 application blocks for Data Access to retrieve data from a database. To study more on the Microsoft Enterprise Library 4.1 application blocks, refer the URL http://msdn.microsoft.com/en-us/library/dd203099.aspx.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Robin_Roy
Other Brilliance Information Sdn Bhd
Malaysia Malaysia
Working as a Senior Consultant with Brilliance MSC, Malaysia.
Love to evaluate new technologies and implement the same.
Believe in sharing knowledge.

Comments and Discussions

 
Questionusing this [DataMember] with [DataMapping("idEmployee")] Pinmemberalsholi20-Apr-12 5:00 
GeneralMy vote of 1 Pinmembereferreyra21-May-10 8:14 
Questionhow can get query result and output parameter togther ? Pinmemberyaweriqbal9-Jan-10 16:37 
GeneralNice Example and Code PinmemberAditithegreat4-Sep-09 17:40 
GeneralRe: Nice Example and Code PinmemberRobin_Roy6-Sep-09 18:17 
GeneralGood Article Pinmemberrahulthecoder2-Sep-09 18:35 
GeneralRe: Good Article PinmemberRobin_Roy6-Sep-09 18:16 
GeneralExcellent PinmemberSaswatiS26-Aug-09 17:43 
GeneralRe: Excellent PinmemberRobin_Roy26-Aug-09 22:35 
GeneralExcellent Article Pinmemberarvindjo25-Aug-09 23:18 
GeneralRe: Excellent Article PinmemberRobin_Roy26-Aug-09 22:36 
Generalgood one Pinmemberlxjhonson24-Aug-09 22:56 
GeneralRe: good one PinmemberRobin_Roy26-Aug-09 22:37 
QuestionPart II? PinmemberStanislav Georgiev19-Aug-09 20:29 
GeneralPoor article ! Pinmemberkalyankrishna110-Aug-09 21:10 
GeneralMy vote of 1 Pinmemberkalyankrishna110-Aug-09 21:09 
GeneralGood Article Pinmemberarvindcoolest10-Aug-09 17:18 
GeneralRe: Good Article PinmemberRobin_Roy26-Aug-09 22:40 
GeneralMy Vote of 5 Pinmemberjohnclark6410-Aug-09 17:11 
GeneralRe: My Vote of 5 PinmemberRobin_Roy26-Aug-09 22:41 
GeneralMy Vote of 5 Pinmemberbrown2010-Aug-09 16:39 
GeneralRe: My Vote of 5 PinmemberRobin_Roy26-Aug-09 22:42 
GeneralMy Vote of 5 Pinmemberalbert_cook10-Aug-09 16:36 
GeneralRe: My Vote of 5 PinmemberRobin_Roy26-Aug-09 22:44 
GeneralMy vote of 1 PinmemberTawani Anyangwe10-Aug-09 2:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 8 Aug 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid