private static TestTable LoadUDTFromDDL(string ddl)
{
TSqlFragment sqlF = ScriptDomFacade.Parse(ddl);
CreateTypeTableStatement stmt_CreateTable = (CreateTypeTableStatement)((TSqlScript)sqlF).Batches[0].Statements[0];
string schemaName = stmt_CreateTable.Name.SchemaIdentifier.Dequote();
string tableName = stmt_CreateTable.Name.BaseIdentifier.Dequote();
var table = new TestTable(schemaName, tableName);
foreach (ColumnDefinition col in stmt_CreateTable.Definition.ColumnDefinitions)
{
string columnName = col.ColumnIdentifier.Dequote();
var columnDbType = ProcedureGenerator.ResolveToDbDataType(col.DataType);
table.AddColumn(columnName, columnDbType, true);
}
return(table);
}
public override void ExplicitVisit(CreateTypeTableStatement node)
{
IsTableType = true;
}
public override void Visit(CreateTypeTableStatement node)
{
string tableTypeName = node.Name.BaseIdentifier.Value;
if (!_visitedTypes.Add(tableTypeName))
{
// This table type has already been added from a previous .sql file.
return;
}
string schemaQualifiedTableTypeName = $"{node.Name.SchemaIdentifier.Value}.{tableTypeName}";
string className = GetClassNameForTableValuedParameterDefinition(node.Name);
string rowStructName = GetRowStructNameForTableType(node.Name);
TypeSyntax columnsEnumerableType = TypeExtensions.CreateGenericTypeFromGenericTypeDefinition(
typeof(IEnumerable <>).ToTypeSyntax(true),
IdentifierName("Column"));
ArrayTypeSyntax columnsArrayType = ArrayType(IdentifierName("Column")).AddRankSpecifiers(ArrayRankSpecifier());
ClassDeclarationSyntax classDeclarationSyntax =
ClassDeclaration(className)
.WithModifiers(TokenList(Token(SyntaxKind.InternalKeyword)))
.AddBaseListTypes(
SimpleBaseType(
GenericName("TableValuedParameterDefinition")
.AddTypeArgumentListArguments(IdentifierName(rowStructName))))
.AddMembers(
ConstructorDeclaration(
Identifier(className))
.WithModifiers(
TokenList(
Token(SyntaxKind.InternalKeyword)))
.AddParameterListParameters(
Parameter(Identifier("parameterName")).WithType(typeof(string).ToTypeSyntax(true)))
.WithInitializer(
ConstructorInitializer(
SyntaxKind.BaseConstructorInitializer,
ArgumentList(SeparatedList(new[]
{
Argument(IdentifierName("parameterName")),
Argument(
LiteralExpression(
SyntaxKind.StringLiteralExpression,
Literal(schemaQualifiedTableTypeName))),
}))))
.WithBody(Block()))
.AddMembers(node.Definition.ColumnDefinitions.Select(CreatePropertyForTableColumn).ToArray())
// Add Columns property override
.AddMembers(
PropertyDeclaration(
columnsEnumerableType,
Identifier("Columns"))
.AddModifiers(Token(SyntaxKind.ProtectedKeyword), Token(SyntaxKind.OverrideKeyword))
.WithExpressionBody(
ArrowExpressionClause(
ArrayCreationExpression(columnsArrayType)
.WithInitializer(
InitializerExpression(
SyntaxKind.ArrayInitializerExpression,
SeparatedList <ExpressionSyntax>(
node.Definition.ColumnDefinitions.Select(c => IdentifierName(c.ColumnIdentifier.Value)))))))
.WithSemicolonToken(Token(SyntaxKind.SemicolonToken)))
// Add FillSqlDataRecord implementation
.AddMembers(
MethodDeclaration(typeof(void).ToTypeSyntax(), Identifier("FillSqlDataRecord"))
.AddModifiers(Token(SyntaxKind.ProtectedKeyword), Token(SyntaxKind.OverrideKeyword))
.AddParameterListParameters(
Parameter(Identifier("record")).WithType(typeof(SqlDataRecord).ToTypeSyntax(useGlobalAlias: true)),
Parameter(
Identifier("rowData")).WithType(IdentifierName(rowStructName)))
.WithBody(
Block(node.Definition.ColumnDefinitions.Select((c, i) =>
ExpressionStatement(
InvocationExpression(
MemberAccessExpression(
SyntaxKind.SimpleMemberAccessExpression,
IdentifierName(c.ColumnIdentifier.Value),
IdentifierName("Set")))
.AddArgumentListArguments(
Argument(IdentifierName("record")),
Argument(
LiteralExpression(
SyntaxKind.NumericLiteralExpression,
Literal(i))),
Argument(
MemberAccessExpression(
SyntaxKind.SimpleMemberAccessExpression,
IdentifierName("rowData"),
IdentifierName(c.ColumnIdentifier.Value)))))))));
StructDeclarationSyntax rowStruct = StructDeclaration(rowStructName)
.AddModifiers(Token(SyntaxKind.InternalKeyword))
// Add a constructor with parameters for each column, setting the associate property for each column.
.AddMembers(
ConstructorDeclaration(
Identifier(rowStructName))
.WithModifiers(
TokenList(
Token(SyntaxKind.InternalKeyword)))
.AddParameterListParameters(
node.Definition.ColumnDefinitions.Select(c =>
Parameter(Identifier(c.ColumnIdentifier.Value))
.WithType(DataTypeReferenceToClrType(c.DataType, IsColumnNullable(c)))).ToArray())
.WithBody(
Block(node.Definition.ColumnDefinitions.Select(c =>
ExpressionStatement(
AssignmentExpression(
SyntaxKind.SimpleAssignmentExpression,
left: MemberAccessExpression(
SyntaxKind.SimpleMemberAccessExpression,
ThisExpression(),
IdentifierName(c.ColumnIdentifier.Value)),
right: IdentifierName(c.ColumnIdentifier.Value)))))))
// Add a property for each column
.AddMembers(node.Definition.ColumnDefinitions.Select(c =>
(MemberDeclarationSyntax)PropertyDeclaration(
DataTypeReferenceToClrType(c.DataType, IsColumnNullable(c)),
Identifier(c.ColumnIdentifier.Value))
.AddModifiers(Token(SyntaxKind.InternalKeyword))
.AddAccessorListAccessors(AccessorDeclaration(SyntaxKind.GetAccessorDeclaration)
.WithSemicolonToken(Token(SyntaxKind.SemicolonToken)))).ToArray());
MembersToAdd.Add(classDeclarationSyntax.AddSortingKey(this, tableTypeName));
MembersToAdd.Add(rowStruct.AddSortingKey(this, tableTypeName));
base.Visit(node);
}
public override void Visit(CreateTypeTableStatement node) { this.action(node); }
public override void ExplicitVisit(CreateTypeTableStatement fragment)
{
_fragments.Add(fragment);
}
public string Convert(CTableType tableType)
{
string[] parts = { tableType.Schema.SchemaName, tableType.TableName };
var createTypeTable = new CreateTypeTableStatement();
///set schema and table name
createTypeTable.Name = new SchemaObjectName();
createTypeTable.Name.Identifiers.Add(new Identifier {
Value = tableType.Schema.SchemaName
});
createTypeTable.Name.Identifiers.Add(new Identifier {
Value = tableType.TableName
});
//add columns
createTypeTable.Definition = new TableDefinition();
foreach (var col in tableType.Column)
{
var dataType = new SqlDataTypeReference
{
SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw)
};
if (DoesNeedLength(col.ColumnSqlDbType))
{
if (col.ColumnLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = col.ColumnLength.ToString()
});
}
}
var column = new ColumnDefinition
{
ColumnIdentifier = new Identifier {
Value = col.ColumnName
},
DataType = dataType
};
if (col.IsIdentity)
{
column.IdentityOptions = new IdentityOptions
{
IdentitySeed = new IntegerLiteral {
Value = "1000"
},
IdentityIncrement = new IntegerLiteral {
Value = "1"
}
}
}
;
column.Constraints.Add(new NullableConstraintDefinition {
Nullable = col.IsNullable
});
if (col.IsUnique)
{
column.Constraints.Add(new UniqueConstraintDefinition());
}
if (col.IsIndexed)
{
column.Index = new IndexDefinition
{
Name = new Identifier {
Value = $"IX_{col.ColumnName}"
},
IndexType = new IndexType
{
IndexTypeKind = IndexTypeKind.NonClustered
}
}
}
;
createTypeTable.Definition.ColumnDefinitions.Add(column);
}
/*
* //generate DDL
* var script = new TSqlScript();
* var batch = new TSqlBatch();
* script.Batches.Add(batch);
* batch.Statements.Add(createTypeTable);
* var dacpacModel = new TSqlModel(SqlServerVersion.Sql120, new TSqlModelOptions());
* dacpacModel.AddObjects(script);
* var existing = dacpacModel.GetObject(Table.TypeClass, new ObjectIdentifier(parts), DacQueryScopes.All);
* return existing.GetScript();
*/
string scriptOut;
var scriptGen = new Sql120ScriptGenerator();
scriptGen.GenerateScript(createTypeTable, out scriptOut);
return(scriptOut);
}