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.
