Monday, May 02, 2011

Thoughts before Alt .NET Seattle: Part II: Using jrcs3.com

I am going to Alt.Net 2011 Seattle Conference next weekend. Since I am not on the bleeding edge of technology at work, I’m looking into modern methods and technologies.

Using my domain

Several years ago, I bought the domain jrcs3.com; it the initials for my legal name John Robert Chilton Stephens III.

Since I will be hanging out with the cool kid and in technology, all the cool kids have vanity domain, I need to make sure that my domain is visible on my cards, etc. So this weekend I:

  • Moved this blog from jrcs3.blogspot.com to brochure.jrcs3.com (it is “brochure” because someone said that it isn’t a blog until it gets comments).
  • Created a jrcs3.com event email address for the Alt.Net Seattle 2011. You will have to attend the event to see what it is.

When I get my next paycheck (Monday or Tuesday) I will:
  • Buy a year of hosting for jrcs3.com
  • Write a simple home page for it in Razor (linking to brochure.jrcs3.com). I have already created a WebMatrix project.
  • Print out a couple of sheets of business cards to hand out.

Friday, April 29, 2011

Thoughts before Alt .NET Seattle: Part I: TDD

I am going to Alt.Net 2011 Seattle Conference next weekend. Since I am not on the bleeding edge of technology at work, I’m looking into modern methods and technologies.

TDD

On my last couple of gigs, when I bring up Test Driven Development, I get a lecture about how it is overly academic and would never work "here" (one place even banned NUnit altogether). Yes, it may take more time to code, but at these places, we spend a lot of time fixing things after the fact.

I agree that it can be taken too far. There are times that it doesn’t make sense to write test before writing trivial code. UI is infamously un-testable. Be pragmatic, it is just as extreme to reject automated testing altogether

I like the way TDD appears to make you think. You know, this function needs to do these 5 things, and what about this edge case, etc. And I have some tests that I can use later, for less effort than if wrote the test later.

I have snuck some tests into the projects that I have more control over. Most of the tests are too large (cover more one thing) and break other TDD rules. Since I can’t get approval to use an IOC container, they are not unit test. Even with these lower quality tests, I have found bugs and have been able to safely re-factor.

Thursday, April 14, 2011

Going to St. Louis

Nine years ago, my sister Susie was run over by a bus and killed. I am going to St. Louis to help my mother plant a tree in her honor.

I both look forward to and dread this trip. After all, it is the place of her death. It is a city that looms large in my mind even though I have never been there. I should have gone years ago and face the demons, now is as good a time as any.

Tuesday, April 12, 2011

Generate Linq To Sql .DBML Fragments

Another user for my post on getting the schema from a Stored Procedure. This time I am generating a Dbml fragment.

In the Column element, I am not providing a DbType. I don’t use Linq to Sql in such a way that it actually uses that value for anything important. I am also assuming that CanBeNull is true.

Yes, this is totally a message for work post. If you need detail as to what I’m doing, check this out.

<#@ assembly name="TsqlDesriptionUtil.dll" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ import namespace="TsqlDesriptionUtil" #>
<#@ import namespace="System.Collections.Generic" #>
<#+
    public class DbmlFunctionFromTSqlCmd : TextTransformation
    {
        public string FunctionName;
        public string FunctionMethod;
        public List<TableDescription> tables;

        public override string TransformText()
        {
#>    <Function Name="<#= FunctionName
                #>" Method="<#= FunctionMethod #>">
<#+ foreach(TableDescription table in tables)
    { #>        <ElementType Name="<#= FunctionMethod #>Result_<#= table.TableName #>">
<#+ foreach(FieldDescription field in table.Fields)
   { #>             <Column Name="<#= field.FieldName #>" 
                      Type="<#= field.FieldType.FullName  #>" 
                      CanBeNull="true" />
<#+ } #>
         </ElementType>
<#+ } #>
    </Function>
<#+ 
        return this.GenerationEnvironment.ToString();
    }
} #>

Again, for Demonstration purposes I wrote a simple T4 Template that includes the template above and uses the library I wrote in this post to get a description of the data.

<#@ template language="C#v3.5" debug="true" 
#><#@ output extension="txt" 
#><#@ include file="DbmlFunctionFromTSqlCmd.tt" 
#><#@ assembly name="TsqlDesriptionUtil.dll" 
#><#@ import namespace="TsqlDesriptionUtil" 
#><#
    // Since I'm generating XML, I will go to extreme measures to avoid new 
    // line characters like the strange formatting of declarations above ^
 
    // Get the table descriptions for given T-SQL
    string conString = @"Data Source=localhost;" +
      @"Initial Catalog=Northwind;Integrated Security=True";
    string commandString = "EXEC  CustOrderHist @CustomerID='ALFKI'";
    List<TableDescription> tables = 
      TsqlCommandSchema.GetFieldDescription(conString, commandString);
 
    // Generate DBML Fragment:
    var gen = new DbmlFunctionFromTSqlCmd();
    gen.FunctionName = "dbo.GetBigTableDataUseTempTab";
    gen.FunctionMethod = "MyNew";
    gen.tables = tables;
    Write(gen.TransformText()); 
#>

(If I were more of a studman programmer dude, I'd write a Visual Studio Add-in that would insert this fragment directly into my .dbml file.)

Tuesday, March 29, 2011

Generate DataSet .XDS

Last week on my Brochure, I wrote about getting the schema of a Stored Procedure or other T-SQL command. Now I am going to bring this a little farther and generate am .XDS file that can be used to generate a Typed DataSet. This T4 Template include file takes a List and a DataSet name and generates the .XSD.

<#@ assembly name="TsqlDesriptionUtil.dll" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ import namespace="TsqlDesriptionUtil" #>
<#@ import namespace="System.Collections.Generic" #>
<#+
    public class DataSetFromTSqlCmd: TextTransformation
    {
        public string dsName;
        public List<TableDescription> tables;

        public override string TransformText()
        {
#><?xml version="1.0" encoding="utf-8"?>
<xs:schema id="<#= dsName #>" 
  targetNamespace="http://jrcs3.blogspot.com/<#= dsName #>.xsd"
  xmlns:mstns="http://jrcs3.blogspot.com/<#= dsName #>.xsd" 
  xmlns="http://jrcs3.blogspot.com/<#= dsName #>.xsd" 
  xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
  xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" 
  attributeFormDefault="qualified" 
  elementFormDefault="qualified">
  <xs:annotation>
    <xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">
      <DataSource DefaultConnectionIndex="0" 
          FunctionsComponentName="QueriesTableAdapter"
          Modifier="AutoLayout, AnsiClass, Class, Public" 
          SchemaSerializationMode="IncludeSchema" 
          xmlns="urn:schemas-microsoft-com:xml-msdatasource">
        <Connections />
        <Tables />
        <Sources />
      </DataSource>
    </xs:appinfo>
  </xs:annotation>
 <xs:element name="<#= dsName #>" 
        msdata:IsDataSet="true" 
        msdata:UseCurrentLocale="true" 
        msprop:Generator_UserDSName="<#= dsName #>" 
        msprop:Generator_DataSetName="<#= dsName #>" 
        msprop:EnableTableAdapterManager="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
<#+ foreach(TableDescription table in tables)
    { #>
        <xs:element 
            name="<#= table.TableName #>"  
            msprop:Generator_UserTableName="<#= table.TableName #>" 
            msprop:Generator_RowDeletedName="<#= table.TableName #>RowDeleted" 
            msprop:Generator_RowChangedName="<#= table.TableName #>RowChanged" 
            msprop:Generator_RowClassName="<#= table.TableName #>Row" 
            msprop:Generator_RowChangingName="<#= table.TableName #>RowChanging" 
            msprop:Generator_RowEvArgName="<#= table.TableName #>RowChangeEvent" 
            msprop:Generator_RowEvHandlerName="<#= table.TableName 
               #>RowChangeEventHandler" 
            msprop:Generator_TableClassName="<#= table.TableName #>DataTable" 
            msprop:Generator_TableVarName="table<#= table.TableName #>" 
            msprop:Generator_RowDeletingName="<#= table.TableName #>RowDeleting" 
            msprop:Generator_TablePropName="<#= table.TableName #>">
          <xs:complexType>
            <xs:sequence>
<#+ foreach(FieldDescription field in table.Fields)
 { #>
                <xs:element 
                    name="<#= field.FieldName #>" 
                    msprop:Generator_UserColumnName="<#= field.FieldName #>"
                    msprop:Generator_ColumnVarNameInTable="column<#= field.FieldName 
                      #>" 
                    msprop:Generator_ColumnPropNameInRow="<#= field.FieldName #>" 
                    msprop:Generator_ColumnPropNameInTable="<#= field.FieldName 
                      #>Column" 
                    type="xs:<#= field.XsdTypeName #>" 
                    minOccurs="0" />
<#+ } #>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
<#+ } #>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>
<#+ 
  return this.GenerationEnvironment.ToString();
 }
} #>

For Demonstration purposes I wrote a simple T4 Template that includes the template above and uses the library I wrote in this post to get a description of the data.

<#@ template language="C#v3.5" debug="true" 
#><#@ output extension="xsd" 
#><#@ include file="DataSetFromTSqlCmd.tt" 
#><#@ assembly name="TsqlDesriptionUtil.dll" 
#><#@ import namespace="TsqlDesriptionUtil" 
#><#
    // Since I'm generating XML, I will go to extreme measures to avoid new line 
    // characters like the strange formatting of declarations above ^

    // Get the table descriptions for given T-SQL
    string conString = @"Data Source=localhost;" +
      @"Initial Catalog=Northwind;Integrated Security=True";
    string commandString = "EXEC  CustOrderHist @CustomerID='ALFKI'";
    List<TableDescription> tables = 
      TsqlCommandSchema.GetFieldDescription(conString, commandString);

    // Generate XSD:
    var gen = new DataSetFromTSqlCmd();
    gen.dsName = "MyNewDS";
    gen.tables = tables;
    Write(gen.TransformText()); 
#>

Since TsqlCommandSchema.GetFieldDescription supports multiple results sets and Stored Procedure that use temporary tables.

At work I will probably integrate this template into an existing code generation tool using my TTCommucator tool.

Tuesday, March 22, 2011

Getting the Schema of a Stored Procedure Results Set

Visual Studio is pretty good at discovering the schema of a stored procedure if the procedure doesn't use temporary tables and returns only one result set. You can drag the stored procedure on to a DataSet or Linq to Sql Design Surface and it is generated for you. In real life, stored procedures don’t fit into that neat box.

Since I am lazy and don’t like to build DataSets by hand, I wrote this little class. Note that I am actually executing the T-SQL command. Any side effect of the stored procedure will take place; this is acceptable in my environment, it may not be in yours.

I am getting less information from this method than I get from static sources like SQL Server’s SMO; for example, I am not getting the length of strings. I am getting enough information to do what I want to do.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace TsqlDesriptionUtil
{
    public class TsqlCommandSchema
    {
        public static List<TableDescription> GetFieldDescription(
            string conString, string commandString)
        {
            var rVal = new List<TableDescription>();
            int tableNumber = 1;
            using (var con = new SqlConnection(conString))
            {
                using (var com = new SqlCommand(commandString, con))
                {
                    con.Open();
                    // WARNING: I am executing the command, beware of side effects!
                    SqlDataReader reader = 
                        com.ExecuteReader(CommandBehavior.CloseConnection);
                    // Table Loop
                    do
                    {
                        var fieldDescriptionList = new List<FieldDescription>();
                        // Field Loop
                        for (int i = 0; i < reader.FieldCount; ++i)
                        {
                            Type fieldType = reader.GetProviderSpecificFieldType(i);
                            fieldDescriptionList.Add(
                                new FieldDescription(reader.GetName(i), 
                                reader.GetDataTypeName(i), reader.GetFieldType(i)));
                        }
                        // T-SQL doesn't give table names, so I will make up my own
                        rVal.Add(new TableDescription(string.Format(
                            "Table{0}", tableNumber++), fieldDescriptionList));
                    } while (reader.NextResult());
                    con.Close();
                }
            }
            return rVal;
        }
    }
    public class TableDescription
    {
        public TableDescription(string tableName, List<FieldDescription> fields)
        {
            this.TableName = tableName;
            this.Fields = fields;
        }
        public string TableName { get; set; }
        public List<FieldDescription> Fields { get; set; }
        public int Count
        {
            get
            {
                if (this.Fields != null)
                    return this.Fields.Count;
                else
                    throw new Exception("Fields not loaded");
            }
        }
    }
    public class FieldDescription
    {
        public FieldDescription(string fieldName, string dataTypeName, Type fieldType)
        {
            this.FieldName = fieldName;
            this.DataTypeName = dataTypeName;
            this.FieldType = fieldType;
        }
        public string FieldName { get; set; }
        public string DataTypeName { get; set; }
        public Type FieldType { get; set; }
        public string XsdTypeName
        {
            get
            {
                Type t = this.FieldType;
                return getXsdTypeNameForType(t);
            }
        }

        private static string getXsdTypeNameForType(Type t)
        {
            string rVal = string.Empty;
            //.NET Framework type XML Schema (XSD) type
            switch (t.FullName)
            {
                case "System.Boolean": rVal = "Boolean"; break;
                case "System.Byte": rVal = "unsignedByte"; break;
                case "System.Byte[]": rVal = "base64Binary"; break;
                case "System.DateTime": rVal = "dateTime"; break;
                case "System.Decimal": rVal = "decimal"; break;
                case "System.Double": rVal = "Double"; break;
                case "System.Int16": rVal = "short"; break;
                case "System.Int32": rVal = "int"; break;
                case "System.Int64": rVal = "long"; break;
                case "System.SByte": rVal = "Byte"; break;
                case "System.String": rVal = "string"; break;
                case "System.String[]": rVal = "ENTITIES"; break;
                case "System.TimeSpan": rVal = "duration"; break;
                case "System.UInt16": rVal = "unsignedShort"; break;
                case "System.UInt32": rVal = "unsignedInt"; break;
                case "System.UInt64": rVal = "unsignedLong"; break;
                case "System.Uri": rVal = "anyURI"; break;
                case "System.Xml.XmlQualifiedName": rVal = "QName"; break;
            }
            return rVal;
        }
    }
}

I compiled the above C# source file into an assembly. Here is a xUnit test that demonstrates its use.

[Fact(DisplayName = "Run SP 001")]
public void RunSp001()
{
    string conString = @"Data Source=localhost;Initial Catalog=My_Db;" + 
        "Integrated Security=True";
    string commandString = "EXEC  GetBigTableDataUseTempTab 101";
    List<TableDescription> rVal = 
        TsqlCommandSchema.GetFieldDescription(conString, commandString);

    foreach (var item in rVal) 
    {
        Console.WriteLine(string.Format("{0}: {1}", item.TableName, item.Count));
        foreach(var field in item.Fields)
            Console.WriteLine(
                string.Format("\t{0}\t{1}\t{2}\t{3}", 
                    field.FieldName, 
                    field.DataTypeName, 
                    field.FieldType, 
                    field.XsdTypeName));
    }
}

Monday, March 21, 2011

GetXsdTypeNameForType() function

For a project I'm working on, I need to convert .NET Framework types into XML Schema (XSD) types. I wrote a little function to do the translation:

public static string GetXsdTypeNameForType(Type t)
{
    string rVal = string.Empty;
    switch (t.FullName)
    {
        case "System.Boolean": rVal = "boolean"; break;
        case "System.Byte": rVal = "unsignedByte"; break;
        case "System.Byte[]": rVal = "base64Binary"; break;
        case "System.DateTime": rVal = "dateTime"; break;
        case "System.Decimal": rVal = "decimal"; break;
        case "System.Double": rVal = "double"; break;
        case "System.Int16": rVal = "short"; break;
        case "System.Int32": rVal = "int"; break;
        case "System.Int64": rVal = "long"; break;
        case "System.SByte": rVal = "byte"; break;
        case "System.String": rVal = "string"; break;
        case "System.String[]": rVal = "ENTITIES"; break;
        case "System.TimeSpan": rVal = "duration"; break;
        case "System.UInt16": rVal = "unsignedShort"; break;
        case "System.UInt32": rVal = "unsignedInt"; break;
        case "System.UInt64": rVal = "unsignedLong"; break;
        case "System.Uri": rVal = "anyURI"; break;
        case "System.Xml.XmlQualifiedName": rVal = "QName"; break;
    }
    return rVal;
}

NOTE: There are places where I had to make a decision as to which XSD type would map to System.TimeDate, System.String, System.Byte[], etc. And there's the question of which XSD type I will map to my custom Employee, Appointment or Cow types!