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

23 Responses to “TableAdapter Connection Strings”

  1. Lengyel Zoltán on June 27th, 2008 12:10 pm

    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.

  2. TheCodeMonk on June 27th, 2008 12:39 pm

    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.

  3. Lengyel Zoltán on June 28th, 2008 6:34 pm

    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.

  4. Jorge on October 21st, 2008 10:42 am

    Great article man!
    I was struggling myself to find a solution! Thanks a lot!

  5. Lyle Hardin on October 24th, 2008 5:20 pm

    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.

  6. Jayandra on May 22nd, 2009 9:10 am

    still could not get how to do it in vb.net (vs 2008)

  7. Steffen on January 13th, 2010 10:18 am

    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!

  8. TheCodeMonk on January 13th, 2010 10:43 am

    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…

  9. Steffen on January 15th, 2010 2:43 am

    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!

  10. TheCodeMonk on January 15th, 2010 10:55 am

    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.

  11. Steffen on January 21st, 2010 5:46 am

    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!

  12. Tom on February 5th, 2010 4:58 pm

    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.

  13. Kiril on April 7th, 2010 4:04 am

    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.

  14. Bob Ranck on April 8th, 2010 11:05 am

    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

  15. Global Application Settings Override (Now with examples!) : TheCodeMonk on July 29th, 2010 11:08 pm

    [...] previous posting about TableAdapter connection strings was a pretty big hit with a lot of people. It’s [...]

  16. David F on August 13th, 2010 1:37 pm

    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.

  17. mady on January 13th, 2011 11:52 pm

    It works. ΤhaƞƘs man, I’ve been trying a lot to reach this. ΤhaƞƘs a lot men,God bless u.

  18. Rizwan on September 7th, 2011 7:26 am

    Thanks..
    it is very easy and time saving solution.
    I helped me a lot.

  19. Stan on October 31st, 2011 3:37 pm

    Nice. This “feature” has always driven me crazy. What was MS thinking? Very handy work around.

  20. aardvark on November 16th, 2011 11:39 am

    Nice article.

    my.Settings doesn’t exist in the Compact Edition. Is there another way to achieve this in the Compact Edition?

  21. TheCodeMonk on November 16th, 2011 12:49 pm

    I have never tried the compact edition, sorry.

  22. Sinople on January 11th, 2012 2:22 pm

    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)

  23. Roy on February 26th, 2014 10:49 am

    Thanks for the code.

    I have successful changed the connection string via you code. But it appears that the table adapters are not filled with the correct data.

    How do the table adapters get updated?

    Thanks for any help you can provide.

    Using VS2012 and VB and win 7

Leave a Reply