Linq2DataSet replaces the DataView

Remember the ADO.NET DataView? It is the old-fashioned way to filter an existing DataSet from the days of ADO.NET 1.0. They are useful for filtering and sorting DataSets that you already have on hand for other reasons.

Let’s say you want a list of all the Northwind employees who live in the United States sorted by LastName:

// If you use DataSets you can probably write your own EmployeeDS & SetupData():
EmployeeDS db = SetupData();

// Filter by Country = “USA”: Sort by LastName
DataView dv = new DataView(
    db.Employees,
    "Country = 'USA'",  // The Filter
    "LastName",  // Sort Order
    DataViewRowState.CurrentRows);

// the DataView consists of a collection of DataRowView, so you have to cast back
// to get to the strongly typed DataRows EmployeeDS.EmployeesRow
foreach (DataRowView dvRow in dv)
{
    EmployeeDS.EmployeesRow item = (EmployeeDS.EmployeesRow)dvRow.Row;
    Console.WriteLine(item.LastName);
}

In the code above, the filter and the sort are in strings, so the compiler can’t find errors; if the Employee table doesn’t have a “Country” field, the above code will compile but throw an EvaluateException at runtime.

In the foreach loop you need to cast the DataRowView into the strongly typed DataRow before you can access the properties. If you really don’t want to do the cast, you could use “dvRow["LastName"]” to get the last name, but that would involve another “magic string”.

Now, let’s get the same list in Linq2DataSet:

// If you use DataSets you can probably write your own EmployeeDS & SetupData():
EmployeeDS db = SetupData();

OrderedEnumerableRowCollection<EmployeeDS.EmployeesRow> linqRows = 
    from e in db.Employees
    where e.Country == "USA"
    orderby e.LastName
    select e;

// Linq2DataSet gives you a collection of strongly typed DataRows.
// You don’t have to cast to get to the LastName element here:
foreach (EmployeeDS.EmployeesRow item in linqRows)
    Console.WriteLine(item.LastName);

In this code, the only thing in a string is “USA”; the compiler will freak out if you don’t have a “Country” field. You don’t have to rely on angry user to find these bugs.

In the foreach loop you don’t have to cast, the output of the Linq query is a collection of strongly typed DataRows (however it isn’t a DataTable, but a DataRow can’t belong to more than one DataTable anyway).

If you really only want LastNames you can get only LastNames:

// If you use DataSets you can probably write your own EmployeeDS & SetupData():
EmployeeDS db = SetupData();

// Get me only LastNames as a collection of the field’s data type (in this case, string):
EnumerableRowCollection<string> LastNames = from e in db.Employees
        where e.Country == "USA"
        orderby e.LastName
        select e.LastName;

// Loop through the strings
foreach (string LastName in LastNames)
    Console.WriteLine(LastName);

Here Linq2DataSet gives us back a collection of strings because in the DataSet LastName is a string; if you ask for HireDate, you will get a collection of DateTimes.

Suppose you want a both LastName and FirstName you could do this (with Anonymous Types):

// If you use DataSets you can probably write your own EmployeeDS & SetupData():
EmployeeDS db = SetupData();

// Here we need to use “var” because the type doesn’t exist until the
// compiler creates it:
var Names = from e in db.Employees
            where e.Country == "USA"
            orderby e.LastName
            select new { e.LastName, e.FirstName };

// Here we are using the same type that the compiler created above:
foreach (var Name in Names)
    Console.WriteLine(string.Format("{0}, {1}",Name.LastName, Name.FirstName));

To make this work, C# created an anonymous type to put the results of the Linq query. Anonymous Types are strong types that are created at compile time; they just don’t have names and there are no class or struct declaration needed in the code.

(On my machine, according to the debugger, Names is a System.Data.EnumerableRowCollection<<>f__AnonymousType0<string,string>>)

More about using Anonymous Types in later posts. . .

Linq2DataSet Introduction

Lately I’ve been working for a client that recently moved an existing application to VS 2008 / .NET 3.5. The app uses some DataSets, so I’ve been playing to what I call Linq2DataSet (or Linq to DataSet).

Getting Started

To use Linq2DataSet will need to use the .NET 3.5 runtime and, in addition to the usual ADO.NET assemblies you will need to add a reference to System.Data.DataSetExtensions.dll.

Quick comparison to Linq2Sql

// Linq To Sql
int Linq2SqlGetCount()
{
     BigTableSQLDataContext data = new BigTableSQLDataContext();
     return (from x in data.Big_Tables select x).Count();
}
// Linq to DataSet
int Linq2DataSetGetCount()
{
     BigTableDS data = new BigTableDS();
     Big_TableTableAdapter da = new Big_TableTableAdapter();
     da.Fill(data.Big_Table);

     return (from x in data.Big_Table select x).Count();
}

The code isn’t that much different. There is a little more code to setup the DataSet than the SQL DataContext. No big deal.

There is a big gotcha. I ran both functions against a table with 20,000 records. The DataSet is fully loaded before anything can happen. In the code above, the Linq2Sql function runs in < 1 second and the Linq2DataSet version takes about a minute and a half.

In Linq2DataSet, all 20,000 are loaded into the DataSet before the Linq code is executed. In Linq2Sql, the engine executes the Linq and generates the SQL to get the data (probably something like SELECT COUNT(*) FROM Big_Table).

Yes, this looks bad, however if you already have the DataSet hanging around, Linq2DataSet may be a Mega-Cool way to solve your problems. For the record, I would not recommend Linq2DataSet for new “Green Field” projects, however, if you already have the DataSet lying around, Linq can make it easy to get that little bit of extra info you need. Linq2DataSets is also a way to get into Linq quickly then you can learn about Linq2Sql or the Entity Framework.

More to come…

Nullable Data Types Demo

The Problem

In several of the projects I’ve worked on, we used some token value to represent null for non-nullable types. These values would be extreme values (for int, we would use int.MinValue or int.MaxValue), or null token. Recently I’ve come across some controls that don’t like this scheme and prefer using nullable types (like int?).

The original code was written in C# 1.X, so nullable types didn’t exist; the code works, so I don’t want to convert the non-nullables to nullable and risk breaking the rest of the application.

In production, I’ve solved the problem by creating “shadow” nullable properties. The code in these properties would translate the null tokens to null. For the most part the translation code is inline.

Taking it to 11 with Generics

This weekend, I played with implementing this stuff with a generic helper class. So the code for a nullable property would look something like this:

public DateTime? DobNullable
{
     get { return NullableHelper.GetNullable<DateTime>(this.Dob); }
     set { this.Dob = NullableHelper.SetNullable<DateTime>(value); }
}

To do this I created a class called NullableHelper that contained translation functions: The to set you use this:

public static T SetNullable<T>(T? futureValue)
     where T : struct
{
     T rVal;
     if (futureValue != null)
          rVal = (T)futureValue;
     else
          rVal = NullForT<T>();
     return rVal;
}

This function echo back the value passed in UNLESS it is null; if it is null, it returns the null token. And to get, you use this:

public static T? GetNullable<T>(T presentValue)
     where T : struct, IComparable
{
     T? rVal = null;

     // I can't get the generic do == & !=, this works, so I'm 
     // going with it for now.
     IComparable ic = presentValue as IComparable;
     if (ic != null)
     {
          T CompareValue = NullForT<T>();
          if (ic.CompareTo(CompareValue) != 0)
               rVal = presentValue;
          // I already set rVal to null above.
          return rVal;
     }
     else
     {
         throw new ApplicationException(
             string.Format("NullableDataTypesDemo.NullableHelper.GetNullable<T>()" +
                  "\r\n'{0}' is can't be cast to IComparable",
                  typeof(T).ToString())
          );
     }
}

In this function I needed to cast T as IComparable to compare the value with the null token.

Both functions use a null token from the following function:

public static T NullForT<T>() 
     where T : struct
{
     T rVal;
     // Here you will need to have an if for each of the 
     // supported types, this function could be really big in production:
     if (typeof(T) == typeof(DateTime))
     {
          DateTime x = DateTime.MaxValue;
          object o = x;
          rVal = (T)o;
     }
     // Set other null tokens here
     else
     {
          throw new ApplicationException(
                 string.Format("NullableDataTypesDemo.NullableHelper.NullForT<T>()" +
                 "\r\n'{0}' is an unsupported Type", 
                 typeof(T).ToString())
          );
     }
     return rVal;
}

This function is probably the Achilles’ heel of this design. You will need an if (typeof(T) block for each type you want to support in this function. Most of the complexity points of this solution are borne by this function.

Conclusion

Over the weekend I came up with a cool way of translating from a non-nullable with null tokens to nullables. Will I implement this in production? Probably not, the code works and I don’t want to risk breaking it. If I need to do similar things in a future project, I may come back to this post.

The Whole Experiment

using System;

namespace NullableDataTypesDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create an instance of the demo entity
            DemoEntityClass x1 = new DemoEntityClass(
                1, 
                100m,
                "Jill Stephens", 
                NullableHelper.NullForT<DateTime>(), 
                NullableHelper.NullForT<int>());

            // Do some querying
            if (x1.IdNullable != null)
            {
                Console.WriteLine(string.Format("ID = {0}", x1.IdNullable));
            }
            if (x1.NumOfChildren == NullableHelper.NullForT<int>())
            {
                Console.WriteLine("Children Unknown (classic)");
            }
            // Set a DOB
            x1.DobNullable = new DateTime(1776, 7, 4);
            Console.WriteLine(string.Format("{0:d}", x1.DobNullable));
            // Set # of children and display
            x1.NumOfChildrenNullable = 2;
            Console.WriteLine("value of Number of Children: {0}", x1.NumOfChildren);
            // Unset # of children
            x1.NumOfChildren = NullableHelper.NullForT<int>();
            if (x1.NumOfChildrenNullable == null)
            {
                Console.WriteLine("Children Unknown (new)");
            }
            // Won't comile, 
            // NullableHelper.NullForT<DemoEntityClass>();
            // Some NullForT that won't compile
            // (you can fix these by adding code to NullForT())
            try
            {
                NullableHelper.NullForT<DemoStruct>();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            try
            {
                NullableHelper.NullForT<double>();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            // Force read line, so this won't go away in VS
            Console.ReadLine();
        }

    }
    /// <summary>
    /// My Demo Entity Class
    /// </summary>
    class DemoEntityClass
    {
        public DemoEntityClass(int id, decimal amount, string name, DateTime dob, 
            int numOfChildren)
        {
            this.Id = id;
            this.Amount = amount;
            this.Name = name;
            this.Dob = dob;
            this.NumOfChildren = numOfChildren;
        }
        public int Id { get; set; }
        public decimal Amount { get; set; }
        public string Name { get; set; }
        public DateTime Dob { get; set; }
        public int NumOfChildren { get; set; }

        public int? IdNullable
        {
            get { return NullableHelper.GetNullable<int>(this.Id); }
            set { this.Id = NullableHelper.SetNullable<int>(value); }
        }
        public decimal? AmountNullable
        {
            get { return NullableHelper.GetNullable<decimal>(this.Amount); }
            set { this.Amount = NullableHelper.SetNullable<decimal>(value); }
        }
        // string is nullable, so I am including this for completeness
        public string NameNullable
        {
            get { return this.Name; }
            set { this.Name = (string)value; }
        }
        public DateTime? DobNullable
        {
            get { return NullableHelper.GetNullable<DateTime>(this.Dob); }
            set { this.Dob = NullableHelper.SetNullable<DateTime>(value); }
        }
        public int? NumOfChildrenNullable
        {
            get { return NullableHelper.GetNullable<int>(this.NumOfChildren); }
            set { this.NumOfChildren = NullableHelper.SetNullable<int>(value); }
        }
    }
    /// <summary>
    /// My Demo struct
    /// </summary>
    struct DemoStruct
    {
        public int x;
        public int y;
        public int xy { get { return x * y; } }
    }
    static class NullableHelper
    {
        /// <summary>
        /// Gets the Null marker value for value types
        /// </summary>
        /// <remarks>
        /// In some old applications that date back to before nullable types
        /// maked fields as being null with extreme values (int.MaxValue for
        /// example).
        /// </remarks>
        /// <typeparam name="T">Tye value type being tested</typeparam>
        /// <returns>The Null marker value</returns>
        public static T NullForT<T>() 
            where T : struct
        {
            T rVal;
            // Here you will need to have an if for each of the 
            // supported types, this function could be really big in production:
            if (typeof(T) == typeof(int))
            {
                int x = int.MaxValue;
                object o = x;
                rVal = (T)o;
            }
            else if (typeof(T) == typeof(decimal))
            {
                decimal x = decimal.MaxValue;
                object o = x;
                rVal = (T)o;
            }
            else if (typeof(T) == typeof(string))
            {
                string x = string.Empty;
                object o = x;
                rVal = (T)o;
            }
            else if (typeof(T) == typeof(DateTime))
            {
                DateTime x = DateTime.MaxValue;
                object o = x;
                rVal = (T)o;
            }
            else
            {
                throw new ApplicationException(
                    string.Format("NullableDataTypesDemo.NullableHelper.NullForT<T>()" +
                      "\r\n'{0}' is an unsupported Type", 
                    typeof(T).ToString())
                );
            }
            return rVal;
        }
        /// <summary>
        /// Used in a set block to set a non-nullable
        /// field from a nullable value
        /// </summary>
        /// <remarks>
        /// Since the null values are gotten from NullForT, this function can be short
        /// </remarks>
        /// <see cref="NullForT"/>
        /// <typeparam name="T">Tye value type being set</typeparam>
        /// <param name="futureValue">nullable new value</param>
        /// <returns>non nullable value  with Null marker value to denote null</returns>
        public static T SetNullable<T>(T? futureValue)
            where T : struct
        {
            T rVal;
            if (futureValue != null)
                rVal = (T)futureValue;
            else
                rVal = NullForT<T>();
            return rVal;
        }
        /// <summary>
        /// Used in a get block to get a nullable field
        /// from the non-nullable value
        /// </summary>
        /// <remarks>
        /// This function is a little more complex because we need to cast
        /// presentValue to IComparable to compare it to the null token 
        /// </remarks>
        /// <see cref="NullForT"/>
        /// <typeparam name="T">Tye value type being set</typeparam>
        /// <param name="presentValue">
        /// non-nullable value with Null marker value to denote null
        /// </param>
        /// <returns>nullable value returned</returns>
        public static T? GetNullable<T>(T presentValue)
            where T : struct, IComparable
        {
            T? rVal = null;

            // I can't get the generic do == & !=, this works, so I'm 
            // going with it for now.
            IComparable ic = presentValue as IComparable;
            if (ic != null)
            {
                T CompareValue = NullForT<T>();
                if (ic.CompareTo(CompareValue) != 0)
                    rVal = presentValue;
                // I already set rVal to null above.
            }
            else
            {
               throw new ApplicationException(
                 string.Format("NullableDataTypesDemo.NullableHelper.GetNullable<T>()" +
                      "\r\n'{0}' is can't be cast to IComparable",
                 typeof(T).ToString())
               );
            }
            return rVal;
        }
    }
}

The Gu Rickrolled no one

You may have heard about Scott Guthrie’s Rick Astley related stunt at the PDC in Los Angeles last week. I don’t think it qualifies as a rickroll. As a developer who uses Microsoft technologies, I have sworn to The Gu’s super-duper deluxe uber-geekiness.

IMO, to qualify as a rickroll, you must willfully click on a link expecting something juicy and end up on suffering through “Never Gonna Give You Up”. You make a bad decision that makes you worthy of such punishment.

If you clicked on a link for Paris Hilton’s latest video (wink, wink) and instead got Rick, you have been rickrolled! If you clicked on a link marked Rick Astley’s famous video, you haven’t been rickrolled, you are getting what you expected.

At the PDC, The Gu’s victims didn’t have the free will not to click the link or take the red pill. In this situation, The Gu launched the video.

If one of The Gu’s minions changed out the “real” video with Rick Astley without his knowledge, then he is the only rickrollee.

It could be argued that the attending a Scott Guthrie presentation is bad decision that qualifies one to be worthy of being rickrolled.

Rant on XML Comments

The purpose of .NET XML comment is to help you generate outside documentation. When you tell Visual Studio to generate the XML file, you get tool tips when you hover over your method and you can use a tool like SandCastle to generate MSDN style help files based on your XML Comments. XML comments are really designed for Black Box documentation of Frameworks and API.

An XML comment is not the same as a development comment. When I am looking at a SandCastle generated help file, I care about how to use your class or method; I don’t care about why you chose to use a bubble sort over a quick sort.

I’ve seen comments like this:

///<summary>
/// DV
///</summary>

where DV is the initials of one of the developers. If you were actually use the XML comment feature, this comment would show up when you mouse over the class in Visual Studio or in the help file you created with SandCastle, same with this comment:

/// <summary>
/// This is the FooBar class!
/// I originally designed on a napkin after midnight after drinking 
/// a fifth of JD & eating 3 orders of hot wings.
/// I was inspired to write this class after Ellen, the goddess of 
/// the 327th Ave NE Pub down the street from my studio apartment,
/// who rejected me after I drank myself silly summoning up the 
/// courage  to ask her out.
/// </summary>

With the VS tooltip feature you lose all formatting, so if you have a comment like

/// <summary>
/// Foobar Class
///
/// Combines Bars with Foos and converts them from Metric to Imperial
///
/// 2005-04-13 – RN – Original implication 
/// 2005-05-03 – RF – Bug #12239 – g to # conversion issue
/// 2008-03-27 – VW – re-Implement Linq to Foo framework
/// </summary>

will appear on 1 or 2 lines without the linefeeds. The tags are ignored when VS creates tooltips.

I know that most of the projects I work on are not frameworks or API, but it still feel the need to avoid using XML comments for things other what I think they are designed for.

I am uptight about XML comments because I have used them, once. We created a help file for a client using SandCastle; the customer was impressed, though I bet they never used it.

How bad is too bad?

I have been assigned the task of updating a program that isn’t terribly well designed.

How deep do I dive in trying to fix this program? Do I take all of the inappropriate code from the Form Classes and move them into Business Classes? Do I get rid of all the SQL queries in code? Do I tear the whole thing to shreds and start over with my brilliantly planned super object oriented design applying all 23 GoF design patterns? My inner geek screams yes, Yes, YES! But according to Joel Spolsky, rewriting software just to do it can be the biggest mistake “you” can make. (http://www.joelonsoftware.com/articles/fog0000000069.html).

I mean, the program basically works. If the world around the program wasn’t changing, it could probably go unchanged. But we’re replacing one system that this program talks to and replacing it with another one.

Looking at the code, I would date the program to around the 2003 timeframe with stuff added later as the program evolved. I see:

• Int32 in place of int, String in place of string, etc. (I remember reading and hearing experts recommending that practice in the early days of .NET)


• Long unstructured functions in the form class. Sort of like VB6 in C#, we know that a lot of that went on in the early 0's.


• There is some object orientation grafted on to the edges of the program but the core is pretty old fashioned pre-OO .NET 1.0 code.


• Uses finally in places where rational modern C# coders would use using.

So it isn’t wonderful code, but, for the most part, it works. But like any spoiled brat, I just wanna do it. Why: just because.

The program was written in the ancient times of yore (5 years ago). When .NET first came out, OO was just starting to catch on in the mainstream. Right now I am hearing rumors that the next big thing is going to be Functional programming; the justification is that it is easier to parallelize so elements of the Functional Fad will probably stick around for the long term. What will my elegant OO code look like to the Functional masters of the future?

SQL Funny Business

Today at work we were talking about mixing old (table, table where join criteria) and new (table join table on join criteria) style joins in the same query. So that got me to thinking about what ON really does. I ran the following queries against the infamous Northwind Database:

-- The modern style join:
SELECT *
FROM dbo.Orders o
 JOIN [Order Details] od ON od.OrderID = o.OrderID 
WHERE o.CustomerID = 'SUPRD'

-- The classic style join:
SELECT *
FROM dbo.Orders o, [Order Details] od
WHERE od.OrderID = o.OrderID
 AND o.CustomerID = 'SUPRD'

-- Inverted modern join (join criteria in WHERE, selection criteria in ON):
SELECT *
FROM dbo.Orders o
 JOIN [Order Details] od ON o.CustomerID = 'SUPRD'
WHERE od.OrderID = o.OrderID

All three queries gave me the same results set.

Is the ON clause just a different place to shove selection criteria (a phantom where). I like the modern style. You can put all the join information together. You could mix things up when you practice Job Security Based Programming.

I think it would be fun to have a obscure SQL programming competition, like the obscure C programming competitions in the days of yore.