Muffin Top architecture is killing us

I want to share a story. I recently encountered a company that had somehow navigated the many hazards that face technology startups and managed to create a solid, successful business.  They had gone public and now after years of struggling with a collection of applications that consisted largely of simple markup and script pages that directly accessed database queries, they were in the process of transitioning everything over to a real architecture, a single unified “Framework”. 

The “Framework” was a thing of architectural beauty, object oriented design as far as the eye could see.  It had been designed by some incredibly bright developers.  So bright in fact that they had progressed beyond the point where they were even involved in writing code for a shipping application.  Some were so smart, that they skipped the mundane part of a programmer’s career where they spend years writing and maintaining application code, and instead they moved directly into the architecture world.

The core of the “Framework” was the data access module which was based on LINQ to SQL, but hid all of the entities as well as the data context behind interfaces. These interfaces would of course need to be updated whenever changes were made to the LINQ data model, but they made the “Framework” feel a lot more object oriented and provided the additional benefit of hiding any functionality that the architecture group didn’t think regular application developers should have access to. 

The LINQ data model also benefited from some extra architectural love.  Mapping directly to tables was out of the question, so every LINQ entity mapped to a collection of sprocs.  After all, the idea was to create an entity model, not just map directly to table data.  No, every entity was carefully planned and modeled, every field the application developers might need was anticipated and aggregated using those sprocs.  Now this did create yet another point (like the interfaces) at which data mapping logic could go wrong and throw errors, but that wasn’t a problem because the architecture team was able to use Visual Studio to generate a massive collection of database unit tests that would exhaustively test every possible crud scenario for every sproc and assert that it worked as expected.  Of course the massive collection of db unit tests and the sprocs themselves would need to be rewritten every time even the smallest change was made to the data model, but architecturally speaking it was just the right thing to do.

Finally the day came when the “Framework” was pushed out to the unwashed masses of developers who would benefit from it’s glorious design.  But an unexpected thing happened.  New application development done on top of the “Framework” slowed to a crawl.  It was taking 3 to 5 times longer for application developers to build code with the “Framework”.  To make matters worse, the code that was produced, ran slower, sometimes much, much slower than the old legacy code that accessed the database directly. Also, the application developers kept coming back to the architecture team requesting that additional fields be added to the entities.   They said these fields were required in order to provide features that were already available in the legacy applications.  It was as if these guys had never been part of the endless modeling meetings where everyone worked out in advance every field each entity would ever need.  The problems just kept piling up, and application ship dates slipped further and further.  Eventually the architects had to face up to an unpleasant conclusion… the company’s application developers just weren’t very good. I’m not kidding, that really was their conclusion. I actually heard that straight from the mouth of one of the architects.  (BTW, in case you’re wondering the application developers were actually some of the most skilled that I’ve ever run into.  They were very good developers.)

The point of this story is that the problems we face as programmers in the .Net world are rapidly changing.  It used to be that the most common problem I ran into was horribly constructed spaghetti code that was then copied and pasted into 20 different pages of the application.  No code reuse, poor logic structure, and of course direct access to the database in the code behind or even in the markup pages.  The problems were largely the result of us as .Net developers just not being skilled enough to properly design our code.  But .Net is a developer-friendly technology and most of us were able to hack together code that worked even if it wasn’t pretty.  Note that I did say “us”.  Some of my early code is horrific.

Now, increasingly I see a much different problem.  As a community we’ve learned a lot more about real software development. We understand all of those architectural things that our Java buddies used to talk about.  We’re using design patterns, object oriented design, automated testing, ORMs, true layered architectures, dependency injection, component architectures, class inheritance all over the place, interfaces, contract based programming, service oriented architecture and a bunch of stuff that sounds like it should enable us to ship more reliable code, and to ship it faster.  The problem is that in my experience, most of the time it doesn’t.  Don’t get me wrong, I'm not saying that the practices listed above are bad, or that we should go back to writing simple markup pages with SQL queries written directly in our scripts.  What I am saying is that in our eagerness to flex our new-found coding muscles, we’re just throwing these things into our applications because we think they’re a “best practice” and the result is a lot of over-architected, impossible to maintain, fat, sprawling code.  I just get a sense that many very intelligent .Net developers are throwing this stuff into their applications because this is what a real architecture looks like, not because they are getting some measurable benefit.  At the end of the day it’s all about shipping code, not creating an architecture that makes us feel like real architects. 

A smart guy that I met at the unspecified company mentioned earlier put a name to it.  He called it Muffin Top Architecture.  I didn’t know what “muffin top” meant until he explained it to me. If you’re confused do a search on Google images for muffin top and you’ll figure it out.  Hint, it doesn’t refer to the images of real muffins.  Anyway, I’m starting to see quite a bit of Muffin Top code.  The company I told the story about is by no means atypical.  I could go on for pages and pages.  I’ve even encountered companies who are unable to make changes to their own code, that they wrote. There are just so many layers of architecture and class inheritance that changes are incredibly painful even for the original developers. 

I don’t have an easy answer, but I do think we need to start producing code that is simpler, easier to maintain, and quicker to produce.  .Net is a great stack and it isn’t difficult to work with, but we’re developing this reputation of “.Net is a solid platform HOWEVER it takes so long to build on that it’s not appropriate for many projects, and definitely not for a startup project”.  I don’t think that’s true.  The problem isn’t .Net, the problem is that we’re over-architecting, and in many cases Microsoft is more than happy to help us further down the path to destruction with new persistence frameworks, designers, and component architectures.   I think we should be looking more and more at technologies like Ruby on Rails and trying to figure out how they achieve the remarkable efficiencies that are possible on that platform.  It’s certainly not through a designer.  I also think that the answer lies in less code that is simpler and easier to understand, not piles of generated code and component architectures that make it impossible to figure out what code is executing when. 

Another big part of the problem is the emergence of the non-coding architect.  This is the person who doesn’t write any shipping code themselves, they just tell everyone else how they should be writing code.  I think the role of architect is tremendously important.  I see the architect as the lead developer, the mentor, the overall system designer.  But let me make this clear, an architect who doesn’t write code isn’t a real programmer, they are an obstacle to real programmers.  It doesn’t matter how skilled that person is, if the architect isn’t writing shipping code then their incentives are not aligned with the other developers.  The real programmer’s number one priority in life is to ship working code.  The non-coding architect’s priority is to create beautiful architecture. 

If you have any thoughts please don’t hesitate to post them.  Personally, I think that Muffin Top Architecture is killing us.  Is there a solution or am I just destined to go be a Rails developer like Mike Moore, Jeff Cohen, and Scott Bellware?  Check out Herding Code 84 for some interesting discussion by the .Net expatriates.

AAPL Part 6: Building a DataMapper and DataMapperFactory

I recently decided that I needed to take a fresh look at how to build a persistence architecture that would provide the flexibility of an ORM, that would embrace change instead of resist it (making maintenance code easier), and would use ADO.Net.  I started building from the ground up, threw away any best practices that resulted in friction, and Agile ADO.Net Persistence Layer is the result.  Part 1 in the series can be found here: Agile ADO.Net Persistence Layer Overview. 

In this post, we’ll get into the DataMapper.  If you’ve been waiting to see some real ADO.Net code, this is the class you’ve been waiting for.

Where the DataMapper classes fit in

Let’s review our solution structure.  Core is our main business layer project (Note: in previous posts and in the sample code Core is actually the BAL project.  I never liked the name BAL so I’ve renamed it Core.  Same code, just a different name.  Sorry for any confusion.).  Core contains Service classes which are our one stop shop for all persistence (see AAPL Part 3 and AAPL Part 4 for more on how the service classes work). The Service classes use the SqlDao for all communication with the database.  The SqlDao uses the DataMapper classes to handle all mapping between the DataReader returned by executing a query, and the desired data shape.  Here’s what our project looks like:

Last post we covered the SqlDao class and saw how it’s data access methods take a query wrapped in an SqlCommand, execute that query, then return either a Single<DTO> (where DTO is our desired data shape), List<DTO>, DataPage<DTO>,or a scalar value.  The data access methods in SqlDao handle all the boilerplate logic for getting a connection, executing a query, stuff like that, but the logic that is specific to a data shape, things like get the value from this column in my reader put it in this property on my returned data shape, that stuff get’s delegated to a DataMapper class. This is what that SqlDao code looks like:

SqlDataReader reader = command.ExecuteReader();

if (reader.HasRows)

{

    reader.Read();

    IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

    dto = (T)mapper.GetData(reader);

    reader.Close();

}

What does the DataMapperFactory do?

You’ll notice in the code above that we get our IDataMapper object from a DataMapperFactory.  If we need to get the DataMapper for type User, we just call the DataMapperFactory.GetMapper method, pass type(User) as a parameter, and GetMapper returns the appropriate concrete DataMapper (Note that the code example above uses generics, so we use generic T instead of a specific type like User, but the logic is the same). DataMapperFactory is a simple factory class with one method, GetMapper. The code looks like this: 

class DataMapperFactory

{

    public IDataMapper GetMapper(Type dtoType)

    {

        switch(dtoType.Name)

        {

            case "ListItemDTO":

                return new ListItemDTOMapper();

            case "User":

                return new UserMapper();

            default:

                return new GenericMapper(dtoType);

        }      

    }

}

Whenever we create a new IDataMapper for a specific type, like User, we need to add it to this factory method.  The method is just a switch statement that uses the type.Name to look for a type-specific implementation of IDataMapper.  If it finds one, it returns it.  If it doesn’t find a type specific IDataMapper, it returns an instance of GenericMapper, which is our reflection based DataMapper that can be used to handle mappings for any query / data shape combination.  We’ll cover GenericMapper in the next post.

What does the DataMapper do?

The behavior of a DataMapper class is defined by the IDataMapper interface.  The interface requires DataMappers to implement two methods.

interface IDataMapper

{

    // Main method that populates dto with data

    Object GetData(IDataReader reader);

    // Gets the num results returned. Needed for data paging.

    int GetRecordCount(IDataReader reader);

}

The GetData method is the important one.  That’s the method that our SqlDao calls whenever it needs to map the current record in an SqlDataReader to a specific data shape.  Remember, this method doesn’t do any mapping of an SqlDataReader to a List, or DataPage, or anything like that. That’s handled by the SqlDao data access methods. The purpose of the DataMapper GetData function is to take an SqlDataReader that’s already open and on a record, read that record, put the column data into an object (DTO) of the desired data shape, then return that single DTO.

Note that GetData returns an Object, so the SqlDao has to cast the GetData result to the proper data shape.  I tried all kinds of designs to get around this cast, I used interfaces, generics, base classes, but in the end all of these options added complexity to the code that I just couldn’t accept.  So, I opted to stick with GetData returning an object.  I think it was the right tradeoff, but if anyone has a better solution please let me know.

Here is a diagram of our IDataMapper interface and a couple of concrete DataMapper implementations.   

 

Inside a DataMapper class

So let’s take a look inside the UserMapper class and see what an IDataMapper implementation looks like. The user class is a data shape that represents, wait for it… a user.  The class looks like this.

So the job of UserDataMapper is to take a SqlDataReader, get the values from it’s columns, and put those values into and instance of the  User class above.  The DataMapper implementation that I use accomplishes this task in a very structured way that is easy to copy and reproduce for new data shapes. 

Before we look at the code, I should mention that I write a fair amount of extra code to get the ordinals for each data field instead of just using the string field names.  The reason for this is that using the ordinals allows me to use strongly typed methods on the SqlDataReader to get each column.  This allows me to avoid the cast to and from Object that occurs when you get a column by name which results in boxing and unboxing for value types.  It also gives me a little type safety.  If the query is returning a column called UserGuid that has a type of Int32, when I call reader.GetGuid() on that column, my code will throw an error and alert me that there is a problem with my query.  For more on the benefits of using ordinals take a look at High Performance Data Access Layer Architecture Part 3. 

Now, with no further ado, here’s the UserMapper implementation.

class UserMapper : IDataMapper

{

    private bool _isInitialized = false;

    private int _ordinal_UserGuid;

    private int _ordinal_UserRole;

    private int _ordinal_CreatedUtc;

    private int _ordinal_ModifiedUtc;

    private int _ordinal_CompanyGuid;

    private int _ordinal_FirstName;

    private int _ordinal_LastName;

    private int _ordinal_Email;

    private int _ordinal_Password;

    private int _ordinal_PhoneWork;

    private int _ordinal_PhoneMobile;

 

    private void InitializeMapper(IDataReader reader)

    {

        PopulateOrdinals(reader);

        _isInitialized = true;

    }

 

    public void PopulateOrdinals(IDataReader reader)

    {

        _ordinal_UserGuid = reader.GetOrdinal("UserGuid");

        _ordinal_UserRole = reader.GetOrdinal("UserRole");

        _ordinal_CreatedUtc = reader.GetOrdinal("CreatedUtc");

        _ordinal_ModifiedUtc = reader.GetOrdinal("ModifiedUtc");

        _ordinal_CompanyGuid = reader.GetOrdinal("CompanyGuid");

        _ordinal_FirstName = reader.GetOrdinal("FirstName");

        _ordinal_LastName = reader.GetOrdinal("LastName");

        _ordinal_Email = reader.GetOrdinal("Email");

        _ordinal_Password = reader.GetOrdinal("Password");

        _ordinal_PhoneWork = reader.GetOrdinal("PhoneWork");

        _ordinal_PhoneMobile = reader.GetOrdinal("PhoneMobile");

    }

 

    public Object GetData(IDataReader reader)

    {

        // This is where we define the mapping between the object properties and the

        // data columns. The convention that should be used is that the object property

        // names are exactly the same as the column names. However if there is some

        // compelling reason for the names to be different the mapping can be defined here.

 

        // We assume the reader has data and is already on the row that contains the data

        //we need. We don't need to call read. As a general rule, assume that every field must

        //be null  checked. If a field is null then the nullvalue for that  field has already

        //been set by the DTO constructor, we don't have to change it. 

        if (!_isInitialized) { InitializeMapper(reader); }

        User dto = new User();

        // Now we can load the data

        if (!reader.IsDBNull(_ordinal_UserGuid)) { dto.UserGuid = reader.GetGuid(_ordinal_UserGuid); }

        if (!reader.IsDBNull(_ordinal_UserRole)) { dto.UserRole = (UserRole)reader.GetInt32(_ordinal_UserRole); }

        if (!reader.IsDBNull(_ordinal_CreatedUtc)) { dto.CreatedUtc = reader.GetDateTime(_ordinal_CreatedUtc); }

        if (!reader.IsDBNull(_ordinal_ModifiedUtc)) { dto.ModifiedUtc = reader.GetDateTime(_ordinal_ModifiedUtc); }

        if (!reader.IsDBNull(_ordinal_CompanyGuid)) { dto.CompanyGuid = reader.GetGuid(_ordinal_CompanyGuid); }

        if (!reader.IsDBNull(_ordinal_FirstName)) { dto.FirstName = reader.GetString(_ordinal_FirstName); }

        if (!reader.IsDBNull(_ordinal_LastName)) { dto.LastName = reader.GetString(_ordinal_LastName); }

        if (!reader.IsDBNull(_ordinal_Email)) { dto.Email = reader.GetString(_ordinal_Email); }

        if (!reader.IsDBNull(_ordinal_Password)) { dto.Password = reader.GetString(_ordinal_Password); }

        if (!reader.IsDBNull(_ordinal_PhoneWork)) { dto.PhoneWork = reader.GetString(_ordinal_PhoneWork); }

        if (!reader.IsDBNull(_ordinal_PhoneMobile)) { dto.PhoneMobile = reader.GetString(_ordinal_PhoneMobile); }

        return dto;

    }

 

    public int GetRecordCount(IDataReader reader)

    {

        Object count = reader["RecordCount"];

        return count == null ? 0 : Convert.ToInt32(count);

    }

}

You’ll notice that the first thing we do is declare an _ordinal_FieldName member for each property in our User class.  These local members are used to store the ordinal location for the column in the SqlDataReader that maps to that User field.  We also create an _isInitialized flag that let’s us know whether we’ve set our _ordinal_FieldName values yet.  This is important when the UserMapper is being used to get more than one User from a DataReader.  The SqlDao code will get a single IDataMapper instance and will then call that mapper over and over for each record that exists in the SqlDataReader. We only want to find the ordinals once, so we create our _isInitialized flag that can be set to true after we set all the ordinal values.

Next is the InitializeMapper method.  This just calls our PopulateOrdinals method and then sets that _isInitialized flag, that we just discussed, to true.

PopulateOrdinals is the method that takes our SqlDataReader, finds the ordinal location of each field we need, then stores those ordinal values in our local _ordinal_FieldName members.

Finally we arrive at GetData, the heart of our class.  We’ve structured our code in such a way that GetData is actually pretty simple.  We check to make sure the _isInitialized flag has been set. If it hasn’t we call PopulateOrdinals.  We then create a new instance of our User class called dto. After that it’s just a matter of repeating the same line of code for each field.

if (!reader.IsDBNull(_ordinal_UserGuid)) { dto.UserGuid = reader.GetGuid(_ordinal_UserGuid); }  

This one line of code performs a null check on the ordinal, if the column exists it uses a strongly typed Get method to get the value of that column and assign it to the dto, if the column is null then we do nothing because the constructor for User already set each property to it’s null value on creation.  And that’s it, after setting each field value (or not setting it) we just return our dto and we’re done.  Simple maintainable, easy to copy and reproduce code.

The last method is GetRecordCount.  We need this method to support the DataPage<DTO> shape.  Our SqlDao needs to know the total record count if it’s returning a data page.  If it’s not returning a data page this method isn’t used.

Conclusion

That’s it.  Now that I look at the DataMapper code its a little anti-climactic because it’s really very simple stuff.  But then that’s the idea, break a complex operation into smaller pieces so that each one is easy to understand, and if needed, reproduce.  To add a new DataMapper we just copy and paste the existing UserMapper and change the field names.  Easy.  We don’t need to touch any of the code at the SqlDao level that handles getting Single<DTO>, List<DTO>, or DataPage<DTO> return types.  We also don’t touch any code that affects any other data shape.  All of our code is encapsulated in this one easy to handle class. 

Also, keep in mind that the real power of this architecture is that we don’t need to create a type specific DataMapper at all.  For the vast majority of tasks, the GenericMapper is going to work just fine.  In practice I never create any type-specific DataMappers during development because it seems like at that early stage data shapes are always in flux, and when a data shape changes, the last thing I want to do is go back and update a bunch of mapping code.  I look at the type specific DataMappers as something that I never want to use unless an app has gone into production and it’s experiencing performance issues related to the GenericMapper.  If that happens then I have a hook in the framework that I can use to create a simple DataMapper for that data shape that contains what I think is the fastest ADO.Net code possible. 

Next time we’ll go over the GenericMapper.

AAPL Part 5: How to Write a Data Access Object (DAO)

I recently decided that I needed to take a fresh look at how to build a persistence architecture that would provide the flexibility of an ORM, that would embrace change instead of resist it (making maintenance code easier), and would use ADO.Net.  I started building from the ground up, threw away any best practices that resulted in friction, and Agile ADO.Net Persistence Layer is the result.  Part 1 in the series can be found here: Agile ADO.Net Persistence Layer Overview. 

In this post, we’ll get into what is probably the most useful class in the framework, the Data Access Object (DAO).

Where does the DAO fit in?

Let’s backtrack a bit and take a quick look at the design of core.  Below we see a snapshot of Core’s folder structure.  We have a Persistence folder that contains our SqlDAO (a DAO written specifically for SqlServer), as well as our DataMappers and Persisters (we’ll go into these later).  The Rules folder contains business rules and validation rules for different entities / data shapes.  Security has a little plumbing code for business layer authorization.  Finally, the Services folder contains the service classes that we’ve focused on during the previous few posts. 

The service classes are the one stop shop that our UI code uses to get and save data.  A typical data access method looks like this.

public User GetUserByEmail(string email)

{

    string sql = @"SELECT *

                  FROM [User]

                  WHERE [Email] = @Email";

    SqlDao dao = SharedSqlDao;

    SqlCommand command = dao.GetSqlCommand(sql);

    command.Parameters.Add(dao.CreateParameter("@Email", email, 100));

    return dao.GetSingle<User>(command);

}

You’ll notice that this is a pretty simple method.  There’s no real Ado.Net code. We’re just defining a query, wrapping it in an SqlCommand, and then passing both the SqlCommand and our desired data shape <User> to our DAO.  This mystery DAO object does all the heavy lifting for us and even helps us create our query parameter in very efficient, single line of code.  This is a very intentional design.  After writing data access code for a few years, I finally noticed that the only things I really change from one data access method to the next are: the query; the parameters; and the return data shape. Everything else is just boilerplate plumbing code that gets repeated over and over again.  So, I took all that data access boilerplate code and extracted it out to a helper class called SqlDao.  The main functions of the DAO are:

• The DAO is the single access point to our database. All communication with the database goes through the DAO.  No code goes around it.
• The DAO encapsulates common ADO.Net tasks like getting a connection, creating query parameters, executing queries, etc.
• The DAO defines methods that take an SqlCommand as a parameter, execute it, and return the data in one of our standard formats, a single DTO, as List<DTO>, a DataPage<DTO>, or a string value.     

There’s a lot of good stuff in there that makes writing data access method almost frictionless.  I can’t go over everything so I included a complete listing for the SqlDao at the end of this post.

The GetSingle<T> and GetList<T> methods

Now let’s look at something a little more interesting. The SqlDao.GetSingle<T> method takes an SqlCommand as a parameter and returns the restults of that command as a single T where T is the class of our desired datashape.  So, the line dao.GetSingle<User>(command); will execute command and then return an object of type User.  If no User was found then the method just returns null.  Here’s how it works.

// GetSingle

public T GetSingle<T>(SqlCommand command) where T : class

{

    T dto = null;

    try

    {

        if (command.Connection.State != ConnectionState.Open)

        {

            command.Connection.Open();

        }               

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)

        {

            reader.Read();

            IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

            dto = (T)mapper.GetData(reader);

            reader.Close();

        }

    }

    catch (Exception e)

    {

        throw new Exception("Error populating data", e);

    }

    finally

    {

        command.Connection.Close();

    }

    // return the DTO, it's either populated with data or null.

    return dto;

}

The code is pretty simple.  We declare a new object of type T called dto (Data Transfer Object) and initialize it to null. We then check the connection on our command and make sure it’s open.  We call ExecuteReader on the command which gets us an SqlDataReader.  If the reader has any rows then we go to our DataMapperFactory and get an IDataMapper for the type T.  A cool thing that is happening behind the scenes is that the DataMapperFactory checks to see if we have an IDataMapper that has been created specifically for type T, if we don’t it returns a GenericMapper that uses reflection to map SqlDataReader fields to fields on our type T.  Once we have a mapper it’s just a matter of calling the mapper.GetData(reader) method and returning the dto. 

Now let’s take a quick look at GetList<T>.  This method is very similar to GetSingle<T>, but it returns a generic list of objects of type T instead of a single object of type T.  If no results are returned when we execute the SqlCommand passed to GetList<T>, we don’t return null, instead we return an empty list.  Here’s the code.                   

// GetList

public List<T> GetList<T>(SqlCommand command) where T : class

{

    List<T> dtoList = new List<T>();

    try

    {

        if (command.Connection.State != ConnectionState.Open)

        {

            command.Connection.Open();

        }  

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)

        {

            IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

            while (reader.Read())

            {

                T dto = null;

                dto = (T)mapper.GetData(reader);

                dtoList.Add(dto);

            }

            reader.Close();

        }

    }

    catch (Exception e)

    {

        throw new Exception("Error populating data", e);

    }

    finally

    {

        command.Connection.Close();

    }

    // We return either the populated list if there was data,

    // or if there was no data we return an empty list.

    return dtoList;

}

It’s really the same as GetSingle<T>.  We just have a different return type List<T>, and a while loop that get’s our individual DTOs and adds them to our return dtoList. The main logic for getting an IDataMapper from the DataMapperFactory and calling mapper.GetData(reader) to parse the reader results into an object of type T remains the same.

Conclusion

At this point you may be thinking, “That’s it???, you didn’t answer anything!  All you did is get some mapper object and call the GetData method on it.”  That’s true to some extent.  Our GetSingle<T> and GetList<T> methods just contain the logic that is always going to be common to those tasks regardless of the data shape passed to them.  Anything that is data shape specific, things like how do I get data from this field in my SqlDataReader and put it in this field in my returned data shape, are delegated to the IDataMapper.  This separation of logic means that it’s very easy to maintain our code.  We can add DataMapper logic for new data shapes and never touch any of the logic for GetSingle, GetList, and GetPage.  In fact, our logic for a specific data shape is encapsulated in a single IDataMapper class that touches nothing else in the application. That means it’s easy to change and maintenance friendly.  Plus, don’t forget that we have the reflection-based GenericMapper, so we never have to create a new IDataMapper if we don’t want to.

Next time we’ll take a look at a data shape specific IDataMapper and the DataMapperFactory. In the meantime, the full source for SqlDao is included below.  Have a look at the implementation for GetDataPage<T> and see if it makes sense now that we’ve gone over GetSingle<T> and GetList<T>.  The CreateParameter methods are worth a look too.   I went with a design where I just overload the method CreateParameter for each type of parameter that I need to create, so the usage syntax for CreateParameter(Guid value) and CreateParameter(int value) are almost identical. Each CreateParameter method was designed with the target of enabling that single line syntax that I use: command.Parameters.Add(dao.CreateParameter("@Email", email, 100)); so each method contains automatic null value checking.  You’ll also see some methods that weren’t in my original spec.  While using this framework on different projects I’ve found it convenient to have methods like GetSingleInt32(command) and GetStringList(command). 

Finally you can download a small, one page sample app that uses aapl at http://aapl.codeplex.com/. 

 

using System;

using System.Linq;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

using hf.Common;

using hf.Common.DataShapes;

using hf.Core.Persistence.DataMappers;

 

 

namespace hf.Core.Persistence

{

    public class SqlDao

    {

 

        #region "Database Helper Methods"

 

 

        // Connection

        private SqlConnection _sharedConnection;

        public SqlConnection SharedConnection

        {

            get

            {

                if (_sharedConnection == null)

                {

                    _sharedConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["hf"].ConnectionString);

                }

                return _sharedConnection;

            }

            set

            {

                _sharedConnection = value;

            }

        }

 

 

        // Constructors

        public SqlDao() { }

        public SqlDao(SqlConnection connection)

        {

            this.SharedConnection = connection;

        }

 

 

        // GetDbSqlCommand

        public SqlCommand GetSqlCommand(string sqlQuery)

        {

            SqlCommand command = new SqlCommand();

            command.Connection = SharedConnection;

            command.CommandType = CommandType.Text;

            command.CommandText = sqlQuery;

            return command;

        }

 

 

        // GetDbSprocCommand

        public  SqlCommand GetSprocCommand(string sprocName)

        {

            SqlCommand command = new SqlCommand(sprocName);

            command.Connection = SharedConnection;

            command.CommandType = CommandType.StoredProcedure;

            return command;

        }

 

 

        // CreateNullParameter

        public  SqlParameter CreateNullParameter(string name, SqlDbType paramType)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = paramType;

            parameter.ParameterName = name;

            parameter.Value = DBNull.Value;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

 

 

        // CreateNullParameter - with size for nvarchars

        public  SqlParameter CreateNullParameter(string name, SqlDbType paramType, int size)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = paramType;

            parameter.ParameterName = name;

            parameter.Size = size;

            parameter.Value = DBNull.Value;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

 

 

        // CreateOutputParameter

        public  SqlParameter CreateOutputParameter(string name, SqlDbType paramType)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = paramType;

            parameter.ParameterName = name;

            parameter.Direction = ParameterDirection.Output;

            return parameter;

        }

 

 

        // CreateOuputParameter - with size for nvarchars

        public  SqlParameter CreateOutputParameter(string name, SqlDbType paramType, int size)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = paramType;

            parameter.Size = size;

            parameter.ParameterName = name;

            parameter.Direction = ParameterDirection.Output;

            return parameter;

        }

 

 

        // CreateParameter - uniqueidentifier

        public  SqlParameter CreateParameter(string name, Guid value)

        {

            if (value.Equals(NullValues.NullGuid))

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.UniqueIdentifier);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.UniqueIdentifier;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

 

        // CreateParameter - int

        public  SqlParameter CreateParameter(string name, int value)

        {

            if (value == NullValues.NullInt)

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.Int);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.Int;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

 

        // CreateParameter - bool

        public SqlParameter CreateParameter(string name, bool value)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = SqlDbType.Bit;

            parameter.ParameterName = name;

            parameter.Value = value ? 1 : 0;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

 

 

        // CreateParameter - datetime

        public  SqlParameter CreateParameter(string name, DateTime value)

        {

            if (value == NullValues.NullDateTime)

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.DateTime);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.DateTime;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

 

        // CreateParameter - nvarchar

        public  SqlParameter CreateParameter(string name, string value, int size)

        {

            if (String.IsNullOrEmpty(value))

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.NVarChar);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.NVarChar;

                parameter.Size = size;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

 

        // CreateTextParameter

        public SqlParameter CreateTextParameter(string name, string value)

        {

            if (String.IsNullOrEmpty(value))

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.Text);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.Text;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

        #endregion

 

 

 

        #region "Data Projection Methods"

 

 

        // ExecuteNonQuery

        public void ExecuteNonQuery(SqlCommand command)

        {

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                command.ExecuteNonQuery();

            }

            catch (Exception e)

            {

                throw new Exception("Error executing query", e);

            }

            finally

            {

                command.Connection.Close();

            }

        }

 

 

        // ExecuteScalar

        public Object ExecuteScalar(SqlCommand command)

        {

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                return command.ExecuteScalar();

            }

            catch (Exception e)

            {

                throw new Exception("Error executing query", e);

            }

            finally

            {

                command.Connection.Close();

            }

        }

 

 

        // GetSingleValue

        public T GetSingleValue<T>(SqlCommand command)

        {

            T returnValue = default(T);

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    reader.Read();

                    if (!reader.IsDBNull(0)) { returnValue = (T)reader[0]; }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnValue;

        }

 

 

        // GetSingleInt32

        public Int32 GetSingleInt32(SqlCommand command)

        {

            Int32 returnValue = default(int);

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    reader.Read();

                    if (!reader.IsDBNull(0)) { returnValue = reader.GetInt32(0); }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnValue;

        }

 

 

        // GetSingleString

        public string GetSingleString(SqlCommand command)

        {

            string returnValue=null;

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    reader.Read();

                    if (!reader.IsDBNull(0)) { returnValue = reader.GetString(0); }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnValue;

        }

 

 

        // GetGuidList

        public List<Guid> GetGuidList(SqlCommand command)

        {

            List<Guid> returnList = new List<Guid>();

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    returnList = new List<Guid>();

                    while (reader.Read())

                    {

                        if (!reader.IsDBNull(0)) { returnList.Add(reader.GetGuid(0)); }

                    }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnList;

        }

 

 

        // GetStringList

        public List<string> GetStringList(SqlCommand command)

        {

            List<string> returnList = new List<string>();

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    returnList = new List<string>();

                    while (reader.Read())

                    {

                        if (!reader.IsDBNull(0)) { returnList.Add(reader.GetString(0)); }                   

                    }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnList;

        }

 

 

        // GetSingle

        public T GetSingle<T>(SqlCommand command) where T : class

        {

            T dto = null;

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }               

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    reader.Read();

                    IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

                    dto = (T)mapper.GetData(reader);

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            // return the DTO, it's either populated with data or null.

            return dto;

        }

 

 

        // GetList

        public List<T> GetList<T>(SqlCommand command) where T : class

        {

            List<T> dtoList = new List<T>();

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }  

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

                    while (reader.Read())

                    {

                        T dto = null;

                        dto = (T)mapper.GetData(reader);

                        dtoList.Add(dto);

                    }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            // We return either the populated list if there was data,

            // or if there was no data we return an empty list.

            return dtoList;

        }

 

 

 

 

        // GetDataPage

        public DataPage<T> GetDataPage<T>(SqlCommand command, int pageIndex, int pageSize) where T : class

        {  

            DataPage<T> page = new DataPage<T>();

            page.PageIndex = pageIndex;

            page.PageSize = pageSize;

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

                    while (reader.Read())

                    {

                        // get the data for this row

                        T dto = null;

                        dto = (T)mapper.GetData(reader);

                        page.Data.Add(dto);

                        // If we haven't set the RecordCount yet then set it

                        if (page.RecordCount == 0) { page.RecordCount = mapper.GetRecordCount(reader); }

                    }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return page;

        }

 

        #endregion

 

    }

}