What ORM can I use for Access 2007 - 2010? I'm after WPF binding to the tables etc

Question

I've a legacy database that all sites have, it describes specific content in a number of catagory/subcatagory/child item format. Until now, adding/editing the content is either manual work in the tables OR raw sql Windows Forms tool (I built when I started out in the job!).

I would like Entity Framework style drag, drop, bind and run coding ability with WPF 4.5 and .net 4.5.

I hesitate to use NHibernate as EF5 is very simple to get going with, I understand Nhibernate is more work (albeit a faster ORM). Are there alternatives that work well? I'm trying to avoid too much manual setup, if possible. The editor isn't a mandatory project and I can't justify lots of extra work on it - but it would make my job easier for the next 2 years if a nice version of it was put together.

All the argument against Access I know really well :) - swapping this isn't an option for at least a year.

Having searched the StackOverflow site, I don't see too many questions asking for this, but apologies if I've missed a good one!

Update: I think I should refine my question slightly as really what I needed to get at what code generation so that I don't need to hand build all the classes for the Access database. From what I can see, Dapper's work is around efficiency but is distinct from generating code. Coming from a entity framework mindset, I can see where I've conjoined the tasks somewhat in my thinking :). So apart from boil my own - does anyone know a good code gen for use with Access. This I can marry to Dapper :).

Uretic

Answer 1

You can't use Entity Framework, because it doesn't work with Access databases.

It's possible to use NHibernate with MS Access, although NH doesn't support Access out of the box.
You need NHibernate.JetDriver from NHContrib and here are example settings for the NH config file.

If I recall it correctly, NH Contrib needs to be compiled against the exact NH version you're using, so you probably need to download the source code and compile it by yourself.

As an alternative, you can use one of the many micro-ORMs, for example Stack Overflow's own Dapper.

Dapper is DB agnostic, so it can connect to everything including Access. Quote from the official site:

Will dapper work with my db provider?
Dapper has no DB specific implementation details, it works across all .net ado providers including sqlite, sqlce, firebird, oracle, MySQL and SQL Server

The disadvantage is that because Dapper is DB agnostic, you have to implement some advanved stuff yourself, like paging.

---

EDIT:

IMO Dapper is in the "fairly easy to run quickly catagory".
Take a look at this:
(complete demo project here)

using System;
using System.Data.OleDb;
using Dapper;

namespace DapperExample
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb"))
            {
                var list = con.Query<Product>("select * from products");

                Console.WriteLine("map to a strongly typed list:");
                foreach (var item in list)
                {
                    Console.WriteLine(item.ProductNumber + " : " + item.Description);
                }

                Console.WriteLine();

                var list2 = con.Query("select * from products");

                Console.WriteLine("map to a list of dynamic objects:");
                foreach (var item in list2)
                {
                    Console.WriteLine(item.ProductNumber + " : " + item.Description);
                }

                Console.ReadLine();
            }
        }
    }

    public class Product
    {
        public string ProductNumber { get; set; }
        public string Description { get; set; }
    }
}

There are two different queries in this example code.

The first one maps to a strongly typed list, e.g. the result is an IEnumerable<Product>. Of course it needs a Product class that it can map to.

The second query returns an IEnumerable<Dynamic> (>= .NET 4.0) which means that the properties are evaluated on the fly and you don't need to define a class before, but the disadvantage is that you lose type safety (and IntelliSense).
My personal opinion is that the missing type safety is a deal breaker for me (I prefer the first query syntax), but maybe this is something for you.

Powerhouse

So there's nothing in the fairly easy to run quickly catagory I'm guessing then? That's a shame. The job is not quite big/important enough to dig into NHibernate really-Sadly, otherwise I'd allocate some days at it. Dapper might integrate with my own cobbled together generator perhaps. Thank you for your help and the links. It's a pity that MS Access has been left out of the ORM party by pretty much most providers-it might not be a proper DB but its dead popular! – Uretic

Whilst I've not given it enough hours, I don't see any advantages of using Dapper for what I'm doing - it looks like I still create all my own classes by hand to marry up to the database? Have I just missed the point (Accepted if so) as I've gotten used to the code generation aspect of Entity Framework. I'm trying to work this out now, but it does seem that way. – Uretic

With Dapper, you create the classes per hand, as you would when using NHibernate. In both cases, the tool just does the work of mapping the query result to the classes. Admittedly, both tools are not able to generate the classes like it's possible with EF. – Powerhouse

I think it'll be a mad moments project-build an Access provider for EF. Pure altruism of course, to relieve other poor Devs who've gotta write for it :). Might take me a while...Thanks Christian. I'll go bother someone else now! Much obliged. – Uretic

I just noticed that if you scroll to the bottom of the official Dapper page, there is a list of 3rd party tools. One of them looks like a code generator. Maybe you can give it a try. – Powerhouse

And Dapper can create dynamic queries too (without the need to generate a class at all). I added an example to my answer, take a look at it! – Powerhouse

Dapper and async ? – Zippy

Answer 2

Hate to resurrect an old thread but I recently did a WPF project using PetaPoco, a micro-ORM, with MS Access so I thought I'd share my implementation.

To add MS Access support to PetaPoco, you just need to add a couple of bits of code:

First add an AccessDatabaseType class. All of the DataBaseType classes are at the end of the PetaPoco.cs file. Just add the new class after SqlServerDatabaseType.

class AccessDatabaseType : DatabaseType
{
    public override object ExecuteInsert(Database db, IDbCommand cmd, string PrimaryKeyName)
    {               
        db.ExecuteNonQueryHelper(cmd);
        return db.ExecuteScalar<object>("SELECT @@@IDENTITY AS NewID;");
    }

}

Next, modify PetaPoco.Internal.DatabaseType.Resolve() to support the AccessDatabaseType. (This code assumes you are using the Jet OLEDB provider)

public static DatabaseType Resolve(string TypeName, string ProviderName)
{
    //...

    if (ProviderName.IndexOf("Oledb", StringComparison.InvariantCultureIgnoreCase) >= 0)
        return Singleton<AccessDatabaseType>.Instance;  

    // Assume SQL Server
    return Singleton<SqlServerDatabaseType>.Instance;
}

Finally, to instantiate PetaPoco use this:

Db = New PetaPoco.Database("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db.mdb", "System.Data.Oledb")

---

Limitations:

• PetaPoco assumes your primary keys are autonumber/identity fields. If you have a PK that's not an autonumber or you have a composite PK, you'll need to implement your own insert & save logic.
• I didn't need paging in my application so I didn't implement it.

Balf

I've noticed that the official PetaPoco repo has been updated with MS Access support. (github.com/CollaboratingPlatypus/PetaPoco) Hrrmm, I would like to think that my post here inspired the new feature. :) – Balf

@@@IDENTITY ? or @@IDENTITY ? – Zippy

Answer 3

We are using Jet Entity Framework Provider. That way we can easily port to another database later.

It does not have all the limitations mentioned above and works great.

Riesling

Answer 4

Tortuga Chain fully supports Access.

https://docevaad.github.io/Chain/Introduction.htm

Wynn