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.