DataSets work

I had not intention of going here, with all of the wonderful things that are going on in the world of Microsoft, but I have to get a project done. I have treid SubSonic and the ADO.NET Entity Framework and I am finding everything I am trying lacking. Let me roll back a minute so you get a better picture.
What I am attempting to do is set up a project that can either talk to a local database or to a database on another server, via a web service. Currently, I am looking at SOAP as my protocol, as we have some vendor solutions in Java. I am not necessarily restricting myself to ASMX, however (ie, WCF is an option).


My first attempt at a solution was SubSonic. I like the fact it can generate my objects for me. I did not like the fact that the only way for me to work in a web service was to own the source for SubSonic as well as my own solution. This means I am back to the drawing board and SubSonic is out. I cannot see adding the complexity necessary to kludge up SubSonic to make it work.
There was one other issue with SubSonic, btw. It does not play well when you attempt to serialize and there are null fields in your data objects. This may not be true with ActiveRecord, but it is certainly an issue with the RepositoryRecord. If I cannot use a web service, I cannot use SubSonic. End of story.

ADO.NET Entity Framework

I was thrilled, this week, to find that .NET 3.5 SP1 was released. I ran the Entity Framework through its paces and thought I would head in that direction. I wired up my data model for a single form (create the recipe and then you can duplicate it through as many forms as you need) and everything seemed to be working fine. Then I got to the save portion of the form and hit a wall. This is not an insurmountable wall, by any means, but here are the steps necessary to get my code working.
1. Create a SIM object
2. Populate the fields that do not reference another table (entity)
So far so good, but watch what you have to do with each field that references another table in the database via a foreign key.
1. Create an EntityKeyMember for the foreign key field
2. Populate the EntityKeyMember’s key and value field (key field always the same in these instances, as there is only one field for hte join)
3. Create an EntityKey
4. Add the EntityKeyMember to the first member of the EntityKey’s EntityKeyValues array
5. Create a Entity reference
6. Add the EntityKey to the EntityKey property of the Entity Reference
7. Add the Entity Reference to the SIM object
I have not delved all the way through this, or run the code, so the following code is to reference the relative complexity of the two solutions. Here is what I would have to do with a strongly typed dataset:
SimDS.Sim.MobileCountryId = 1;
Here is what I have to do to reference via the Entity model version
EntityKeyMember member = new EntityKeyMember();
member.Key =
member.Value = 1;

EntityKey key = new EntityKey();
key.EntityKeyValues[0] = member;

EntityReferenceOfMobileCountry er = new EntityReferenceOfMobileCountry();
er.EntityKey = key;

sim.MobileCountryReference = er;

Not extremely complex, but way overkill for the Entity Model I was attempting to use. Now, there is a solution to this. I can opt to create mutliple entity models for my solution rather than use a single entity model for the entire database. In other words, I create entity models for the different bits of functionality. Ah, but this has a caveat. I have to create the entities in different namespaces, lest I clash when I have to use a single entity in more than one set of functionality. Plus, this option only buys me a little, as I still have to create EntityKeys for the entities in the solution.
Now, I can still go with this. I only end up adding 16 lines for this particular table. It will end up biting me down the line, however, as I end up with 8 lines of code where I should only have to have one.
NOTE: I have abandoned this direction, so I will have to run tests on the solution later to determine what happens when you fire off an update with code like this. I assume it will work fine, but I really do not want to have to code this much scaffolding.


My solution is to return to the "age old" DataSet model. I am not sure I would do this for an application that needs to scale, but if this particular application scales to any size of merit, we have a success problem, which means plenty of money to solve it. In trade for scale that will probably never be needed, I a very maintainable code base that easily allows all of the normal CRUD.

I will spend more time in the ADO.NET Entity Framework later. I think it is a great model and will work on many applications. It is extremely flexible, which is why there is a bit of added complexity (having to create an entity key and tell it the field value every time, for example), but this will work well for those wishing to opt for this approach. The ability to autogenerate your business entities is definitely worth a lot. I am sure I will also play a bit more with SubSonic before I am through with my career. I like what I see thus far. I will not be using iether for THIS project, however.

The funny thing is I had this conversation with Billy Hollis many years ago. He and Rocky (Lhotka) had gotten into a disucssion about DataSets versus objects. Rocky is a big proponent of objects on the business layer, while Billy is fine with DataSets. My take was both can be used effectively, depending on the particulars of the project. I am not sure I would use a DataSet to manipulate heavily, but it is a great option when you are transporting between services in a SOA set of applications. In fact, one of the most interesting architectures I have seen wrapped the DataSet row into a business object. Very clever. I am nopt sure how it would scale, but it allowed one to code up his DAL with drag and drop (note that I am not an advocate of coding by numbers, just that I thought this was clever).

I am still not completely sold on the TableAdapter model for .NET 2.0+ DataSets, but tests we did when 2.0 came out showed that the performance was quite a bit better than we imagined it would be when we first heard about TableAdapters. If you have objects that mirror your database schema (ActiveRecord type of objects), then the TableAdapter works fine.

What’s the Beef

It’s not so much a beef as an observation. As we try to be everything to everybody, we end up with a lot of complexity. The EntityKey, for example, is necessary to cover table joins on multiple fields. The reality, however, is most of us have single field joins. THere are a variety of reasons for this:

  • It is simpler, so junior devs adopt a single key per table and per join strategy
  • We can easily add uniques to multiple fields that require one
  • It is more sensible to use integers for joins, even when there is a human identifiable unique "key"
  • It scales better (primarliy due to the point above)

So, we have a model that makes it more complex to join on a single key because there are people out there that join on multiple key fields. I am not berating Microsoft for this choice, as there are plenty of people who do have multi-field key relationships and joins. In fact, they are quite common in systems that have migrated from mainframe and midframe systems. It just makes my life a bit more complex. When I get back to this, I will find a way to genericize the single key, however, to make my life simpler. 🙂

As we go forward, I think we may eventually see a return to custom DALs. I am not sure I like that idea, but I see the increased complexity (for flexibility) pushing some that way. I also see the fact that it is getting harder and harder to understand what is going on underneath the hood pushing people that way. I hope i am wrong, as coming in as a consult to fix a custom DAL is a pain in the rear end.

Peace and Grace,


One Response to DataSets work

  1. Kelly says:

    Excellent posting……..just installed it myself and about to play with it tonight.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: