|
Group: Forum Members
Posts: 16,
Visits: 102
|
Quick question and I apologize if this has already been answered.
Example - DDT has been used to create a new SQL database. Now I've made changes in the DDT and I apply those changes to my SQL database.
Question - How does the DDT keep track of updates, revisions, etc? More important, How can I check that outside the DDT in my Strataframe/VB.net app?
I'm working on a merge application that will take a database and import it's data into another database and I want to be able to check that the databases are a matching schema and then store that schema version number into a config database.
Thanks
Matt
|
|
Group: StrataFrame MVPs
Posts: 1.9K,
Visits: 21K
|
Hi Matt.
The DDT will keep records of every change you make into your project, which you will find in the "Object Name History" node in the DDT treeview.
So, once you have created your database and deployed it to a SQL Server, you can make as many changes as you like in the DDT project and once you go back to deploy it again it will bring it to your last version, no matter what version your actual database were left on.
|
|
Group: StrataFrame Users
Posts: 2.4K,
Visits: 23K
|
Hi Matt, There is nothing in the DDT that will help you verifying development and production database versions. The good news is that I have done that with suggestions from Trent, Ivan and Greg in the past, so I will try to explain what I have done: - Create your own table to keep track of production version, of course using the DDT. Mine has the following fields:
- PK (AutoInc)
- DatabaseVersion VARCHAR(16)
- ApplicationVersion VARCHAR(16)
- Somewhere in your application create a Constant variable DATABASEVERSION as String and there assign the version number as "1.0.1" or whatever value you want to start up with.
- In your AppMain.vb create a method that will check if the currently used database needs to be updated, I called mine CheckIfDatabaseNeedsToBeUpdated(), I will post the code below only for reference since I do many other things which I will not explain now like sending me an email if there is an error. The Basics.UpdateDatabase() is my function based on the DataMigration class to do the actual update of the database:
Private Shared Sub CheckIfDatabaseNeedsToBeUpdated()
Try
Using loBO As New Business.bizApplicationInfo
loBO.FillTopX(1)
If loBO.Count = 0 Then
loBO.NewRow()
loBO.Save()
End If
'-- Check if a database update is needed here
Dim newDatabaseVersion As Version = New Version(BuildInformation.DatabaseVersion)
Dim currentDatabaseVersion As Version = New Version(loBO.DatabaseVersion)
If newDatabaseVersion > currentDatabaseVersion Then
'-- Before updating, make sure nobody is using the database except ourself.
If Basics.UpdateDatabase(True, BuildInformation.DataDeploymentOptions) = DialogResult.OK Then
Business.bizApplicationInfo.UpdateDatabaseVersion()
'-- Check to run any specific stored procedure here
Select Case newDatabaseVersion
Case New Version("4.2.0")
frmSplashScreen.SetMessage("Please wait, updating database to version 4.2.0...")
If loBO.RunUpdateStoredProcedure("dbo.spUpdate2-0") Then
frmSplashScreen.SetMessage("Update 4.2.0 completed...")
End If
End Select
End If
End If
End Using
Catch ex As System.Data.SqlClient.SqlException
'-- If the ApplicationInfo table or Database does not exist, then update the structure anyway.
If ex.Number = 208 OrElse ex.Number = 4060 Then
Dim connectedToServer As String = Basics.GetApplicationActiveConnectionStringTitle(ConnectionManager.ApplicationKey, "")
If MicroFour.StrataFrame.Messaging.MessageForm.ShowMessageByKey("Database Update Service", connectedToServer) = MicroFour.StrataFrame.Messaging.MessageFunctionType.Button1 Then ' Update Now
If Basics.UpdateDatabase(True, Enumerations.DataDeploymentOptions.StandardDeployment) = DialogResult.OK Then
Business.bizApplicationInfo.UpdateDatabaseVersion()
Else
End
End If
Else
End
End If
Else
SendErrorMessageViaEmail(ex)
Dim sb As New StringBuilder
sb.AppendLine("There was an error getting the Database version information.")
sb.AppendFormat("Error #: {0}", ex.Number)
sb.AppendLine()
sb.AppendLine(ex.Message)
Basics.DisplayMessage("Data Error", sb.ToString, MicroFour.StrataFrame.Messaging.MessagingIcon.StopSign, MicroFour.StrataFrame.Messaging.MessagingSounds.ErrorSound)
'-- Load the connection string to give a chance to change it.
Basics.SetDatabaseConnection()
End
End If
Catch ex As Exception
SendErrorMessageViaEmail(ex)
Dim sb As New StringBuilder
sb.AppendLine("There was an error getting the Database version information.")
sb.AppendFormat("Error #: {0}", "")
sb.AppendLine()
sb.AppendLine(ex.Message)
Basics.DisplayMessage("Data Error", sb.ToString, MicroFour.StrataFrame.Messaging.MessagingIcon.StopSign, MicroFour.StrataFrame.Messaging.MessagingSounds.ErrorSound)
End
End Try
End Sub
- In the AppMain.InitApplication() method, add a call to CheckIfDatabaseNeedsToBeUpdated(). I found this method a very good place to make this call, but you have to try in your application and change the place to call this method if needed.
Enjoy and good luck!!!
Edhy Rijo
|
|
Group: Forum Members
Posts: 16,
Visits: 102
|
Thank you both for your quick responses.
Ivan,
This app is to merge 2 existing databases. Those databases would have been created using the DDT schema in another app. This "wizard" will not deploy or update the databases in any way. It would only need to validate that the schemas match between the 2 databases.
Edhy,
This is more along the lines of what I need to do although I was not expexting to create an additional table. I don't have any problems implementing something along these lines and I appreciate you sharing the code to give me a jump start.
There is a way that the DDT is looking at the target database to determine that the database needs or does not need updating. Is there no actual value stored into the SQL database to check against? How does the DDT qualify an update is needed? What does it look at on the receiving end to say you are behind a version or you are fine?
Thanks again,
Matt
|
|
Group: StrataFrame Users
Posts: 2.4K,
Visits: 23K
|
Matt Tabone (3/28/2012)
There is a way that the DDT is looking at the target database to determine that the database needs or does not need updating. Is there no actual value stored into the SQL database to check against? How does the DDT qualify an update is needed? What does it look at on the receiving end to say you are behind a version or you are fine?As Ivan explained, the "Object Name History" will be used to keep the database updated as needed. My implementation is more for me (the developer) to have control on when to execute the update instead of running the update all the time just to make sure the database is in sync. I have some applications generating millions of records and a simple field change can take several minutes to update and re-create indexes, so I need to have control using my custom version, also sometimes I don't have access to customer servers so I needed the process to be fully automatic and self aware of when it needed to execute the data update, of course customer is responsible of backing up the data before an update will be applied.
Edhy Rijo
|
|
Group: StrataFrame MVPs
Posts: 1.9K,
Visits: 21K
|
Have a look at the SF source code and check the DatabaseMigrator class. If you go through it you will see how the DDT package is used for deployment.
|