Data Access Layer newbie

Question

Newbie to .NET data apps here, coming from a Visual Foxpro background.

I'm planning on an ASP.NET and/or Silverlight UI, and maybe some WPF client stuff too on our LAN too, so I want to craft a data access layer that can support all these front ends.

My data will be in SQL server. I have already made a test run of pushing the data from Foxpro to SQL Server 2005. It went well. That gave me a SQL server data store to play with.

Now, here are the data tools I played with so far, trying to get familiar with .NET data access:

1. I've played with Linq-To-Sql, and made a test form using the typed objects and collections from L2S, and then populating some WPF listviews and other UI controls. That was cool. Linq is cool! WPF is cool. See screenshot of form here: http://twitpic.com/26w26/full

2. I've played with what I guess you'd call classic ADO.Net DataSets. Man, DataSets seem like a lot of work... A SQLConncetion A SQLCommand A DataSet A TableAdapter

...and worst of all, I had to type SQL code within quotes, with absolutely no help from the IDE to to keep me from making typing errors, or writing just plain wrong SQL code, and I had to know the columns names from my data table every time. Lots of room for errors! And query parameters, yuck.

So, let me ask.... does the .NET developer community really work with SQLCmds, DataSets, and DataTables to read and write data? Is that how it works?

I know all about the O/RM battles out there, and EF too.

It sure looks like you can hook up any of the UI controls from ASP.NET/Silverlight/WPF/and WinForms to either Object collections (via OR/M) or DataSets/DataTables, right? Is it always a choice between one of those two?

So, it's decision time for me, but, I don't know that to choose. They all seem to work, but that darn DataSet thing just seems scary to me, but, somehow, it also seems to be commonly used.

Answer 1

There is nothing wrong with ADO.NET "Classic" (the DataSet/DataTable API), and on at least one occasion, I decided to use it rather than an ORM. That being said, the fact that ADO.NET "Classic" is more often used is largely an artifact of history. For a long time, that was the only viable option, especially if you wanted to stick with a Microsoft-only solution.

Answer 2

People who use Datasets actually use the IDE to create strongly typed datasets.

Using this you have a visual query builder similar to SQL Management Studio's query builder. This generates function to load/save etc, with parameters.

The fact that you see datasets everywhere is largely due to the fact that it was the only solution available up to now, short of hand coding SQL everywhere.

ORMs are all the rage these days, and they do provide a really nice abstraction layer.

Answer 3

I have used the classic old ADO.NET for many years, and you just get used to it. For a larger application, you can invest some time in creating a data layer once, then re-use it with many other business objects.

A couple of other options:

1. Strongly Typed datasets. You can create a strongly typed dataset that will allow you to drag and drop database objects and will generate a lot of the plumbing code for you.

2 linq To Sql designer, which also allows you to drag and drop objects into a designer, and generates a dbml file that you use to connect and manipulate your data.

I have recently gotten onto the Linq bandwagon and it does simplify your life when treating data as objects.

Good luck!

Answer 4

First off, the only way to get data to SilverLight is via WCF web services. Direct database access is not allowed in Silverlight (you are running in a browser after all)

Second: (I'll take flack for this) DataSets are evil. Direct spawn of the devil. Avoid at all costs, unless someone is holding a gun to your head. Problem one is performance: there isn't any. Problem two is data consumption: 1k automatically turns into 3k. Not a big issue for a desktop application, but huge for web applications.

Third: learn what it mean to create a domain object. From there you can use EF or NHibernate. I prefer NHibernate, but there is a learning curve. Once you have a good domain object, you can then pass that to any client you listed.