none
Custom connection string RRS feed

  • Question

  • I've created a custom dataset based on the tables I created in my SQL database.  I really like the way Visual Studio builds the adapters, tables, etc. necessary to exchange data with the server, but I hate the fact that it then requires you to use an app.config file.  I'd like to be able to get rid of the app.config file containing the connection string and simply specify the connection string myself.  Does anyone know of an easy way to use the custom dataset generated by VS while doing away with the app.config file?

    Thanks!
    Wednesday, July 1, 2009 6:21 PM

Answers

  • I believe I solved my own problem.  Here's what I came up with:

    When a strongly-typed dataset is created in Visual Studio, several items obviously get added to the active project.  Among these are some settings files and the problematic app.config file.  It turns out that the Settings.Designer.cs file is generated with the System.Configuration.DefaultSettingValueAttribute which specifies the default value for a given setting.  It seems that because the default value is defined, you can simply delete the app.config file if you don't want to use it.  The settings class will detect that there is no settings file and simply use the default value.

    So, this way, I can continue to use the strongly typed dataset, but I don't have to worry about deploying the stupid configuration file.

    Now, it seems that doing so will cause a problem with allowing users to change the connection string at runtime, but as that is precisely the thing I'm trying to prevent, it works for me.  From my research on this problem, it does seem as if you can add an event handler to the settings class which is called immediately after settings are loaded.  In this way, it should be possible to load settings (that is, use the defaults since app.config doesn't exist), and then override them if necessary.  In fact, doing so might be a good idea, since it would otherwise be possible for a user to simply drop an properly formed app.config file into the directory containing the executable to override the connection string.

    In any case, pretty sure I've solved my problem.  Thanks for all the help.
    Thursday, July 2, 2009 4:48 PM

All replies

  • I don't think you can really work around this. For the DataSet file that you have go review the Designer code for a TableAdapter. Take a look at the InitConnection function. In there it something like this

    this._connection.ConnectionString = global::DataSetBindingThreading_issues.Properties.Settings.Default.AdventureWorksConnectionString;

    This is where it calls and gets the connection string. The problem is it generates this code for every tableadapter. As far as I can see the only way for you to change this is to muck with the code directly. But if you do this when you change the TypedDataSet it will replace that code with the newly generated code.

    If you are going to use TypedDataSet's with the TableAdapter code I don't see how can get around this.

    Now if you are bound and determined to do something else. I suppose what you could do is create your own TableAdapters that you can use. Then you can control how the connection is created.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 1, 2009 9:39 PM
  • Normally the Connection property is "internal" so you cannot set it unless you are in the same assembly.

    You can use a partial class to add another property that makes the connection available for you to set its ConnectionString outside of the assembly.  (You can't call this property Connection as that would be the same name as the internal property.)

    At top level in your source code:

    namespace WindowsApplicationXYZ.TestDataSetTableAdapters
    {
        partial class CustomerTableAdapter
        {
            public SqlConnection ConnectionObject
            {
                get
                {
                    return this.Connection;
                }
            }
        }
    }


    (Substitute TestDataSet with the name of your DataSet and CustomerTableAdapter with the name of your TableAdapter.)





    Wednesday, July 1, 2009 11:10 PM
  • As BinaryCoder has suggested you can use partial classes to add to the existing classes. But if you are calling Fill on the adapter it will call the InitConnection code for each tableAdapter no? I don't see how adding things through a partial class is going to be able to remove the need for the appconfig.

    Thanks
    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 2, 2009 12:54 AM
  • > But if you are calling Fill on the adapter it will call the InitConnection code for each tableAdapter no?

    Actually, I'm not seeing this problem.

    The call to InitConnection happens the first time the Connection property is accessed (only).

    Thursday, July 2, 2009 1:03 AM
  • I understand when Initconnection occurs, but I don't see how creating a new Connection property on its own will connect the rest of the adapters to use this property. The Connection property that is generated ou on the tableAdapter code is an internal property. So you can't just set it.

    Now I suppose that since it is internal and settable you could always do something like the following

    public void InitMyConnection(SqlConnection conn)
    {
         this.Connection=conn;
    }

    But you will now be required to call this before you use every adapter to fill the connection with the connection you want to use rather than it going through the app.config.

    Do you see any other novell way to alter this other than rewriting InitConnection itself? I don't think there is but perhaps I missed an extensibility point.

    Thanks
    Chris Robinson
    Program manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 2, 2009 3:25 PM
  • I believe I solved my own problem.  Here's what I came up with:

    When a strongly-typed dataset is created in Visual Studio, several items obviously get added to the active project.  Among these are some settings files and the problematic app.config file.  It turns out that the Settings.Designer.cs file is generated with the System.Configuration.DefaultSettingValueAttribute which specifies the default value for a given setting.  It seems that because the default value is defined, you can simply delete the app.config file if you don't want to use it.  The settings class will detect that there is no settings file and simply use the default value.

    So, this way, I can continue to use the strongly typed dataset, but I don't have to worry about deploying the stupid configuration file.

    Now, it seems that doing so will cause a problem with allowing users to change the connection string at runtime, but as that is precisely the thing I'm trying to prevent, it works for me.  From my research on this problem, it does seem as if you can add an event handler to the settings class which is called immediately after settings are loaded.  In this way, it should be possible to load settings (that is, use the defaults since app.config doesn't exist), and then override them if necessary.  In fact, doing so might be a good idea, since it would otherwise be possible for a user to simply drop an properly formed app.config file into the directory containing the executable to override the connection string.

    In any case, pretty sure I've solved my problem.  Thanks for all the help.
    Thursday, July 2, 2009 4:48 PM
  • I was assuming by "simply specify the connection string myself" that the original poster would be okay with setting the property after every create of an instance of the adapter.

    The usual problem is the "internal" nature of the Connection property, so I gave a fairly typical solution to wrap it.  I agree that it does not completely automate this.

    Thursday, July 2, 2009 10:48 PM
  • In your Setting.cs file you can override the property name of the connectionstring that was saved in your setting file. The code below will allow you so set the connection string at runtime. This will allow your typed datasets to remain unchanged since they are calling the connectionstring property and you are capturing this and returning the connection string you want. You can just create a property in Settings as a connection string but not provide a value and fill it in yourself.

    public override object this[string propertyName]
            {
                get
                {
                    if (propertyName == "nameofconnectionstringinsettings")
                    {
    
                        string returnValue = null;
                        // Look for the name in the connectionStrings section.
                        ConnectionStringSettings settings =
                            ConfigurationManager.ConnectionStrings[GlobalVar.ConnectionStringName];
    
                        // If found, return the connection string.
                        if (settings != null)
                            returnValue = settings.ConnectionString;
                        return returnValue;
                    }
                    return base[propertyName];
                }
                set
                {
                    base[propertyName] = value;
                }
            }
    Friday, December 4, 2009 10:39 PM