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