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
<#@ 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.
No comments:
Post a Comment