F# and Units of Measure

On Channel 9 they have an interview with Andrew Kennedy about the new Unit of Measure feature in F#.

This is a really cool feature and I have plans to use it, however there is one problem I have with it: The UOM information is baked into the compiler and can't be persisted.

An example that Andrew uses it the NASA's Mars Climate Orbiter that crashed due to an issue with Units of Measure (long story short the program thought it was using Newtons when it was actually using Pounds Force). This is a good example of how when you are doing calculations it would be nice if something other than the brain of the programmer was checking the units. But there is another time that UOM is important: When inputting the data.

I recently worked on an application where the units for a given value could be in English or SI (metric) units and we had an elaborate system to deal with them. The end user could even come up with their own unit system if they wanted to and the application had to be able to handle that.

Internally and for storage we degreed that all units would be in metric, no If's And's or But's about it. It was only when we displayed the values to the end user did we convert it to their chosen unit system. And for importing data we required that the imported values have their UOM indicated.

The F# UOM feature is a truly remarkable step in solving one of my pet issues. The next step will have to include a way to persist the UOM information with the data to any data store because without this there is no way to guarantee that the data values coming in are really what we say they are.

Found the following in the article ASP.NET MVC: Using RESTful Architecture and thought it worth noting. It describes what the MVC command actions should be, no more than this is needed:

The endpoint needs to be something meaningful, and Rails uses a nice convention that divides the endpoints into 7 main bits:

• Index - the main “landing” page. This is also the default endpoint.
• List - a list of whatever “thing” you’re showing them - like a list of Products.
• Show - a particular item of whatever “thing” you’re showing them (like a Product)
• Edit - an edit page for the “thing”
• New - a create page for the “thing”
• Create - creates a new “thing” (and saves it if you’re using a DB)
• Update - updates the “thing”
• Delete - deletes the “thing”

Normally the last 3 are “action only ” and don’t have a view associated with them. So if you “create” a Product (from the New view, using Create as the action on the form), you’d just redirect then to the List or Edit views. Likewise if you Update a Product from the Edit page (using Update as the action on the form) you might want to go back to the Edit view and show a status update.

I don't agree that this should be the ONLY command results you can have but I think it is a good guideline to follow. Now I have to go update my MVC commands.

Json for jqGrid from ASP.Net MVC

jqGrid takes a specific format for its json (taken from jqGrid documentation):

{
 total: "xxx",page: "yyy", records: "zzz",
 rows : [
   {id:"1", cell:["cell11", "cell12", "cell13"]},
   {id:"2", cell:["cell21", "cell22", "cell23"]},
    ...
 ]}

The tags mean the following:

total - Total number of Pages.
page - Current page Index.
records - Total number of records in the rows group.
rows - An array with the data plus an identifier.
id - The unique row identifier, needs to be an int from what I have found.
cell - An array of the data for the grid.

The ASP.Net MVC framework has the JsonResult response type which we can use to populate the jqGrid. As an example I created a Person model and a method to return some data:

public class Person

{

    public int ID { get; set; }

    public string Name { get; set; }

    public DateTime Birthday { get; set; }

}

public IEnumerable<Person> GetABunchOfPeople()

{

    yield return new Person {ID = 1, Name = "Darren", Birthday = new DateTime(1970, 9, 13)};

    yield return new Person {ID = 2, Name = "Dawn", Birthday = new DateTime(1971, 6, 1)};

    yield return new Person {ID = 3, Name = "Thomas", Birthday = new DateTime(1995, 10, 3)};

    yield return new Person {ID = 4, Name = "Zoey", Birthday = new DateTime(1997, 8, 15)};

}

Generating the JSON is as follows, this going into a PersonModel class:

public JsonResult GetABunchOfPeopleAsJson()

{

    var rows = (GetABunchOfPeople()

        .Select(c => new

                         {

                             id = c.ID,

                             cell = new[]

                                        {

                                            c.ID.ToString(),

                                            c.Name,

                                            c.Birthday.ToShortDateString()

                                        }

                         })).ToArray();

    return new JsonResult

               {

                   Data = new

                              {

                                  page = 1,

                                  records = rows.Length,

                                  rows,

                                  total = 1

                              }

               };

}

The controller then would look like:

public class PersonController : Controller

{

    public ActionResult Index()

    {

        return View();

    }

    public JsonResult GetAllPeople()

    {

        var model = new Models.PersonModel();

        return model.GetABunchOfPeopleAsJson();

    }

}

The view doesn't need to inherit any model other than View page if all you want is for the jqGrid to show the data. In the view, Index.aspx in this case, you add table and div for the jqGrid:


<table id="dictionary" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager" class="scroll" style="text-align: center;"></div>

Configuring jqGrid is:

$(document).ready(function() {

$("#dictionary").jqGrid({
caption: "Tank Dictionary",
pager: $("#pager"),
url: '<%= ResolveUrl("~/Person/GetAllPeople") %>',
editurl: '<%= ResolveUrl("~/Person/Edit") %>',
datatype: 'json',
myType: 'GET',
colNames: ['ID', 'Name', 'Birthday'],
colModel: [
{ name: 'ID', index: 'ID', width: 150, resizable: true, editable: false },
{ name: 'Name', index: 'Name', width: 200, resizable: true, editable: true },
{ name: 'Birthday', index: 'Birthday', width: 300, resizable: true, editable: true }
],
sortname: 'ID',
sortorder: 'desc',
viewrecords: true,
height: '100%',
imgpath: '<%= ResolveUrl("~/Scripts/jquery/jqGrid/themes/basic/images") %>'//,
});

The url tag is set to call the Person controller to get the JSON results. Note the datatype is json.

The editurl tag will be talked about later.

LINQ to SQL Roundtrips: SQL Trace

I was looking into reducing the number of database round trips that LINQ to SQL took and found an article by David Hayden that fit the bill. I wanted to see what was actually happening so I slapped together a simple demo.

Using the Pubs database I created a console app, added the LINQ to SQL classes then created a simple repository class:

public class AuthorRepository

{

    public author GetAuthorWithTitles(string authorId)

    {

        var db = new PubsDataClassesDataContext();

        return db.authors.FirstOrDefault(a => a.au_id == authorId);

    }

    public author GetAuthorWithTitlesWithUsing(string authorId)

    {

        using (var db = new PubsDataClassesDataContext())

            return db.authors.FirstOrDefault(a => a.au_id == authorId);

    }

    public author GetAuthorWithTitlesPrefecth(string authorId)

    {

        using (var db = new PubsDataClassesDataContext())

        {

            var options = new DataLoadOptions();

            options.LoadWith<author>(a => a.titleauthors);

            options.LoadWith<titleauthor>(ta => ta.title);

            db.LoadOptions = options;

            return db.authors.FirstOrDefault(a => a.au_id == authorId);

        }

    }

}

I created a simple method to dump the author and titles:

class Program

{

    static void Main(string[] args)

    {

        var repo = new AuthorRepository();

        DumpAuthorToConsole(

            repo.GetAuthorWithTitles("998-72-3567")

            , "Authors without prefetch and without using statement");

        DumpAuthorToConsole(

            repo.GetAuthorWithTitlesWithUsing("998-72-3567")

            , "Authors without prefetch and but with using statement");

        DumpAuthorToConsole(

            repo.GetAuthorWithTitlesPrefecth("998-72-3567")

            , "Authors with prefetch and with using statement");

    }

    private static void DumpAuthorToConsole(author author, string message)

    {

        Console.WriteLine();

        Console.WriteLine(new string('-', 50));

        Console.WriteLine(message);

        try

        {

            Console.WriteLine("Author Name: {0} {1}", author.au_fname, author.au_lname);

            Console.WriteLine("Count of Titles: {0}", author.titleauthors.Count);

            foreach (var titleauthor in author.titleauthors)

                Console.WriteLine("\tBook Title: {0}", titleauthor.title.title1);

        }

        catch (Exception e)

        {

            Console.WriteLine(">>> FAIL! <<<");

            Console.WriteLine(e.Message);

        }

    }

}

I did cheat and added some titles to the chosen author just so I could have at least five titles returned. The results are:

--------------------------------------------------
Authors without prefetch and without using statement
Author Name: Albert Ringer
Count of Titles: 5
Book Title: Silicon Valley Gastronomic Treats
Book Title: Secrets of Silicon Valley
Book Title: Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Book Title: Is Anger the Enemy?
Book Title: Life Without Fear

--------------------------------------------------
Authors without prefetch and but with using statement
Author Name: Albert Ringer
>>> FAIL! <<<
Cannot access a disposed object.
Object name: 'DataContext accessed after Dispose.'.

--------------------------------------------------
Authors with prefetch and with using statement
Author Name: Albert Ringer
Count of Titles: 5
Book Title: Silicon Valley Gastronomic Treats
Book Title: Secrets of Silicon Valley
Book Title: Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Book Title: Is Anger the Enemy?
Book Title: Life Without Fear
Press any key to continue . . .

Notice that there are three cases above, with one failing.

What are we looking at?

Case 1: Authors without prefetch and without using statement

This is the GetAuthorsWithTitles() method, it simple creates the DataContext and returns the author object. Simple, clean and easy, but not very effecient.

First note that the DataContext was not disposed so it is still lingering out there, waiting to be garbage collected. The SQL trace looks like this:




The first sp_executesql loads the author, the second loads all the titleauthor rows for the author and the rest select each individual title from the titles table. So for one author, a count of his titles and a list of each title requiers seven round trips to the database. Notice that each time a round trip is made a connection has to be created.

Case 2: Authors without prefetch and but with using statement

This is the GetAuthorsWithTitleWithUsing() method, which is the same as before in that it simple creates a DataContext and returns the author but then properly disposes of the context. This fails to return the count of the titles and the individual titles because the lazy loading cannot happen on a disposed context so an exception is thrown in this case.

The trace only shows one sp_executesql:

Case 3: Authors with prefetch and with using statement

In this case we use the DataLoadOptions to tell LINQ to load the titleauthors with the author and to load the titles with the titleauthors. The DataContext is disposed after returning the author.




Notice that two sp_executesql's were executed and that both were on the same connection. The first sql returned the author:

exec sp_executesql N'SELECT TOP (1) [t0].[au_id], [t0].[au_lname], [t0].[au_fname], [t0].[phone], [t0].[address], [t0].[city], [t0].[state], [t0].[zip], [t0].[contract]
FROM [dbo].[authors] AS [t0]
WHERE [t0].[au_id] = @p0',N'@p0 varchar(11)',@p0='998-72-3567'

the second returned the titleauthor and titles:

exec sp_executesql N'SELECT [t0].[au_id], [t0].[title_id], [t0].[au_ord], [t0].[royaltyper], [t1].[title_id] AS [title_id2], [t1].[title] AS [title1], [t1].[type], [t1].[pub_id], [t1].[price], [t1].[advance], [t1].[royalty],
[t1].[ytd_sales], [t1].[notes], [t1].[pubdate]
FROM [dbo].[titleauthor] AS [t0]
INNER JOIN [dbo].[titles] AS [t1] ON [t1].[title_id] = [t0].[title_id]
WHERE [t0].[au_id] = @x1',N'@x1 varchar(11)',@x1='998-72-3567'

Leasons Learned

First beware of disposing the DataContext when you want to LazyLoad entities. If you are going to do this then come up with a way to properly dispose of the context.

Second round trips to the database can be reduced by using the DataLoadOptions. This is not a guaranty of better performance but it is a step in the right direction.

DataTable: Finding Differences in Column Values

I have two tables in a typed DataSet and I want to compare one column in each table to see if TableA has values that are not in TableB.

IEnumerable<string> valuesInA = typedDataSet.TableA.AsEnumerable().Select(row => row.AField);

IEnumerable<string> valuesInB = typedDataSet.TableB.AsEnumerable().Select(row => row.BField);

foreach (var notInB in valuesInA.Except(valuesInB))

    Debug.WriteLine(string.Format("Value not in TableB.BField: {0}", notInB));

This is assuming that both AField and BField are the same type.

DataSet: More reasons to not like it

So I am still living in the world of DataSets, when will I ever learn?

The task was simple, take the contents of DataSetA and merge it into DataSetB. Sure, just use:

DataSetA.Merge(DataSetB, true)

and life will be good. But wait! Why is it when I try to save the merged data from DataSetB to the database it doesn't show up?

Because the RowState of all the rows in all the tables are set to Unchanged! And a merge operation does not change the RowState. So if I want all the data from B to be saved to A I have to change all the row states of all the tables to Added. Sure, I should be able to loop thorough it all and set the row's state as such:

row.RowState = DataRowState.Added;

Not so fast grasshopper! row.RowState does not have a setter! Isn't that Asinine! You have to use:

row.SetAdded();

What ever, I say setter, you say method. Long story short I created an extension method that works.

public static DataSet MergerAllDataAsNew(this DataSet target, DataSet source)
{
foreach (DataTable table in source.Tables)
foreach (DataRow row in table.Rows)
row.SetAdded();

target.Merge(source, true);

return target;
}

Brute Force works.

Using IDataErrorInfo for Validation

I read an article on CodeProject titled Total View Validation where the author complains that IDataErrorInfo is inadequate for WPF validation.  The assumption he makes is that all the validation code needs to go into the IDataErrorInfo.this[string] property as such:

publicstringthis[string name]

{

   get

    {

        string result = null;

        if (name == "Age")

        {

            if (this.age < 0 || this.age > 150)

            {

                result = "Age must not be less than 0 or greater than 150.";

            }

        }

        return result;

    }

}

This should not be the way that IDataErrorInfo is used as it puts business logic in the model, as the author points out.  His solution though was to create another mechanism to notifiy the user of validation errors and to not use IDataErrorInfo at all.

A solution I would propose though would be to follow how DataTables and DataTables use IDataErrorInfo by implementing methods to set and clear the objects error information:

public SomeClass() : IDataErrorInfo

    {

        publicstring Name { get; set; }

        publicstring Age { get; set; }

        #region Data Error Info

        private Dictionary<string, string> _propertyErrors;

        privatevoid InitDataErrorInfo()

        {

            var properties = this.GetType().GetProperties();

            _propertyErrors = new Dictionary<string, string>();

            // This will act as an overall error message for the entire object.

            _propertyErrors.Add(this.GetHashCode().ToString(), string.Empty);

            foreach (var propertyInfo in properties)

            {

                _propertyErrors.Add(propertyInfo.Name, string.Empty);

            }

        }

        publicvoid ClearDataErrorInfo()

        {

            foreach (var property in _propertyErrors.Keys)

            {

                _propertyErrors[property] = string.Empty;

            }

        }

        publicvoid ClearDataErrorInfo(string propertyName)

        {

            AssertThisHasPropertyWithName(propertyName);

            _propertyErrors[propertyName] = string.Empty;

        }

        publicvoid SetError(string error)

        {

            SetError(this.GetHashCode().ToString(), error);

        }

        publicvoid SetError(string propertyName, string error)

        {

            AssertThisHasPropertyWithName(propertyName);

            _propertyErrors[propertyName] = string.Format("{0}{1}{2}", _propertyErrors[propertyName]

                , Environment.NewLine,  error);

        }

        publicstringthis[string propertyName]

        {

            get

            {

                AssertThisHasPropertyWithName(propertyName);

                return _propertyErrors[propertyName];

            }

        }

        publicstring Error

        {

            get

            {

                var errors = new StringBuilder();

                foreach (var propertyError in _propertyErrors)

                {

                    if (string.IsNullOrEmpty(propertyError.Value)) continue;

                    errors.AppendLine(propertyError.Value);

                }

                return errors.ToString().Trim();

            }

        }

        protectedvoid AssertThisHasPropertyWithName(string propertyName)

        {

            if (!_propertyErrors.ContainsKey(propertyName))

            {

                thrownewArgumentException(string.Format("No property named {0} on {1}."

                    , propertyName, this.GetType().FullName));

            }

        }

        #endregion

    }

Note that there is no validation here, only reporting if the object has errors.  Using this takes advantage of the already existing validation notification built into WPF as well as WinForms.

Y2K38

Eight seconds past 3:14 a.m., on January 19, 2038, most computers in the world will think it’s actually a quarter to 9 p.m. on December 13, 1901.

Oh Crap.

UPDATE: At 11:31:30 pm UTC on Feb 13, 2009, Unix time will reach 1,234,567,890.

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;

    2 

    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:

Settings.Default["EquipmentConnectionString"]

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.

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.