| Author |
Message |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
 Posted: Wed Jan 09, 2008 3:11 pm Post subject: |
 |
|
This one redisters for Data Compare on two threads, and also reports the registration process' current task. Since this is all asynchronous, you would more than likely see things happening in a mixed-up order.
| Code: |
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQL.Shared
Imports RedGate.SQLDataCompare.Engine
Imports System.Threading
Public Class Form1
Dim db1 As New Database
Dim db2 As New Database
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
db1.ConnectionProperties = New ConnectionProperties("localhost", "WidgetLive")
db2.ConnectionProperties = New ConnectionProperties("localhost", "WidgetDev")
db1.Status = New StatusEventHandler(AddressOf Me.Status1CallBack)
db2.Status = New StatusEventHandler(AddressOf Me.Status2CallBack)
TextBox1.Text = TextBox1.Text & "Registering " & db1.ConnectionProperties.DatabaseName & " on server " & db1.ConnectionProperties.ServerName & vbCrLf
Application.DoEvents()
'Start the async operation to register the database and call RegisterDatabase1Complete when complete
ThreadPool.QueueUserWorkItem(AddressOf register1, db1)
TextBox1.Text = TextBox1.Text & "Registering " & db2.ConnectionProperties.DatabaseName & " on server " & db2.ConnectionProperties.ServerName & vbCrLf
Application.DoEvents()
ThreadPool.QueueUserWorkItem(AddressOf register2, db2)
'Start the async operation to register the database and call RegisterDatabase2Complete when complete
End Sub
Sub register1(ByVal db As Object)
Dim serv As DatabaseServices = New DatabaseServices()
serv.BeginRegisterForDataCompare(CType(db, Database), AddressOf RegisterDatabase1Complete, Nothing)
End Sub
Sub register2(ByVal db As Object)
Dim serv As DatabaseServices = New DatabaseServices()
serv.BeginRegisterForDataCompare(CType(db, Database), AddressOf RegisterDatabase2Complete, Nothing)
End Sub
'This will run when db1 is done registering
Private Sub RegisterDatabase1Complete(ByVal r As IAsyncResult)
OutputMessage("Registered " & db1.ConnectionProperties.DatabaseName & " on server " & db1.ConnectionProperties.ServerName & vbCrLf)
End Sub
'This will run when db2 is done registering
Private Sub RegisterDatabase2Complete(ByVal r As IAsyncResult)
OutputMessage("Registered " & db2.ConnectionProperties.DatabaseName & " on server " & db2.ConnectionProperties.ServerName & vbCrLf)
End Sub
Private Delegate Sub OutputMessageDelegate(ByVal msg As String)
Private Sub OutputMessage(ByVal msg As String)
If Me.InvokeRequired Then
' if operating on a thread, invoke a delegate
' on the UI thread.
Dim omd As OutputMessageDelegate = _
New OutputMessageDelegate(AddressOf OutputMessage)
Dim arx As IAsyncResult = Me.BeginInvoke( _
omd, New Object() {msg})
Me.EndInvoke(arx)
Return
End If
TextBox1.AppendText(msg & Chr(13) & Chr(10))
End Sub
Public Sub Status1CallBack(ByVal o As Object, ByVal e As StatusEventArgs)
If e.Percentage = -1 Then OutputMessage(db1.ConnectionProperties.DatabaseName & ": " & e.Message)
End Sub
Public Sub Status2CallBack(ByVal o As Object, ByVal e As StatusEventArgs)
If e.Percentage = -1 Then OutputMessage(db2.ConnectionProperties.DatabaseName & ": " & e.Message)
End Sub
End Class
' Class for running the register methods asynchronously
Public Class Registration
Implements IAsyncResult
Dim _userState As Object
Dim _waitHandle As ManualResetEvent = New ManualResetEvent(False)
Dim _database As Database
Dim _callback As AsyncCallback
#Region "IAsyncResult Members"
Public ReadOnly Property AsyncState() As Object Implements System.IAsyncResult.AsyncState
Get
Return _userState
End Get
End Property
Public ReadOnly Property AsyncWaitHandle() As System.Threading.WaitHandle Implements System.IAsyncResult.AsyncWaitHandle
Get
Return _waitHandle
End Get
End Property
Public ReadOnly Property CompletedSynchronously() As Boolean Implements System.IAsyncResult.CompletedSynchronously
Get
Return False
End Get
End Property
Public ReadOnly Property IsCompleted() As Boolean Implements System.IAsyncResult.IsCompleted
Get
Return _waitHandle.WaitOne(0, False)
End Get
End Property
#End Region
Sub Begin(ByVal db As Database, ByVal callback As AsyncCallback, ByVal state As Object)
_database = db
_callback = callback
_userState = state
_database.RegisterForDataCompare(_database.ConnectionProperties, Options.Default)
_waitHandle.Set()
If Not _callback Is Nothing Then _callback(Me)
End Sub
Function Finish() As Database
AsyncWaitHandle.WaitOne()
AsyncWaitHandle.Close()
Finish = _database
End Function
End Class
'Async methods class -- this is called from your main thread
Public Class DatabaseServices
Public Function BeginRegisterForDataCompare(ByVal d As Database, ByVal callback As AsyncCallback, ByVal userState As Object) As IAsyncResult
Dim reg As Registration = New Registration
reg.Begin(d, callback, userState)
BeginRegisterForDataCompare = reg
End Function
Public Function EndRegisterForDataCompare(ByVal r As IAsyncResult) As Database
EndRegisterForDataCompare = CType(r, Registration).Finish()
End Function
End Class
|
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
 |
Rawden
Joined: 07 Nov 2006
Posts: 23
|
| Posted: Wed Jan 09, 2008 4:52 pm Post subject: |
|
|
Well, I'm officially out of my depth. But I got it working. Thanks.
I orignally had it wrapped up in a class. Would I need to change much on your code to get it working in a class. It's moaning about no InvokeRequired method etc. |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
| Posted: Wed Jan 09, 2008 5:22 pm Post subject: |
|
|
I am not sure what you mean -- I've got quite a few classes in my example. If you wanted to make it more readable, you could remove each class to its' own code file, but it works better having all of the classes in one file so I can post the code to the forum.
InvokeRequired should be a property of your Windows Form -- it's the thing that prevents other threads from being able to update controls on the form. It should be built-in to the Form class, so I don't know why you would get a complaint that it's not implemented.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Rawden
Joined: 07 Nov 2006
Posts: 23
|
| Posted: Wed Jan 09, 2008 6:05 pm Post subject: |
|
|
Sorry I think I was just being an idiot. What I meant was, I had it in a re-useable class module. I put the OutputMessageDelegate, OutputMessage and the StatusCallBacks on the form and it's working now.
I have just two more questions:
1. What do I need to do to add in the other methods? (I am playing around with this now, so I may actually crack it with my limited knowledge anyway.)
and
2. If I want to give the user the ability to cancel the compare at any stage (now that you have sorted the user input side of things so they can actually press the button) would this cause problems if I were to call the CancelOperation methods? |
|
| Back to top |
|
|
Rawden
Joined: 07 Nov 2006
Posts: 23
|
| Posted: Thu Jan 10, 2008 4:54 pm Post subject: |
|
|
Hi Brian,
I'm slowly going through this asynchronous stuff and getting my head round it. 
Can you tell me if (and where) the Registration.Finish() function is called from. I can't seem to see a reference to it.
(or rather the DatabaseServices.EndRegisterForDataCompare function actually.)
Thanks,
Rawden. |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
| Posted: Thu Jan 10, 2008 8:18 pm Post subject: |
|
|
Hi Rawden,
You're right; the Finish methods never seem to run. Plus the DatabaseServices class seems to be entirely redundant. Here is an updated version that seems to be doing the job... The form has since grown a progress bar and a label; hopefully this doesn't introduce too much confusion...
| Code: |
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQL.Shared
Imports RedGate.SQLDataCompare.Engine
Imports System.Threading
Public Class Form1
Dim db1 As New Database
Dim db2 As New Database
Dim mappings As SchemaMappings = New SchemaMappings
Dim currentDatabase As Database
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
db1.ConnectionProperties = New ConnectionProperties("localhost", "WidgetLive")
db2.ConnectionProperties = New ConnectionProperties("localhost", "WidgetDev")
currentDatabase = db1
Application.DoEvents()
'Start the async operation to register the database and call RegisterDatabase1Complete when complete
ThreadPool.QueueUserWorkItem(AddressOf register, db1)
End Sub
Sub register(ByVal db As Object)
Dim reg As New AsyncDatabaseMethods
CType(db, Database).Status = New StatusEventHandler(AddressOf Me.StatusCallBack)
UpdateProgressLabel("Register " & currentDatabase.ConnectionProperties.DatabaseName)
If currentDatabase.ConnectionProperties.ServerName = db1.ConnectionProperties.ServerName And currentDatabase.ConnectionProperties.DatabaseName = db1.ConnectionProperties.DatabaseName Then
reg.BeginRegisterDatabase(CType(db, Database), AddressOf RegisterDatabase1Complete, Nothing)
Else : reg.BeginRegisterDatabase(CType(db, Database), AddressOf RegisterDatabase2Complete, Nothing)
End If
End Sub
Sub script(ByVal session As Object)
Dim scr As New AsyncDatabaseMethods
scr.BeginGenerateScript(CType(session, ComparisonSession), True, AddressOf GenerateScriptComplete, Nothing)
End Sub
Sub map(ByVal dummy As Object)
Dim comp As AsyncDatabaseMethods = New AsyncDatabaseMethods
UpdateProgressLabel("Mapping tables")
OutputMessage("Mapping Tables")
mappings.Status = New StatusEventHandler(AddressOf StatusCallBack)
comp.BeginCreateDefaultMappings(db1, db2, mappings, AddressOf TableMappingsComplete, Nothing)
End Sub
Sub compare(ByVal dummy As Object)
Dim comp As AsyncDatabaseMethods = New AsyncDatabaseMethods
UpdateProgressLabel("Comparing " & db1.ConnectionProperties.DatabaseName & " and " & db2.ConnectionProperties.DatabaseName)
OutputMessage("Comparing " & db1.ConnectionProperties.DatabaseName & " and " & db2.ConnectionProperties.DatabaseName)
Application.DoEvents()
comp.BeginComparison(db1, db2, mappings, SessionSettings.Default, AddressOf CompareDataComplete, Nothing)
End Sub
'This will run when db1 is done registering
Private Sub RegisterDatabase1Complete(ByVal r As IAsyncResult)
UpdateProgressBar(0)
OutputMessage("Registered " & db1.ConnectionProperties.DatabaseName & " on server " & db1.ConnectionProperties.ServerName & vbCrLf)
currentDatabase = db2
CType(r, AsyncDatabaseMethods).FinishRegisterDatabase()
ThreadPool.QueueUserWorkItem(AddressOf register, db2)
End Sub
'This will run when db2 is done registering
Private Sub RegisterDatabase2Complete(ByVal r As IAsyncResult)
UpdateProgressBar(0)
OutputMessage("Registered " & db2.ConnectionProperties.DatabaseName & " on server " & db2.ConnectionProperties.ServerName & vbCrLf)
CType(r, AsyncDatabaseMethods).FinishRegisterDatabase()
Dim mappings As SchemaMappings = New SchemaMappings
ThreadPool.QueueUserWorkItem(AddressOf map, Nothing)
End Sub
Private Sub TableMappingsComplete(ByVal r As IAsyncResult)
UpdateProgressBar(0)
CType(r, AsyncDatabaseMethods).FinishCreateDefaultMappings()
ThreadPool.QueueUserWorkItem(AddressOf compare, Nothing)
End Sub
Private Sub CompareDataComplete(ByVal r As IAsyncResult)
Dim session As ComparisonSession = CType(r, AsyncDatabaseMethods).FinishComparison()
UpdateProgressBar(0)
OutputMessage("Generating SQL Script...")
ThreadPool.QueueUserWorkItem(AddressOf script, session)
End Sub
Private Sub GenerateScriptComplete(ByVal r As IAsyncResult)
Dim script As String = CType(r, AsyncDatabaseMethods).FinishGenerateScript()
UpdateProgressBar(0)
UpdateProgressLabel("Idle")
OutputMessage(script)
End Sub
#Region "Form Control update delegates"
Private Delegate Sub OutputMessageDelegate(ByVal msg As String)
Private Sub OutputMessage(ByVal msg As String)
If Me.InvokeRequired Then
' if operating on a thread, invoke a delegate
' on the UI thread.
Dim omd As OutputMessageDelegate = _
New OutputMessageDelegate(AddressOf OutputMessage)
Dim arx As IAsyncResult = Me.BeginInvoke( _
omd, New Object() {msg})
Me.EndInvoke(arx)
Return
End If
TextBox1.AppendText(msg & Chr(13) & Chr(10))
End Sub
Private Delegate Sub UpdateProgressLabelDelegate(ByVal msg As String)
Private Sub UpdateProgressLabel(ByVal msg As String)
If Me.InvokeRequired Then
' if operating on a thread, invoke a delegate
' on the UI thread.
Dim upl As UpdateProgressLabelDelegate = _
New UpdateProgressLabelDelegate(AddressOf UpdateProgressLabel)
Dim arx As IAsyncResult = Me.BeginInvoke( _
upl, New Object() {msg})
Me.EndInvoke(arx)
Return
End If
Label1.Text = msg
End Sub
Private Delegate Sub UpdateProgressBarDelegate(ByVal percentage As Integer)
Private Sub UpdateProgressBar(ByVal percentage As Integer)
If Me.InvokeRequired Then
' if operating on a thread, invoke a delegate
' on the UI thread.
Dim upl As UpdateProgressBarDelegate = _
New UpdateProgressBarDelegate(AddressOf UpdateProgressBar)
Dim arx As IAsyncResult = Me.BeginInvoke( _
upl, New Object() {percentage})
Me.EndInvoke(arx)
Return
End If
ProgressBar1.Increment(percentage)
ProgressBar1.Update()
End Sub
#End Region
Public Sub StatusCallBack(ByVal o As Object, ByVal e As StatusEventArgs)
If e.Percentage = -1 Then
If Not currentDatabase Is Nothing Then OutputMessage(currentDatabase.ConnectionProperties.DatabaseName & ": " & e.Message)
Else : UpdateProgressBar(e.Percentage - ProgressBar1.Value)
End If
End Sub
End Class
Public Class AsyncDatabaseMethods
Implements IAsyncResult
Dim _userState As Object
Dim _waitHandle As ManualResetEvent = New ManualResetEvent(False)
Dim _database1 As Database
Dim _database2 As Database
Dim _database As Database
Dim _session As ComparisonSession = New ComparisonSession
Dim _script As String
Dim _sessionsettings As SessionSettings
Dim _mappings As SchemaMappings
Dim _callback As AsyncCallback
#Region "IAsyncResult Members"
Public ReadOnly Property AsyncState() As Object Implements System.IAsyncResult.AsyncState
Get
Return _userState
End Get
End Property
Public ReadOnly Property AsyncWaitHandle() As System.Threading.WaitHandle Implements System.IAsyncResult.AsyncWaitHandle
Get
Return _waitHandle
End Get
End Property
Public ReadOnly Property CompletedSynchronously() As Boolean Implements System.IAsyncResult.CompletedSynchronously
Get
Return False
End Get
End Property
Public ReadOnly Property IsCompleted() As Boolean Implements System.IAsyncResult.IsCompleted
Get
Return _waitHandle.WaitOne(0, False)
End Get
End Property
#End Region
Sub BeginComparison(ByVal db1 As Database, ByVal db2 As Database, ByVal mappings As SchemaMappings, ByVal settings As SessionSettings, ByVal callback As AsyncCallback, ByVal state As Object)
'_database1 = db1
'_database2 = db2
_callback = callback
_userState = state
_mappings = mappings
_session.CompareDatabases(db1, db2, mappings, settings)
_waitHandle.Set()
If Not _callback Is Nothing Then _callback(Me)
End Sub
Function FinishComparison() As ComparisonSession
AsyncWaitHandle.WaitOne()
AsyncWaitHandle.Close()
FinishComparison = _session
End Function
Sub BeginCreateDefaultMappings(ByVal db1 As Database, ByVal db2 As Database, ByVal mappings As SchemaMappings, ByVal callback As AsyncCallback, ByVal state As Object)
_callback = callback
_userState = state
_mappings = mappings
_mappings.CreateMappings(db1, db2)
_waitHandle.Set()
If Not _callback Is Nothing Then _callback(Me)
End Sub
Function FinishCreateDefaultMappings() As SchemaMappings
AsyncWaitHandle.WaitOne()
AsyncWaitHandle.Close()
FinishCreateDefaultMappings = _mappings
End Function
Sub BeginRegisterDatabase(ByVal db As Database, ByVal callback As AsyncCallback, ByVal state As Object)
_database = db
_callback = callback
_userState = state
_database.RegisterForDataCompare(_database.ConnectionProperties, Options.Default)
_waitHandle.Set()
If Not _callback Is Nothing Then _callback(Me)
End Sub
Function FinishRegisterDatabase() As Database
AsyncWaitHandle.WaitOne()
AsyncWaitHandle.Close()
FinishRegisterDatabase = _database
End Function
Sub BeginGenerateScript(ByVal session As ComparisonSession, ByVal runontwo As Boolean, ByVal callback As AsyncCallback, ByVal state As Object)
Dim provider As New SqlProvider
_callback = callback
_userState = state
Dim block As ExecutionBlock = provider.GetMigrationSQL(session, runontwo)
_script = block.GetString()
_waitHandle.Set()
If Not _callback Is Nothing Then _callback(Me)
End Sub
Function FinishGenerateScript() As String
AsyncWaitHandle.WaitOne()
AsyncWaitHandle.Close()
FinishGenerateScript = _script
End Function
End Class |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Rawden
Joined: 07 Nov 2006
Posts: 23
|
| Posted: Fri Jan 11, 2008 11:50 am Post subject: |
|
|
Brilliant! Thanks a lot Brian. It works really well.
I've changed the databases and session objects etc. to go in ByRef and that way when I cancel the operation on the form, they cancel in the Async methods as well. I can then also do away with the AsyncDatabaseMethod variables and the Finish functions.
 |
|
| Back to top |
|
|
|
