Synchronizing Schema - Live Database, Scripts folder VB

From RedGateWiki

Back to SQL Comprison SDK

These examples show how you can synchronize one schema to another and how you can synchronize folders containing SQL scripts.

---

Assembly references:

• RedGate.SQLCompare.Engine.dll
• RedGate.Shared.Utils.dll
• RedGate.Shared.SQL.dll

Synchronising Databases

Demonstration that synchronises all objects in WidgetProduction with those currently contained in WidgetStaging

1. Imports System
2. Imports RedGate.Shared.SQL
3. Imports RedGate.Shared.SQL.ExecutionBlock
4. Imports RedGate.SQLCompare.Engine
5.  
6. Module Module1
7.         Sub Main(ByVal args As String())
8.                 Using widgetStaging As New Database(), widgetProduction As New Database()
9.                         ' Retrieve the schema information for the two databases
10.                         widgetStaging.Register(New ConnectionProperties(".", "WidgetStaging"), Options.[Default])
11.                         widgetProduction.Register(New ConnectionProperties(".", "WidgetProduction"), Options.[Default])
12.  
13.                         ' Compare widgetStaging to widgetProduction. Comparing in this order makes WidgetProduction the second database
14.                         Dim stagingVsProduction As Differences = widgetStaging.CompareWith(widgetProduction, Options.[Default])
15.  
16.                         ' Select the differences to include in the synchronization. In this case, we're using all differences.
17.                         For Each difference As Difference In stagingVsProduction
18.                                 difference.Selected = True
19.                         Next
20.  
21.                         Dim work As New Work()
22.  
23.                         ' Calculate the work to do using sensible default options
24.                         ' The script is to be run on WidgetProduction so the runOnTwo parameter is true
25.                         work.BuildFromDifferences(stagingVsProduction, Options.[Default], True)
26.  
27.                         ' We can now access the messages and warnings
28.                         Console.WriteLine("Messages:")
29.  
30.                         For Each message As Message In work.Messages
31.                                 Console.WriteLine(message.Text)
32.                         Next
33.  
34.                         Console.WriteLine("Warnings:")
35.  
36.                         For Each message As Message In work.Warnings
37.                                 Console.WriteLine(message.Text)
38.                         Next
39.  
40.                         ' Disposing the execution block when it's not needed any more is important to ensure
41.                         ' that all the temporary files are cleaned up
42.                         Using block As ExecutionBlock.ExecutionBlock = work.ExecutionBlock
43.                                 ' Display the SQL used to synchronize
44.                                 Console.WriteLine("SQL to synchronize:")
45.                                 Console.WriteLine(block.GetString())
46.  
47.                                 ' Finally, use a BlockExecutor to run the SQL against the WidgetProduction database
48.                                 ' BlockExecutor executor = new BlockExecutor();
49.                                 ' executor.ExecuteBlock(block, ".", "WidgetLive");
50.  
51.                         End Using
52.                 End Using
53.  
54.                 Console.WriteLine("Press [Enter]")
55.                 Console.ReadLine()
56.         End Sub
57. End Module

Synchronizing scripts folders

1. Imports System
2. Imports RedGate.Shared.SQL
3. Imports RedGate.SQLCompare.Engine
4. Imports RedGate.SQLCompare.Engine.ReadFromFolder
5.  
6. ''' <summary>
7. ''' Demonstration that synchronizes the C:\WidgetDevScripts scripts folder against the
8. ''' C:\WidgetProductionScripts scripts folder.
9. ''' </summary>
10. ''' <remarks>
11. ''' The scripts folder for this demonstration can be generated by running
12. ''' the SaveScriptsFolder demo.
13. ''' </remarks>
14. Module Module1
15.         ''' <summary>
16.         ''' The folder that this demonstration will synchronize against
17.         ''' </summary>
18.         Private Const c_WidgetProductionFolder As String = "C:\WidgetProductionScripts"
19.         Private Const c_WidgetDevFolder As String = "C:\WidgetDevScripts"
20.  
21.  
22.         Sub Main(ByVal args As String())
23.                 Using widgetDevScripts As New Database(), widgetProductionScripts As New Database()
24.                         ' Read the schema for the WidgetStaging database
25.                         widgetDevScripts.Register(c_WidgetDevFolder, Nothing, Options.[Default])
26.  
27.                         ' Establish the schema from the scripts stored in the WidgetProduction scripts folder
28.                         ' Passing in null for the database information parameter causes SQL Compare to read the
29.                         ' XML file supplied in the folder.
30.                         widgetProductionScripts.Register(c_WidgetProductionFolder, Nothing, Options.[Default])
31.  
32.                         ' Compare the database against the scripts.
33.                         ' Comparing in this order makes the WidgetProduction scripts folder the second database
34.                         Dim stagingVsProduction As Differences = widgetDevScripts.CompareWith(widgetProductionScripts, Options.[Default])
35.  
36.                         ' Select all of the differences for synchronization
37.                         For Each difference As Difference In stagingVsProduction
38.                                 difference.Selected = True
39.                         Next
40.  
41.                         ' Use the default folder structure for any new files
42.                         Dim folderOptions As New WriteToFileOptions()
43.  
44.                         ' Calculate the work to do using sensible default options
45.                         ' The WidgetProductionScripts folder is to be updated, so the runOnTwo parameter is true
46.                         Dim work As New Work()
47.                         work.BuildFromDifferences(stagingVsProduction, Options.[Default], True, folderOptions)
48.  
49.                         ' We can now access the messages and warnings
50.                         Console.WriteLine("Messages:")
51.  
52.                         For Each message As Message In work.Messages
53.                                 Console.WriteLine(message.Text)
54.                         Next
55.  
56.                         Console.WriteLine("Warnings:")
57.  
58.                         For Each message As Message In work.Warnings
59.                                 Console.WriteLine(message.Text)
60.                         Next
61.  
62.                         ' Disposing the execution block when it's not needed any more is important to ensure
63.                         ' that all the temporary files are cleaned up
64.                         Using block As ExecutionBlock.ExecutionBlock = work.ExecutionBlock
65.                                 ' Display the SQL used to synchronize
66.                                 Console.WriteLine("SQL to synchronize:")
67.  
68.                                 ' Finally, use a BlockExecutor to run the SQL against the WidgetProduction database
69.                                 ' BlockExecutor executor = new BlockExecutor();
70.                                 ' executor.ExecuteBlock(block, ".", "WidgetLive");
71.                                 Console.WriteLine(block.GetString())
72.                         End Using
73.                         ' and we can also perform the synchronization now
74.  
75.                         ' Retrieve the list of actions to perform to synchronize the database against the scripts folder
76.                         Dim scriptActions As ScriptSynchronizationActions = work.ScriptSynchronizationActions
77.  
78.                         ' Display the files that will be modified during the synchronization
79.                         Console.WriteLine("Script files that will be modified:")
80.                         For Each action As ScriptSynchronizationAction In scriptActions.SynchronizationActions.Values
81.                                 Console.WriteLine("{0}", action.FileName)
82.                         Next
83.  
84.                         ' Check for any read-only files
85.                         Try
86.                                 scriptActions.Check()
87.                         Catch generatedExceptionName As SqlCompareException
88.                                 ' An exception here means that there are read-only files in the scripts folder.
89.                                 ' We can continue, but they will be forcibly overwritten. For the purposes of
90.                                 ' this demonstration we choose to replace any read-only files, so we ignore
91.                                 ' the exception.
92.  
93.  
94.                         End Try
95.                         ' Synchronize the scripts folder, overwriting any read-only files
96.                         scriptActions.Execute()
97.                 End Using
98.  
99.                 Console.WriteLine("Press [Enter]")
100.                 Console.ReadLine()
101.         End Sub
102. End Module