TableAdapter Connection Strings
Posted on February 18, 2008
Filed Under .Net Code, CSharp, VB.Net
* 7/29/2010 Update – I have uploaded a sample project here for those of you still struggling with this. It includes C# and VB.Net projects. Enjoy!
I don’t know how a lot of people handle it, but one thing that always bugged me about table adapters and datasets is the way it handles it’s connection strings. It starts out innocent enough. A new dataset is created with it’s associated table adapter. The connection string is saved to the application settings file and that property is saved in the dataset. Running it on the development machine (or on the same network) is no big deal and just works. But what if you send that application to someone else that has their own SQL Server?
Did anyone at Microsoft actually use this scenario in a production environment? What were they thinking?
In order to run that in a production environment you have to set the connection string in the app.config file. While this may be fine for some people, what about the people like me that do not want my users to access that database? I don’t want them to have that username and password… While the chances of a normal user loading SQL Server Management Studio and logging in are slim, it’s still possible and it’s definitely possible if a user purposely wants to get out of having to do work that day.
What are the options? Well, one option is to use the encryption to encrypt the settings in app.config. For me, this option is not ideal. Reports of those settings getting corrupted are quite high, plus you have to deal with the loading and saving of those settings, which isn’t all that easy to do.
The other option is to take the route I was taking for a while… I had a function that would build me a connection string. Then I could: TableAdapter.Connection.ConnectionString = myLibrary.ConnectionStringFunction()
This was great, until that day came where I was in a hurry and added a few more tables to a form but forgot to set the ConnectionStrings. Whoops.
So I needed a solution that would stop me from having to set those ConnectionString properties, keep my connection string out of the app.config, and be easy to use (i.e. Just Works).
I started out by just giving all my datasets the same connection string. Then on application startup, I tried to change that one application setting. Hmm.. It seems those ConnectionString properties are set to friend and are read only.
Upon further investigation, it seems that there are some events that fire, such as SettingsLoaded. This event fires when the app.config is read and all the settings are loaded. When this event fires, it fires inside the MySettings class. This should allow that property to be changed.
Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
Me.Item("MyAppConnectionString") = MyLibrary.BuildConnectionString()
End Sub
This will set the MyAppConnectionString setting to the proper connection string. Now, all table adapters will have an up to date connection string.
So what happens if you want to change the connection string later while the application is still running? Well, there is no way to do that. So it’s time to come up with a way to trick it into updating that property.
In looking at the MySettings class, there is another event called PropertyChanged. We can use this event if we create another setting that can be updated anywhere in the application. First, we create a new string setting that has a User scope (I called mine ConnectionString). This will allow the application to update the setting at any time.
Next, we need to create a function that will update that property with our connection string.
Public Shared Sub ChangeConnectionString()
My.Settings.ConnectionString = BuildConnectionString()
End Sub
Now we can change the events in the MySettings class to look like this.
Private Sub MySettings_PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Handles Me.PropertyChanged
If e.PropertyName = "ConnectionString" Then
Me.Item("MyAppConnectionString") = My.Settings.ConnectionString
End If
End Sub
Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
MyLibrary.ChangeConnectionString()
End Sub
End Class
Now, every time that ChangeConnectionString() is called, the MyAppConnectionString will be updated. The ChangeConnectionString procedure can be changed so that it can accept a string parameter that is the actual connection string. Then you can build a Connection String anywhere and just pass it to that procedure.
Comments
22 Responses to “TableAdapter Connection Strings”
Leave a Reply
-
About
A site dedicated to software development. The industry, the code, and the lifestyle.
Find It
Donations
Did I help you in some way that you want to show me your appreciation?Cure Diabetes
Support Scott Hanselman as he walks to cure diabetes
Excellent Companies List
Categories
Recently Written
- ASP.Net Web Forms Templates – Part 1
- Using Master Pages as Templates in ASP.Net WebForms – Introduction
- Gr1d – Persistent multiplayer online programming game.
- Global Application Settings Override (Now with examples!)
- Wrap your ASP.Net session state into a more meaningful object.
- WCF in an ASP.Net Application
- First usage of WCF and specified fields.
- Why excellent vendor support matters.
- How VB.Net can screw up a newbie without them knowing it.
- Linq to SQL in web services
easy way to change TableAdapter Connection runtime;
open the DataSetDesigner.cs where the DataSet contains the TableAdapter what you want to use.
Look at the class definition of the TableAdapter:
namespace DataSources.MyTableAdapters {
///
///Represents the connection and commands used to retrieve and save data.
///
[global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "2.0.0.0")]
[global::System.ComponentModel.DesignerCategoryAttribute("code")]
[global::System.ComponentModel.ToolboxItem(true)]
[global::System.ComponentModel.DataObjectAttribute(true)]
[global::System.ComponentModel.DesignerAttribute("Microsoft.VSDesigner.DataSource.Design.TableAdapterDesigner, Microsoft.VSDesigner" +
", Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")]
[global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
public partial class myTableAdapter : global::System.ComponentModel.Component {
private global::System.Data.OleDb.OleDbDataAdapter _adapter;
.
.
.
.
Look at the Connection property definition, and change the following line to the next one;
internal global::System.Data.OleDb.OleDbConnection Connection {
public global::System.Data.OleDb.OleDbConnection Connection {
build the dataset project, and you can access to the Connection property of the TableAdapter in the future.
While that works well, it will get reset back if you make any changes in the Visual Studio designer and you would have to do it again. Aside from changing the connection string, with table adapters, there is no other reason to access the connection property (at least for me, anyway). With the solution above, I don’t even need to see the connection property anymore. It just changes when my user changes the server or database to connect to.
When you have to work with more databases (same structure) from an application thats a “good enough reason” to me.
And another good reason; You cannot pass an opened connection to the TableAdapter, but that always opens the prepared one, than close it again. Yes I know there are the code in the DataSet definition, what checks the state of the connection, and won’t open again if thats opened, but I don’t know other way to give an opened connection to the TableAdapter. I think, in some cases, and for some type of applications this is not a real problem, but when your application do lot of requests to the database server, this can slow that down.
Great article man!
I was struggling myself to find a solution! Thanks a lot!
TheCodeMonk – you are the man!! What an elegant solution to this very comman requirement. I have searched all over the net for a solution – found others ie 1) Modify the app.config file, 2) Create Partial Class for each and every TableAdapter 3) Use the Connection property of each and every table adapter 4) Modify wizard generated TableAdapter – All these other solutions had major drawbacks. Yours, however, stays inbounds and is extremely simple to implement. Thank you very much. Just as a note, I did put the two events in a MySettings Partial Class (VB) as I am sure you were recommending.
still could not get how to do it in vb.net (vs 2008)
Programming a Windows Form Application in vs2005 c++. I did all the development work on a SQL Express database running on my local machine. At the moment I’m trying to change the ConnectionString in the DataSet Class to a network DataBase. I change the ConnectionString in DataSet.h to the correct ConnectionString, but still connects to the local database. If I change the connectionString of the TableAdapter during runtime it works… Any Input? Thanks!
Since I haven’t done any data access in C++/CLI, I can only guess… Is there a application setting for the connection string? By default, when you create a dataset, it creates an application setting that sets that connection string. The table adapter when instantiated is set to this application setting. My guess is that it is still doing that, so by changing it in the header file it’s still being overwritten by the application setting at runtime. Changing the connection string on the table adapter after it’s been instantiated will work because it’s already set the connection string to the application setting and you have just over written it.
Using a method similar to the one in this posting can solve your issue as well…
Thanks a lot! Changed the ConnectionString Setting of the adapter to NONE and set it per code afterwards. I tried your method, but I couldn’t get the eventhandling function right! Any suggestions on how and where to integrate the settingsloaded eventhandler?
Once again, thanks a lot!
The settings loaded event is fired from the Application Settings object. You can wire it up inside this object pretty easy. The quickest way to find the settings object is to go to your project properties and in the settings area, click on View Code.
Hey man,
I’m really sorry to bother you again. I tried around a lot, but I just can’t get the SettingsLoaded Event integrated. In the project properties dialog is no “view code” option (using vs2005). I just don’t get it straight where to integrate the event.
Thanks once again!
I’ve been looking just like you. I’m using VS2008 but maybe it’s the same.
Double click My Project so that you see the page with Application, Compile, Debug, References, etc…
Click on Settings. At the top of area that shows the settings there should be a View Code. Click that and you’ll be on the correct code page to add your event.
Good Luck.
That’s exactly the code that I need! Could you translate in C# though, I haven’t worked with Settings a lot and I am not sure how to set this up.
Adam,
This is a nice piece of work, and so elegant. Best of all you published it in VB. Thank you very much.
Bob Ranck
[...] previous posting about TableAdapter connection strings was a pretty big hit with a lot of people. It’s [...]
The “right” way to do this is to not specify a username at all, but use Windows Authentication. Then there is no usernames in the connection string and hence no security issues.
It works. ΤhaƞƘs man, I’ve been trying a lot to reach this. ΤhaƞƘs a lot men,God bless u.
Thanks..
it is very easy and time saving solution.
I helped me a lot.
Nice. This “feature” has always driven me crazy. What was MS thinking? Very handy work around.
Nice article.
my.Settings doesn’t exist in the Compact Edition. Is there another way to achieve this in the Compact Edition?
I have never tried the compact edition, sorry.
This solution works pretty nice.
You just need to know that all current adapter must be “reconstruct” after a change in the connection string. (tableadapter connection are not reference)
A dataset clear and fill operation on an opened forms will still use the old connection. You have to close (and save data) all “data” forms before making the connection change. (Clear a dataset, rebuild the adapter and fill the dataset again will work too but it’s a very dirty way)