private object GetRedefines(CTable table)
{
var pkColumns = table.GetPrimaryKeyColumns();
var stringBuilder = new StringBuilder();
var first = true;
foreach (var column in table.Column)
{
if (column.IsModifiedDate)
{
continue;
}
if (column.IsCreatedDate)
{
continue;
}
if (!first)
{
stringBuilder.Append(", ");
}
stringBuilder.Append($" @{column.ColumnName} AS [{column.ColumnName}] ");
first = false;
}
return(stringBuilder.ToString());
}
private object GetInsertUpdateColumnMappings(CTable table)
{
var pkColumns = table.GetPrimaryKeyColumns();
var stringBuilder = new StringBuilder();
var first = true;
foreach (var column in table.Column)
{
if (column.IsIdentity)
{
continue;
}
if (column.IsComputed)
{
continue;
}
if (column.IsRowVersion)
{
continue;
}
if (column.IsCreatedDate)
{
continue;
}
if (pkColumns.Contains(column))
{
continue;
}
if (!first)
{
stringBuilder.Append(", ");
}
if (column.IsModifiedDate)
{
stringBuilder.Append($"T.[{column.ColumnName}] = GetUtcDate()");
}
else
{
stringBuilder.Append($"T.[{column.ColumnName}] = NEW_T.[{column.ColumnName}]");
}
first = false;
}
return(stringBuilder.ToString());
}
public CStoredProcedure Convert(CTable table)
{
var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer)
{
DataOperationIs = COperationIs.Delete | COperationIs.CRUD
};
storedProcedure.Schema = new CSchema {
SchemaName = $"{table.Schema.SchemaName}Api"
};
storedProcedure.StoredProcedureName = $"{table.TableName}Delete";
//storedProcedure.ResultSetName = table.TableName;
storedProcedure.Parameter = new List <CStoredProcedureParameter>();
var pkColumns = table.GetPrimaryKeyColumns();
foreach (var pkColumn in pkColumns)
{
var parameter = new CStoredProcedureParameter
{
ParameterName = $"{pkColumn.ColumnName}",
ParameterType = pkColumn.ColumnType,
ParameterTypeRaw = pkColumn.ColumnTypeRaw,
ParameterLength = pkColumn.ColumnLength,
SourceColumn = pkColumn
};
storedProcedure.Parameter.Add(parameter);
}
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"DELETE FROM [{table.Schema.SchemaName}].[{table.TableName}] ");
stringBuilder.AppendLine("WHERE");
var first = true;
foreach (var pkColumn in pkColumns)
{
if (!first)
{
stringBuilder.Append(" AND ");
}
stringBuilder.AppendLine($"\t\t[{pkColumn.ColumnName}] = @{pkColumn.ColumnName}");
first = false;
}
storedProcedure.StoredProcedureBody = stringBuilder.ToString();
return(storedProcedure);
}
private string GetPrimaryKeyCompares(CTable table)
{
var pkColumns = table.GetPrimaryKeyColumns();
var stringBuilder = new StringBuilder();
var first = true;
foreach (var pkColumn in pkColumns)
{
if (!first)
{
stringBuilder.Append(" AND ");
}
stringBuilder.Append($"T.[{pkColumn.ColumnName}] = NEW_T.[{pkColumn.ColumnName}]");
first = false;
}
return(stringBuilder.ToString());
}
public CStoredProcedure Convert(CTable table)
{
var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer);
storedProcedure.Schema = new CSchema {
SchemaName = $"{table.Schema.SchemaName}Api"
};
storedProcedure.StoredProcedureName = $"{table.TableName}Update";
storedProcedure.ResultSetName = table.TableName;
storedProcedure.Parameter = new List <CStoredProcedureParameter>();
foreach (var column in table.Column)
{
var parameter = new CStoredProcedureParameter
{
ParameterName = $"{column.ColumnName}",
ParameterType = column.ColumnType,
ParameterTypeRaw = column.ColumnTypeRaw,
ParameterLength = column.ColumnLength,
SourceColumn = column
};
storedProcedure.Parameter.Add(parameter);
}
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"UPDATE [{table.Schema.SchemaName}].[{table.TableName}] ");
stringBuilder.AppendLine("SET");
var pkColumns = table.GetPrimaryKeyColumns();
var first = true;
foreach (var column in table.Column)
{
if (column.IsIdentity)
{
continue;
}
if (pkColumns.Contains(column))
{
continue;
}
if (!first)
{
stringBuilder.Append("\t\t,");
}
stringBuilder.AppendLine($"{column.ColumnName} = @{column.ColumnName}");
first = false;
}
stringBuilder.AppendLine("WHERE");
first = true;
foreach (var pkColumn in pkColumns)
{
if (!first)
{
stringBuilder.Append(" AND ");
}
stringBuilder.AppendLine($"\t\t{pkColumn.ColumnName} = @{pkColumn.ColumnName}");
}
storedProcedure.StoredProcedureBody = stringBuilder.ToString();
return(storedProcedure);
}
public CreateTableStatement Convert(CTable table)
{
var parser = new TSql120Parser(false);
string[] parts = { table.Schema.SchemaName, table.TableName };
var createTable = new CreateTableStatement();
///set schema and table name
createTable.SchemaObjectName = new SchemaObjectName();
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = table.Schema.SchemaName
});
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = $"[{table.TableName}]"
});
//add columns
createTable.Definition = new TableDefinition();
foreach (var col in table.Column)
{
if (col.ColumnType == DbType.Object)
{
continue;
}
var dataType = new SqlDataTypeReference
{
SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw)
};
if (col.ColumnLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = col.ColumnLength.ToString()
});
}
var column = new ColumnDefinition
{
ColumnIdentifier = new Identifier {
Value = col.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, table.ConvertToSnakeCase)
},
DataType = dataType
};
if (!string.IsNullOrEmpty(col.DefaultValue))
{
IList <ParseError> errors;
//var defaultValueText = "CONVERT([char](32), REPLACE(CONVERT([char](36),NEWID()),'-',''))";
var scriptDefault = parser.ParseExpression(new StringReader(col.DefaultValue), out errors);
column.DefaultConstraint = new DefaultConstraintDefinition {
Expression = scriptDefault
};
}
if (col.IsIdentity)
{
column.IdentityOptions = new IdentityOptions
{
IdentitySeed = new IntegerLiteral {
Value = col.ColumnType == System.Data.DbType.Byte ? "1" : "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
}
}
}
;
createTable.Definition.ColumnDefinitions.Add(column);
}
//add PK's
var pks = table.GetPrimaryKeyColumns();
if (pks.Count > 0)
{
var primaryKeyConstraint = new UniqueConstraintDefinition {
IsPrimaryKey = true
};
primaryKeyConstraint.Clustered = true; // todo: use metadata
foreach (var pk in table.GetPrimaryKeyColumns())
{
var columnIdentifier = new MultiPartIdentifier();
columnIdentifier.Identifiers.Add(new Identifier {
Value = pk.ColumnName
});
var columnRefExpression = new ColumnReferenceExpression
{
MultiPartIdentifier = columnIdentifier
};
var columnWithSortOrder = new ColumnWithSortOrder {
Column = columnRefExpression
};
primaryKeyConstraint.Columns.Add(columnWithSortOrder);
}
var pkConstraintName = $"PK_{table.Schema.SchemaName}_{table.TableName}_{pks.First().ColumnName}";
primaryKeyConstraint.ConstraintIdentifier = new Identifier {
Value = pkConstraintName
};
createTable.Definition.TableConstraints.Add(primaryKeyConstraint);
}
//add foreign keys
foreach (var col in table.Column)
{
if (col.ForeignKeyColumn == null || col.ForeignKeyColumn.Count == 0)
{
continue;
}
foreach (var fk in col.ForeignKeyColumn)
{
var fkConstraintName = $"FK_{table.TableName}_{fk.Table.TableName}";
var foreignKeyConstraint = new ForeignKeyConstraintDefinition();
foreignKeyConstraint.ConstraintIdentifier = new Identifier {
Value = fkConstraintName
};
foreignKeyConstraint.Columns.Add(new Identifier {
Value = col.ColumnName
});
foreignKeyConstraint.ReferenceTableName = new SchemaObjectName();
foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier
{
Value = fk.Table.Schema.SchemaName
});
foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier
{
Value = fk.Table.TableName
});
foreignKeyConstraint.ReferencedTableColumns.Add(new Identifier {
Value = fk.ColumnName
});
createTable.Definition.TableConstraints.Add(foreignKeyConstraint);
}
}
return(createTable);
}
}
}