Click here to Skip to main content
15,943,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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;
}
}
Posted

1 solution

See my improved version of "SMO Tutorial 3 of n": SMO Tutorial 3 of n - Scripting[^]
This includes routines to generate Keys, indexes and constraints.
 
Share this answer
 
Comments
Yonathan1111 20-Jul-16 3:25am    
Hi all,

I appreciate all your efforts to help me through, just to be clear, is that even possible to create the missing column at destination with the position with the original position that comes from source database?

For example: table1 (source) has firstId at 2nd position, and table1 (destination) has three fields or columns, and I wanted to create the missing column (firstId from source) into table1 (destination) at exactly 2nd position.

Thanks
RickZeeland 20-Jul-16 3:33am    
Normally the order of fields will be no problem, but you can run into problems when you do things like bulk export/import to CSV files where the order matters.
If you really want the same order of fields I would suggest creating a new table, create the fields in the correct order, copy all the data, delete the original table and rename the table.
Yonathan1111 20-Jul-16 4:33am    
Dear RickZeeland,

That helps a lot, my understanding is now that it is somehow hard to recreate a field at destination with its original order from source. I have tried but it did not work out, please look at it, and deleting the respective table and recreating it is not an option.
e.g.:
tblDestination.Columns.Add(missingColumn, 2); // the number 2 is the position, however either it creates me an exception, or creates it at last order.

Order is mandatory, that is why I'm insisting.

Thanks a lot.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900