Multiple table mapping

si_he

Hi

I am new to this so please excuse my ignorance. I have set up the following code to compare one tables data -

Dim session As New ComparisonSession

Dim db1 As New Database
Dim db2 As New Database

db1.RegisterForDataCompare(New ConnectionProperties("4444", "4444", "4444", "4444"), Options.Default)
db2.RegisterForDataCompare(New ConnectionProperties("4444", "4444", "4444", "4444"), Options.Default)

Dim mappings As New TableMappings

Dim tableMapping As TableMapping = mappings.Join(db1.Tables("[dbo].[SERVER"), db2.Tables("[dbo].[SERVER]"))

session.CompareDatabases(db1, db2, mappings)

Dim difference As TableDifference = session.TableDifferences("[dbo].[SERVER]")
Dim row As row
For Each row In difference.ResultsStore
Dim field As FieldPair
Dim i As Int32 = 0
'Console.WriteLine("Row {0} type {1}", row.Index, row.Type.ToString())
For Each field In difference.ResultsStore.Fields
Dim field1 As Int32 = field.OrdinalInResults1
Dim field2 As Int32 = field.OrdinalInResults2

If (field1 <> field2) Then
Dim value1 As Object = row.Values(field1)
Dim value2 As Object = row.Values(field2)
If (value1 Is Nothing) Then
value1 = "NULL"
End If
If (value2 Is Nothing) Then
value2 = "NULL"
End If
'If row.FieldDifferent(i) Then
'Console.WriteLine("{0}:{1} <> {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
'Else
'Console.WriteLine("{0}:{1} == {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
'End If
Else
Dim value As Object = row.Values(field1)
'Console.WriteLine("*{0}:{1}", field.Field(False).Name, value.ToString())
End If
i += 1
Next
Next
Dim o As New EngineDataCompareOptions
Dim provider As New SqlProvider
provider.Options.SqlOptions = SqlOptions.None
Dim block As ExecutionBlock = provider.GetMigrationSQL(session, True)
Console.WriteLine("Migration SQL:")
Console.WriteLine(block.GetString())

session.Dispose()
block.Dispose()
db1.Dispose()
db2.Dispose()

The production SQL server has hundreds of tables, however, I only need to compare about 20 of these with the SQL subscriber machine.

I'm unsure as to what needs to be used to acheive this, any help would be greatly appreciated. Please provide code examples if possible rather than pointing me to help files I am constantly referring to them already.

Thanks

Brian Donahue

Hi,

You can explicitly specify tables that you want to compare data using the SQL Toolkit. You specify tables by joining them and adding the resulting new TableMapping class to the list of mappings, as you've already done with your single table: (sorry I don't have a VB example handy)

using System;
using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQL.Shared;

namespace RedGate.SQLDataCompare.ExampleTests
{
public class TableMappingExample
{
public void RunExample()
{
Database db1=new Database();
Database db2=new Database();
ComparisonSession session=new ComparisonSession();
SqlProvider prov=new SqlProvider();

db1.RegisterForDataCompare(new ConnectionProperties("Server\\DB", "DB1"));
db2.RegisterForDataCompare(new ConnectionProperties("SERVER\\DB", "DB2"));                                    
// Create the mappings between a certain table
	TableMappings mappings = new TableMappings();
TableMapping tableMapping = (TableMapping)mappings.Join(db1.Tables["[dbo].[foo]"], db2.Tables["[dbo].[foo]"]);
TableMapping tableMapping2 = (TableMapping)mappings.Join(db1.Tables["[dbo].[bar]"], db2.Tables["[dbo].[bar]"]);
	mappings.Add(tableMapping);
                mappings.Add(tableMapping2);
	//compare databases
	session.CompareDatabases(db1, db2, mappings);	
	ExecutionBlock eb=prov.GetMigrationSQL(session, true);
	Console.WriteLine(eb.GetString());
	//cleanup
	session.Dispose();
	db1.Dispose();
	db2.Dispose();
	}
    }
}

What may be beneficial would be to write a function that accepts a Hashtable or Array of table names to make the code a bit more reusable.

richardjm

What you're after is either using the
SchemaMappings.CreateMappings call and exclude the tables that you don't want or continue doing...

mappings.Join(db1.Tables("[dbo].[SERVER]"), db2.Tables("[dbo].[SERVER]"))
mappings.Join(db1.Tables("[dbo].TABLE1"), db2.Tables("[dbo].TABLE1"))
mappings.Join(db1.Tables("[dbo].TABLE2"), db2.Tables("[dbo].TABLE2"))
mappings.Join(db1.Tables("[dbo].TABLE3"), db2.Tables("[dbo].TABLE3"))
...
finally
session.CompareDatabases(db1, db2, mappings) 

for all the different tables you wish to compare. You can then loop through the objects in your TableDifferences or do whatever you want from there.

HTH

si_he

Thats just what I was after thanks.

One final question if I wanted to include a where clause in the tableMapping for say one or more tables in the group, how would I do this?

Thanks again

richardjm

mappings.Join(db1.Tables("[dbo].TABLE1"), db2.Tables("[dbo].TABLE1")).WhereClause = new WhereClause("i > 1")
or...
mappings.Join(db1.Tables("[dbo].TABLE1"), db2.Tables("[dbo].TABLE1")).WhereClause = new WhereClause("i > 1", "i > 505")

Should work for you, of course you could use a variable TableMapping and then do the assign. But I come from a C background so I like to be succinct.

si_he

It's not loving that syntax. The exact error is -

'WhereClause' is not a member of 'RedGate.SQLCompare.Engine.mapping'

richardjm

((TableMapping)mappings.Join(db1.Tables("[dbo].TABLE1"), db2.Tables("[dbo].TABLE1"))).WhereClause = new WhereClause("i > 1", "i > 505")

*takes a bow*

si_he

Sorry, I'm obviously missing something blatantly obvious here, your example gives a syntax error -

Dim mappings As New TableMappings

Dim tableMapping As TableMapping = mappings.Join(db1.Tables("[dbo].[DOMAIN]"), db2.Tables("[dbo].[DOMAIN]"))
mappings.Join(db1.Tables("[dbo].[SERVER]"), db2.Tables("[dbo].[SERVER]"))
mappings.Join(db1.Tables("[dbo].[FTP_ACCOUNT]"), db2.Tables("[dbo].[FTP_ACCOUNT]"))

richardjm

Evidently I was trying to be too succint for VB - I didn't test it .

There is example code (in C# and VB) in the SQL Toolkit sample files

Basically you need to cast the Mapping result of the Join operation to a TableMapping and then assign to the Where property.

That's the mistake I made - the property is called Where rather than WhereClause.

si_he

Many thanks for that, finaly got it working.

...don't suppose you know how to omit certain fields in a table from the compare? :roll:

richardjm

From TableMapping - you now look into the property FieldMappings of the TableMapping and set Include Property of the FieldMapping you don't want Include to be false.