Multiple table mapping

si_hesi_he Posts: 25
edited September 7, 2007 6:55AM in SQL Toolkit Previous Versions
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 :wink: I am constantly referring to them already.

Thanks

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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
    &#123;
    public class TableMappingExample
    &#123;
    public void RunExample&#40;&#41;
    &#123;
    Database db1=new Database&#40;&#41;;
    Database db2=new Database&#40;&#41;;
    ComparisonSession session=new ComparisonSession&#40;&#41;;
    SqlProvider prov=new SqlProvider&#40;&#41;;
    
    db1.RegisterForDataCompare&#40;new ConnectionProperties&#40;"Server\\DB", "DB1"&#41;&#41;;
    db2.RegisterForDataCompare&#40;new ConnectionProperties&#40;"SERVER\\DB", "DB2"&#41;&#41;;                                    
    // Create the mappings between a certain table
    	TableMappings mappings = new TableMappings&#40;&#41;;
    TableMapping tableMapping = &#40;TableMapping&#41;mappings.Join&#40;db1.Tables&#91;"&#91;dbo&#93;.&#91;foo&#93;"&#93;, db2.Tables&#91;"&#91;dbo&#93;.&#91;foo&#93;"&#93;&#41;;
    TableMapping tableMapping2 = &#40;TableMapping&#41;mappings.Join&#40;db1.Tables&#91;"&#91;dbo&#93;.&#91;bar&#93;"&#93;, db2.Tables&#91;"&#91;dbo&#93;.&#91;bar&#93;"&#93;&#41;;
    	mappings.Add&#40;tableMapping&#41;;
                    mappings.Add&#40;tableMapping2&#41;;
    	//compare databases
    	session.CompareDatabases&#40;db1, db2, mappings&#41;;	
    	ExecutionBlock eb=prov.GetMigrationSQL&#40;session, true&#41;;
    	Console.WriteLine&#40;eb.GetString&#40;&#41;&#41;;
    	//cleanup
    	session.Dispose&#40;&#41;;
    	db1.Dispose&#40;&#41;;
    	db2.Dispose&#40;&#41;;
    	&#125;
        &#125;
    &#125;
    
    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.
  • 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&#40;db1.Tables&#40;"&#91;dbo&#93;.&#91;SERVER&#93;"&#41;, db2.Tables&#40;"&#91;dbo&#93;.&#91;SERVER&#93;"&#41;&#41;
    mappings.Join&#40;db1.Tables&#40;"&#91;dbo&#93;.TABLE1"&#41;, db2.Tables&#40;"&#91;dbo&#93;.TABLE1"&#41;&#41;
    mappings.Join&#40;db1.Tables&#40;"&#91;dbo&#93;.TABLE2"&#41;, db2.Tables&#40;"&#91;dbo&#93;.TABLE2"&#41;&#41;
    mappings.Join&#40;db1.Tables&#40;"&#91;dbo&#93;.TABLE3"&#41;, db2.Tables&#40;"&#91;dbo&#93;.TABLE3"&#41;&#41;
    ...
    finally
    session.CompareDatabases&#40;db1, db2, mappings&#41; 
    

    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
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • 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
  • mappings.Join&#40;db1.Tables&#40;"&#91;dbo&#93;.TABLE1"&#41;, db2.Tables&#40;"&#91;dbo&#93;.TABLE1"&#41;&#41;.WhereClause = new WhereClause&#40;"i &gt; 1"&#41;
    or...
    mappings.Join&#40;db1.Tables&#40;"&#91;dbo&#93;.TABLE1"&#41;, db2.Tables&#40;"&#91;dbo&#93;.TABLE1"&#41;&#41;.WhereClause = new WhereClause&#40;"i &gt; 1", "i &gt; 505"&#41;
    

    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. ;)
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • It's not loving that syntax. The exact error is -

    'WhereClause' is not a member of 'RedGate.SQLCompare.Engine.mapping'
  • &#40;&#40;TableMapping&#41;mappings.Join&#40;db1.Tables&#40;"&#91;dbo&#93;.TABLE1"&#41;, db2.Tables&#40;"&#91;dbo&#93;.TABLE1"&#41;&#41;&#41;.WhereClause = new WhereClause&#40;"i &gt; 1", "i &gt; 505"&#41;
    


    *takes a bow*
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • 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]"))
  • 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.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
  • 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: :wink:
  • 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.
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
Sign In or Register to comment.