public bool AlterTableForSchema(TableSchema schema)
{
List<string> actualColumns = GetTableColumns(schema.Name);
if (actualColumns.Count == 0)
{
Trace.TraceError("ERROR, {0} has no columns, but it does exist???", schema.Name);
return false;
}
// check which of the columns exist in the schema, but not in the database: these must be added now
List<ColumnSchema> columnsToAdd = new List<ColumnSchema>();
foreach (ColumnSchema c in schema.Columns)
{
if (!actualColumns.Contains(c.Name.ToLower()))
{
ExecuteNonQuery(string.Format("alter table {0} add column {1} {2};", schema.Name, c.Name, GetNativeSqlType(c.Type)));
}
}
// check which columns exist in the database, but no longer in the schema
foreach (string cn in actualColumns)
{
bool found = false;
foreach (ColumnSchema c in schema.Columns)
{
if (c.Name.ToLower() == cn)
{
found = true;
break;
}
}
if (!found)
{
ExecuteNonQuery(string.Format("alter table {0} drop column {1};", schema.Name, cn));
}
}
return true;
}
/// <summary>
/// Create this table
/// </summary>
/// <param name="schema"></param>
public virtual void CreateTableFromSchema(TableSchema schema)
{
try
{
string stmt = GetCreateTableStatementFromSchema(schema);
if (stmt != null)
{
ExecuteNonQuery(stmt);
}
}
catch (DbException)
{
// do nothing if this fails - this is normally not a severe condition...
}
}
public virtual string GetCreateTableStatementFromSchema(TableSchema schema)
{
StringBuilder result = new StringBuilder();
result.AppendFormat("create table {0} (\n", schema.Name);
bool first = true;
foreach (ColumnSchema column in schema.Columns)
{
if (first)
first = false;
else
result.Append(",\n");
result.AppendFormat("{0} {1}", column.Name, GetNativeSqlType(column.Type));
}
result.Append(");");
return result.ToString();
}
