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.

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));
    }
}

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!

Boise Code Camp 2011 Presentation Notes

Sorry I’m late getting this up. I will give usual excuses of being busy.

Since I developed this talk on this blog, this entry will essentially be a link list. I put a copy of the slides in Google doc and you can reference it here

Template Parts

I presented a slide that showed the basic template parts in a small mock template:

<#@ template language="C#" #>
<#@ directive property=“value” #>
<# var item = "Statement Block"; #>
Text Block 
<#= "Expression Block" #>
<#+ 
    string ClassFeatureBlock(string thingy)
    {
        return thingy;
    } 
#>

Hello World Monty

I showed some of the elementary features of T4 templates by refactoring a basic template that would create a text file that contained the infamous words “Hello World”. A script of that exercise is here

Basic Code Generation Demonstration

To demonstrate Code Generation I set out to generate a simple T-SQL Select Statement using Sql Management Objects (SMO).

The Target Code looked something like this:

Include (SelectSpInclude.tt)

<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#+
    public class SelectSpInclude: TextTransformation
    {
        public string ServerName;
        public string DbName;
        public string TableName;

        public override string TransformText()
        {
 
        Table table = getTable(ServerName, DbName, TableName);
#>
SELECT <#+ bool forgetComma = true;
 foreach (Column col in table.Columns)
{ #><#+if (!forgetComma) Write("      ,");#>[<#= col.Name #>]
<#+ forgetComma = false;
} #>
FROM <#= table.Name #><#+

        return this.GenerationEnvironment.ToString();
    } 
    Table getTable(string serverName, string dbName, string tableName)
    {
        Server server = new Server(serverName);
        Database database = new Database(server, dbName);
        Table table = new Table(database, tableName);
        table.Refresh();
        return table; 
    }
}
#>

Template (EmployeesSp.tt)

<#@ template language="C#v3.5" #>
<#@ output extension="sql" #>
<#@ include file="SelectSpInclude.tt" #>
<#
    SelectSpInclude gen = new SelectSpInclude();
    gen.ServerName = @"localhost\SQLEXPRESS";
    gen.DbName = "Northwind";
    gen.TableName = "Employees"; Write(gen.TransformText());
#>

Result (EmployeesSp.sql)

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,[TitleOfCourtesy]
      ,[BirthDate]
      ,[HireDate]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[HomePhone]
      ,[Extension]
      ,[Photo]
      ,[Notes]
      ,[ReportsTo]
      ,[PhotoPath]
FROM Employees

Somewhere in this part of the presentation, someone asked if it is possible to generate a series of files from a single template, a separate code file for each table in a database. Oleg Sych has a blog post that does just that here

Generating a Nullable ADO.NET DataSet Wrapper

This is when I went to Show and Tell Mode. I demonstrate a Nullable DataSet Wrapper that I wrote in these blog entries. I wrote the templates in VB.NET to generate code in C#.

Part 1, Part 2, Part 3 & Part 4

T4 Template Tour of First Gear

At the Boise Code Camp, I lead off with a demonstration of some basic features of T4 Templates. The point of the presentation is to get you from no T4 to the level of most blog examples. I got good reviews from the few who attended my session, so I thought I would attempt to write it down. Here goes…

The Script

First, create a Text file and change the extension to "tt". We’ll call it "Hello.tt". I will add a template directive and tell T4 that I want to use C#. I add the text "Hello World" below the directive. When I save it, "Hello.cs" appears below the template.

Template (Hello.tt):

<#@ template language="C#" #>
Hello World

Output (Hello.cs):

Hello World

Also notice that Visual Studio complains that Hello.cs won’t compile. For this demonstration, I don’t want to create a C# code file, so I will tell the template to create a "txt" file with an output directive.

Template (Hello.tt):

<#@ template language="C#" #>
<#@ output extension="txt" #>
Hello World

Output (Hello.txt):

Hello World

Visual Studio is happy because it doesn’t try to compile "txt" files. (Since it doesn't change, I won't repeat the output again.)

Now, let’s add some actual C# template code, I’m going to create a variable called "name" in a Statement Block and reference it in an Expression Block.

Template (Hello.tt):

<#@ template language="C#" #>
<#@ output extension="txt" #>
<# string name = "World"; #>
Hello <#= name #>

Now I’m going to replace the variable with a function. The function will need to be in a Class Feature Block. Just to speed things up, I’m going to use a Write call in a Statement Block to render the name. You can write text to the output using either Expression Blocks OR calls to Write().

Template (Hello.tt):

<#@ template language="C#" #>
<#@ output extension="txt" #>
Hello <# Write(GetName()); #>
<#+ 
    string getName()
    {
        return "Word";
    }
#>

Now I want to move my function into an include file. I will create a new text file and call it "HelloFunctions.tt" I move the Class Feature Block into that file. Notice that there is no template directive. Also, you must save the include file BEFORE you save the template file.

Include (HelloFunctions.tt)

<#+ 
    string getName()
    {
        return "Word";
    }
#>

Template (Hello.tt):

<#@ template language="C#" #>
<#@ include file="HelloFunctions.tt" #>
<#@ output extension="txt" #>
Hello <# Write(GetName()); #>

Now, for a brief look under the hood, I can show you the source code for the Text Transformation. Here I add "debug="true"" to the template file, save it and look in the temp directory (you can get your temp directory by typing "set temp" in a command prompt)

Template (Hello.tt):

<#@ template language="C#" #>
<#@ include file="HelloFunctions.tt" #>
<#@ output extension="txt" #>
Hello <# 
// This comment will show up in .cs file
Write(GetName()); 
#>

Generated C# File (hcs3svyj.0.cs in this case)

namespace Microsoft.VisualStudio.TextTemplatingBFD11436D0A4595408B06EA4E1B2A636 {
    using System;
    using Microsoft.VisualStudio.TextTemplating.VSHost;

    
    #line 1 "c:\vsprojects\SimpleT4\Hello.tt"
    public class GeneratedTextTransformation : 
        Microsoft.VisualStudio.TextTemplating.TextTransformation {
        public override string TransformText() {
            try {
                this.Write("Hello ");

                #line 4 "c:\vsprojects\SimpleT4\Hello.tt"

// This comment will show up in .cs file
Write(getName()); 

          
                #line default
                #line hidden
            }
            catch (System.Exception e) {
                System.CodeDom.Compiler.CompilerError error = new 
                    System.CodeDom.Compiler.CompilerError();
                error.ErrorText = e.ToString();
                error.FileName = "c:\\vsprojects\\SimpleT4\\Hello.tt";
                this.Errors.Add(error);
            }
            return this.GenerationEnvironment.ToString();
        }

        #line 1 "c:\vsprojects\SimpleT4\HelloFunctions.tt"

 string getName()
 {
  return "World";
 }

  
        #line default
        #line hidden
    }

    #line default
    #line hidden
}

These source files have helped me understand what is going on behind the scenes. For example, I didn’t really get the difference between Statement Blocks (<# #>) and Class Feature Blocks (<#+ #>) until I saw the generated.

Afterthought

This isn’t a transcription! Written and spoken communication is different by nature.

In the presentation I would change the value of "World" with different words ("Earth", "Boise", "Boise Code Camp", etc); In a presentation, this added proofiness that the templates were run and some lame humor to lighten the mood of the talk. In the written form, it would force me to repeat the Output after each sample (and make this longer).