Comparing tables without primary key using API and VB.NET

jbravoh

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

Brian Donahue

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.

jbravoh

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 Donahue

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.

jbravoh

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 Donahue

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.

jbravoh

Before tableMapping.RefreshMappingStatus() and after I get status: UnableToCompare.

I continue trying...

jbravoh

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 Donahue

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.

jbravoh

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