Rants Tagged with “ADO.NET”
In Visual Studio 2005, when you create a Typed DataSet, it automatically creates TableAdapters for you. These are interesting objects that use a DataAdapter internally to make a more cohesive data access layer. It will certainly help the RAD developers get started. I am not so sure about how they will work long-term though.
One of the more interesting things that these new Typed DataSets do is store a link to connection information in the Typed DataSet. These seems to be used for the TableAdapters to do their open's with. Problem seems to be that if you migrate a Typed DataSet from 1.1, there is no way to insert this connection information. Even if you do, the designer in Beta 2 doesn't allow you to attach TableAdapters to your existing Typed DataSet. This means that if you want to use TableAdapters you will need to re-do your Typed DataSets entirely.
I just finished an article talking about about some of these migration issues. I'll post a link when it gets published.
Not sure how I missed this before. I was very impressed by this discussion of the issues around Typed DataSets. Yeah, sure he agrees with most of my opinions...but I like hearing that as well as dissenting opinions.
I was reading this article which is mostly about Oracle's grid database support, but what surprised me was this
link that talks about
Microsoft's partnership to include the CLR in 10g? This i've got to check out.
In previous builds, the DataSet had a property on them that said whether they should clear the DataSet whenever it is Filled by a DataAdapter. It seems to be missing in the latest builds. I actually prefer this because the nature of DataSets (and often overlooked) is that successive DataAdapter.Fill's will allow a DataSet to grow incrementally. New rows will be added, and existing rows will be updated (unless it is dirty, then you would get an exception).
Maybe my ranting to Steve Lasker paid off in a small way.
The original LadyBug on this is here if you have an opinion and want to vote to have it changed. I've reopened it to figure out what they've done with it.
UPDATE: Steve Lasker correctly pointed out that it is on the TableAdapter (d'oh), but not in the designer for some reason. I am going to update the LadyBug to complain about the designer issue.
After looking over the latest Data Access Block, i'll say it is better. But I still have my gripes:
- They are still calling PrepareCommand with an open connection. Even if you get the parameter out fo the parameter cache, the check with teh Database is still going to happen. I think a code-gen solution (a la Typed DataSets) for Command objects is a good idea (and Whidbey and I both have different ways to handle this).
- There is still no way to update multiple datatables within a single transaction.
- There are still issues with Table/Column Mappings in that they are being obfuscated from teh user therefore they'll never know when they can use them.
It is better in a number of areas though:
- Much better connection management.
- More more generalized away from SQL Server. Complete usage of IDb* interfaces helps, but real database independence is a pipe-dream IMHO.
On the whole though, I'd still recommend not using it and rolling your own DataAccess object with some of these ideas, but not most of them.
I have talked with many people over the last few years about my issues with the the Data Access Block, but it seems I've never enumerated my issues in 'print'. Here they are. If this has been fixed in the EL version the Data Access Block then yippie!
- PrepareCommand is called from many of the helper functions. PrepareCommand causes the connection to be open before the Parameters are adding to the connection. This causes each parameter to be 'checked' with the server causing many unnecessary round-trips.
- Opening connections too early and closing them too late.
- Obfuscates the way the data access works so when there is an issue, the users must dive into levels and levels of overloaded methods to find the private method that is really doing the work.
- Filling DataSets is doing TableMappings but no ColumnMappings. Hiding this detail from developers only delays the learning of this important concept.
- No support for multiple DataTable updates within a single transaction.
I seem to be getting a lot of questions and reviewing a lot of code that isn't use TableMappings and I wonder why. For example I see this occassionally:
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
// Setup Adapter here ds.Fill(ds);
ds.Tables["Table"].TableName = "Customers";
//etc.
The problem here is that you don't even need TableMappings, you could call the fill like so:
ds.Fill(ds, "Customers");
The problem really presents itself when you're returning multiple resultsets because the fill won't take multiple names. The solution is TableMappings:
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
// Setup Adapter here
// Setup a mapping for each resultset
da.TableMappings("Table", "Customers");
da.TableMappings("Table1", "Orders");
da.TableMappings("Table2", "Order Details");
da.TableMappings("Table3", "Products");
ds.Fill(ds);
Lastly, this is especially important because if you use Typed DataSets you need to fill the Tables that already exist in the Typed DataSet.
HTH
Uh oh, do you think Larry Elisson knows about this?
Check out this excellent post on logical vs. physical data tiers.
I've been spending some time lately reviewing how companies are doing data access in .NET. When I look at how most of them have crufted up solutions, I am amazed. The model that Microsoft supports seems so obvious to me, but I am neck deep in it. I'd like to hear from my readers their specific experience with creating data access in .NET; with an eye to why or why not use COM+ for transactions; Typed DataSet or DataReaders; Business Objects or Messages. I am trying to understand where the community is.
Thanks in advance...
