StrataFrame Forum

Dynamically connect to different databases

http://forum.strataframe.net/Topic9902.aspx

By Michel Levy - 7/2/2007

Hi,

I'm a french absolute beginner in both StrataFrame and VB.Net (but 23 years with Clipper and Fox)...

Data are on several databases on SQL Server (DataBcuv1, DataBcuv2, ...) and global parameters are in ParaBcuv on the same Server. The most important of these is the correspondence between "folder" ID and database name (first folder may be linked to databcuv4, if my customer requires it).

In my main form, I launch (in its shown method) a form "choixdossier" as showdialog(), to chooses working folder (i.e. database), and I return a connect string in a property of my main form.

I use one BO for this first connection to parabcuv, and I need to dynamically re-set the connection strings of all BO to databcuv which are used in all forms in that application (all BO are in a BO library referenced in final application).
But method SetDataSources is in appmain.vb, not in my main form :-(

How can I do that?

By Greg McGuffey - 7/2/2007

You can manually drop the data source and re-add it with a different connection string any time you need to change it. Just be sure to use the same name (the default is an empty string).
By Michel Levy - 7/2/2007

Thanks for your reply, Greg, but...

I don't want to reset datasource in design mode, but let the user reset it using the form "choixdossier".

form choixdossier sends "numdossier" to form ecran, and in form ecran, I have

Public Property numDossier() As Integer

Get
Return _numDossier
End Get

Set(ByVal value As Integer)
' remplissage du BO parabcuv
Me.ParaBcuv1.getdossiers_bydossier()
Me.ParaBcuv1.Refresh()
_numDossier = value
_strtoSeek = "dossier = " & value.ToString
If Me.ParaBcuv1.Seek(_strtoSeek) Then

Me.ParaBcuv1.numdossier = value
Me.ParaBcuv1.Refresh()
Me.nomDossier = Me.ParaBcuv1.societe.Trim

Dim loDataCnx As New Data.SqlClient.SqlConnectionStringBuilder(MicroFour.StrataFrame.Data.DataBasics.DataSources(0).ConnectionString)

loDataCnx.InitialCatalog = Me.ParaBcuv1.databcuve

MicroFour.StrataFrame.Data.DataBasics.DataSources(0).ConnectionString = loDataCnx.ToString

Me.connectStringdata = loDataCnx.ToString

End If

End Set

End Property

--

How can I use that connectstringdata in BO at runtime?
By Greg McGuffey - 7/2/2007

Just to make sure you understand what I'm taking about, I'm saying that the BO is configured to use a specific datasource, accesses via a key. The default key is an empty string. What I'm saying is that if you drop the actual datasource and re-add it with a new connection string, the BO never needs to be changed. This can be done at runtime easily by just dropping the current data source and adding the new one:



' Drop existing

DataLayer.DataSources.Remove("")

' Add a new one with the new connection string

DataLayer.DataSources.Add(New SqlDataSourceItem("","Data Source=myServer;Initial Catalog=....")





Now any BO that uses the default datasource (key = "") will get/pull data from a new data source, no changes to BO needed and done at runtime.


By Michel Levy - 7/2/2007

OK Greg,

now I understand what you were talking about (all my apologies for misunderstanding, but english is not my native language), and it seems to be exactly what I was looking for.

I'll try it tomorrow (it's 9 PM here, time to stop working Doze ) and give a feedback.

Thanks

By Greg McGuffey - 7/2/2007

nowI understand what you were talking about (all my apologies for misunderstanding, but english is not my native language), and it seems to be exactly what I was looking for.




No worries. I had all sorts of misunderstandings like this when I started. I think you're doing great!



I'll try it tomorrow (it's 9 PM here, time to stop working Doze ) and give a feedback.




Bon Chance et Bon Nuit!



(and that's about it for my français)
By StrataFrame Team - 7/3/2007

Yep, Greg is absolutely right (once again Wink).  If you change the ConnectionString property on a DataSource, you're not guaranteed that it's going to be updated throughout the application (it gets cached off for speed so it doesn't need to be looked up every time).  So, instead, remove the DataSource and re-add it to the DataSources collection, just like in Greg's sample, and all of the business objects will update and start using the new data source.
By Michel Levy - 7/3/2007

Greg and Ben,

Thanks a lot ! you've driven me exactly on the road I needed, so now I can move forward by myself.

Thanks again

By Greg McGuffey - 7/3/2007

Excellent!
By Michel Levy - 7/4/2007

Hi,

no, not so excellent Crying

There's something I don't understand:

Database is a read-only property, and in BO Mapper (when i build my BO and then generate my dll which is referenced in my app), Database is "hard coded" as I can read in datacuves.designer.

''' <summary>

''' The name of the database that contains the table to which this business object is mapped.

''' </summary>

''' <remarks></remarks>

Public Overrides ReadOnly Property Database() As String

Get

Return "databcuv1"

End Get

End Property

and even if I fill initial catalog with "databcuv2" in the connectionstring of the new sqldatasourceitem (as you said), database is still the old one, not the new.

Sure I've missed something, but what?? and where??

By Trent L. Taylor - 7/5/2007

Sure I've missed something, but what?? and where??

This property is no longer used and was left in for backward compatability.  The data source is the ONLY thing that determines from where the data will be pulled at run-time.  Ths database property is generated using the name of the database from which you pulled the schema....but it ends there and is not actually used at run-time (or any other time for that matter) Smile