Quit writing data access code

I’ve written about your DWR before.   While deleting code is certainly a way to increase it, it’s not really the entire point.  The real point is that you should be writing less code.  I’m a huge fan of writing less code – especially less of the redundant, mind-numbing, why-can’t-I-hire-an-intern-to-write-this code.  You know what I’m talking about – all of that data access layer garbage that we churn out to get our class data into and out of our relational databases.


If your job is primarily working on one application, you probably don’t do this a whole lot, but when you are frequently starting new projects, you find yourself getting into this tedious stuff frequently, and I really, really hate doing it.  Not only is it torture, it’s highly prone to errors since it’s typically a copy, paste and adjust-the-names process.  How often have you have to write code to generate your database?  Does code like this look familiar?


public Book[] GetAllBooks()
{
  var books = new List<Book>();

  using (SqlCeCommand cmd = new SqlCeCommand(“SELECT * FROM Book”, Connection))
  {
    using (var results = cmd.ExecuteResultSet(ResultSetOptions.Insensitive))
    {
      while (results.Read())
      {
        if (m_bookOrdinals.Count == 0)
        {
          for (int i = 0; i < results.FieldCount; i++)
          {
            m_bookOrdinals.Add(results.GetName(i), i);
          }
        }

        books.Add(new Book
        {
          BookID = results.GetInt32(m_bookOrdinals[“BookID”]),
          AuthorID = results.GetInt32(m_bookOrdinals[“AuthorID”]),
          Title = results.GetString(m_bookOrdinals[“Title”])
        });
      }
    }
  }

  return books.ToArray();
}


Well, I finally got tired of it and decided to spend some time writing code that would free me from having to do that stuff any longer.  The result is a new, open-source project called the OpenNETCF.ORM Framework.  It’s a simple, lightweight ORM that helps take care of this tedium.  For example, the above block of code now looks like this:


public Book[] GetAllBooks()
{
  return Store.Select<Book>();
}


And it pulls all of the books from the underlying SQLCE database. Yes, it’s that simple.  Of course getting it to do that requires a little infrastructure work.  For this one the Book class has to look like this:


[Entity]
public class Book
{
  [Field(IsIdentity=true, IsPrimaryKey=true)]
  public int BookID { get; set; }

  [Field]
  public int AuthorID { get; set; }

  [Field]
  public string Title { get; set; }

  [Field(SearchOrder=FieldSearchOrder.Ascending)]
  public BookType BookType { get; set; }
}


But that’s a small price to pay in my book.  We went from 11 lines of code (not counting brackets) to one.  Multiply that out by the number of entities, filtering logic, paging logic and all the other code you typoically write and you’d greatly decreased your LOC count.


Now I’m not saying that OpenNETCF.ORM does everything that something like the Entity Framework does.  Remember, this is a small scale framework (the core is only 14k and the SqlCE implementation adds 17k) designed for mobile and embedded systems.  It’s also put together by a team of one and as a side project while doing other work.


What it does have, though, is performance. It’s actually faster than using direct SQL calls in many cases because it avoid the query processor whenever it can).  It has extensibility. The source code has a full implementation for SQL CE but I’ve also included the skeleton for an XML implementation for anyone who wants to try their hand at it, and it would be pretty easy to do a MySQL or SQLite implementation as well.  Most important, though, is that it has my commitment.  Like the IoC framework, I’ve already rolled ORM into a production application.  That means that as I find problems, they are going to get fixed.  As I find features that are missing that would help me get my job done, I’m going to add them.  Bottom line is that this is not a science project that I’m doing purely for fun and that will get abandoned when I get bored with it.


So if you’re as tired of writing DAL code as I am, give it a try.  If you like it, let me know, or better yet update the docs or a new implementation.


If you’re ready to get started, pull down the latest code (there are no releases quite yet) and look at the test project.  There should be enough to get you going on how to use it, but if you have questions, feel free to post them.

4 thoughts on “Quit writing data access code”

  1. Absolutely. The default behavior is to just take the class/property name but you can remap field names or entity names. For example an entity can be remapped like this: [Entity(NameInStore="MyTableName")] or a Field like this: [Field(FieldName="MyColumnName")]

    Like

  2. I’ve just read the post about this ORM and I’m having a quick look at it, but one thing that I wonder about is the overhead of using reflection for getting/setting the values of properties when fetching/saving the entities. In my current project we’re thinking about using reflection for some of the common entity-related logic, but we’re afraid of the performance hit when fetching entities for some of the list screens (let’s say 500-1K rows of data).

    What’s your experience with reflection for property getting/setting?

    Like

  3. You’d have to run some perf tests for the data sizes you’re talking about. My testing so far (and the tests are checked in with the source) shows that the speed is just fine for several hundred. I’ve not done any selecting of thousands yet, though I’d make the case that you can’t be displaying that much data anyway. The ORM has a facility for fetching pages of data (use Fetch instead of Select), which would help things a lot on large sets of data, both for speed as well as memory footprint.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s