We have grown our own DAL library, but are researching using a third party DAL - to relieve the maintenance overhead. I know that Microsoft and some others have written simple DALs - although Microsoft's doesn't seem to play well with Oracle databases.
Any other suggestions? Or are we stuck with what we have?
You're certainly not stucked. There's plenty of choices: Linq to SQl although this one is only for MS SQL Server, it generates classes for you and you program with linq. ADO.NET Entity Framework which is supposed to work with any database if provider is written for that database. This one is havier one, so there's some overhead but it's quite powerful. Hibernate - also quite powerful, non-MS, implemented in Java and also in .net. On the other hand, your own DAL is not always bad choice. The fact that it's your code requires maintenance but on the other hand you may optimize thing that are important to you and you alway know what's going on in it. So it really depends on your situation which way to go.
NHibernate
I know it really isn't a DAL, but it has many of the properties if not more of what you expect from a DAL.
A DAL is easy enough to write on your own.
I use one that I adapted (heavily) but is loosely based on the one in DNN.
Most of the work involved in it IMO is just adapting to your DB schema.
Stick with what you got. DAL typically work for regular "normal" architecture but fall down when you don't fit the model.
Subsonic generates a great DAL for small/medium-sized self contained projects.
Check out Microsoft's new Entity Framework. . . That is where you want to get.
We do a lot of DB centric development and only use stored procedures. We generate code from db and have our own library to support the calls.
One of the advantages is to be able to enforce good coding standards, like not having SQL in code and so on. The other main advantage is that the library is a stable repository of knowledge: we try to solve the problems once and for all instead of reinventing the wheel.
All the free libraries that are available did not cut it for us: either they were missing something, or too complete or inefficient. Please bear in mind that we develop a lot of sites which are quite similar in scope, but your situation could differ.
EDIT: Forgot to add, we modeled the API calls after Subsonic (but in an ex-novo implementation)
If you're of the .NET persuasion, you could try SubSonic. I use that at work and love it. There is also NHibernate, but I can't speak to the usage of that. There is a post here on the difference between SubSonic and NHibernate as well.
If you are going to use a DAL, you might want to use Enterprise Libraries DAL in it. It wraps a lot of the BS crap that you have to do with ADO.Net. Writing your own is fine, if it works for you. DAL's are a pain, and I haven't found a great solution yet either.
If you are going to go the ORM route, most of the hot ORMS were listed above, NHibernate, Subsonic, LLBLGen. I've also heard that Ibatis.net is good, especially if you want great control over your SQL.
You might want to try XPO from DevExpress - great DAL if you don't use stored procedures a lot.
I've been using the .NET XSD DataSet/TableAdapter combination for a while now and it has been great. Using the XSD DataSets allows me to have strongly typed access to the data, and the TableAdapters allow me to consume the stored procedures I've written in a couple of clicks and have all of the standard CRUD operations readily available.
What is really nice when using this with SQL Server is that I can simply drag and drop the existing table structure into the XSD workarea and it generates the objects for me. I still have to "configure" the TableAdapters by using the wizard, but I like that anyway since it gives me a level of verification to make sure things have been written correctly.
All in all this proves to be a great middle ground between doing the ADHOC SQL on the fly (with parameterized queries 8^D) and having to do all of these column data conversions and going with a full fledged 3rd party library that may need additional configuration or another DLL to make sure is ported out to the solution.
I would recommend checking out LINQ, it does everything I could hope for in a DAL in Visual Studio 2008 right off the shelf.
LLBLGenPro - been using it for a few months now, and it is brilliant, I am an extremely happy customer.
It is not only an awesome code generator, but the out of the box architecture (actually, two architectures, each available in c# or vb) that comes with it is incredibly powerful; I suspect it will do anything you want it to do out of the box (it is many years under development, and someone else will likely have already requested the feature be added in the past)....and you can always fall back on writing your own stored procs whenever you want, and it exposes them in a consistent manner for you.
Point it at your database, generate your code, and you are cranking out services and screens in literally minutes.
Some slides you can look at here for a quick overview: http://www.llblgen.com/pages/convince.aspx
Oh yeah, support is excellent as well. And documentation out the yin yang. Its not free, or open source (actually, it is mostly open source, in that you have full access to the underlying source code in the runtime libraries.....so if you are wondering why something is behaving the way it is, you are fully able to get down into the code and see how its implemented, and if you find a legitimate issue (rarely, Frans will include the fix in the next build). I think it is about $250 or so, but worth every dollar.
In the past I have looked into many other products (after of course making a half assed attempt at writing my own), and while some are "good", I found most were hard to figure out (or even install) or tedious to configure. I've found this tool just worked right from the start, and when you start to want to do more complicated things, you can get into that gradually, rather than having to do tons of reading before you can do anything at all. Most of what you want to do will be ready 30 minutes after you start.
And the developer, Frans Bouma is a smart dude. Read his multi-post blog series on implementing LINQ within LLBLGen here: http://weblogs.asp.net/fbouma/archive/2007/09/11/developing-linq-to-llblgen-pro-day-0.aspx
Or, try totally understanding this article. (If you can, you're also a very smart person): http://weblogs.asp.net/fbouma/archive/2008/06/17/linq-to-llblgen-pro-feature-highlights-part-1.aspx
How about EntitySpaces.net I still like LLBLGen but EntitySpaces.net is easier to setup and supports VistaDB and a few other db's that LLBLGen does not support.
EntitySpaces now runs under Visual Studio, you can grab the ES2009 trial here without even registering ==> HERE
Try DataObjects .NET 4.0. It's fast, easy to use and powerful framework. Uses "Code-First" approach and mapping through attributes. Rather comprehensive support of LINQ.
It has a lot of samples and online documentation. Cuyrrently it has providers for MS SqlServer 2005+, PostgreSql 8.1+. Provider for Oracle will be completed in August.