Comparing tables without primary key using API and VB.NET

jbravohjbravoh Posts: 9
I´m using API SQL Data Compare 5 with VB.NET 2003 and I don´t find a method to select another key due to the tables that I´m using don´t have primary keys.

Using product SQL Data Compare 5 I saw that I can to select a field as key.

How can I do this using API?

Regards,

Jorge Bravo

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Jorge,

    You can do pick a comparison key using the FieldMappings class:
    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].[bar]"]);
    	tableMapping.FieldMappings.Add(new FieldMapping(tableMapping.Obj1.Fields["fooID"],tableMapping.Obj2.Fields["barID"],FieldMappingStatus.Success));
    tableMapping.RefreshMappingStatus();
    	mappings.Add(tableMapping);
    	//compare databases
    

    This code maps table 'foo' to table 'bar' and maps the 'fooID' to the 'barID' to create a comparison key.
  • Thanks, Brian.

    I´m testing but dont works.

    I have two tables with same structure but the first have two rows and second, three: script don´t make nothing.

    My code is this:

    Dim db1 As New Database
    Dim db2 As New Database
    db1.RegisterForDataCompare(New ConnectionProperties(MyServer1, MyDB1, myAccount1, myPass1), Options.Default)
    db2.RegisterForDataCompare(New ConnectionProperties(MyServer2, MyDB2, myAccount2, myPass2), Options.Default)
    Dim mappings As New TableMappings
    Dim tableMapping As TableMapping = CType(mappings.Join(db1.Tables("[dbo].[foo]"), db2.Tables("[dbo].[bar]")), TableMapping)
    tableMapping.FieldMappings.Add(New FieldMapping(tableMapping.Obj1.Fields("WidgetID"), tableMapping.Obj2.Fields("WidgetID"), FieldMappingStatus.Success))
    tableMapping.RefreshMappingStatus()
    mappings.Add(tableMapping)

    Dim session As New ComparisonSession
    session.CompareDatabases(db1, db2, mappings)
    Dim provider As New SqlProvider
    Dim block As ExecutionBlock
    Try
    block = provider.GetMigrationSQL(session, True)
    Dim executor As RedGate.SQL.Shared.BlockExecutor = New BlockExecutor
    executor.ExecuteBlock(block, MyServer2, MyDB2, False, myAccount2, myPass2)
    Catch ex As SystemException
    Dim err As String
    err = "Error: " & ex.Message
    Finally
    block = provider.Block
    If (TypeOf block Is ExecutionBlock) Then
    block.Dispose()
    End If
    End Try
    session.Dispose()
    db1.Dispose()
    db2.Dispose()
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    The example is only for comapring a single table. And it does look like you have left the table names from the example, so naturally the foo and bar need to be changed to the name of your table.
  • It´s clear, Brian. But don´t works.

    My code is like:

    Dim db1 As New Database
    Dim db2 As New Database
    db1.RegisterForDataCompare(New ConnectionProperties(MyServer1, MyDB1, myAccount1, myPass1), Options.Default)
    db2.RegisterForDataCompare(New ConnectionProperties(MyServer2, MyDB2, myAccount2, myPass2), Options.Default)
    Dim mappings As New TableMappings
    Dim tableMapping As TableMapping = CType(mappings.Join(db1.Tables("[dbo].[myTable1]"), db2.Tables("[dbo].[myTable2]")), TableMapping)
    tableMapping.FieldMappings.Add(New FieldMapping(tableMapping.Obj1.Fields("myField1"), tableMapping.Obj2.Fields("myField2"), FieldMappingStatus.Success))
    tableMapping.RefreshMappingStatus()
    mappings.Add(tableMapping)

    Dim session As New ComparisonSession
    session.CompareDatabases(db1, db2, mappings)
    Dim provider As New SqlProvider
    Dim block As ExecutionBlock
    Try
    block = provider.GetMigrationSQL(session, True)
    Dim executor As RedGate.SQL.Shared.BlockExecutor = New BlockExecutor
    executor.ExecuteBlock(block, MyServer2, MyDB2, False, myAccount2, myPass2)
    Catch ex As SystemException
    Dim err As String
    err = "Error: " & ex.Message
    Finally
    block = provider.Block
    If (TypeOf block Is ExecutionBlock) Then
    block.Dispose()
    End If
    End Try
    session.Dispose()
    db1.Dispose()
    db2.Dispose()
    This is your code translated to VB.NET and don´t works to me.

    Regards,

    Jorge
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    I'm sorry, but there is not enough information here to say why it fails. Since I've tested my code in real-life, I'd say it must me something to do with the schema more than the program code.

    You may start by checking the status of the mapping; after mappings.RefreshMappingStatus() try checking mapping.Status and see if the mapping had failed.
  • Before tableMapping.RefreshMappingStatus() and after I get status: UnableToCompare.

    I continue trying...
  • Ok, really works!

    Adding this:

    Dim mappings As New TableMappings
    '-It´s is new
    mappings.CreateMappings(db1.Tables, db2.Tables)
    Dim cant, hay As Integer
    hay = mappings.Count
    For cant = hay - 1 To 0 Step -1
    mappings.RemoveAt(cant)
    Next
    '
    ...
    tableMapping.FieldMappings.Add(New FieldMapping(tableMapping.Obj1.Fields("myField1"), tableMapping.Obj2.Fields("myField2"), FieldMappingStatus.Success))

    '----and adding this
    tableMapping.MatchingMappings.Clear()
    tableMapping.MatchingMappings.Add(tableMapping.FieldMappings("myField1"))

    I get a Status = Success.

    Regards,

    Jorge Bravo
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Jorge,

    I see that you use CreateMappings and then unmap everything. It's a bit odd, but it's fine if it works!

    I'll see if maybe my code just doesn't work in VB for some reason.
  • You´re right, Brian.

    I my past test I get a Status=UnableToCompare but, modifying code and including CreateMappings and unmapping all works well.

    Now, I tested my code commenting those lines (without CreateMappings...) and code works very fine.

    I don´t know... maybe I have a trouble in my machine...

    Really your code works fine!

    Regards,

    Jorge Bravo
Sign In or Register to comment.