Feb 032012
 

In Part 1: Out-of-the-box Features, I went through some of the great new features with Enterprise Library 5 Data Access including accessors and mappers. Before version 5, most of my EntLib extensions code was in place to perform these new features (not as eloquently, of course). I have become attached to a few of the extensions I had put in place over the years. I will keep my extensions around now for only a few reasons. 1) Customized database exceptions 2) IDataReader usability enhancements 3) Reduced mapping footprint.

Extensions

I typically have a Utils.dll that I import in every project. For data/resource access projects, I also include my Utils.Data.dll. Utils.Data started its career as a data access application block similar to SqlHelper from the pre-EntLib days. Today, Utils.Data is a set of extensions that merely makes Enterprise Library more fun to be with.

IDataReaderExtensions

Out of the box, System.Data.IDataRecord only gives you the ability to access fields by their integer index value. As an architect that does not have supervisory control over the database or the objects within, this scares me. Any additions or re-ordering of the output fields will surely cause your index-based mapping to blow up. You could solve this with a call to .GetOrdinal(fieldName) first to get the index, but that is twice the code (not to mention boring plumbing code). My extensions do nothing novel. They simply provide string-based extensions like .GetInt32(string name) that do the retrieval and casting for you. I also added a few frequently-used new extensions like .GetNullableInt(string name) to keep my result mapping as clean as concise as possible.

Reader use with built-in features:

jeep = new Jeep()
{
	ID = row.GetInt32(0),
	Name = row.GetString(1),
	Description = row.GetString(2),
	Status = row.GetBoolean(3)
};

Reader use with extensions:

jeep = new Jeep()
{
	ID = reader.GetInt32(“JeepID”),
	Name = reader.GetString(“Name”),
	Description = reader.GetString(“Description”),
	Status = reader.GetBoolean(“Status”),
};

I advise that you never use string literals in data access code. Data access code is hit hard, so take your performance improvements when you can. I prefer having const strings locally in my data access class or having an internal static class with const strings to share with all classes in my data access project. The attached solution has examples.

Parameter and Result/Row Mapping

The now built-in ParameterMapper, RowMapper, and ResultSetMapper are beautiful. Sometimes you need a little sumpin’ special to make your code easier to read and work consistently when getting one or ten entities in a database call. Similar to how ExecuteSprocAccessor works with row and result set mappers, CreateObject and CreateCollection support generics and build an object or collection of the specified type. Instead of deriving a new class from a base mapper class, I chose to have one delegate method that generates a single object from a reader. This delegate is used by both CreateObject and CreateCollection. Let’s look at the differences with code.

Creating an object with EntLib5 features:

public Jeep GetJeepByID(int id)
{
	Database db = DatabaseFactory.CreateDatabase();
	IParameterMapper jeepParameterMapper = new JeepParameterMapper();
	IRowMapper<Jeep> jeepRowMapper = new JeepRowMapper();
	IEnumerable<Jeep> jeeps = db.ExecuteSprocAccessor<Jeep>(StoredProcedures.GetJeepByID, jeepParameterMapper, jeepRowMapper, id);
	return jeeps.First();
}

internal class JeepRowMapper : IRowMapper<Jeep>
{
	public Jeep MapRow(System.Data.IDataRecord row)
	{
		return new Jeep()
		{
			ID = row.GetInt32(0),
			Name = row.GetString(1),
			Description = row.GetString(2),
			Status = row.GetBoolean(3)
		};
	}
}

Creating an object with extensions:

public Jeep GetJeepByID(int id)
{
	Database db = DatabaseFactory.CreateDatabase();
	DbCommand cmd = db.GetStoredProcCommand(StoredProcedures.GetJeepByID, id);
	Jeep jeep = db.CreateObject(cmd, GenerateJeepFromReader);
	return jeep;
}

private Jeep GenerateJeepFromReader(IDataReader reader)
{
	Jeep jeep = null;
	if (reader.Read())
	{
		jeep = new Jeep()
		{
			ID = reader.GetInt32(Fields.JeepID),
			Name = reader.GetString(Fields.JeepName),
			Description = reader.GetString(Fields.JeepDescription),
			Status = reader.GetBoolean(Fields.JeepStatus),
		};
	}
	return jeep;
}

One more thing to note is that my CreateObject, CreateCollection, and their GetAccessor equivalents have my customized exception handling logic that makes use of the StoredProcedureException. We’ll go through that now.

Customized and Standardized Exceptions

The only value in logging exceptions is if your entire system logs exceptions and other messages in a consistent and meaningful manner. If error messages are logged as “ERROR!” or “All bets are off!!!” then you shouldn’t bother logging. In the real world, few developers, architects, or support staff have access to production databases. Having meaningful and detailed error messages is key to troubleshooting an issue and meeting your SLAs. I created a simple StoredProcedureException that provides the executed (or attempted) command as part of the stack trace.

WARNING: You should never, ever, ever show the stack trace in your application or let your users see the real error messages.
Log the real message and stack trace, then show “Data access exception” to your users. Please!

 

In the attached code samples, you’ll see two data access methods that call “ExceptionStoredProcedure” that does nothing other than RAISERROR(‘This is an exception’, 16, 1). With the built-in features, you can expect a SqlException and a stack trace that looks like this:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) 
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() 
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) 
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) 
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) 
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) 
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) 
      in e:BuildsEntLibLatestSourceBlocksDataSrcDataDatabase.cs:line 460 
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) 
      in e:BuildsEntLibLatestSourceBlocksDataSrcDataDatabase.cs:line 846 
   at Microsoft.Practices.EnterpriseLibrary.Data.CommandAccessor`1.d__0.MoveNext() 
      in e:BuildsEntLibLatestSourceBlocksDataSrcDataCommandAccessor.cs:line 68 at System.Linq.Enumerable.First[TSource](IEnumerable`1 source) 
   at DataAccess.JeepDataAccess.GetJeepByIDShowingException(Int32 id) 
      in C:DevCookbookUtilitiesEntLibExtensions5.0EntLibExtensionsDataAccessJeepDataAccess.cs:line 58 
   at Client.Program.TestExceptionGetWithEntLib5Only() 
      in C:DevCookbookUtilitiesEntLibExtensions5.0EntLibExtensionsClientProgram.cs:line 58 
   at Client.Program.Main(String[] args) 
      in C:DevCookbookUtilitiesEntLibExtensions5.0EntLibExtensionsClientProgram.cs:line 22

With my extensions, you can expect a StoredProcedureException that includes the text of the full stored procedure being executed at the time. This has saved me countless times as my log table stores the full stack trace and I can reproduce exactly what happened without guessing. The InnerException of the StoredProcedureException will be the same SqlException seen above. The customized stack trace will look like this:

[Stored procedure executed: ExceptionStoredProcedure @RETURN_VALUE=-6, @JeepID=1]
   at Soalutions.Utilities.Data.DatabaseExtensions.CreateObject[T](Database db, DbCommand cmd, GenerateObjectFromReader`1 gofr) 
      in C:DevCookbookUtilitiesEntLibExtensions5.0EntLibExtensionsEntLibExtensionsDatabaseExtensions.cs:line 49
   at DataAccess.JeepDataAccess.GetJeepByIDShowingExceptionWithExtensions(Int32 id) 
      in C:DevCookbookUtilitiesEntLibExtensions5.0EntLibExtensionsDataAccessJeepDataAccess.cs:line 65
   at Client.Program.TestExceptionGetWithExtensions() 
      in C:DevCookbookUtilitiesEntLibExtensions5.0EntLibExtensionsClientProgram.cs:line 66
   at Client.Program.Main(String[] args) 
      in C:DevCookbookUtilitiesEntLibExtensions5.0EntLibExtensionsClientProgram.cs:line 23

So that’s really it. There is some other hidden goodness in there, but it’s not really worth talking about in this post.

Download sample solution: EntLibExtensions.zip – 140 KB (143,360 bytes)