Red Gate forums :: View topic - sample VB.NET code for sql data compare with backups
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

sample VB.NET code for sql data compare with backups

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
RBohannon



Joined: 02 Nov 2007
Posts: 18

PostPosted: Thu Jan 10, 2008 9:32 pm    Post subject: sample VB.NET code for sql data compare with backups Reply with quote

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
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 394
Location: Red Gate Software Ltd

PostPosted: Fri Jan 11, 2008 9:38 am    Post subject: Reply with quote

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 Smile ).

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
View user's profile Send private message Send e-mail
RBohannon



Joined: 02 Nov 2007
Posts: 18

PostPosted: Fri Jan 11, 2008 3:59 pm    Post subject: Reply with quote

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
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 394
Location: Red Gate Software Ltd

PostPosted: Fri Jan 11, 2008 4:07 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
RBohannon



Joined: 02 Nov 2007
Posts: 18

PostPosted: Fri Jan 11, 2008 4:16 pm    Post subject: Reply with quote

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
View user's profile Send private message
richardjm



Joined: 20 May 2005
Posts: 394
Location: Red Gate Software Ltd

PostPosted: Fri Jan 11, 2008 4:43 pm    Post subject: Reply with quote

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)

Smile - Got that Friday feeling.
_________________
Richard Mitchell
Project Manager
Red Gate Software Ltd
Back to top
View user's profile Send private message Send e-mail
elizas



Joined: 17 Mar 2010
Posts: 1

PostPosted: Wed Mar 17, 2010 11:25 am    Post subject: Backup SQL Database Through Code In VB.NET Reply with quote

Using the SQL SMO object we can easily take backup of SQL database through code.


http://www.mindfiresolutions.com/Backup-SQL-Database-Through-Code-In-VBNET-846.php
_________________
Cheers,
Eliza
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group