public string GenerateDropDefaultValueConstraint(DefaultConstraint constraint)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($@"ALTER TABLE ""{constraint.SchemaName}"".""{constraint.TableName}"" DROP CONSTRAINT ""{constraint.Name}""");
return(sb.ToString());
}
public string GenerateCreateDefaultValueConstraint(DefaultConstraint constraint)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($@"ALTER TABLE ""{constraint.SchemaName}"".""{constraint.TableName}""");
sb.AppendLine($@"ADD CONSTRAINT ""{constraint.Name}"" DEFAULT {constraint.ColumnDefault} FOR ""{constraint.ColumnName}"";");
return(sb.ToString());
}
public string GenerateCreateDefaultValueConstraint(DefaultConstraint constraint)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($"ALTER TABLE[{constraint.SchemaName}].[{constraint.TableName}]");
sb.AppendLine($"ADD CONSTRAINT[{constraint.Name}] DEFAULT{constraint.ColumnDefault} FOR[{constraint.ColumnName}]");
sb.AppendLine($"GO");
return(sb.ToString());
}
public virtual DefaultConstraint Clone()
{
DefaultConstraint constraint = new DefaultConstraint();
constraint.Name = this.Name;
constraint.TableName = this.TableName;
constraint.ColumnName = this.TableName;
constraint.ColumnDefault = this.TableName;
constraint.SchemaName = this.SchemaName;
constraint.IsRowGuid = this.IsRowGuid;
return(constraint);
}
public List <DefaultConstraint> GetDefaultConstraintsForDatabase()
{
List <DefaultConstraint> response = new List <DefaultConstraint>();
string query = $@"SELECT
OBJECT_SCHEMA_NAME(C.object_id) AS SchemaName,
OBJECT_NAME(C.object_id) as TableName,
COL_NAME(C.object_id, column_id) ColumnName,
D.Name ConstraintName,
D.[definition] ColumnDefault,
C.is_rowGuidCol IsRowGuidColumn
FROM sys.columns AS C
JOIN sys.default_constraints AS D
ON C.column_id = D.parent_column_id
WHERE
D.parent_object_id = C.object_id AND
OBJECT_SCHEMA_NAME(C.object_id) <> 'SYS' AND
OBJECT_NAME(C.object_id) <> '__EFMigrationsHistory'
Order by SchemaName, TableName, ColumnName, ConstraintName";
using (SqlConnection conn = new SqlConnection(this._connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
command.CommandTimeout = 300;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var defaultConstraint = new DefaultConstraint()
{
ColumnDefault = reader["ColumnDefault"].ToString(),
ColumnName = reader["ColumnName"].ToString(),
Name = reader["ConstraintName"].ToString(),
SchemaName = reader["SchemaName"].ToString(),
TableName = reader["TableName"].ToString(),
IsRowGuid = Convert.ToBoolean(reader["IsRowGuidColumn"].ToString())
};
response.Add(defaultConstraint);
}
}
return(response);
}
public virtual void AddDefaultConstraint(DefaultConstraint constraint)
{
this.DefaultConstraints.RemoveAll(x => x.Name == constraint.Name);
this.DefaultConstraints.Add(constraint);
}
