|
When you want to change the connection string in run-time and also have the change saved when the program exits, you do this My.Settings.SetUserOverride("ConnectionString1", "My new string") .
When you want to change the connection string in run-time but do not want the change saved when the program exits, you do this My.Settings.Item("ConnectionString1") = "My new string" .
And if you use table adapters, then after you change a connection string in run-time you must also re-created the table adapter object in run-time (MyTableTableAdapter = New MyDataSetTableAdapters.MyTableTableAdapter) since the original table adapter will have the old connection string.
|
|
|
|
|
After downloading the code, I discovered that it differs from the code displayed in the article and I wondered if you can tell me which is correct.
In the article, the private sub userOverride_SettingsSaving has this line of code:
Me(userProperty) = Me(appProperty)
In the downloaded code, this subroutine has this line of code:
Me(appProperty) = Me(userProperty)
Which one is correct? I changed the downloaded code to match the article's code and didn't notice any difference in execution...both ways seem to work fine.
Also, I'm having a difficult time understanding when and where the userOverride_SettingsLoaded and userOverride_SettingsSaving subs fire. Can you explain?
Thanks,
dlcarp
|
|
|
|
|
SettingSaving has the line "Me(userProperty) = Me(appProperty) ".
SettingsLoaded has the line "Me(appProperty) = Me(userProperty) ".
The need to be like this and opposite from each other.
MySettings.SettingsLoaded is fired upon the first reference to My.Settings . MySettings.SettingsSaving is fired after the last form is closed and after My.Application.Shutdown .
|
|
|
|
|
Hey, mjmeans..thanks for the feedback!
|
|
|
|
|
Hi, im trying to change my connection string on runtime. I tried this code but its not working for me. I have a DBConnectionString with application scope and I made DBConnectionStringUser with User scope.
The value for DBConnectionString is "Data Source=SERVER1;Initial Catalog=DB1;Integrated Security=True". I have a user login form where user enters username and password and a combobox where he choose a database(DB1,DB2,DB3). This is what I put in cmbbox_selectedindexchanged:
sqlcon = "Server=SERVER1;DataBase=" & cmbbox.selectedtext & ";Integrated Security=SSPI;Connect Timeout=1"
My.Settings.SetUserOverride("DBConnectionString", "Data source= SERVER1;Initial Catalog=" & cmbbox.selectedtext & ";Integrated Security=true")
The problem is that it doesnt use the conn.string i put in SetUserOverride but it uses the value I set for DBConnectionStringUser in project properties.
Am I doing something wrong?
|
|
|
|
|
The first thing that comes to mind is that you might be seeing this in the IDE. Since each time you build your project, a new set of .config files are copied to the bin folder. So what you are experiencing might be related to your user overrides being overwritten by the IDE when you recompile.
The second possibility is that you might not have checked the box on the project properties to save the settings on exit.
So, try setting a couple of break points in SettingsLoaded and SettingsSaved and make sure that they are being called by the Windows Application Framework.
Next try running your app directly from windows explorer, browse to and run the exe file in the bin directory. then check the .config file and see it is saved the changes.
A third possibility that might cause this problem is if you are using clickonce deployment. The framework needs an explicit call to My.Settings.Upgrade() whenever a new version using clickonce is run, otherwise it looses the user settings from the previous version. The problem is that the program has no way of knowing whenther it is a newer version, and you dont want to repeatedly call Upgade(); so the a quick little workaround is to add Boolean user setting called "CallUpgrade" and set it to True. Then very early in your startup code run this little snippet:
If My.Settings.CallUpgrade Then<br />
My.Settings.Upgrade()<br />
My.Settings.CallUpgrade = False<br />
End If
|
|
|
|
|
It's working now. The problem was in calling the SetUserOverride after making the connection. It should go before making a connection.
Thank you!
modified on Thursday, June 19, 2008 9:24 AM
|
|
|
|
|
Well done Code-project, I find this article remarkable for two reasons:
1. That it is so clearly written, easy to implement, and works first time. Thank you very much.
2. That it is required at all, took me so long to find, and that Visual Studio was so poorly signposted to get here, and frankly that Microsoft requires such a workaround for the simple task of implementing my application on a different server with a different name to the development server. How opaque and poorly documented can a major development environment be, and how unable to accommodate such a simple and regular development requirement? Blown away.
So Remarkable: well done code-project, and thanks so much for your article.
Regards,
Pete
Web and Software Development in Cornwall - United Kingdom
|
|
|
|
|
You're welcome. And thank you for the kind words. Your second reason is exactly the reason I wrote this. My own product is developed to use SQL server and strongly typed datasets as well. All the other "workarounds" I found were so obtuse and difficult to maintain that I decided a better solution was needed.
When I thought of this idea, I decided it was too good to keep to myself; maybe even good enough to actually make some money on. But it was such a tiny piece of code that I really wouldn't feel right charging a consultancy fee for it. Maybe I can convice codeproject to kick me some of the ad revenue they generated from my article? Yeah... like that could ever happen. NOT.
Mark
|
|
|
|
|
I downloaded the code and plugged it right into my application and it worked on the first build. This is just what I needed. Now I can continue with my project. Thanks again.
|
|
|
|
|
Thanks alot for pointing me to the MySettings_SettingsLoaded - event.
Of course I was one of these "workaround-workers", who had to replace the connection in each damned command-object in whole the application - booo!
I'd like to remark, to evade the Readonly-restriction of a setting-Property may concern security matters.
(Usually I don't care about security - how secure is storing a connectionstring in a setting-property at all??)
To exceed this lack of security I'd like to introduce my way of dealing with connectionstrings:
I simply replace the DataDirectory with the current WorkingDirectory.
The WorkingDir again can be redirected by creating a File-Link and edit its WorkingDirectory-Property (and run the Application by clicking the Link).
So I can use the same Application.exe with different Databases, if I want.
What do you think about this hack?
Option Strict On
Option Explicit On
Imports System.Reflection
Imports System.Configuration
Namespace My
Partial Friend NotInheritable Class MySettings
Private Sub MySettings_SettingsLoaded( _
ByVal sender As Object, ByVal e As SettingsLoadedEventArgs) Handles Me.SettingsLoaded
Dim BindFlags As BindingFlags = _
BindingFlags.GetProperty Or _
BindingFlags.Public Or _
BindingFlags.Instance
For Each Prop As PropertyInfo In GetType(MySettings).GetProperties(BindFlags)
Dim Attr As SpecialSettingAttribute = DirectCast( _
Attribute.GetCustomAttribute(Prop, GetType(SpecialSettingAttribute)), _
SpecialSettingAttribute)
If Attr IsNot Nothing AndAlso Attr.SpecialSetting = SpecialSetting.ConnectionString Then
Dim DataDir As String = Environment.CurrentDirectory
Me(Prop.Name) = Prop.GetValue(Me, Nothing).ToString.Replace("|DataDirectory|", DataDir)
End If
Next
End Sub
End Class 'MySettings
End Namespace
|
|
|
|
|
Hmmm. At first glance, what comes to mind is that this approach seems would be for a database that is local to the specific application (especially when working with Vista). To share the database among different programs of the same vendor, then the application shared directory should be used. Also, be aware that Vista does not allow access to the programs installation directory at run time. All changeable files are saved in a special redirection folder. If you commonly save log files and what not in the applications current working directory, these will be redirected to another location. For user local databases, they should be stored in the users data directory. For shared application databases, they should be stored in the shared directory or on a network UNC. It's the network UNC issue...
As for connectiostrings and security in general, my approach is specifically for an application that will connect to databases which may be on network locations which are unknown at the time of development or may change from time to time. In a client-server environment this is often the case. It also allows the database connection string to be easily change after installation between, for example, a test database and a live database, but using the exact same code base and strongly typed database objects. For me this has huge benefits in product testing since you always know the code to access each database is absolutely identical without special fiddling with connection strings each time a database is opened. It also can help satisfy HIPAA security restrictions that would allow a support person to test a live installation of a client progam by easily changing the connection string to a test database which contains only dis-identified patient data.
|
|
|
|
|
because its a good idea and many developer indeed will use it
why we dont make things clear
i see one single person work this code out
and we all watching whats going on ?
please we need to clear this code more than that
thanks
|
|
|
|
|
I have no idea what you are asking for...... (he says, hoping this is not a troll)
|
|
|
|
|
hi mjmeans
sorry , i thought this code working with u.
because i did tried everything and following the comments and still doesnt work
thanks
|
|
|
|
|
First comfirm that you are writing in Visual Basic. This does not work at all in C# and will not ever work in C# because it does not have the built-in application framework that Visual Basic has. Then go over the instructions again. It DOES work. maybe you created the xxxxUserOverride setting as a connection string, which is wrong; it must be set as a normal user scoped string.
|
|
|
|
|
Hi, I saw your article and it seems to ve very good, I tried to use your solution, but I can´t understand something:
1.- I use My.Settings.SetUserOverride("TRASLADOSDBUserOverride", strRuta)
In here I use TRASLADOSDB as my Application scope connection string and TRASLADOSDBUserOverride as the User scope string. strRuta is the string in which I have my new conection string.
2.- in the module you have 2 events (userOverride_SettingsLoaded and userOverride_SettingsSaving)
But When are they accesed???? or How do I have to Access them in order to perform the change of the conection string from TRASLADOSDBUserOverride to TRASLADOSDB?, because TRASLADOSDB never changes
3.- I have followed step by step your article but I still can't change the connection string
Please I'm very
|
|
|
|
|
You should call My.Settings.SetUserOverride("TRASLADOSDB", "newstring") whenever you want to change the saved DB connection string. Do not include "UserOverride" in the name of the string to be overridden when calling SetUserOverride().
|
|
|
|
|
Thanks!! I have changed the Property from "TRASLADOSDBUserOverride" to "TRASLADOSDB", It works fine!!!
Thanks a lot!!!
|
|
|
|
|
Is it possible for you to translate this great class in C# ? I'm a novice and so I can't do it by myself . Thank you in advantage!
|
|
|
|
|
I don't think this is possible. The code requires the application framework created by the VS2005 when you enable the project property "Enable application framework", and "Dave My.Settings on Shutdown" for VB projects. I don't think C# projects have that support.
|
|
|
|
|
I've tried almost everything to get through this problem, and the most reasonable solution I found was to change the app.config file dynamically. But your solution is exactly what I've been looking for. It worked great. Vote:5
|
|
|
|
|
HI. I generally just use app.config files. I copy name of connection string from app.config of project that contains TableAdapters and then I put them in final app.config.
This I put in final app.config and it can be changed before you start application.
<connectionStrings>
<add name="Model.My.MySettings.ConnectionString" connectionString="Data Source=ORAX1;Persist Security Info=True;User ID=u1;Password=p1;Unicode=True" providerName="System.Data.OracleClient" />
</connectionStrings>
For client-server applications, i also similar type of configuration, but change it at run time on login with. With this type of authentication, user passes username and password on login. Tableadapters than use modified connection string.
<connectionStrings>
<add name="Model.My.MySettings.ConnectionString" connectionString="Data Source=ORAX1;Persist Security Info=True;User ID={0};Password={1};Unicode=True" providerName="System.Data.OracleClient" />
</connectionStrings>
dim pattern=My.Settings("ConnectionString")
dim conn as string=String.format(pattern,username,password)
My.Settings("ConnectionString")=conn
|
|
|
|
|
Yes. There are several ways to solve the problem.
But my solution is, in my opinion, better and more elegant because it works for applications that use strongly typed datasets where the table adapters are created automatically by the designer. The table adapter's connection string is also automatically coded by the designer to an application scoped (and uneditable) My.Settings string that was used on the developer's machine and automatically persisted. This original developers's machine connection string is then automatically loaded from the application scoped My.Settings string upon first use of the table adapter.
Numerous other people have come up with workarounds that involve 1) reading and writing to the registry, or INI file, or XML or other config file and, 2) reading them back in and manually applying the connection strings to the table adapters which 3) already have the wrong, but original, developer's machine connection string applied.
One other intriguing solution I have seen involved manually editing the .vb class behind the XML data source to change the My.Settings call to a different string. But if you made the slightest change to the schema, Visual Studio would instantly and without warning overwrite your change and replace it with the original developers application scoped connection string.
It is easy for bugs to occur with the other methods also because 1) if a designer created table adapter is used BEFORE setting its connection string, it will use the wrong string and setting connection string and the table adapter will ignore changes to its connection string property afterwards and, 2) as a program grows to enterprise complexity, more and more table adapters and possibly more data sources would be used and it is easy to forget to set the conenction string peroperty of the table adapter to the correct string.
This class solved all these problems at once with minimal editing required with each new application scoped connection string, can easily support dozens of connection strings with virtually no additional programming code, and is ClickOnce friendly.
|
|
|
|
|
I guess I am missing something. The way I do it now, I just copy-paste connection string from app.config from datasets.dll to finalexeapplication.exe.config. This can be done on deployment server. Upon initialization of tableadapters, they look for connection string in finalexeapplication.exe.dll, not in original dll.
My table adapters are also automatically created.
But then I really don't know if my connection string are aplication or user scoped, I just use default. I also don't know if my method will work in future releases of .net. And I also don't know how this would effect ClickOnce.
Anyway thanks for great article, it is always nice to read about configuration/deployment issues.
|
|
|
|