Setting custom mappings on multiple tables

JVWay

(I posted this in the SQL compare forum with no response. Though there's little action in this forum I thought I'd try here.)

I have a database with multiple tables that need custom mappings.
Most of the tables in the database do have legitimate mappings so
I do a SchemaMappings mappings = new SchemaMappings();
mappings.CreateMappings(fromDB, toDB);

But there are still 14 tables that need custom mappings.

I don't see how I can add multiple custom mappings to a SchemaMappings object. I'd really rather not have to create table mappings for all 47 tables in the database.

I'll keep working on it but any suggestions appreciated.

Thanks,
Jerry

richardjm

Try using the TableMappings.CreateMappings(fromDB.Tables, toDB.Tables) method directly or use the TableMappings property of the relevant SchemaMapping.

e.g. mySchemaMapping.TableMappings.Join(fromDB.Tables["[dbo].[table1]"],toDB.Tables["[dbo].[table2]"]);

Not sure I've got the syntax bang on but hopefully that'll give you the idea.

JVWay

Thanks,

But I've still got a bit of a problem. This is the code I'm using.

SchemaMappings mappings = new SchemaMappings();
mappings.CreateMappings(fromDB, toDB);
TableMapping tableMapping = (TableMapping)mappings.TableMappings.Join(fromDB.Tables["[dbo].[deleted_responses]"], toDB.Tables["[dbo].[deleted_responses]"]);
mappings.TableMappings.Add(tableMapping.FieldMappings["responseid"]);

When I try to add the FieldMapping, which I understand to define the custom mapping, I get the following error:
TableMappings only supports TableMapping objects

My problem with using the TableMappings method directly is that I would need to do that for 47 tables in the database and I'd like a more efficient method if it's available.
So, am I missing something about adding the FieldMapping to the SchemaMapping?

Thanks for your help on this

richardjm wrote:

Try using the TableMappings.CreateMappings(fromDB.Tables, toDB.Tables) method directly or use the TableMappings property of the relevant SchemaMapping.

e.g. mySchemaMapping.TableMappings.Join(fromDB.Tables["[dbo].[table1]"],toDB.Tables["[dbo].[table2]"]);

Not sure I've got the syntax bang on but hopefully that'll give you the idea.

richardjm

There is the property FieldMappings on a TableMapping class which you can Join() with fields from the table from either side of the database.

And to anticipate your next question there is MatchingMappings on a TableMapping object which contains the fields which are used to compare the tables. If there isn't a primary key or unique index that can be used by default you can set a custom comparison key by adding FieldMapping objects for that TableMapping into the MatchingMappings. After you've added the fields you want be sure to call RefreshMappingStatus so that you can confirm comparison can take place. You can also use a specific index via UseIndexForMapping on the TableMapping, probably doesn't hurt to call RefreshMappingStatus after UseIndexForMapping either.

The full help for the TableMapping class can be found here