Thursday, December 30, 2010

The Seahawks and the Playoffs

If the Seattle Seahawks win their football game against the Rams early next year, a team with a losing record will make the NFL playoffs!

If I were the King of Football (or any other sport), I’d make a rule that a team would have to win at least as many games as they lose to make the playoffs.

If a division winner had a losing record, that playoff position would go to an additional wild card team. The New York Giants and Tampa Bay (both have 9-6 records and would lose tie breakers to Green Bay) may be home watching the Seahawks. Under my scheme New Orleans could host a game.

In College Football, a team must have an even record to qualify for a bowl game. There was a big deal around here about the Washington Huskies having to win the Apple Cup in Pullman to get to 6-6 to qualify for a bowl.

Sunday, December 19, 2010

Generating a Nullable ADO.NET DataSet Wrapper

Part 4: The Template and putting it all together

So, I have my target code, my DataSet describer and some T4 template tools (including the ITextTemplatingEngineHost class I took from MSDN), but I haven’t generated any DataSet Wrapper code! It is Christmas time, but that’s still a lame excuse.

Running the Template

In Part 3, I created a class to pass parameters to a T4 Template and an overly simple template that echoed the parameters to the generated document. Here I’m using the same basic from the Test Console Application, but I am referencing a template that will generate the DataSet Wrapper and passing the appropriate parameters to generate the wrapper I want. This code use a DataSet in an assembly.
// Set up some parameters:
var parms = new TTCommucator.GetParams("setting.txt");
parms.CurrentParameters.Add("DsSource", "Assembly");
parms.CurrentParameters.Add("AssembyPath", @"C:\GeneratorProject\MyAssembly.dll");
parms.CurrentParameters.Add("DataSetName", "MyDS");
parms.CurrentParameters.Add("FileNameSpace", "MyAssembly.DataSets");
parms.CurrentParameters.Add("DsNameSpace", "NewProject.DataSetWrappers");
parms.Save();

// Set the Template File
string templateFileName = "DataSetX.tt";

// Set up host and engine
CustomCmdLineHost host = new CustomCmdLineHost();
Engine engine = new Engine();
host.TemplateFileValue = templateFileName;

//Read the text template.
string input = File.ReadAllText(templateFileName);

//Transform the text template.
string result = engine.ProcessTemplate(input, host);
// Save logic would be here…

Notes on the Template

In the template, I am using TTCommucator.GetParams to read the parameter I’ve written in code above; same as in Part 3. I use the DataSetDesciber.ReflectDataSet.DataSetDef from Part 2 to get the tables, columns, types, etc. from the target DataSet. The generated code will be different from the target code in Part 1. For example:
  • I’m using .NET type names (like System.Int32) instead of C# type names (like int). That’s what Type.ToString() returns and I see no reason to convert it just to look pretty.
  • I’m not using Nullable’s ? operator. No good reason, it just seems more consistent with the .NET types in code (I don’t see System.Int32? in code all that often).
The purpose of the target code is to provide a starting point for generation. The code will change as a result of what you learn in writing the generation and testing. In the Template I am generating C# code, but the template code is in VB.NET; it is easier for me to keep straight which is which.

The Template

<#@ template debug="true" hostspecific="true" language="VB" #>
<#@ output extension=".cs" #>
<#@ assembly name="C:\GeneratorProject\TTCommucator.dll" #>
<#@ assembly name="C:\GeneratorProject\DataSetDesciber.dll" #>
<#@ import namespace="DataSetDesciber.ReflectDataSet" #>
<#@ import namespace="TTCommucator" #>
<# 
    ' Load the Parameter object
    Dim parms As new GetParams(Host.ResolvePath("setting.txt"))
    parms.Load()

    ' Get the namespace
    Dim FileNameSpace As String = parms.CurrentParameters.Item("FileNameSpace")
    Dim DsNameSpace As String = parms.CurrentParameters.Item("DsNameSpace")

    ' a description of the DataSet to be wrapped
    Dim refx As DataSetDef = GetDsDescripter(parms)
#>
// Generated with DataSetWrapper Version 1
using System;
using System.Collections;
using System.Collections.Generic;
using <#=DsNameSpace #>;

namespace <#=FileNameSpace #>
{
    public class <#=refx.DataSetName #>X
    {
        private <#=refx.DataSetName #> _ds;
        public <#=refx.DataSetName #>X(<#=refx.DataSetName #> ds)
        {
            _ds = ds;
        }
<#For Each table As TableDef In refx.TableDefList #>
        public <#=table.TableName#>DataTableX <#=table.TableName#>
        {
            get { return new <#=table.TableName#>DataTableX(_ds.<#=table.TableName#>); }
            //set { _ds.<#=table.TableName#> = value; }
        }
<#Next#>
        public <#=refx.DataSetName #> DataSet
        {
            get { return _ds; }
        }
    }

<#For Each table As TableDef In refx.TableDefList #>
    public class <#=table.TableName#>DataTableX: IEnumerable<<#=table.TableName#>RowX>
    {
        private <#=refx.DataSetName #>.<#=table.TableName#>DataTable _theTable;
        public <#=table.TableName#>DataTableX(<#=refx.DataSetName #>.<#=table.TableName#>DataTable table)
        {
            _theTable = table;
        }

        public <#=refx.DataSetName #>.<#=table.TableName#>DataTable Table
        {
            get { return _theTable; }
        }

        #region IEnumerable<<#=table.TableName#>RowX> Members

        public IEnumerator<<#=table.TableName#>RowX> GetEnumerator()
        {
            //return new <#=table.TableName#>RowXEnum(_rows);
            return new <#=table.TableName#>TableEnum(_theTable);
        }

        #endregion

        #region IEnumerable Members

        IEnumerator IEnumerable.GetEnumerator()
        {
            return (IEnumerator)this;
        }

        #endregion
    }

    public class <#=table.TableName#>TableEnum : IEnumerator<<#=table.TableName#>RowX>
    {
        private <#=refx.DataSetName #>.<#=table.TableName#>DataTable _collection;
        private int curIndex;
        private <#=table.TableName#>RowX curBox;

        public <#=table.TableName#>TableEnum(<#=refx.DataSetName #>.<#=table.TableName#>DataTable collection)
        {
            _collection = collection;
            curIndex = -1;
            curBox = default(<#=table.TableName#>RowX);
        }

        public bool MoveNext()
        {
            //Avoids going beyond the end of the collection.
            if (++curIndex >= _collection.Count)
                return false;
            else
                // Set current <#=table.TableName#>RowX to next item in collection.
                curBox = new <#=table.TableName#>RowX((<#=refx.DataSetName #>.<#=table.TableName#>Row)_collection.Rows[curIndex]);
            return true;
        }

        public void Reset() { curIndex = -1; }

        public void Dispose() { }

        public <#=table.TableName#>RowX Current
        {
            get { return curBox; }
        }

        object IEnumerator.Current
        {
            get { return curBox; }
        }
    }

    public class <#=table.TableName#>RowX
    {
        <#=refx.DataSetName #>.<#=table.TableName#>Row _row;
        public <#=table.TableName#>RowX(<#=refx.DataSetName #>.<#=table.TableName#>Row row)
        {
            _row = row;
        }
        /// <summary>
        /// The wrapped DataRow
        /// </summary>
        public <#=refx.DataSetName #>.<#=table.TableName#>Row Row
        {
            get { return _row; }
        }
        #region Properties
        <#For Each column As ColumnDef In table.ColumnDefList#>
            <#If column.AllowDBNull Then#>


        public <#=GetNullableType(column.DataType.ToString())#> <#=column.ColumnName#>
        {
            get
            {
                if (_row.Is<#=column.ColumnName#>Null())
                    return null;
                else
                    return _row.<#=column.ColumnName#>;
            }
            set
            {
                if (<#=GetHasValueFunction(column.DataType.ToString())#>)
                    _row.Set<#=column.ColumnName#>Null();
                else
                    _row.<#=column.ColumnName#> =  
                        <#=GetNullableValueName(column.DataType.ToString())#>;
            }
        }
        <#Else#>

        public <#=column.DataType.ToString()#> <#=column.ColumnName#>
        {
            get { return _row.<#=column.ColumnName#>; }
            set { _row.<#=column.ColumnName#> = value; }
        }
        <#End If#>
    <#Next#>

        #endregion Properties
    }
<#Next#>
}
<#+ 
    ''' <summary>
    ''' Gets a DataSetDef bases on parms passed in
    ''' </summary>
    ''' <param name="parms">
    ''' The GetParms object containing parameters needed to create DataSetDef:<br />
    ''' AssembyPath: The path of the assembly containig the DataSet<br />
    ''' DataSetName: The fully qualified name of the dataset
    ''' </param>
    ''' <returns>The DataSetDef</returns>
    Function GetDsDescripter(parms As GetParams) AS DataSetDef
        Dim AssembyPath As String = parms.CurrentParameters.Item("AssembyPath")
        Dim DataSetName As String = parms.CurrentParameters.Item("DataSetName")
        Return DataSetReflector.GetDataSetDef(AssembyPath, DataSetName)
    End Function
    ''' <summary>
    ''' Gets the nullable type name for a given type name
    ''' </summary>
    ''' <param name="typeName">
    ''' The type name as a string (as returned from Type.ToString())</param>
    ''' <returns>The Nullable type name</returns>
    ''' <remarks>
    ''' Since strings are nullable and not compatible with Generic Nullable class, 
    ''' must be treated special 
    ''' </remarks>
    Function GetNullableType(typeName As String) As String
        Dim rVal as string
        Select Case typeName
        Case "System.String"
            rVal = typeName
        Case Else
            rVal = "Nullable<" & typeName & ">"
        End Select
        Return rVal
    End Function
    ''' <summary>
    ''' Gets the string used to check nullablity in a set property method
    ''' </summary>
    ''' <param name="typeName">
    ''' The type name as a string (as returned from Type.ToString())
    ''' </param>
    ''' <returns>
    ''' string of an expression that will return true if value is null
    ''' </returns>
    ''' <remarks>
    ''' Most nullable types have a HasValue property but not all
    ''' </remarks>
    Function GetHasValueFunction(typeName As String) As String
        Dim rVal as string
        Select Case typeName
        Case "System.String"
            rVal = "System.String.IsNullOrEmpty(value)"
        Case Else
            rVal = "!value.HasValue"
        End Select
        Return rVal
    End Function
    ''' <summary>
    ''' Gets the string used on the right side of set property method
    ''' </summary>
    ''' <param name="typeName">
    ''' The type name as a string (as returned from Type.ToString())
    ''' </param>
    ''' <returns>The Value property name</returns>
    ''' <remarks>
    ''' Most nullable types have a Value property but not all
    ''' </remarks>
    Function GetNullableValueName(typeName As String) As String
        Dim rVal as string
        Select Case typeName
        Case "System.String"
            rVal = "value"
        Case Else
            rVal = "value.Value"
        End Select
        Return rVal
    End Function
#>

Generating a Nullable ADO.NET DataSet Wrapper: The Series

Thursday, December 09, 2010

Generating a Nullable ADO.NET DataSet Wrapper

Part 3: Detour hacking parameters for T4 Templates (VS 2008)

I decided to use T4 Templates instead of my home grown code generation techniques. I didn’t want to spend several pages on my way; besides, I probably need to get with the times and play with the same toys as the cool kids.

This post is to describe a punt. I couldn’t find a way to pass parameters to a VS 2008 .tt file. I know that VS 2010 has the parameter directive, but I don’t have VS 2010, so thus the hack below.

TTCommucator.GetParams

Since I can reference .NET assemblies, I am storing my parameters as a Dictionary to a file outside of the template and then reading it in from inside the template.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace TTCommucator
{
    /// <summary>
    /// Used to pass parameters to a T4 Template
    /// </summary>
    public class GetParams
    {
        private const char SEPERATOR_CHAR = '|';
        private string _fileName;
        /// <summary>
        /// The Dictionary containing the Parameters
        /// </summary>
        public Dictionary<string, string> CurrentParameters { get; set; }
        /// <summary>
        /// Creates a GetParmas with a Filename
        /// </summary>
        /// <param name="fileName">The name of the file to store the parameters</param>
        /// <remarks>Both sides of the transaction must agree on file name</remarks>
        public GetParams(string fileName)
        {
            CurrentParameters = new Dictionary<string, string>();
            this._fileName = fileName;
        }
        /// <summary>
        /// Loads the data from the file into the Dictionary CurrentParameters 
        /// </summary>
        public void Load()
        {
            CurrentParameters = new Dictionary<string, string>();
            string line;
            using (TextReader tr = new StreamReader(_fileName))
            {
                while ((line = tr.ReadLine()) != null)
                {
                    string[] parts = line.Split(new char[] { SEPERATOR_CHAR }, 2);
                    if (parts.Count() > 1)
                        CurrentParameters.Add(parts[0], parts[1]);
                }
                tr.Close();
            }
        }
        /// <summary>
        /// Saves the data in the Dictionary CurrentParameters to the file
        /// </summary>
        public void Save()
        {
            using (TextWriter tw = new StreamWriter(_fileName))
            {
                foreach (var item in CurrentParameters)
                {
                    tw.WriteLine(item.Key + SEPERATOR_CHAR + item.Value);
                }
                tw.Close();
            }
        }
    }
}

Test Template

I created a simple template to demonstrate that I can receive parameters using my GetParams class. Note that I use VB.NET for code in my templates. Since I usually generate C# code, I find it easier to read templates when the template code isn’t C#, besides “<#}#>” is downright nasty looking.

<#@ template debug="true" hostspecific="true" language="VB" #>
<#@ assembly name="C:\Projects\TTCommucator\TTCommucator.dll" #>
<#@ import namespace="TTCommucator" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".txt" #>
<# 
 ' Load the Parameters here:
 Dim parms As new GetParams(Host.ResolvePath("setting.txt"))
 parms.Load()
#>
Values in parms:
<#For Each item As KeyValuePair(Of String, String) In parms.CurrentSettings#>
    <#=item.Key#> = <#=item.Value#>
<#Next#>

Test Console Application

Here is a simple program to test with. It doesn’t do anything more than necessary. NOTE: You will need to install Visual Studio 2008 SDK v1.1 to run this code:

using System;
using System.IO;
using Microsoft.VisualStudio.TextTemplating;
using TTCommucator;

namespace TTCommunicatorConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            // Set up some parameters:
            var parms = new GetParams("setting.txt");
            parms.CurrentParameters.Add("Greeting", "Hello World!");
            parms.CurrentParameters.Add("Run Time", DateTime.Now.ToString());
            parms.Save();

            string templateFileName = "SimpleTemplate.tt";

            // Set up host and engine
            CustomCmdLineHost host = new CustomCmdLineHost();
            Engine engine = new Engine();
            host.SetTemplateFile(templateFileName);

            //Read the text template.
            string input = File.ReadAllText(templateFileName);

            //Transform the text template.
            string output = engine.ProcessTemplate(input, host);

            // Show your work
            Console.WriteLine(output);

            // Wait for me
            Console.ReadKey();
        }
    }
}

Host Class

I lifted this class from MSDN and removed the comments to save space. You can learn more about it by going here. NOTE: The MSDN code doesn't include the Serializable attribute, but I needed to add it to avoid errors. You will need to include a reference to Microsoft.VisualStudio.TextTemplating.

using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.IO;
using System.Text;
using Microsoft.VisualStudio.TextTemplating;

namespace TTCommucator
{
    [Serializable]
    public class CustomCmdLineHost : ITextTemplatingEngineHost
    {
        internal string _templateFile;
        public string TemplateFile
        {
            get { return _templateFile; }
        }
        private string _fileExtension = ".txt";
        public string FileExtension
        {
            get { return _fileExtension; }
        }
        private Encoding _fileEncoding = Encoding.UTF8;
        public Encoding FileEncoding
        {
            get { return _fileEncoding; }
        }
        private CompilerErrorCollection _errors;
        public CompilerErrorCollection Errors
        {
            get { return _errors; }
        }
        public IList StandardAssemblyReferences
        {
            get { return new string[] { typeof(System.Uri).Assembly.Location }; }
        }
        public IList StandardImports
        {
            get { return new string[] { "System" }; }
        }
        public bool LoadIncludeText(string requestFileName, out string content, 
            out string location)
        {
            content = string.Empty;
            location = string.Empty;
            if (File.Exists(requestFileName))
            {
                content = File.ReadAllText(requestFileName);
                return true;
            }
            else
                return false;
        }
        public object GetHostOption(string optionName)
        {
            object returnObject;
            switch (optionName)
            {
                case "CacheAssemblies":
                    returnObject = true;
                    break;
                default:
                    returnObject = null;
                    break;
            }
            return returnObject;
        }
        public string ResolveAssemblyReference(string assemblyReference)
        {
            if (File.Exists(assemblyReference))
                return assemblyReference;
            string candidate = Path.Combine(Path.GetDirectoryName(this.TemplateFile), 
                assemblyReference);
            if (File.Exists(candidate))
                return candidate;
            return string.Empty;
        }
        public Type ResolveDirectiveProcessor(string processorName)
        {
            if (string.Compare(processorName, "XYZ", StringComparison.OrdinalIgnoreCase) 
                == 0)
            {
            }
            throw new Exception("Directive Processor not found");
        }
        public string ResolvePath(string fileName)
        {
            if (fileName == null)
                throw new ArgumentNullException("the file name cannot be null");
            if (File.Exists(fileName))
                return fileName;
            string candidate = Path.Combine(Path.GetDirectoryName(this.TemplateFile), 
                fileName);
            if (File.Exists(candidate))
                return candidate;
            return fileName;
        }
        public string ResolveParameterValue(string directiveId, string processorName, 
            string parameterName)
        {
            if (directiveId == null)
                throw new ArgumentNullException("the directiveId cannot be null");
            if (processorName == null)
                throw new ArgumentNullException("the processorName cannot be null");
            if (parameterName == null)
                throw new ArgumentNullException("the parameterName cannot be null");
            return string.Empty;
        }
        public void SetFileExtension(string extension)
        {
            _fileExtension = extension;
        }
        public void SetTemplateFile(string templateFile)
        {
            _templateFile = templateFile;
        }
        public void SetOutputEncoding(Encoding encoding, 
            bool fromOutputDirective)
        {
            _fileEncoding = encoding;
        }
        public void LogErrors(CompilerErrorCollection errors)
        {
            _errors = errors;
        }
        public AppDomain ProvideTemplatingAppDomain(string content)
        {
            return AppDomain.CreateDomain("Generation App Domain");
        }
    }
}

Generating a Nullable ADO.NET DataSet Wrapper: The Series

Tuesday, November 30, 2010

Generating a Nullable ADO.NET DataSet Wrapper

Part 2: Describing the DataSet

The next thing I need for code generation is a reliable source of meta data describing the code I want to generate. Here I merely ask the DataSet to describe itself and store the description into a set of simplified description objects. I am moving the metadata from the DataSet into a simpler object to make it easier to deal with when I start generating code in later parts of this series.

public static DataSetDef GetDataSetDefFromDS(DataSet ds) 
{ 
    // Code for this class is in “The Describer Classes” section below 
    var dsDef = new DataSetDef(ds.DataSetName, ds.Namespace); 
    // The DataSet has a collection of DataTables 
    foreach (var rawTable in ds.Tables) 
    { 
        DataTable tab = (DataTable)rawTable; 
        // See “The Describer Classes” 
        var tabDef = new TableDef(tab.TableName); 
        // And each DataTable has a collection of DataColumns 
        foreach (var rawColumn in tab.Columns) 
        { 
            DataColumn col = (DataColumn)rawColumn; 
            // See “The Describer Classes” 
            var colDef = new ColumnDef(col.ColumnName, col.DataType, col.AllowDBNull); 
            tabDef.ColumnDefList.Add(colDef); 
        } 
        dsDef.TableDefList.Add(tabDef); 
    } 
    return dsDef; 
} 

Getting the DataSet to Interrogate

OK, I can generate a description of the data from a DataSet, but the DataSet is in a different project. I want to make a tool that I can use

XSD Schema File

When you create a typed DataSet in Visual Studio, creates a .xsd file that it uses to generate your DataSet code. I can read the schema from an XSD file into a DataSet using the ReadXmlSchema() method:

public static DataSetDef GetDataSetDefFromXsd(string XsdPath) 
{ 
    DataSet ds = new DataSet(); 
    try 
    { 
        ds.ReadXmlSchema(XsdPath); 
        // Call the method above: 
        return GetDataSetDefFromDS(ds); 
    } 
    catch // I don’t care what it is right now 
    { 
        return null; 
    } 
} 

In an Assembly

The DataSet may exist in a previously existing assembly; we may not even have access to the source code. Using reflection, we can activate the type and cast it as a DataSet:

public static DataSetDef doRefelctDataSetFromType(Type myObjectType) 
{ 
    // Create an instance of the Type: 
    object obj = Activator.CreateInstance(myObjectType); 
    // Try to cast it as a DataSet: 
    DataSet ds = obj as DataSet; 
    if (ds != null) 
    { 
        // Call the method above: 
        return GetDataSetDefFromDS(ds); 
    } 
    return null; 
} 

The Describer Classes

These are the classes to make the objects loaded in GetDataSetDefFromDS(). They represent a simplified description of the data. I only get the data that I think I need to generate the code later. If I later need more data, I can alter GetDataSetDefFromDS() and these classes add what I need.

public class DataSetDef 
{ 
    public List TableDefList { get; private set; } 
    public DataSetDef() 
    { 
        TableDefList = new List(); 
    } 
    public DataSetDef(string dataSetName, string nameSpace) : 
        this() 
    { 
        DataSetName = dataSetName; 
        NameSpace = nameSpace; 
    } 
    public string DataSetName { get; set; } 
    public string NameSpace { get; set; } 
} 
public class TableDef 
{ 
    public List ColumnDefList { get; private set; } 
    public TableDef() 
    { 
        ColumnDefList = new List(); 
    } 
    public TableDef(string tableName) : 
        this() 
    { 
        TableName = tableName; 
    } 
    public string TableName { get; set; } 
} 
public class ColumnDef 
{ 
    public ColumnDef(string columnName, Type dataType, bool allowDBNull) 
    { 
        ColumnName = columnName; 
        DataType = dataType; 
        AllowDBNull = allowDBNull; 
    } 
    public string ColumnName { get; set; } 
    public Type DataType { get; set; }  // .NET Type, not SQL Type 
    public bool AllowDBNull { get; set; } 
} 

Generating a Nullable ADO.NET DataSet Wrapper: The Series

Wednesday, November 24, 2010

Generating a Nullable ADO.NET DataSet Wrapper

Part 1: The Target Code

Classic ADO.NET is the most successful client side database technology in the history of Microsoft. Even if the Entity Framework (officially "ADO.NET Entity Framework") is as successful as Microsoft thinks it will be, ADO.NET DataSets will be around for years.

One of the biggest weaknesses of the ADO.NET DataSet is the turn of the century way it handles nullability. Since Nullable didn’t exist until .NET 2, the designers of the DataSet handled nullability with two methods and one exception. I decided to generate a nullable wrapper for ADO.NET DataSets.

Target Code

To generate code, I need sample target code, so I know where I am going

DataRow

I plan on wrapping each typed DataRow in my own proxy class:

public class EmployeesRowX
{
    // The wrapped row
    NorthwindDS.EmployeesRow _row;
    public EmployeesRowX(NorthwindDS.EmployeesRow row)
    {
        _row = row;
    }
    // Direct access to the row, if you need something I didn’t implement
    public NorthwindDS.EmployeesRow Row
    {
        get { return _row; }
    }
    // …
}

For a non-nullable field, I just pass the data back without doing anything fancy:

public string FirstName
{
    get { return _row.FirstName; }
    set { Row.FirstName = value; }
}

For a nullable field, I have to translate ADO.NET the pre-nullable logic (IsXXXNull() & SetXXXNull()) to a modern nullable:

public DateTime? BirthDate
{
    get
    {
        if (_row.IsBirthDateNull())
            return null;
        else
            return _row.BirthDate;
    }
    set
    {
        if (!value.HasValue)
            _row.SetBirthDateNull();
        else
           _row.BirthDate = value.Value;
    }
}

DataSet and DataTables

I will wrap the DataSet and DataTables in their own proxies:

public class NorthwindDSX
{
    private NorthwindDS _ds;
    public NorthwindDSX(NorthwindDS ds)
    {
        _ds = ds;
    }

    public EmployeesDataTableX Employees
    {
        get { return new EmployeesDataTableX(_ds.Employees); }
    }

    public NorthwindDS DataSet
    {
        get { return _ds; }
    }
}

public class EmployeesDataTableX: IEnumerable
{
    private NorthwindDS.EmployeesDataTable _theTable;
    public EmployeesDataTableX(NorthwindDS.EmployeesDataTable table)
    {
        _theTable = table;
    }

    public NorthwindDS.EmployeesDataTable Table
    {
        get { return _theTable; }
    }

    #region IEnumerable Members
    public IEnumerator GetEnumerator()
    {
        return new EmployeesTableEnum(_theTable);
    }
    #endregion

    #region IEnumerable Members
    IEnumerator IEnumerable.GetEnumerator()
    {
        return (IEnumerator)this;
    }
    #endregion
}

DataTable’s IEnumberator<T>

And each Table needs an Enumerator:

public class EmployeesTableEnum : IEnumerator
{
    private NorthwindDS.EmployeesDataTable _collection;
    private int curIndex;
    private EmployeesRowX curBox;

    public EmployeesTableEnum(NorthwindDS.EmployeesDataTable collection)
    {
        _collection = collection;
        curIndex = -1;
        curBox = default(EmployeesRowX);
    }

    public bool MoveNext()
    {
        //Avoids going beyond the end of the collection.
        if (++curIndex >= _collection.Count)
            return false;
        else
            // Set current EmployeesRowX to next item in collection.
            curBox = new EmployeesRowX(
                (NorthwindDS.EmployeesRow)_collection.Rows[curIndex]);
        return true;
    }

    public void Reset() { curIndex = -1; }

    void IDisposable.Dispose() { }

    public EmployeesRowX Current
    {
        get { return curBox; }
    }

    object IEnumerator.Current
    {
        get { return Current; }
    }
}

Summing it up

So now that I know where I am going, I can get started at making generating code. The code will change as I work through the process.

Since a Typed DataSet is generated, it is possible to generate a full DataSet with nullable fields; that is a job for Microsoft.

Generating a Nullable ADO.NET DataSet Wrapper: The Series

Sunday, October 24, 2010

Adding MongoDB samus / mongodb-csharp to the Linq Comparison

Last month I compared several Linq providers. Lately I’ve been playing with MongoDB. MongoDB is a Document (read NoSQL) database that runs on windows and has a .NET provider.

To do this, I am going to set up an environment with the same data as I used in the previous examples (the infamous Northwind database from SQL Server).

Create the Data

To recreate the data from Northwind, I wrote the following query to create insert statements.

SELECT 'db.Product.insert({' 
    + 'ProductID: ' + CAST(p.ProductID AS VARCHAR(10)) + ', '
    + 'ProductName: "' + p.ProductName + '", '
    + 'SupplierID: ' + CAST(p.SupplierID AS VARCHAR(10)) + ', '
    + 'CategoryID: ' + CAST(p.CategoryID AS VARCHAR(10)) + ', '
    + 'QuantityPerUnit: "' + p.QuantityPerUnit + '", '
    + 'UnitPrice: ' + CAST(p.UnitPrice AS VARCHAR(10)) + ', '
    + 'UnitsInStock: ' + CAST(p.UnitsInStock AS VARCHAR(10)) + ', '
    + 'UnitsOnOrder: ' + CAST(p.UnitsOnOrder AS VARCHAR(10)) + ', '
    + 'ReorderLevel: ' + CAST(p.ReorderLevel AS VARCHAR(10)) + ', '
    + 'Discontinued: ' + CASE WHEN p.Discontinued = 1 THEN '"true"' ELSE '"false"' END + ', '
    + 'CategoryName: "' + c.CategoryName + '"'
    + '})'
FROM Northwind.dbo.Products p
    JOIN Northwind.dbo.Categories c ON p.CategoryID = c.CategoryID
ORDER BY p.ProductID

I run the above query against a Northwind Database that I have lying around on my home computer and I pasted the result set into a Mongo Shell window.

Notice that I added CategoryName to Product. From a relational point of view, this is criminal. However, Mongo is a DOCUMENT database and repeating data isn’t quite sin; I am also leaving this in the CategoryName because repeating data is in the spirit of document databases as I understand them. (Besides the version of MongoDB.DLL I’m using doesn’t appear to support joins very well.)

The POCO Class

To run straightforward Linq queries using MongoDB.DLL, I need a POCO class to hydrate the data into. Since MongoDB is a document database, the data can be unstructured, but to work in Linq, structured data makes things easier.

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int SupplierID { get; set; }
    public int CategoryID { get; set; }
    public string QuantityPerUnit { get; set; }
    public decimal UnitPrice { get; set; }
    public int UnitsInStock { get; set; }
    public int UnitsOnOrder { get; set; }
    public int ReorderLevel { get; set; }
    public bool Discontinued { get; set; }
    public string CategoryName { get; set; }
}

The Code

Now that I have copied the data that I need to “simulate” the Northwind Product and Category data into a MongoDB collection, I can use this code to do the same thing as I did in the last post:

public static IMongoDatabase GetContext()
{
    var mongo = new Mongo();
    mongo.Connect();
    return mongo.GetDatabase("Northwind");
}

public static void DoDemo()
{
    var mdb = GetContext();
    var list = from p in mdb.GetCollection().Linq()
               where p.CategoryName == "Seafood"
               orderby p.ProductName
               select new
               {
                   p.ProductID,
                   p.ProductName,
                   p.UnitPrice,
                   p.CategoryName
               };

    Console.WriteLine("MongoDB Demo");
    foreach (var item in list)
    {
        Console.WriteLine(
            string.Format("{0}\t{1:c}\t{2}\t{3}",
                item.ProductID,
                item.UnitPrice,
                item.ProductName,
                item.CategoryName));
    }
}

GetContext() is straight forward: You new-up a Mongo, connect and get a database.

The most interesting statement is mdb.GetCollection().Linq(). The GetCollection method links the content with the name of T to an IMongoCollection of Ts. LinqExtensions.Linq() is an extension method that converts the IMongoCollection to IQueryable.

Last month I compared several Linq providers. Lately I’ve been playing with MongoDB. MongoDB is a Document (read NoSQL) database that runs on windows and has a .NET provider.

To do this, I am going to set up an environment with the same data as I used in the previous examples (the infamous Northwind database from SQL Server).

Create the Data

To recreate the data from Northwind, I wrote the following query to create insert statements.

SELECT 'db.Product.insert({' 
    + 'ProductID: ' + CAST(p.ProductID AS VARCHAR(10)) + ', '
    + 'ProductName: "' + p.ProductName + '", '
    + 'SupplierID: ' + CAST(p.SupplierID AS VARCHAR(10)) + ', '
    + 'CategoryID: ' + CAST(p.CategoryID AS VARCHAR(10)) + ', '
    + 'QuantityPerUnit: "' + p.QuantityPerUnit + '", '
    + 'UnitPrice: ' + CAST(p.UnitPrice AS VARCHAR(10)) + ', '
    + 'UnitsInStock: ' + CAST(p.UnitsInStock AS VARCHAR(10)) + ', '
    + 'UnitsOnOrder: ' + CAST(p.UnitsOnOrder AS VARCHAR(10)) + ', '
    + 'ReorderLevel: ' + CAST(p.ReorderLevel AS VARCHAR(10)) + ', '
    + 'Discontinued: ' + CASE WHEN p.Discontinued = 1 THEN '"true"' ELSE '"false"' END + ', '
    + 'CategoryName: "' + c.CategoryName + '"'
    + '})'
FROM Northwind.dbo.Products p
    JOIN Northwind.dbo.Categories c ON p.CategoryID = c.CategoryID
ORDER BY p.ProductID

I run the above query against a Northwind Database that I have lying around on my home computer and I pasted the result set into a Mongo Shell window.

Notice that I added CategoryName to Product. From a relational point of view, this is criminal. However, Mongo is a DOCUMENT database and repeating data isn’t quite sin; I am also leaving this in the CategoryName because repeating data is in the spirit of document databases as I understand them. (Besides the version of MongoDB.DLL I’m using doesn’t appear to support joins very well.)

The POCO Class

To run straightforward Linq queries using MongoDB.DLL, I need a POCO class to hydrate the data into. Since MongoDB is a document database, the data can be unstructured, but to work in Linq, structured data makes things easier.

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int SupplierID { get; set; }
    public int CategoryID { get; set; }
    public string QuantityPerUnit { get; set; }
    public decimal UnitPrice { get; set; }
    public int UnitsInStock { get; set; }
    public int UnitsOnOrder { get; set; }
    public int ReorderLevel { get; set; }
    public bool Discontinued { get; set; }
    public string CategoryName { get; set; }
}

The Code

Now that I have copied the data that I need to “simulate” the Northwind Product and Category data into a MongoDB collection, I can use this code to do the same thing as I did in the last post:

public static IMongoDatabase GetContext()
{
    var mongo = new Mongo();
    mongo.Connect();
    return mongo.GetDatabase("Northwind");
}

public static void DoDemo()
{
    var mdb = GetContext();
    var list = from p in mdb.GetCollection().Linq()
               where p.CategoryName == "Seafood"
               orderby p.ProductName
               select new
               {
                   p.ProductID,
                   p.ProductName,
                   p.UnitPrice,
                   p.CategoryName
               };

    Console.WriteLine("MongoDB Demo");
    foreach (var item in list)
    {
        Console.WriteLine(
            string.Format("{0}\t{1:c}\t{2}\t{3}",
                item.ProductID,
                item.UnitPrice,
                item.ProductName,
                item.CategoryName));
    }
}

GetContext() is straight forward: You new-up a Mongo, connect and get a database.

The most interesting statement is mdb.GetCollection().Linq(). The GetCollection method links the content with the name of T to an IMongoCollection of Ts. LinqExtensions.Linq() is an extension method that converts the IMongoCollection to IQueryable.

Thursday, September 30, 2010

Comparing different Linq Providers

I’ve been playing with Linq and different "back ends" (not using "providers" since Linq to DataSets probably doesn’t qualify).

In all three examples below, I create the automatic default model with Northwind’s Products and Categories tables. The Linq queries require a join and a filter.

Linq to DataSet

Over the past year or so, I’ve been working fairly heavily what I call Linq To DataSets, you know, Linq on top of classic ADO.NET. I have written several blog entries and made a couple of Code Camp presentations on this subject.

What makes Linq to DataSets is that it uses a disconnected data model (data is eagerly loaded).

public static NorthwindDS CreateContext()
{
    NorthwindDS ds = new NorthwindDS();
    using (var catda = new CategoriesTableAdapter())
    {
        catda.Fill(ds.Categories);
    }
    using (var proda = new ProductsTableAdapter())
    {
        proda.Fill(ds.Products);
    }
    return ds;
}

public static void DoDemo()
{
    var ds = CreateContext();
    var list = from p in ds.Products
             join c in ds.Categories on p.CategoryID equals c.CategoryID
        where c.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            c.CategoryName
        };

        Console.WriteLine("DataSet Demo");
        foreach (var item in list)
        {
            Console.WriteLine(
                string.Format("{0}\t{1:c}\t{2}\t{3}", 
                    item.ProductID, 
                    item.UnitPrice,
                    item.ProductName,
                    item.CategoryName));
        }
    }
}

This one requires real code to load. And everything is loaded. If I need to, I can filter at the DataAdapter level, but that requires fancy Designer work.

Linq to SQL

On my last project, we used Linq to SQL. It is easy to develop and capable, but limited. You will use SQL Server, You will not use Stored Procedures to read and write data (it does have some stored procedure support, but not even to the level of Classic ADO.NET).

public static NorthwindL2SDataContext CreateContext()
{
    return new NorthwindL2SDataContext();
}

public static void DoDemo()
{
    var context = CreateContext();
    var list = from p in context.Products
            join c in context.Categories on p.CategoryID equals c.CategoryID
        where c.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            c.CategoryName
        };

    Console.WriteLine("Linq2Sql Demo");
    foreach (var item in list)
    {
        Console.WriteLine(
            string.Format("{0}\t{1:c}\t{2}\t{3}",
                item.ProductID,
                item.UnitPrice,
                item.ProductName,
                item.CategoryName));
    }
}

Creating the context in Linq to Sql is trivial, just new it up. The Linq is really similar to the Linq to DataSets that I’ve been working with for the last year.

The cool thing is that the data isn’t gotten until I ask for it in the Linq query and only the records I ask for.

Linq to Sql’s model is really close to the table structure of the underlining database. It is less capable than the Entity Framework (below), but it is also less complicated.

Entity Framework

I have also played with the Entity Framework. It is more powerful than the others but more difficult to use than the others.

public static NorthwindEntities CreateContext()
{
    return new NorthwindEntities();
}

public static void DoDemo()
{
    var ds = CreateContext();
    var list = from p in ds.Products
        where p.Categories.CategoryName == "Seafood"
        orderby p.ProductName
        select new
        {
            p.ProductID,
            p.ProductName,
            p.UnitPrice,
            p.Categories.CategoryName
        };

    Console.WriteLine("EntityFramework Demo");
    foreach (var item in list)
    {
        Console.WriteLine(
            string.Format("{0}\t{1:c}\t{2}\t{3}",
                item.ProductID,
                item.UnitPrice,
                item.ProductName,
                item.CategoryName));
    }
}

Again it is simple to make a new context.

The Entity Framework supports lazy loading and using stored procedures for Insert, Update and Delete operations.

Notice that there is no join in this query, the Category is imbedded in to the Products. The Entity Framework can work with data models that differ from the underlining data store.

The same treatment is applied to Mongo DB

Friday, August 27, 2010

Quickly change Themes in Visual Studio 2008

I like dark backgrounds when I code; my co-workers don’t. I want to be able to change the screen back and forth from Darkness and Light. So I created a couple of toolbar buttons to switch themes (don’t use the tool until I say you can do so).

  1. I save my current theme (Tools => Import and Export Settings… => Export … => Next => Next => "C:\VS_Themes\Darkness.vssettings" => Finish).
  2. Create a standard theme (Tools => Import and Export Settings… => Reset all Settings).
  3. Save the standard theme (same steps as 1 but call it "C:\VS_Themes\Default.vssettings")
  4. Open Macros IDE (((Alt+F11) || (Tools => Macros => Macros IDE …))
  5. Create a module (Right Click on MyMacros => Add => Add Module => Name it "LoadThemes")
  6. Type the following VB.NET code into the module:
  7. Public Module LoadThemes 
        Public Sub SetThemeToDarkness()
            LoadThemeByFilename("C:\VS_Themes\Darkness.vssettings")
        End Sub<
        Public Sub SetThemeToDefault()
            LoadThemeByFilename("C:\VS_Themes\Default.vssettings")
        End Sub<
        Private Sub LoadThemeByFilename(ByVal fileName As String)
            DTE.ExecuteCommand("Tools.ImportandExportSettings", "/import:""" &
                fileName & """")
        End Sub
    End Module
    
  8. And I hooked them to buttons on my tookbar (Tools => Customize => New… "Theme" => Commands => Select Macros => Drag your macros onto the new Toolbar).
  9. Resave the Darkness Theme (The toolbar is part of the theme, otherwise when you use the tool bar to switch themes, you will lose the toolbar)
  10. Do the same thing for the Default Theme.

Now you are set.

Sunday, July 18, 2010

Avoid Duplicate Detail Forms in MDI programs

In a MDI Windows Forms/CRUD application, I don't want to display the same Detail form more than once at the same time. What if the user saves the address on one form and the email on another? Even if you can trust the user to do the right thing, it just feels icky to have more than 1 form for "John Smith" up at the same time.

It is less dangerous to have duplicate list forms, but I still don't like to gunk up the application with extra forms hanging around.

This code is a modernization of some code I used a couple of years ago. I am using Generics and Extension methods to reduce the amount of code I used in the old .NET 2.0 code!

using System.Windows.Forms;
namespace AvoidDupeForms
{
    /// <summary>
    /// A Detail Form is a form that can display a single record.
    /// In this example, there can me more than 1 of these loaded
    /// at once, as long as a record is only loaded once.
    /// </summary>
    public interface IDetailForm
    {
        /// <summary>
        /// Get the Id loaded in this form
        /// </summary>
        int Id { get; }
        /// <summary>
        /// Set the id for the form to load
        /// </summary>
        /// <param name="id">Id # to be loaded</param>
        void SetId(int id);
    }
    public static class FormsUtil
    {
        /// <summary>
        /// Reveal a form, load a new one if one doesn't yet exist 
        /// in the context of this MDI 
        /// </summary>
        /// <typeparam name="T">The type of the form to be revealed</typeparam>
        /// <param name="thisForm">
        /// The calling form (used to get MdiParent to be searched)
        /// </param>
        /// <example>
        /// <![CDATA[
        /// // As an extention method
        /// this.RevealForm<ListForm>();
        /// // The oldfashioned way
        /// FormsUtil.RevealForm<ListForm>(this);
        /// ]]>
        /// </example>
        public static void RevealForm<T>(this Form thisForm) 
            where T : Form, new()
        {
            Form MdiParent = thisForm.GetMdiParent();
            if (MdiParent != null)
            {
                foreach (Form frm in MdiParent.MdiChildren)
                {
                    if (frm.GetType() == typeof(T))
                    {
                        frm.BringToFront();
                        return;
                    }
                }
                T flf = new T();
                flf.MdiParent = MdiParent;
                flf.Show();
            }
        }
        /// <summary>
        /// Reveal a form with a given detailId, load a new one if 
        /// one doesn't yet exist in the context of this MDI 
        /// NOTE: > 1 instance of T can exist, but only
        /// 1 fore each IDetailForm.Id
        /// </summary>
        /// <typeparam name="T">The type of the form to be revealed</typeparam>
        /// <param name="thisForm">
        /// The calling form (used to get MdiParent to be searched)
        /// </param>
        /// <param name="detailId">The ID of the Detail record to be displayed</param>
        /// <example>
        /// <![CDATA[
        /// // As an extention method
        /// this.RevealDetailForm<DetailForm>(42);
        /// // The oldfashioned way
        /// FormsUtil.RevealDetailForm<DetailForm>(this, 42);
        /// ]]>
        /// </example>
        public static void RevealDetailForm<T>(this Form thisForm, int detailId) 
            where T : Form, IDetailForm, new()
        {
            Form MdiParent = thisForm.GetMdiParent();
            if (MdiParent != null)
            {
                foreach (Form frm in MdiParent.MdiChildren)
                {
                    if (frm.GetType() == typeof(T))
                    {
                        T saFrm = (T)frm;
                        if (saFrm.Id == detailId)
                        {
                            saFrm.BringToFront();
                            return;
                        }
                    }
                }
                T modify = new T();
                modify.MdiParent = MdiParent;
                modify.SetId(detailId);
                modify.Show();
            }
        }
        /// <summary>
        /// Gets the MdiParent for a given form
        /// (it could be self)
        /// </summary>
        /// <param name="thisForm">
        /// The calling form (used to get MdiParent to be searched)
        /// </param>
        /// <returns>
        /// The MdiParent of thisForm, or null if thisForm has no parent
        /// </returns>
        /// <example>
        /// <![CDATA[
        /// // As an extention method
        /// Form MdiParent = thisForm.getMdiContainer();
        /// // The oldfashioned way
        /// Form MdiParent = FormsUtil.getMdiContainer(thisForm);
        /// ]]>
        /// </example>
        public static Form GetMdiParent(this Form thisForm)
        {
            if (thisForm.IsMdiContainer)
                return thisForm;
            if (thisForm.IsMdiChild)
                return thisForm.MdiParent;
            return null;
        }
    }
}

Saturday, June 26, 2010

Back in Time

I took this picture this year on Indiana Street in Spokane.


 
Posted by Picasa

Saturday, June 05, 2010

Unscientific polling in the internet

I see the following tweet:

@ChrisLove: Do You Prefer a Stick Shift or Automatic? 
[Stick Shift: http://twp.li/6lbj || Automatic: http://twp.li/p8y4 ]
#twittpoll

(I really like the way he used “||” instead of “or”.) Being a stick shift enthusiast (ok weirdo), I clicked on http://twp.li/6lbj without thinking about it; when the page loaded I saw that the stick shift was beating automatic 80 to 20. This doesn’t match what I see in the world.

The problem is that people who prefer stick care deeply, but people who prefer automatic don’t feel strongly about their preference (unless they are forced to drive a stick).

This example is probably harmless. However, both the Republicans and Democrats in the US are using this kind of polling to push their point of view.

Monday, May 17, 2010

Portland Code Camp and Me

I will be presenting Introduction to Linq and Linq2DataSets an the Portland Code Camp from 9:00 to 10:15 AM on Saturday, May 22, 2010 (Session 0x01) in Room 13.

Thursday, April 08, 2010

Tuesday, March 30, 2010

My Boise Code Camp Presentation Notes

If you are looking for my Linq2DataSets presentation from Saturday's code camp, I am in the progress of getting them ready and finding a place to post item.

Monday, March 15, 2010

Group By doesn’t appear to work in Linq2DataSet

As I’m working on my presentation for Boise Code Camp, I’ve come across some issues trying to demonstrate Group By. I haven’t been able to make a Group By query work. When I run the same query using Linq2Sql, it works just fine.

For example, given the following code (in VB 10):

Dim MyList = From o In myData.Orders _
    Join od In myData.Order_Details On o.OrderID Equals od.OrderID _
    Join e In myData.Employees On o.EmployeeID Equals e.EmployeeID _
    Group By FullOrder = New With _
        { _
            .OrderId = od.OrderID, _
            .EmployeeName = (e.FirstName & " " & e.LastName), _
            .ShipCountry = o.ShipCountry, _
            .OrderDate = o.OrderDate _
        } _
    Into Amount = Sum(od.Quantity * od.UnitPrice) _
    Where FullOrder.ShipCountry = "USA" _
    Order By FullOrder.OrderId _
    Select FullOrder.OrderId, _
        FullOrder.OrderDate, _
        FullOrder.EmployeeName, _
        Amount

For Each x In MyList
    Console.WriteLine( _
        String.Format( _
           "{0}; {1:d}; {2}: {3:c}", _
           x.OrderId, _
           x.OrderDate, _
           x.EmployeeName, _
           x.Amount))
Next

When myData is a DataSet: I get:

10262; 7/22/1996; Laura Callahan: $204.00
10262; 7/22/1996; Laura Callahan: $360.00
10262; 7/22/1996; Laura Callahan: $60.80
10269; 7/31/1996; Steven Buchanan: $120.00
10269; 7/31/1996; Steven Buchanan: $556.00
10271; 8/1/1996; Michael Suyama: $48.00
10272; 8/2/1996; Michael Suyama: $388.80
10272; 8/2/1996; Michael Suyama: $400.00
10272; 8/2/1996; Michael Suyama: $667.20

There is more than 1 entry for each OrderId, the query failed to group properly. Order 10262 has 3 lines corresponding to the 3 line items of the order.

If I run the same Linq query with myData as a Linq2Sql DataContext, I get:

10262; 7/22/1996; Laura Callahan: $624.80
10269; 7/31/1996; Steven Buchanan: $676.00
10271; 8/1/1996; Michael Suyama: $48.00
10272; 8/2/1996; Michael Suyama: $1,456.00

Here everything is grouped together and Amount is totaled. Order 10262 has 1 entry as I would expect.

There may be a slightly more complex way to make this query work using the DataSet and I haven’t found it, but right now, it doesn’t look good for Linq2DataSets in this area.

Tuesday, February 23, 2010

Loading Object Graphs with Linq2DataSets

The data base likes to keep data in relational tables and the middle tier likes to present the data in object graphs where the details are stored within the patient object, so everything is close at hand. So how do you transform the data from tables to graphs? I use Linq2DataSets.

The Stored Procedure

Suppose you are asked to load an object graph with the following stored procedure (using Northwind, my favorite database of all times):

-- This procedure returns 2 results sets the first includes all of the Orders and 
-- the second all of the Order Details (regardless of order).
CREATE PROCEDURE dbo.GetAllOrders
AS
SELECT o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate, 
    o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity, 
    o.ShipRegion, o.ShipPostalCode, o.ShipCountry 
  FROM [Orders] o

SELECT od.OrderID, od.ProductID, od.UnitPrice, od.Quantity, od.Discount
  FROM [Order Details] od

In real life, I would probably have parameters to limit the result sets to the data that I really want. For the purpose of demonstration, it is helpful to process a lot of data.

Getting the data into the Object Graphs

Now, suppose you want to load this data into Object Graphs. In my example, I have an Order class that includes a list of its Order Details (a real production SP would probably include parameters to filter the data returned; you don’t want to load all the orders for a real company).

The SP returns the two lists that you will have to stitch together yourself in code:

/// <summary>
/// Loads all the orders into a list of Order objects
/// </summary>
public static List GetAllOrders(OrderDS ds)
{
    return new List(
        from orders in ds.Orders
        select new Order(
            orders.OrderID,
            orders.CustomerID,
            orders.EmployeeID,
            orders.OrderDate,
            orders.RequiredDate,
            !orders.IsShippedDateNull() ? orders.ShippedDate : (DateTime?)null,
            orders.ShipVia,
            orders.Freight,
            orders.ShipName,
            orders.ShipAddress,
            orders.ShipCity,
            !orders.IsShipRegionNull() ? orders.ShipRegion : string.Empty,
            !orders.IsShipPostalCodeNull() ? orders.ShipPostalCode : string.Empty,
            orders.ShipCountry,
            getOrderDetails(orders.OrderID, ds)));
}
/// <summary>
/// Loads the order details associated with a given order into a list of 
/// OrderDetal objects
/// </summary>
public static List getOrderDetails(int orderId, OrderDS ds)
{
    return new List(
        from orderDetails in ds.OrderDetails
        where orderDetails.OrderID == orderId
        select new OrderDetail(
            orderDetails.OrderID,
            orderDetails.ProductID,
            orderDetails.UnitPrice,
            orderDetails.Quantity,
            orderDetails.Discount));
}

From the above code, I would like to point out

  • GetAllOrders() calls getOrderDetails() from within the linq query. When you call the Orders constructor, you can call any code you would call in any other constructor.
  • Even though I’m in the middle of a Linq query in GetAllOrders(), I can create a second Linq query in getOrderDetails using the same DataSet.
  • Since we are using DataSets and other classic ADO.NET objects, we need to call IsFooNullable() to avoid an InvalidCastException.
  • When I’m passing null to the Order constructor, I need to cast it to the correct type.

Appendix

Here are the classes that I loaded the in the code above

public class Order
{
    public int OrderID { get; set; }
    public string CustomerID { get; set; }
    public int EmployeeID { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime RequiredDate { get; set; }
    public DateTime? ShippedDate { get; set; }
    public int ShipVia { get; set; }
    public decimal Freight { get; set; }
    public string ShipName { get; set; }
    public string ShipAddress { get; set; }
    public string ShipCity { get; set; }
    public string ShipRegion { get; set; }
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }
    public List Details { get; set; }

    public Order(int orderID, string customerID, int employeeID, DateTime orderDate, 
        DateTime requiredDate, DateTime? shippedDate, int shipVia, decimal freight, 
        string shipName, string shipAddress, string shipCity, string shipRegion, 
        string shipPostalCode, string shipCountry, List details)
    {
        this.OrderID = orderID;
        this.CustomerID = customerID;
        this.EmployeeID = employeeID;
        this.OrderDate = orderDate;
        this.RequiredDate = requiredDate;
        this.ShippedDate = shippedDate;
        this.ShipVia = shipVia;
        this.Freight = freight;
        this.ShipName = shipName;
        this.ShipAddress = shipAddress;
        this.ShipCity = shipCity;
        this.ShipRegion = shipRegion;
        this.ShipPostalCode = shipPostalCode;
        this.ShipCountry = shipCountry;
        this.Details = details;
    }
}
public class OrderDetail
{
    public int OrderID { get; set; }
    public int ProductID { get; set; }
    public decimal UnitPrice { get; set; }
    public int Quantity { get; set; }
    public float Discount { get; set; }

    public OrderDetail(int orderID, int productID, decimal unitPrice, int quantity, 
        float discount)
    {
        this.OrderID = orderID;
        this.ProductID = productID;
        this.UnitPrice = unitPrice;
        this.Quantity = quantity;
        this.Discount = discount;
    }
}

And GetDataSet() the function that loads the DataSet

/// <summary>
/// Loads OrderDS using the SP GetAllOrders
/// </summary>
public static OrderDS GetDataSet()
{
    var ds = new OrderDS();
    SqlConnection conn = new SqlConnection(CONNECTION_STRING);

    using (SqlCommand cmd = new SqlCommand("GetAllOrders", conn))
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        SqlDataAdapter DA = new SqlDataAdapter(cmd);
        DA.TableMappings.Add("Table", "Orders");
        DA.TableMappings.Add("Table1", "OrderDetails");

        DA.Fill(ds);
    }
    return ds;
}

Since I’m too lazy to do graphics on this blog, I will leave it to the reader to make the actual DataSet.

Sunday, February 21, 2010

Generate Stored Procedures with data from SQL Server System Tables

Here’s an experiment I wrote over the weekend to create an Insert SP for a table using SQL Server System Tables.

This code generates code for a specific code pattern and won’t necessarily work for any table that is thrown at it. Supporting every possible situation can make the code really complex. If I am faced with a table that this can’t handle, I am face with the choice of expanding the program to handle the new situation or write the code by hand.

These SPs are the same except for:

  • The SP name – Based on Table Name
  • The Table Name
  • The Fields
  • Which Field is the Primary Key
  • Which Fields are Nullable

From the System Tables I use:

  • sysObjects.name: the name of the table
  • sysColumns.name: the column name
  • sysTypes.name: the type name
  • sysColumns.length: the length of the field
  • sysColumns.isnullable: is the field nullable?
  • sysColumns.status: used to determine if this is the primary key/auto-number column (look for 0x80)

The Code

using System;
using System.Data.SqlClient;
using System.Text;

namespace GenerateSP
{
    class Program
    {
        /// <summary>
        /// The Connection String
        /// </summary>
        private const string CONNECTION_STRING = @"Data Source=localhost\SQLEXPRESS;" + 
             "Initial Catalog=Northwind;Integrated Security=True";
        /// <summary>
        /// Number of list items per line in the generated procedure
        /// </summary>
        private const int ITEMS_PER_LINE = 5;
        /// <summary>
        /// Shows the Code Generation in action
        /// </summary>
        static void Main(string[] args)
        {
            Console.WriteLine(CreateInsertSp("Employees"));
            Console.ReadKey();
        }
        /// <summary>
        /// Creates an INSERT Stored Procedure for a given table as a string
        /// </summary>
        public static string CreateInsertSp(string tableName)
        {
            string identityField = string.Empty;
            // There are 3 places in the SP that refer to the field names
            // so I am using 3 string builders to generate those parts
            // of the SP;
            StringBuilder parametersSb = new StringBuilder();
            StringBuilder InsertValueListSb = new StringBuilder();
            StringBuilder InsertFieldListSb = new StringBuilder();
            using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
            {
                int lineNumber = 0; // Used to 
                SqlDataReader reader = GetTableDefReader(tableName, conn);
                while (reader.Read())
                {
                    string colName = reader.GetString(0);
                    string typeName = reader.GetString(1);
                    int length = reader.GetInt32(2);
                    int isNullable = reader.GetInt32(3);
                    int status = reader.GetInt32(4);

                    AddToParameterSb(parametersSb, colName, typeName, length, 

                        isNullable, status);
                    if (status == 0x80)
                        identityField = colName;
                    else
                    {
                        lineNumber++;
                        AddInsertFieldListToSb(InsertFieldListSb, colName, lineNumber);
                        AddInsertValueListToSb(InsertValueListSb, colName, lineNumber);
                    }
                }
            }
            // Put everything together
            return
                string.Format("\nCREATE PROCEDURE [dbo].[Insert{0}]\n", tableName) +
                parametersSb.ToString() +
                string.Format("\nAS\n\nINSERT INTO [{0}] (", tableName) +
                InsertFieldListSb.ToString() +
                string.Format(")\nVALUES (") +
                InsertValueListSb.ToString() +
                string.Format(")\n\nSET @{0} = @@IDENTITY\n\nGO\n", identityField);
        }
        /// <summary>
        /// Gets a DataReader containing selected column information for all of the 
        /// columns in the indecated table from SQL Server System Tables 
        /// </summary>

        private static SqlDataReader GetTableDefReader(string tableName, 

            SqlConnection conn)
        {
            SqlDataReader reader;
            string getDataForTable =
                "SELECT c.name AS col_name, " +
                "   t.name AS type_name, " +
                "   CAST(c.length AS INT) AS length, " +
                "   c.isnullable, " +
                "   CAST(c.status AS INT) AS status " +
                "FROM " +
                "  sysObjects o " +
                "  JOIN sysColumns c ON c.id = o.id " +
                "  JOIN systypes t on c.xtype = t.xtype AND t.status = 0 " +
                "WHERE o.name = @tableName " +
                "ORDER BY c.colid ";
            using (SqlCommand cmd = new SqlCommand(getDataForTable, conn))
            {
                cmd.Parameters.AddWithValue("@tableName", tableName);
                conn.Open();
                reader = cmd.ExecuteReader();
            }
            return reader;
        }
        /// <summary>
        /// Adds the current field to the 
        /// </summary>
        private static void AddToParameterSb(StringBuilder sb, string colName, 
             string typeName, int length, int isNullable, int status)
        {
            if (sb.Length > 0)
                sb.Append(",\n");
            sb.AppendFormat("\t@{0} ", colName);
            switch (typeName.ToLower())
            {
                case "varchar":
                case "nvarchar":
                    string lenString = length.ToString();
                    if (length == -1)
                        lenString = "MAX";
                    sb.AppendFormat("{0}({1})", typeName, lenString);
                    break;
                default:
                    sb.Append(typeName);
                    break;
            }
            if (status == 0x80)
                sb.Append(" OUT");
            else if (isNullable != 0)
                sb.Append(" = NULL");
        }
        /// <summary>
        /// Adds the current field to the VALUES list for an INSERT query
        /// </summary>
        private static void AddInsertValueListToSb(StringBuilder sb, string colName, 
            int lineNumber)
        {
            AddListSeperatorToSb(sb, lineNumber);
            sb.AppendFormat("@{0}", colName);
        }
        /// <summary>
        /// Adds the current field to the Field list for an INSERT query
        /// </summary>

        private static void AddInsertFieldListToSb(StringBuilder sb, string colName, 
            int lineNumber)
        {
            AddListSeperatorToSb(sb, lineNumber);
            sb.AppendFormat("[{0}]", colName);
        }
        /// <summary>
        /// Adds the comma and or new line to the list StringBuilder
        /// </summary>
        /// <remarks>
        /// Shouldn't have a comma before the first item in the list.<br />
        /// Should only insert new ine after each ITEMS_PER_LINE items
        /// </remarks>
        private static void AddListSeperatorToSb(StringBuilder sb, int lineNumber)
        {
            if (sb.Length > 0)
                sb.Append(", ");
            if ((lineNumber % ITEMS_PER_LINE) == 0)
                sb.Append("\n\t");
        }
    }
}

I adapted this code for T4 Templates here

Saturday, January 30, 2010

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. . .

Thursday, January 14, 2010

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…