4
\$\begingroup\$

In building any new application I end up using something similar to this data access type code. I know it well, and it works well. But in the act of wanting to try something new on a new project I thought I could review it again and maybe simplify it more, or move it into an ORM. I've been looking at Dapper, though I know there are a whole host of ORM's.

This is code to retrieve a List of any sort of data. Something thats completed often in my code.
"Business" code

public class MyClassA
{
    public int Id { get; set; }
    public DateTime CreatedDate { get; set; }
    // Many other properties

    internal static MyClassA BuildPostsFromRow(DataRow dr)
    {
        MyClassA _myclass = new MyClassA();

        _myclass.Id = (int)dr["ContentId"];
        /*   Many other properties   */
        _myclass.CreatedDate = (DateTime)dr["CreatedDate"];

        return _myclass;
    }

    public List<Post> ListRecentForInstance(int id)
    {
        DataAccess _da = new DataAccess();
        return _da.ListForInstance(id);
    }
}

My actual Data access

public class DataAccess
{
    public readonly string Con = ConfigurationManager.ConnectionStrings["MyDb"].ToString();

    public List<MyClassA> ListForInstance(int instanceId)
    {
        var _sql = @"
            SELECT Id, 
                   CreatedDate,
                   ...other data
              FROM MyData
            WHERE InstanceId = @instanceId
            ";
        SqlParameter[] _params = new SqlParameter[1];
        _params[0] = new SqlParameter("@instanceId", SqlDbType.Int) { Value = instanceId };

        return DataHelper.ExecuteQuery(Con, _sql, _params).Tables[0].AsEnumerable()
            //MyClassA.BuildPostsFromRow can also be split out to be inline rather and 
            // calling another method but I'm reusing the BuildPostsFromRow method for now
                           .Select(row => MyClassA.BuildPostsFromRow(row))
                           .ToList();
    }
}

public class DataHelper
{
    // A few other similar methods to wrap up data access code and sql logging
    // only the DataAccess class calls these methods
    public static DataSet ExecuteQuery(string connection, string sql, params SqlParameter[] paramsToCommand)
    {
        using (DataSet ds = new DataSet())
        {
            using (SqlConnection _con = new SqlConnection(connection))
            {
                if (_con.State == ConnectionState.Closed)
                    _con.Open();

                using (SqlCommand _cmd = new SqlCommand(sql, _con))
                {
                    AttachParameters(_cmd, paramsToCommand);

                    using (SqlDataAdapter adapter = new SqlDataAdapter(_cmd))
                    {
                        adapter.Fill(ds);
                    }
                }
            }
            return ds;
        }
    }

    private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
    {
        if (command == null)
            throw new ArgumentNullException("command");

        if (commandParameters != null)
        {
            foreach (SqlParameter p in commandParameters)
            {
                if (p != null)
                {
                    if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
                        p.Value = DBNull.Value;

                    command.Parameters.Add(p);
                }
            }
        }
    }
}

Yes I'm sure everyone thinks "oh great another DAL". But I'm really wanting some change. I'd really like some pointers on how to move away from this model and slip into something more modern.

Edit:
In the thought of testing, I spent today figuring out Linq 2 Sql and Dapper. I really want something to work, I really do. So here is my app, as short and sweet and full of nothing as it is:

class Program
    {
        public static readonly string connectionString = 
@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True";
    public static SqlConnection GetOpenConnection()
    {
        var connection = new SqlConnection(connectionString);
        if (connection.State != ConnectionState.Open)
            connection.Open();
        return connection;
    }
    SqlConnection connection = GetOpenConnection();

    static void Main(string[] args)
    {
// Added as it was taking time the first time to connect the db to the instance
            using (var connection = new SqlConnection(connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
            }

        Stopwatch sw1 = new Stopwatch();
        sw1.Start();
        int i = 0;

        // Linq2Sql
        for (i = 0; i <= 100; i++)
        {
            CoreClassesDataContext _dc = new CoreClassesDataContext();

            var _posts = (from p in _dc.Posts

                          select p).ToList();

            foreach (var item in _posts)
            {
                int id = item.Id;
            }
            _dc.Dispose();
        }
        sw1.Stop();
        Console.WriteLine("Linq 2 Sql");
        Console.WriteLine(sw1.ElapsedMilliseconds);
        sw1.Reset();
        sw1.Start();
        // Dapper
        for (i = 0; i <= 100; i++)
        {
            Program _p = new Program();
            var data = _p.connection.Query<Post>("select Id, PostContent from Post").ToList();
            foreach (var item in data)
            {
                int id = item.Id;
            }
        }
        sw1.Stop();
        Console.WriteLine("Dapper");
        Console.WriteLine(sw1.ElapsedMilliseconds);
        sw1.Restart();

        // Plain ol ADO
        for (i = 0; i <= 100; i++)
        {
            Data _d = new Data();
            foreach (var item in _d.BuildList())
            {
                int id = item.Id;
            }
        }
        sw1.Stop();
        Console.WriteLine("DAL");
        Console.WriteLine(sw1.ElapsedMilliseconds);

        Console.ReadKey();
    }
    class Data
    {
        public List<Post> BuildList()
        {
            var _sql = "select Id, PostContent from Post";
            return DataHelper.ExecuteQuery(connectionString, _sql).Tables[0].AsEnumerable()
                           .Select(row => new Post { Id = (int)row["Id"],
                                                     PostContent = (string)row["PostContent"]
                           }).ToList();
        }
    }

    class Post
    {
        public int Id { get; set; }
        public string PostContent { get; set; }
    }
}

After many, many runs, it almost always works out like this or close to:

Linq 2 Sql
   205
Dapper
   394
DAL
   105

I thought part of an ORM was to help with speed? So why is my dead simple, hand crafted DAL beating the pants off both? Am I doing something wrong?

\$\endgroup\$
  • \$\begingroup\$ In this small solution, speed is not going to be an issue. It's easy to get picky when it comes to performance, but in this case it doesnt matter. And Dapper should be one of the fastest ORM's out there according to: eggheadcafe.com/tutorials/ado/… \$\endgroup\$ – Mattias Nov 4 '11 at 8:41
  • \$\begingroup\$ Who's saying this is going to be a small solution? I know I didn't. The application usage is pretty wide. I'm using similar patterns with both personal work (MS SQL) like my example. As well as at work in MySQL with many millions of rows of data. This is why I was looking at things like Dapper, one implementation, many database types. I also work with Oracle at work along with MySQL in the same application. \$\endgroup\$ – Tim Meers Nov 4 '11 at 10:53
  • \$\begingroup\$ Performance is also very critical for me, not so much the time it takes to write all the data access. As a company, we don't do TDD. So while it would be nice to be able to someday, its not important to the powers that be at this time. I'd even wager a bet that my senior dev doesn't even know what it is. I'm just trying to find something that's really fast, not too complex, and maybe just maybe be able to run tests against. \$\endgroup\$ – Tim Meers Nov 4 '11 at 11:01
  • \$\begingroup\$ Im sure LINQ-TO-SQL will do: blog.stackoverflow.com/2008/09/… \$\endgroup\$ – Mattias Nov 4 '11 at 11:21
3
\$\begingroup\$

Your headline question is in two parts.

Taking the first part, "How to simplify", I'd say that you shouldn't bother trying. All of that effort is already in other frameworks, whether they are small helpers like Dapper or fully-featured ORMs like NHibernate. The world, as you've realised, has moved on.

To answer the second part, "shift it into an ORM", I'd suggest that is not possible. Taking the example of EF code first or Fluent NHibernate (preferably with AutoMapping), you start from your POCOs that model your domain, in the case of your code above, a single 'Post' class. The rest of the code is already in the framework.

My advice on how to pick up a modern model, as you put it, would be to do a learning exercise. Put aside the codebase you have for a while and look at creating a small application for yourself that uses EF code first or Fluent NHibernate with Automapping. You could even see how easy it is to branch/refactor and use the other ORM - a great way to learn the essentials.

UPDATE: hopefully this implementation of a rough PostRepository with Fluent NHibernate Automapping will encourage you in your studies. There are holes in the following but you could probably get it working. Use NuGet to fetch Fluent NHibernate.

public static class Configuration
{
    public static FluentConfiguration GetConfiguration(string connectionString)
    {
        var executingAssembly = Assembly.GetExecutingAssembly();

        var configuration =
            Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2008.ConnectionString(connectionString))
            .Mappings(
                m =>
                m.AutoMappings.Add(
                    AutoMap.Assemblies(new AutomappingConfiguration(), executingAssembly)
                    .IgnoreBase<Entity>())
                .ExposeConfiguration(BuildSchema);

        return configuration;
    }

    // Set SchemaScript to True to see the generated DDL.
    // Set SchemaExport to True to build the database.
    private static void BuildSchema(NHibernate.Cfg.Configuration configuration)
    {
        new SchemaExport(configuration)
            .SetOutputFile(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "schema.sql"))
            .Create(Properties.Settings.Default.SchemaScript, Properties.Settings.Default.SchemaExport);
    }
}

The following class allows you to specify the namespace of your entity (i.e. the 'Post' model).

public class AutomappingConfiguration : DefaultAutomappingConfiguration
{
    public override bool ShouldMap(Type type)
    {
        return type.Namespace == "MyPostProject.Entities";
    }
}

NHibernate likes everything to have an identity. Here's a base class for that. Everything needs to be virtual so NHibernate can create proxies to your model objects.

public abstract class Entity
{
    public virtual int Id { get; protected internal set; }
}

Here's a basic Post entity.

public class Post : Entity
{
    public virtual string Content { get; set; }
}

A base repository class that uses the Session as its Unit of Work.

public abstract class Repository<TEntity> : IRepository<TEntity> where TEntity : Entity
{
    protected readonly ISession Session;

    protected Repository(ISessionSource sessionSource)
    {
        this.Session = sessionSource.CreateSession();
    }

    public virtual void SaveOrUpdate(TEntity entity)
    {
        this.Session.SaveOrUpdate(entity);
        this.Session.Flush();
    }

    public virtual TEntity GetById(int id)
    {
        return this.Session.Get<TEntity>(id);
    }

    public virtual IEnumerable<TEntity> GetAll()
    {
        return this.Session.CreateCriteria<TEntity>().List<TEntity>();
    }
}

The PostRepository.

public class PostRepository : Repository<Post>, IDeletionRepository<Post>, IPostRepository
{
    public PostRepository(ISessionSource sessionSource) : base(sessionSource)
    {
    }

    public void Delete(Post post)
    {
        this.Session.Delete(post);
        this.Session.Flush();
    }
}

A persistence test that checks your mappings are correct.

using Configuration = MyPostProject.Configuration;

[TestClass]
public class PersistenceSpecificationTests
{
    private ISession session;

    [TestInitialize]
    public void SetUp()
    {
        var sessionSource = new SessionSource(Configuration.GetConfiguration("Data Source =.; Initial Catalog =Post; Integrated Security=True;"));
        session = sessionSource.CreateSession();
    }

    [TestMethod]
    public void CanMapPost()
    {
        using (var transaction = session.BeginTransaction())
        {
            new PersistenceSpecification<Post>(session)
                .CheckProperty(m => m.Content, "Lorum ipsum")
                .VerifyTheMappings();

            transaction.Rollback();
        }
    }
}

Create a dummy post (use an Object Mother generator like NBuilder or AutoFixture) and try to exercise your repository.

    [TestMethod]
    public void ShouldSaveUser()
    {
        using (var transaction = session.BeginTransaction())
        {
            session.Save(dummyPost);
            transaction.Rollback();
        }
    }

There's a lot more besides with Fluent NHibernate's Overrides, that let you set up small changes to the default automapping if required, and Conventions, that pretty up your DDL. Your repository should also be able to take specifications (criteria to search on).

\$\endgroup\$
  • \$\begingroup\$ My main problem is that this model is dead simple. It's just very time consuming to hand write all the SQL. When I look at EF I see major complexities. Maybe I just need to find the right example, covering a common goal or problem, that is not over engineered. I'm able to learn and adapt, but it has to make sense to me, and so far I haven't found the right example or tutorial. \$\endgroup\$ – Tim Meers Nov 2 '11 at 20:09
  • \$\begingroup\$ I can't point you to a perfect tutorial, I'm afraid, and my own implementation is more complicated, but I'll edit my answer to show a rough PostRepository with Fluent NHibernate Automapping. \$\endgroup\$ – Boggin Nov 3 '11 at 9:40
  • 1
    \$\begingroup\$ If NHibernate feels too much overkill, you should definitely utilize LINQ-TO-SQL. Perfect for a smaller scenario. \$\endgroup\$ – Mattias Nov 3 '11 at 10:43
  • \$\begingroup\$ @Mattias That's a really sensible suggestion - wish I'd thought of that! Mark up your entity with attributes, one line to create your data context and you're away. The queries even look like SQL. \$\endgroup\$ – Boggin Nov 3 '11 at 13:35
  • \$\begingroup\$ Very nice! I will give this a whirl soon. \$\endgroup\$ – Tim Meers Nov 3 '11 at 16:44
2
\$\begingroup\$

If NHibernate feels too much overkill, you should definitely utilize LINQ-TO-SQL. Perfect for a smaller scenario.

\$\endgroup\$
2
\$\begingroup\$

I'm a BIG fan of Entity Framework. I generally use database first and then update my data model from the tables I select. I then use linq with lambdas expressions. With this, combination, I can write some pretty concise code and no SQL. I have to recommend giving it a try.

http://msdn.microsoft.com/en-us/data/gg685489

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.