Simple Data Access Layer

Question

Can anyone suggest a simple Data Access Layer (C# .NET)? Not keen on using the Microsoft Application Data Access block, seems very bloated and overkill. Also don't want to use LINQ to SQL for various reasons. I want to build upon this and create our own in-house ORM, again for various reasons. In the past I've always had the Data Access Layer already built so was never involved in building....

Answer 1

Do you want to be able to use Linq?

Do you want to use DataSets / DataTables?

Do you want to use a code generator (of your own or someone else's)?

Do you want to use stored procedures?

Honestly, it's not that hard to grow your own DAL/ORM from scratch as long as you don't care for advanced features of any sort and you don't mind the mind numbing tedium of the process. You have to be a little crazy though. :)

I'm assuming you don't want to use anything like SubSonic, EntityFramework, or NHibernate as well, but correct me if that is a bad assumption.

Answer 2

Other individuals and organizations have spent months or years developing their own ORMs and technologies -- many of which you listed -- and many are free to use. You should devote your resources toward your core application logic instead of trying to build another whole beast called ORM. There are enough offerings out there to satisfy all kinds of applications.

You said you've never been involved in building your down DAL. It's a classic starting error to try to roll your own resulting ORM (as far as time and resources, not necessarily knowledge), for those reasons stated above. If you think some of the existing offerings seem overkill, just wait until you get into the intricacies of building your own.

However if you want to compete in the ORM market and that IS your product, then go right ahead. That's my 2 cents.

Edit: If you have concerns about being bound to a certain ORM product or offering in your project, you can simply hide away whatever software you choose behind an interface and swap in other ORMs as needed...

public interface IBusinessDataOperations {
   // hides any ORM of choice
}

And if someday you have some free cycles and want to build your own ORM, even after trying other products, you can just slip it in behind this interface too.

Answer 3

For very simple requirements (and I mean simple!) I'd create a DAL based on the repository pattern, where the sql querying is done in the repository methods and then simple POCO's (Plain old CLR objects) are created and returned.

You can call sprocs or parametized SQL if required. Then map the data to your poco (for this just use a standard SQLDataReader)

But to be honest the moment the queries get big or complex or there are lots of fields in your objects you're better off letting a proper DAL/ORM take the strain and concentrate on your application.

Answer 4

Have a look at SubSonic version 2.x if you need to avoid linq. This is a simple/ lite ORM that you can build upon. Why roll your own when there is great code like this.

Answer 5

Actually the simplest SQLHelper file that I've found has been the early 2000 era MS Data Access Application Block. After unpackaging and opening the solution you find that you have just one file called SQLHelper.cs which basically just wraps alot of the raw ADO that you would be writing with a home grown DAL. It is best practice code (at the time yes) and easily converts to .net 2.0 framework and above. After you place SQLHelper into your solution, then you can easily add in your own data access components to do your basic CRUD work, but you won't have to worry about the low level coding of opening a connection or a dataset or whatever.

I know what you're thinking, why suggest this because you said you didn't like the blocks. Well this one is the only i've found that doesn't use a provider factory and all of that extra code that you're saying is overkill. This block is only for SQL Server so do keep that in mind. But overall, you might find that this is a good starting point in your project.

Here is a download link to the block itself.

Good luck, and hope this helps.

Answer 6

Before you can make your decisions there are some questions that you have to answer to yourself before begin, not just if you dont want linq or ms data access layer those are some of that questions:

• Do you want to be able to modify the code (generator) or do you want a DAL that gives you the service of accesing the DB?
• Do you want to deal with XML configuration files or do you prefer a DAL based on reflection?
• Do you want support for multiple DBs or you can live with an specific one?

Some years ago I had to build a DAL for the company I was working for and resulted that it was easier than I though, the result was an ORM based on reflection who supports many DBs at this time SqlServer, Oracle, PostgreSql and SqLite, I can share the code for you it may help you build your own or extend it to your needs.

If it helps you for anything build your own DAL (if you havee the time) its a great learning experience you will learn some Sql and several features of the language that you might never use again.

Good luck with that...

Answer 7

Check out DbExtensions, you can use it to simplify DAL code, or you can build your own ORM on top.

Answer 8

I found this the other day:

http://www.simple-talk.com/dotnet/.net-framework/.net-application-architecture-the-data-access-layer/

Answer 9

Here is a complete list: ORM tools ".Net"