| Author |
Message |
RBohannon
Joined: 02 Nov 2007
Posts: 18
|
 Posted: Thu Jan 10, 2008 9:32 pm Post subject: sample VB.NET code for sql data compare with backups |
 |
|
Hello,
Do you have any sample code for calling SDC 6 from VB when comparing two backup files? The output will be a SQL change script. I'm using VB.NET 2.0 and SQL Server 2005.
Thank you. |
|
| Back to top |
|
 |
richardjm
Joined: 20 May 2005
Posts: 359
Location: Red Gate Software Ltd
|
| Posted: Fri Jan 11, 2008 9:38 am Post subject: |
|
|
You're in luck, we're just about to release 6.1 of SQL Data Compare and we've updated the code samples to give you code to do this sort of thing...
You will need a few extra references in your project...
| Code: |
.NET dependencies
RedGate.BackupReader.BackupReader.dll (needed to compare against any database backup file)
RedGate.BackupReader.SqbReader.dll (if you intend to compare against SQL Backup files)
RedGate.SQLCompare.ASTParser.dll (a new component of the SQL Compare engine - necessary)
RedGate.SQLCompare.Rewriter.dll (another new component of the SQL Compare engine - necessary)
RedGate.SQLCompare.BackupReader.dll (needed to compare against any database backup file)
System.Data.SQLite.dll (needed for comparison to any database backup file)
Non .NET dependencies
RedGate.BackupReader.CryptoHelper.dll (only needed to compare against Encrypted SQL Backup files)
rglz.dll (needed for SQL Backup Compression level 1 (or compressed temporary files - 6.1 only))
zlib1.dll (needed for SQL Backup Compression levels 2 & 3)
|
And here's the example code I've written, the only real difference between comparing to a live database and a backup database is the method of registration - apart from that they can be used the same ( except of course synchronizing to a backup  ).
| Code: |
Option Explicit On
Imports RedGate.SQLCompare.BackupReader
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLDataCompare.Engine
Imports RedGate.SQLDataCompare.Engine.ResultsStore
Imports RedGate.SQL.Shared
Public Class BackupComparisonExample
Sub RunExample()
Dim session As New ComparisonSession
'register the databases for comparison
Dim backupDB As New BackupDatabase
Dim liveDB As New Database
Try
' First register the backup file
Console.WriteLine("Registering backup")
backupDB.Status = New StatusEventHandler(AddressOf StatusCallback)
backupDB.RegisterForDataCompare(New String() {"c:\\widgetdev.bak"}, Nothing)
' Secondly register the live database
Console.WriteLine("Registering live database")
liveDB.Status = New StatusEventHandler(AddressOf StatusCallback)
liveDB.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.Default)
Dim mappings As New SchemaMappings
mappings.CreateMappings(backupDB, liveDB)
'compare the databases
session.CompareDatabases(backupDB, liveDB, mappings)
Dim mapping As TableMapping
For Each mapping In mappings.TableMappings
Dim table As ViewTableSuperClass = mapping.Obj1
Dim difference As TableDifference = session.TableDifferences(table.FullyQualifiedName)
' Any tables that couldn't be compared we don't output the results
If difference Is Nothing Then
Continue For
End If
Dim row As Row
For Each row In difference.ResultsStore 'loop through all the rows
If (row.Type <> row.RowType.Same) Then 'go through the non same records
Dim field As FieldPair
Dim i As Int32 = 0
Console.WriteLine("{0} Row {1} type {2}", table.FullyQualifiedName, row.Index, row.Type.ToString())
For Each field In difference.ResultsStore.Fields
'work out where about in the results the field data is stored
'if we were comparing identical records, or records present in one
'database but not the other then we would not need to
'use the OrdinalInResults1 and OrdinalInResults2 properties
'but just OrdinalInResults
Dim field1 As Int32 = field.OrdinalInResults1
Dim field2 As Int32 = field.OrdinalInResults2
If (field1 <> field2) Then
'get the values
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
'this is part of the unique index we are comparing on
Dim value As Object = row.Values(field1)
Console.WriteLine("*{0}:{1}", field.Field(False).Name, value.ToString())
End If
i += 1
Next
End If
Next
Next
Finally
'dispose of the objects
session.Dispose()
backupDB.Dispose()
liveDB.Dispose()
End Try
End Sub
Sub StatusCallback(ByVal sender As Object, ByVal e As StatusEventArgs)
If Not (e.Message Is Nothing) Then
Console.WriteLine(e.Message)
End If
If e.Percentage <> -1 Then
Console.WriteLine("{0}%", e.Percentage)
End If
End Sub
End Class
|
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Last edited by richardjm on Fri Jan 11, 2008 4:45 pm; edited 3 times in total |
|
| Back to top |
|
|
RBohannon
Joined: 02 Nov 2007
Posts: 18
|
| Posted: Fri Jan 11, 2008 3:59 pm Post subject: |
|
|
Thanks. Your code snippet will work with 6.0, right? I'd like to put this code into production next week.
When is 6.1 coming out? |
|
| Back to top |
|
|
richardjm
Joined: 20 May 2005
Posts: 359
Location: Red Gate Software Ltd
|
| Posted: Fri Jan 11, 2008 4:07 pm Post subject: |
|
|
Should do the API is compatible. We're aiming for 6.1 to be out in the next few weeks just fixing a few issues on it and doing final testing.
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd |
|
| Back to top |
|
|
RBohannon
Joined: 02 Nov 2007
Posts: 18
|
| Posted: Fri Jan 11, 2008 4:16 pm Post subject: |
|
|
Great. I'll integrate your code into my program today and let you know how it goes. Thanks for the help.
If I have issues in the future, does Redgate have a public code library where I can find sample code? I couldn't find one. |
|
| Back to top |
|
|
richardjm
Joined: 20 May 2005
Posts: 359
Location: Red Gate Software Ltd
|
| Posted: Fri Jan 11, 2008 4:43 pm Post subject: |
|
|
We have mainly this forum you're looking at and the API documentation. There's also a zip file containing toolkit samples which you can download via the support area of the website.
Also I forgot another dll (Rob has just berated me for being forgetful)
| Code: |
zlib1.dll (not .NET, needed for SQL Backup Compression levels 2 & 3)
|
I've updated the dlls in the post above (SQLite.dll *is* .NET) - D'Oh)
- Got that Friday feeling.
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd |
|
| Back to top |
|
|
elizas
Joined: 17 Mar 2010
Posts: 1
|
|
| Back to top |
|
|
|
