How to simplify my data access or shift it into an ORM

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 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 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
{

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())
                .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 : IRepository 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(id);
    }

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

The PostRepository.

public class PostRepository : Repository, IDeletionRepository, 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(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.Begin

StackExchange Code Review Q#5744, answer score: 3