Tuesday, May 13, 2008

Changing Typed DataSet Connection String

I was working on a WinForm app that connected to a MS Access database. Yeah, Access sucks but I didn't have a choice in the matter.

The app itself is used to import a bunch of CSV files into the Access database. It is more of a utility program and it has going through several variations, from being a simple hand driven command line tool to being GUI driven.

The command line version was all hand controlled. I had to go in and update configuration files to point to the CSV files and the MDB database. That got old fast so I decided to make a GUI version that would allow me to pick the MDB file and each of the CSV files to import. Picking and using the CSV files was easy, it was changing the connection string for the MDB that proved to be the hardest.

I am using strongly typed datasets in VS2005. If you have ever worked with them you find out soon that the connection string gets saved with the project in the app.config file, even if it is a seperate DAL dll project. My guess is that Microsoft assumed that if you ever had a connection to one database then your strongly typed dataset would not have to change to another database, but if you did you could always just update the configuration file.

As my drill instructor was always fond of saying: "Wrong f***ing answer!"

I wanted, in the case, to use a strongly typed dataset. I also wanted to use different Access files, and I wanted to be able to select which Access file I used while the application was running. Why is that so hard?

I searched the web and found several not so useful suggestions. It appears that there are two camps of people for this issue: Those that understood what I wanted to do and were trying to do it also, and those that didn't understand what the problem was.

Those in the latter camp always resorted to the same suggestion: Just update the configuration files to point to the new database. This doesn't work in this case because I would then have to restart the app in order for the new setting to be picked up.

The other popular option was to completely re-write the Settings.Designer.cs file so that when the DataTables called to get the connection string it would call a method you created so you could pass anything into it you wanted. The problem with this approach is that if you changed any of the other settings then your code would get over-written by Visual Studio.

The least popular option was to go to each DataTable and create partial classes that override the InitConnection() method. This royally sucks if you have lots of DataTables.

In the Settings class, all Connection String types are application level and cannot be made into User Settings. This leads to the other issue, namely all Connection String settings are read-only. But this is true only for saving the connection string, which is not something I needed to do. I just need to be able to change it.

Also the Settings class is internal and sealed, which means it cannot be accessed from outside of the current project. So my GUI project cannot directly access my DAL project and update the Settings value. To get around that limitation I just created a proxy class. The resulting class is:

    1 using DC.Catalog.DAL.Properties;
    3 namespace DC.Catalog.DAL
    4 {
    5     public static classEquipmentConnectionSettings
    6     {
    7         public static void SetEquipmentToConnectToMdb(string MdbFilename)
    8         {
    9             // This only overrides the in-memory copy of the setting.  It is not perminate.
   10             Settings.Default["EquipmentConnectionString"] = string.Format(
   11                 Settings.Default.EquipmentConnectionStringTemplate
   12                 , MdbFilename);
   13         }
   14     }
   15 }

Notice that to update the setting value I had to use:


Since the setting Settings.Default.EquipmentConnectionString is readonly I had to use the other way to access the value. The EquipmentConnectionStringTemplate is simple:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}

Every time I change the Access file I am pointing at I call the SetEquipmentToConnectToMdb() method, magic happens in the Typed DataSet and it all just works.

Saturday, February 09, 2008

DataSets - Love em Hate em

I have a love-hate relationship with DataSets.  On the one hand they are easy to use and contain lots of built in functionality.  On the other hand they are abused by almost everyone but at the same time severely under utilized, meaning that I rarely see anyone use most of the features available.

I get the feeling that the majority of developers that use DataSets have blinders on:  DataSets are only useful for reading and writing data to the database.  End of story.  If you are an ASP.NET developer you absolutely refuse to use them and use DataReaders only.  And in the last three years I have not seen too many applications that use the typed DataSets that can be created in Visual Studio 2005.

I don't like using DataSets because I prefer to use NHibernate or ActiveRecord to interact with databases.  But lately I have been having a change of heart as I have been delving more and more into using DataSets in the application I am currently working on.  There is a lot of functionality baked into DataSets that I have been overlooking and since I have been working with the strongly typed DataSets generated by VS 2005 I have to ask myself why I need to recreate it with POCO's.

The way I am looking at DataSets now is not as an in-memory relational database but as a highly versatile data structure that handles a lot of things I need it to handle:  Relationships, constraints, databinding, serializable and data error information.  Sometimes I see it as being nothing more than a collection of Hashtables, each Hashtable being another collection of Hashtables.  And the fact that there is a designer to work with it makes it better.

I worked on a home grown messaging system project, the message object orginated as an XSD that got converted to C# classes using the xsd.exe tool.  The tool didn't work to well (this was in .NET 1.1) so all the names and name spaces were so screwed that most of the classes had the name space in the class name.  Intellisense really sucked at times.  Me being who I am I started trying to figure out a simpler message object when it donned on me that a DataSet would have been a perfect fit for what they were doing.  A .NET 1.1 DataSet serialized to XML was ugly but in 2.0 it got a much needed face lift.   You should have seen them laugh at me when I suggested switching the message object out.  "You dummy, DataSets are for Databases!!  Ha Ha Ha, we're writing SOAP messages!" 

Yeah, I left that place.

When it comes to data binding DataSets can't be beat.  WinForms have been especially built for binding to DataSets.  One thing I wished that would be easy to do is to bind one DataSet to another without using  form.  Currently I have a form that has two user controls, each with its own DataSet that require data in one form to update data in the other.  I am working on a way to databind the two DataSets together, currently the form is manually updating the data in the other DataSet as it changes.  I want to pull all the code out of the forms and create a Database Synch Service.

A lot of the data validation I have seen in OPA's (other peoples apps) has been in the form.  Typcially if an error occurs a nice little pop-up shows some nasty message then either won't let the user save the data or reverts it back to a previous value.  DataSets have a way to set a Row and Column with a specific error message which can be used by the form to indicate there is an error.

As for the actual validation, I want to create a dictionary of methods  to validate the DataTables and DataColumns.  The validators would take the row, column and DataSet being validated and the validation errors will be entered using the rows SetColumnError method.

Still though, DataSets are going to become mute when Linq comes out.  Maybe.

Tuesday, February 05, 2008

Value Objects as Measurement Objects

So I was reviewing PEAA looking to see if Fowler had come up with the idea of Measurment Objects, that horse I've been kicking around lately.  I found that he has a couple of close patterns:  Value Object and Money.

Value Object:  A small simple object, like money or a date range, whose equality isn't based on identity.

Money: Represents a monetary value.

According to Fowler Value Objects are light weight and almost primative types.  They should also me immutable since they have no identity.  He also suggests that in .NET structs could be used as Value Objects.

The money pattern looks like a special case of a Measurement Object with the UnitOfMeasure being set to currency (currency being USD or Euro).

I see the usefulness of using structs but there is the boxing issue if we implement them with interfaces.

I will have to ponder on this.

Monday, February 04, 2008

Measurement Objects

In my previous post I described setting up a database to track measurements.  I ran into problems quickly with the data model and I could see that it was going to become a convoluted mess pretty quick.  So I want to take a step back and look at it from an application point of view.

What I want is a way to track an objects measurements.  I need a system that is easy to understand, any one looking at an object should readily understand what each measurement property is and what units it is in.  The system should accommodate almost any existing system of measurement but at the same time it needs to be simple.  Brain dead simple.

Measurement Object Diagram
What I am thinking is to start out with a abstract base class for our Measurement Object.  It has an ID that can be an int, string or Guid, take your pick.  The Text property is the string representation of the Value, Value being in an abstract class that extends MeasurementBase.

I think that it is important to seperate the actual Value into another class of Type T.  This allows us to create collections of MeasurementBase objects and add MeasurementBase<T> objects to it.  I am still struggling with this idea as it totally blows apart expectations on what a Value is (is it an int?  a DateTime?), but this is the reason I stuck the Text property on the MeasrumentBase.  The Text property takes care of the conversion of the string representation to the actual Value.

Already this is getting complicated.  What is it I'm trying to do here?

My ultimate goal is to have the means to convert a measurment from one system to another.  The objects themselves should take care of the conversion.  The way I have seen it in the past is to have conversion code scattered throughout the entire application layer, including the UI and the data access layer.

This is one of those ideas that is on the tip of my neurons, I know it has been solved before but I haven't found it.  And every time I start working on it I naturally want to drop into the Database thinking frame of mind.

I need to review some of the Enterprise Patterns, see if it already exits.

Wednesday, January 30, 2008

Databases Suck

I am sick of working with databases, they all suck.  I can generalize this to any type of data store, they all suck.

OK, they don't really suck but I sure have seen a lot of database designs that do.  I am tired of working with applications that have lots and lots of large tables, tables with lots and lots of fields.  Of course all the fields but the primary key are nullable because you might not always need that extra address field but it is there, just in case.

And don't get me started on compound primary keys.  If I see tables with compound primary keys then I am trying to get off of that project.

What if we have to change the database, add a field, remove a field or just plain rename a field?  Well that just broke the application, not just the code you own but the code in a module you never heard of that the reporting team is using.  Why can we not update schema's without worrying about causing the build to fail?

And what about all the boiler plate code needed to communicate with your database?  We have to invest in code generators that get it about 80% right.  Then the databinding to the forms, after all a database does no good unless you can get data into it.

So how do we solve this problem?  Get ride of the database?  Use only CSV files?  Go back to pencils and paper?  Those are options but not necessarily good ones.  We have the technology so why not use it, only use it in a different way.

What are we trying to do with the database any way?  Store values so we can retrieve them later.

What about history?  How do we know when someone changed a value?  And what was the value before?

What is a value?  If we don't know what it is we are storing then how are we going to know how to store it?

Based on the majority of the applications I have worked on values tend to be descriptions or measurements.  Descriptions are remarks, model numbers, serial numbers or some other form of text that has meaning to the object.  Measurements are attributes of an object that magnitude relative to some unit of measurement system.  Wikipeda has a better description of it than I do so check it out then come back.

Note that this is not a new idea, I read about measurement values somewhere but I can't for the life of me find who actually came up with it orginally.  And if you really want to get technical about measurement values there are scalers (magnitude only) and vectors (magnitude and direction).

So how do we handle this in databases?  First we create a measurement table like such:
Measurement Table
This table is pretty basic:  You have your primary key Id, the magnitude of the measurement is the value and the system used is determined by the UnitOfMeasurement.  The CreatedBy and CreatedOn is for audit and historical purposes.  To maintain a history in the database we will not allow deletes from this table, only inserts.  This allows us to see all the changes made to this value when they were made and by who.  The value that was added last is the current value.

Now that we have the measurement what exactly are we measuring?  We need some properties of some kind.  So we need a Property table and a link to the Measurement table.
Property Measurement
We have the Id again which becomes the foreign key in the Measurement table.  The name is what the property is called and the TypeOfMeasurement is the attribute being measure.  This could be length, mass, weight, currency, velocity, acceleration, etc.  Note that it is the UnitOfMeasurement in the Measurement table that dictates if we use feet, meters, kilograms, pounds, US Dollars, Euros, meters per second, etc.

What do these properties apply to though?  We need an object that has properties:
Object Properties

Now this is just getting crazy.  Our object can have many properties but also our properties can belong to many objects.  We will have to work that little kink out but for now I think I will stop playing with pictures.

What is it I really am trying to do?  I want to reduce the pain of making changes to the database for an application.  What does that mean?

Let us say for example we create an app that needs to track squares.  Nothing fancy, just a square.  Our first instinct is to create a table such as:

After building many forms and reports off of our square table management comes along and says we need to track cubes!  Those eF'ers!  You have to go back, change the name of the table, wait, no just leave the name the same, I'll remember what it really should be.  Add the new column Height, modify all the data access code, forms and reports to use the new value and life goes on.

Till six months later when management comes back and says that some federal requirements mandate that all changes to the cubes be tracked for three years.  So you look for your cube table which doesn't exist.  There is this square table with all the right fields, but squares don't have height, they are flat!  Screw it just add a history table, copy the rows as they are changed, create some triggers, job done.

Except management finds out that Canada has been entering values in metric but in the states it is in feet.  Plus Canada has been updating some of the US'es cubes.  Now is the time to get the resume ready.

Databases suck.  Working with databases is a pain but it really shouldn't be.  This extends beyond databases, it applies to objects as well.  How to stop the pain?  Stop adding values directly to the table.  Granted this is not a 100% solution, I mean really if I had a square object I wouldn't worry a lot about keeping track of two fields.

But if I had some complicated object that had many properties, and I was still in the development cycle where all of the properties still had not been identified, I think I would take the Measurement table approach.

Technorati Tags    

Saturday, January 05, 2008

Need Tape

Those are the words spoken to me by my son. He was playing with his GI Joe (one of the fake all plastic ones) when he accidentally pulled off its head. Oops!

This sucks because now I have to retape the head on evy time it falls off.

Of course the real GI Joe's head wouldn't have come off so easy. Growing up we used to regularly abuse them by dropping them off of roofs, blowing them up with fire works, running them over with the car, etc. and they always survived. These cheap plastic ripe offs, well they just suck.