Comparing tables without primary key using API and VB.NET

• 
  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.

  Brian Donahue August 09, 2007 11:44

  - 0 +
• 
  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()

  jbravoh August 09, 2007 13:23

  - 0 +
• 
  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.

  Brian Donahue August 10, 2007 10:48

  - 0 +
• 
  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

  jbravoh August 13, 2007 09:53

  - 0 +
• 
  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.

  Brian Donahue August 13, 2007 11:21

  - 0 +
• 
  Before tableMapping.RefreshMappingStatus() and after I get status: UnableToCompare.
  
  I continue trying...

  jbravoh August 13, 2007 15:26

  - 0 +
• 
  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

  jbravoh August 13, 2007 16:33

  - 0 +
• 
  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.

  Brian Donahue August 14, 2007 04:18

  - 0 +
• 
  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

  jbravoh August 14, 2007 10:17

  - 0 +

Add comment

Please sign in to leave a comment.