Dear all,
I have been developing a simple wizard application which maps object's schema from source to a destination database. And, I used SMO (Microsoft.SqlServer.Management.Smo) library to do the mappings. It creates for tables, views, storedprocedures, and even fields/columns, if they are not existed or missed at destination database.
The problem I encountered is that, how to move or create a column field at destination table with all its properties found from source database?
I wanted to include the columns constraints, position in table, and other default properties that come from source table.
Thanks in Advance
What I have tried:
Table tb = dbSource.Tables[field.Parent];
Microsoft.SqlServer.Management.Smo.Column column = tb.Columns[field.Name];
Table tblDest = dbDestination.Tables[tb.Name];
if (column != null & tblDest != null)
{
try
{
Microsoft.SqlServer.Management.Smo.Column newColumn =
new Microsoft.SqlServer.Management.Smo.Column(tblDest, column.Name,
column.DataType)
{
DefaultSchema = column.DefaultSchema,
};
tblDest.Columns.Add(newColumn);
}
catch (Exception e)
{
}
try
{
//persist changes back to database
tblDest.Alter();
}
catch (SmoException ex)
{
throw ex;
}
}