How Do *You* Do Data Access in .NET?

My company has been using a data-access component for the last couple of years that coordinates DataAdapters for multiple tables in a DataSet.  It's worked out very well for us, so much so that we've decided to turn it into a product.  But, my point here is that I'm curious about what *your* tools of choice are for data-access in .NET.  Specifically:

1. Do you primarily use DataSets or custom entity-objects, and why?
2. What libraries, O/R mappers, or other tools do you use to build and maintain your data-access layer?  What convinced you to use those tools?

Let me know what you think, and thanks!

-- John B.
http://johnsbraindump.blogspot.com

Well, using a DAL to get from the database into a dataset, then an ORM to get from dataset to strong types, and my ORM's are done by hand, I'll think of using a generator later on.

We design our domain objects based on business entities, then we use nHibernate to map them to tables in the database. The objects are responsible for maintining their state.

Before nHibernate, we'ld created CRUD procs and use data readers to retrieve the objects and sql command objects to store, update and delete objects.

We try to avoid datasets whenever possible. [A]

jb43081 wrote:

We try to avoid datasets whenever possible.

OK, so we're on opposite sides of the DataSet fence, but that's fine.

We use DataSets because with our product (mentioned above), we don't need to write or maintain any data access code.  Changes to the data model involve changing the DataSet and the database, and everything in between takes care of itself.

Sure, you may take a slight performance hit for using DataSets over custom business object classes, but rapid development and ease of maintenance seemed to outweigh that hit in my mind.

Can you give a bullet point or two about why you avoid DataSets? 

And by the way, I'm a J.B. in 43081 too .  Send me an email offline, since I may know you, or someone you know.

Thanks,

John B.
http://johnsbraindump.blogspot.com

jmbledsoe wrote:

And by the way, I'm a J.B. in 43081 too .  Send me an email offline, since I may know you, or someone you know.

Do I smell a secret society?

W3bbo wrote:



jmbledsoe wrote: And by the way, I'm a J.B. in 43081 too .  Send me an email offline, since I may know you, or someone you know.

Do I smell a secret society?

Yes, James Brown started a secret club for everyone with the initial JB.

I like data objects, myself.

I like to use simple queries DataSets to initialise objects. That way you keep your Object Orientation which may make it easier to adear to business rules.

I don't really use an Object Relational (O/R) system. Although, I can see why people would, it effectively takes the burden of developing custom business classes of the system designers. You just need to make sure you get the correct object model in your database.

A quick tip, avoid running large queries from class constructors to initialise business objects. Try to impliment a .Load(SqlGuid rowID) method. Unless this really does not seem natural. Constructors should be quick and anyone programming againt your business objects won't expect them to take time to load.

As with any rule, there are always exceptions.

In WinForms, I just access a DataSet through tableadapters and bindingsources.  In ASP.NET pages, I build my datasets and access them via DataSet.Tables[].

brentnewbury wrote:



I like to use simple queries DataSets to initialise objects. That way you keep your Object Orientation which may make it easier to adear to business rules.

I don't really use an Object Relational (O/R) system. Although, I can see why people would, it effectively takes the burden of developing custom business classes of the system designers. You just need to make sure you get the correct object model in your database.

A quick tip, avoid running large queries from class constructors to initialise business objects. Try to impliment a .Load(SqlGuid rowID) method. Unless this really does not seem natural. Constructors should be quick and anyone programming againt your business objects won't expect them to take time to load.

As with any rule, there are always exceptions.

Yeah you should never put data access code in your constructor, very bad practice...Although you can initialize your business object, ie. set certain members that are able to have defaults to their defaults but other than that a static load method that returns the filled business object is what I do...Or you could go the other route i guess and keep them all seperate and use Dumb Object where all they are responsible for is being passed from your Data Access Layer where they are filled and then into your Business Layer where they are validated and then into your Presentation Layer where you could have a Model View Presenter type of thing setup so that you can load all of your data completely seperate of the UI code itself...

I have seen the last one implemented very well by a couple of fellow coders and if its done right it is very very smooth and modular.  Very nice to look at and very easy to update later on, of course I struggle with the design of it since it involves mutliple layers on multiple levels...

I use entities whenever is posible, the only exception being, when I get lists o reports because I prefer using DataTable, not DataSets. (in that escenario entities don't bring any advantage, since you are not doing any thing to the data in it just passing through to show them in your UI).

Entities are the way to go when you want to program your business logic in a clear, performant (if you don't abuse from collections) and maintenable fashion.

Aside from that I use a very very thin DAL helper class tasked to handle, both, parameter passing and to enforce the open-late/close-early connection policy without extra code in the DAL Classes.  And a generator for CRUD and entity generation based on the DB schemas.

Both, DAL Helper class and generator are homemade.


Raptor

jmbledsoe wrote:



jb43081 wrote: We try to avoid datasets whenever possible.

OK, so we're on opposite sides of the DataSet fence, but that's fine.

We use DataSets because with our product (mentioned above), we don't need to write or maintain any data access code.  Changes to the data model involve changing the DataSet and the database, and everything in between takes care of itself.

Acutally, if you use a persistance framework, such as nHibernate, you don't have to write ore maintain any dalc code either, just the XML based mapping file, which is much lighter (in memeory) than a datastore.

jmbledsoe wrote:

 Sure, you may take a slight performance hit for using DataSets over custom business object classes, but rapid development and ease of maintenance seemed to outweigh that hit in my mind.

Can you give a bullet point or two about why you avoid DataSets?

Well, for me it comes down to this:

Datasets take more system resources than using domain objects that are able to manage their own storage. 

With nHibernate you can write one save, one load and one delete method in you base class. You create a mapping for each domain and that's it, your  done! (OK, 99% of time, that's it.)

Conceptually, my thinking is that a domain object should be able to manage it's own persistance state without having to rely on an object or structure like a dataset. To me, it's just cleaner architecture.

Now, that's not to say that I never use dataset. They are hand for things like if I need to retreive my data and export it as XML. But, for just simple CRUD, I don't see the need for the added overhead.

jmbledsoe wrote:

 And by the way, I'm a J.B. in 43081 too .  Send me an email offline, since I may know you, or someone you know.

Lol! "WESTERVILLE IN DA HOUSE!"

Will do!

Just clicked, the company name in the link was familiar and it reminded on this post...

Cool Automated Command Builder for DataSet development

Which was three guys from the same company recommended their product.  I think the conclusion was that the post was spam...

I think this is just another lame promotion post.  Getting links posted so search engines bump it up.

brentnewbury wrote:



I like to use simple queries DataSets to initialise objects. That way you keep your Object Orientation which may make it easier to adear to business rules.

Acutally, I think it's the opposite. To me, a domain object that can manage it's own persistance and not rely on an "external" object or service is more encapsulated than using a dataset. There are ways to take it to the nth degree. Certinally nHibernate helps with that since you can write one save, one load and one delete in your base class and let the mapping do the rest. Or, you could probably use a strategy patter to really abstract out the data access and it doesn't have to be tightly coupled.

One thing that I guess also rubs me wrong about this apporach is that to make the data store approach as effecient as you can you really need to do a shared one for all of your objects. So, you have this large shared object with business data in it that is also in your business objects now. That just seems "wrong" to me, the data should only live in one place. I can't think of which OO rule it would technically violate (and maybe it doesn't violate any of them) but it just doesn't seem "correct" to me.

brentnewbury wrote:

I don't really use an Object Relational (O/R) system. Although, I can see why people would, it effectively takes the burden of developing custom business classes of the system designers. You just need to make sure you get the correct object model in your database.

In general, I design in this order: front end, domain layer, database layer. Doing it this way you are ensured that your object model and data model (where it makes sense) are in sync.

Just my $0.02

I used to use NHibernate for a lot of applications, but I found that the coupling is in some cases a little too tight. It has some nasty side-effects when using Webservices as a front-end for the data access layer.

Now I use a mix of custom entities and datasets, just what fits the bill best for the application I'm making. I'm still a fan of NHibernate, its a great tool for using a database in an application without writing any custom data access code.

jb43081 wrote:

Datasets take more system resources than using domain objects that are able to manage their own storage. 

I've believed this for a long time, but after reading this article, I'm not sure how true it is now that .NET 2.0 is out.  When I get a chance (Friday?), I'd like to write a similar performance test to the one supplied in the article, using custom objects and a framework like NHibernate to fill them.  I'll add an actual database call to the original test, of course, to get an accurate comparison of the whole process of moving from database to business object. 

I'll let you all know the results, (unless they show that DataSets are awfully slow )

Another_Darren wrote:

I think this is just another lame promotion post.  Getting links posted so search engines bump it up.

A reasonable thought, Darren, but I'm actually quite interested in how other developers are solving this problem.  I've been using DataSets and the DataSet Toolkit (in various forms) for a few years now, and I haven't had any need to use an O/R framework or other tool in my DAL.  But, I know that there is more than one way to do data access, and I want to understand what different developers like and why.  Personally, I want to be a more well-rounded developer.

Now if anybody is looking for a solution to this problem, I certainly won't stop them from trying out the tool I helped build  But the discussion here has been lively, and I've already learned quite a bit.