public string CreateQueryForTableCreation(IManageable model)
{
if (model.Configuration.ManagedProperties.Count == 0)
{
return(string.Empty);
}
StringBuilder queryBuilder = new StringBuilder();
ITransactionable sqlTransaction = new MsSqlTransaction();
FullyQualifiedTableName tableName = new FullyQualifiedTableName(model.Configuration.Schema, $"{Manager.TablePrefix}{model.Configuration.TableName}");
queryBuilder.Append(sqlTransaction.AddTable(tableName, model.Configuration.PrimaryKeyProperty.Name, GetSqlDataType(model.Configuration.PrimaryKeyProperty.PropertyType, false, 0), model.Configuration.PrimaryKeyProperty.PropertyType.Equals(typeof(int?)) || model.Configuration.PrimaryKeyProperty.PropertyType.Equals(typeof(int))));
// Aqui se colocan las propiedades del objeto. Una por columna por su puesto (excepto para la primary key).
foreach (KeyValuePair <string, PropertyInfo> property in model.Configuration.ManagedProperties.Where(q => q.Key != model.Configuration.PrimaryKeyProperty.Name))
{
string sqlDataType = GetSqlDataType(property.Value.PropertyType, model.Configuration.UniqueKeyProperties.ContainsKey(property.Value.Name), GetDataLengthFromProperty(model, property.Key));
queryBuilder.Append(sqlTransaction.AddColumn(tableName, property.Value.Name, sqlDataType));
queryBuilder.Append(!IsNullable(property.Value) ? sqlTransaction.AddNotNullToColumn(tableName, property.Value.Name, sqlDataType) : string.Empty);
queryBuilder.Append(IsUnique(model, property.Value.Name) ? sqlTransaction.AddUniqueToColumn(tableName, property.Value.Name) : string.Empty);
queryBuilder.Append(IsDefault(model, property.Value.Name) ? sqlTransaction.AddDefaultToColumn(tableName, property.Value.Name, model.Configuration.DefaultAttributes[property.Value.Name].Value) : string.Empty);
}
Logger.Info("Created a new query for Create Table:");
Logger.Info(queryBuilder.ToString());
return(queryBuilder.ToString());
}
public string CreateQueryForTableAlteration(IManageable model, Dictionary <string, ColumnDefinition> columnDetails, Dictionary <string, ConstraintDefinition> constraints)
{
if (model.Configuration.ManagedProperties.Count == 0)
{
return(string.Empty);
}
StringBuilder queryBuilder = new StringBuilder();
ITransactionable sqlTransaction = new MsSqlTransaction();
FullyQualifiedTableName tableName = new FullyQualifiedTableName(model.Configuration.Schema, $"{Manager.TablePrefix}{model.Configuration.TableName}");
foreach (KeyValuePair <string, PropertyInfo> property in model.Configuration.ManagedProperties)
{
columnDetails.TryGetValue(property.Value.Name, out ColumnDefinition columnDefinition);
string sqlDataType = GetSqlDataType(property.Value.PropertyType, model.Configuration.UniqueKeyProperties.ContainsKey(property.Value.Name), GetDataLengthFromProperty(model, property.Key));
if (IsNewColumn(columnDefinition))
{
queryBuilder.Append(sqlTransaction.AddColumn(tableName, property.Value.Name, sqlDataType));
columnDefinition = new ColumnDefinition();
}
if (IsColumnDataTypeChanged(columnDefinition, sqlDataType))
{
if (!string.IsNullOrWhiteSpace(columnDefinition.Column_Default))
{
queryBuilder.Append(sqlTransaction.RemoveDefaultFromColumn(tableName, $"DF_{tableName.Schema}_{tableName.Table}_{property.Value.Name}"));
columnDefinition.Column_Default = null;
}
queryBuilder.Append(sqlTransaction.ChangeColumnDataType(tableName, property.Value.Name, sqlDataType));
columnDefinition.Is_Nullable = null;
}
queryBuilder.Append(IsNowNullable(columnDefinition, property.Value) ? sqlTransaction.RemoveNotNullFromColumn(tableName, property.Value.Name, sqlDataType) : string.Empty);
queryBuilder.Append(IsNoLongerNullable(columnDefinition, property.Value) ? sqlTransaction.AddNotNullToColumn(tableName, property.Value.Name, sqlDataType) : string.Empty);
queryBuilder.Append(IsNowUnique(constraints, $"UQ_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.AddUniqueToColumn(tableName, property.Value.Name) : string.Empty);
queryBuilder.Append(IsNoLongerUnique(constraints, $"UQ_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.RemoveUniqueFromColumn(tableName, $"UQ_{tableName.Schema}_{tableName.Table}_{property.Value.Name}") : string.Empty);
queryBuilder.Append(IsNowDefault(columnDefinition, property.Value) ? sqlTransaction.AddDefaultToColumn(tableName, property.Value.Name, model.Configuration.DefaultAttributes[property.Value.Name].Value) : string.Empty);
queryBuilder.Append(IsDefaultChanged(columnDefinition, property.Value) ? sqlTransaction.RenewDefaultInColumn(tableName, property.Value.Name, model.Configuration.DefaultAttributes[property.Value.Name].Value) : string.Empty);
queryBuilder.Append(IsNoLongerDefault(columnDefinition, property.Value) ? sqlTransaction.RemoveDefaultFromColumn(tableName, $"DF_{tableName.Schema}_{tableName.Table}_{property.Value.Name}") : string.Empty);
queryBuilder.Append(IsNowPrimaryKey(constraints, $"PK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.AddPrimaryKeyToColumn(tableName, property.Value.Name) : string.Empty);
queryBuilder.Append(IsNoLongerPrimaryKey(constraints, $"PK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.RemovePrimaryKeyFromColumn(tableName, $"PK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}") : string.Empty);
queryBuilder.Append(IsNoLongerForeignKey(constraints, $"FK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}", property.Value) ? sqlTransaction.RemoveForeignKeyFromColumn(tableName, $"FK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}") : string.Empty);
}
foreach (KeyValuePair <string, ColumnDefinition> columnDetail in columnDetails.Where(q => !model.Configuration.ManagedProperties.Keys.Contains(q.Key)))
{
queryBuilder.Append(sqlTransaction.RemoveColumn(tableName, columnDetail.Key));
}
return(queryBuilder.ToString());
}
public string GetCreateForeignKeysQuery(IManageable model, Dictionary <string, ConstraintDefinition> constraints = null)
{
StringBuilder queryBuilder = new StringBuilder();
ITransactionable sqlTransaction = new MsSqlTransaction();
FullyQualifiedTableName tableName = new FullyQualifiedTableName(model.Configuration.Schema, $"{Manager.TablePrefix}{model.Configuration.TableName}");
foreach (KeyValuePair <string, PropertyInfo> property in model.Configuration.ForeignKeyProperties)
{
if (constraints == null)
{
queryBuilder.Append(sqlTransaction.AddForeignKeyToColumn(tableName, property.Value));
continue;
}
if (!constraints.ContainsKey($"FK_{tableName.Schema}_{tableName.Table}_{property.Value.Name}"))
{
queryBuilder.Append(sqlTransaction.AddForeignKeyToColumn(tableName, property.Value));
}
}
Logger.Info("Created a new query for Create Foreign Keys:");
Logger.Info(queryBuilder.ToString());
return(queryBuilder.ToString());
}
