By Chan - 12/21/2008
Hi,
I have questions as below:
1. How could I allow user to decide database name during DDT deployment?
2. I have some view that join tables from local and remote database. The remote database name could be vary among customer even though it is has the same structure. How could I tell DDT at runtime so that it will "change" my view defination to map to the remote database?
Please advice.
Thank you
|
By Trent L. Taylor - 12/22/2008
1. How could I allow user to decide database name during DDT deployment? Well, there are a number of ways to do this. In our medical application, we have a custom installer (see the Database Installer Sample for an idea about this) where we deploy our structures. During our installation, we check to see if this is a new install or another instance. If this is a new instance, we ask them for an instance name where we then tack on the instance name to the database (i.e. MyNewInstance_MyDatabaseName). You have 100% control of the database name if using the DatabaseMigrator class. The second option would be to use the standard MDDeployMain dialog (also shown in the database installer sample) where there is a screen that let's the user enter any name they would like. But in either case, you can change the name to be whatever you like. The DatabaseMigrator class accepts an array with all of the names that will be deployed to...so it then just becomes a matter of you asking the end-user for the name at some point prior to the DDR deployment. 2. I have some view that join tables from local and remote database. The remote database name could be vary among customer even though it is has the same structure. How could I tell DDT at runtime so that it will "change" my view defination to map to the remote database? One thing you may consider here is using the Pre/Post Deployment Scripts. You can implement any logic you need within this scripts. This might be a good example of where you would want to do this. Let the DDT deploy as it would normally, then in a post deployment script, you could execute some script that updates the view to whichever remote database you need. That would be one way of doing this.
|
By Keith Chisarik - 12/22/2008
1) If using ASP.NET (I think you do from past posts) when you go to do #1, you will find that the database migrator class looks for a control/object that supports INVOKE as the third parameter, since web controls dont you will have to create your own as a workaround. I have a web application that allows users to do just this, name and create their own databases. I just add a _XXX identifier to them so I know which ones to update when it comes time.Public Class DBMigratorInvokeObject Implements System.ComponentModel.ISynchronizeInvoke Public Function BeginInvoke(ByVal method As System.Delegate, ByVal args() As Object) As System.IAsyncResult Implements System.ComponentModel.ISynchronizeInvoke.BeginInvoke Throw New NotSupportedException() End Function Public Function EndInvoke(ByVal result As System.IAsyncResult) As Object Implements System.ComponentModel.ISynchronizeInvoke.EndInvokeThrow New NotSupportedException()End FunctionPublic Function Invoke(ByVal method As System.Delegate, ByVal args() As Object) As Object Implements System.ComponentModel.ISynchronizeInvoke.InvokeReturn method.DynamicInvoke(args)End FunctionPublic ReadOnly Property InvokeRequired() As Boolean Implements System.ComponentModel.ISynchronizeInvoke.InvokeRequiredGetReturn TrueEnd GetEnd PropertyEnd Class
|
By Chan - 12/23/2008
Hi,
Do you meant any view/stored procedure that access remote database got to be maintain seperately? Then, in DDT pre/post script ask migrator class to execute it?
Thank you
|
By Trent L. Taylor - 12/27/2008
Well, if this remote DB is not managed by another DDT package, and you are only managing a fews views/sprocs on that remote database, then yes. You have several options, you can use the post deployment script to just create the views and sprocs or create another DDT package. I would probably just use the post deployment script if the remote DB wasn't deployed with a DDT project and I just needed to insert a few views ans sprocs. But if there were a lot of views and sprocs, and I had control of the remote database, then I would probably go ahead and create another DDT package.
|
By Chan - 1/21/2009
Hi,
After I re-read this thread for many times, I found that it is not what I want.
I have database (DB1) that used by my application. I need to join/left join data with remote database as well (DB_Remote). Therefore, I have created view/stored proc as below in DB1:
SELECT * FROM DB1.Table1 JOIN DB_Remote.Table1
I am using DDT to deploy DB1 database. Problem, the remote database name could be vary. It might not always called DB_Remote. How could I let DDT to ask for its database and replace the db name in my view/stored proc script?
Thank you
|
By Trent L. Taylor - 1/22/2009
That could be more difficult. I would probably create a view that is always going to e the same name and then within that view call a UDF or something along those lines to return the proper database name. There is no replacement logic wihtin a script in the DDT at the moment. This might be a good idea for an enhancement in the future, but at present, there is not any logic to go and replace internal text within the DDT sprocs, etc.
|
By Chan - 1/22/2009
Hi,
Is it possible to access DatabaseMigrator property within pre/post script? Or, any "hook" I can plug my custom logic in?
I was thinking to
1. Create a dummy database, without any table. It is just to let user to specify my "remote database" name.
2. In Pre/Post script/any hook, access the remote database name value specified by user from DatabaseMigrator class and replace my view/sp script by my own code.
Is it doable?
Thank you
|
By Trent L. Taylor - 1/22/2009
Yeah, that is not a bad idea on the dummy database. When you pick the database to execute in the Pre/Post script, it will execute on the renamed database. So if the remote database name was Chan and they entered Chan_New, then it would execute that script on Chan_New.All of this is giving me an idea for an enhancement which would be the introduction of profile variables that you could place within your sproc code and then those variables would be replaced at the time of deployment with the value you give it. This is actually a really good idea and would be a good application for this very thing. The bad part is that it isn't there yet  since the idea just came to me. But I will add this to the list and this is something that will most likely make it into the DDT. Good idea, Chan.
|
By Chan - 1/24/2009
Hi,
I would like to add-on is that, I found that profile script only support T-SQL, is it? Any possible to place .NET code?
Thank you
|
By Trent L. Taylor - 1/25/2009
Well, if you want to implement .NET, code, then I suggest creating a CLR stored procedure. This is what the CLR stored procedures are designed for and can make doing certain things much easier versus doing it all in T-SQL code.
|
By Chan - 1/25/2009
Hi,
Hmmm... the reason to use .NET code in my case is not to execute something at SQL Server. Instead, during DDT deployment process. In order words, I would like to have custom logic during deployment database.
Any ideas?
Thank you
|
By Trent L. Taylor - 1/26/2009
Well, at this point there is not anything like that from directly within the DDT. That is why this thread was filled with some good ideas to use DDT variables (which don't presently exist) so that you can manipulate this at the time of deployment and add your own logic. There are a lot of good ideas here. However, at the moment, you could still create a CLR sproc that is deployed using the standard DDT logic and then executed as a post deployment script. You could then use that CLR sproc to update any other elements that need updating.
|
By Chan - 2/7/2009
Trent L. Taylor (01/22/2009)
Yeah, that is not a bad idea on the dummy database. When you pick the database to execute in the Pre/Post script, it will execute on the renamed database. So if the remote database name was Chan and they entered Chan_New, then it would execute that script on Chan_New. All of this is giving me an idea for an enhancement which would be the introduction of profile variables that you could place within your sproc code and then those variables would be replaced at the time of deployment with the value you give it. This is actually a really good idea and would be a good application for this very thing. The bad part is that it isn't there yet since the idea just came to me. But I will add this to the list and this is something that will most likely make it into the DDT. Good idea, Chan.
Hi,
Just would like to add-on, this enhancement should not only add to pre/post profile script. Instead, it would be great if this logic can be apply to stored proc/view/trigger/udf and etc as well.
Thank you
|