public string DFToSqlString(SDefaultConstraint df)
{
string template = "alter table {0} add constraint DF_{0}_{1} default {2} for {1} ;";
string s = string.Format(template, df.column.table.name, df.column.name, df.defaultvalue);
return(s);
}
private List <SDefaultConstraint> GetDefaultConstraints(DbConn dbconn, string tableid, List <SColumn> columns)
{
string sql1 = @"select DC.name,DC.object_id,DC.parent_column_id,DC.definition,DC.parent_column_id, AC.name as columnname from sys.default_constraints DC
left join sys.all_columns AC on DC.parent_object_id=AC.object_id and DC.parent_column_id=AC.column_id
where DC.parent_object_id=@objid";
DataTable tb = dbconn.SqlToDataTable(sql1, new { objid = tableid });
List <SDefaultConstraint> cons = new List <SDefaultConstraint>();
foreach (DataRow dr in tb.Rows)
{
string cn = dr["columnname"].ToString();
var c = columns.FirstOrDefault(x => x.name == cn);
if (c == null)
{
throw new Exception(cn + "列在表中不存在");
}
var defattr = new SDefaultConstraint()
{
column = c,
defaultvalue = dr["definition"].ToString()
};
c.defaultconstraint = defattr;
cons.Add(defattr);
}
return(cons);
}
public string DFToSqlString(SDefaultConstraint df)
{
df.defaultvalue = df.defaultvalue.Trim();
if (df.defaultvalue.StartsWith("(") && df.defaultvalue.EndsWith(")"))
{
while (df.defaultvalue.StartsWith("(") && df.defaultvalue.EndsWith(")"))
{
if (df.defaultvalue.Length == 2)
{
df.defaultvalue = "";
}
else
{
df.defaultvalue = df.defaultvalue.Substring(1, df.defaultvalue.Length - 2);
}
if (df.defaultvalue.IndexOf(')') < df.defaultvalue.IndexOf('('))
{
df.defaultvalue = "(" + df.defaultvalue + ")";
break;
}
}
}
if (df.defaultvalue.ToLower() == "getdate()")
{
df.defaultvalue = "'1900-01-01 00:00:00'";
}
if (!(df.defaultvalue.StartsWith("'") && df.defaultvalue.EndsWith("'")))
{
df.defaultvalue = "'" + df.defaultvalue + "'";
}
if (df.defaultvalue.Substring(0, df.defaultvalue.Length - 2) == "" && !df.column.filedtype.canemptydefault)
{
df.defaultvalue = "'" + df.column.filedtype.defaultvalue + "'";
}
// alter table表名 alter column字段名 set default默认值;
string template = "alter table {0} alter column {1} set default {2} ;";
string s = string.Format(template, df.column.table.name, df.column.name, df.defaultvalue);
return(s);
}