Jonas Stawski

Everything .NET and More

ConnectionString on a DataSet/DataTable with DataAdapter

UPDATE: I would not recommend using this approach. You shouldn't change any code that is automatically generated because it might get overwritten when modifying the designer.

When you add a DataTable with a DataAdapter on a DataSet for a web app, Visual Studio takes you through a wizard to set some properties of the DataAdapter. One of those properties is the ConnectionString and it saves that info on the web.config, which is exactly where we want that piece of information. The problem comes when you want to set the DataSet on an external project with a DLL output. For example when you're implementing N-Tiering (the DataAccess layer.) The ConnectionString gets saved on the app.config file.

When you add the reference of the DataAccess layer the app.config doesn't get moved to the bin directory of the project and therefore the reference to the ConnectionString is lost. You could move that file manually and would solve the problem, but you wouldn't want to have the ConnectionStrings on multiple files, would you? It becomes a nightmare when you want to change them. Over here I'm going to show you a work around for this.

When you use the wizard, Visual Studio creates the code for you, that code is stored on the [DataSetName].Designer.cs (or .vb). What we need to do is change the location of where the ConnectionString is retrieved from. In my case the DataSet is called Product so on my Product.Designer.cs I have this:

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
  private void InitConnection() {
   this._connection = new System.Data.SqlClient.SqlConnection();
   this._connection.ConnectionString = global::DataAccess.Properties.Settings.Default.AdventureWorksConnectionString;
  }

the second line contains the ConnectionString which is being accessed from the Settings file, which eventually retrieves the value from the App.Config. We need to comment that line out:

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
        private void InitConnection() {
            this._connection = new System.Data.SqlClient.SqlConnection();
            //this._connection.ConnectionString = global::DataAccess.Properties.Settings.Default.AdventureWorksConnectionString;
        }

and we need to add a new constructor with a string parameter, which will represent the ConnectionString:

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
        public ProductTableAdapter(string ConnectionString)
        {
            this.ClearBeforeFill = true;
            InitConnection();
            this._connection.ConnectionString = ConnectionString;
        }

Over here we do the same thing as the default constructor and at the end we set the ConnectionString property of our connection with the ConnectionString passed in to the constructor.

So from your web project, you would initialize the ProductTableAdapter like this:

private ProductTableAdapter pta = new ProductTableAdapter(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString);

This small changes will give you more flexibility on the storage of your ConnectionStrings.

Happy Programming!

Comments (7) -

If you change something in xsd such as add new stored procedure or anything else - all changes will be lost, am I rigth? How are you resolved this problem?

Reply

You can't modify the dataset xsd class but you can change the setting class which is used by xsd GUI.
You need to set the temporary connection string in the setting and then override it with correct connection string

internal sealed class Settings
{
  private static Settings defaultInstance = new Settings();
  public static Settings Default
  {
     get { return defaultInstance; }
  }

[global::System.Configuration.DefaultSettingValueAttribute("")]
public string ConnectionString
  {
     get { return (string) your method ;}
  }
}

I am using app config to store connection string and I retrieved it as below:-
System.Configuration.ConfigurationManager.ConnectionStrings[System.Configuration.ConfigurationManager.AppSettings["schema"].ToString()].ConnectionString;

Reply

Irwin Moya
Irwin Moya

Great work.  

As an alternative when creating a TableAdpter using the designer you can set the ConnectionModifier property of the TableAdapter to "Public" in the properties window.  

Then in your code all you need to do is set the connection property of the table adapter to point to your own connection string.

This solution IMHO is much cleaner since you do not have to mess around with the designer generated code.

Happy coding.

Reply

I admit, I have not been on this webpage in a long time... however it was another joy to see It is such an important topic and ignored by so many professionals. I thank you to help making people more aware of possible issues.
Great stuff as usual....

Reply

if you want to change dataset connectionstring, you can change dataset tableadapter.connection.connectionstring with your own new connection string.
if you want to change datacontext add just end of datacontext declaration your new connectionstring
..... datacontext(newConnectionString)
best regards
judasis

Reply

Thomas Fairris
Thomas Fairris

Thank you for this article.  I did a typed dataset for a program I am planning on releasing to the general public.  I have a connection string in the my.settings for the end user to configure on their machine.  With the help of this article, I was able to find the connections string for the dataset and change it.

Reply

Linus Darimaani
Linus Darimaani

I have successfully created a database application using VB and SQL. After deploying its works perfectly on my machine but when i install it on a different machine it gives me an sql connection error.
I don't know exactly where to change my connection. I tried using the app config but it did not work. This is my app config file.Any help?

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Registration.My.MySettings.NRGPFINConnectionString"
            connectionString="Data Source=LINUS-PC;Initial Catalog=NRGPFIN;User ID=sa;Password=syntho"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <system.diagnostics>
        <sources>
            <!-- This section defines the logging configuration for My.Application.Log -->
            <source name="DefaultSource" switchName="DefaultSwitch">
                <listeners>
                    <add name="FileLog"/>
                    <!-- Uncomment the below section to write to the Application Event Log -->
                    <!--<add name="EventLog"/>-->
                </listeners>
            </source>
        </sources>
        <switches>
            <add name="DefaultSwitch" value="Information" />
        </switches>
        <sharedListeners>
            <add name="FileLog"
                 type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL"
                 initializeData="FileLogWriter"/>
            <!-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->
            <!--<add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="APPLICATION_NAME"/> -->
        </sharedListeners>
    </system.diagnostics>
</configuration>

Thank you

Reply

Add comment

biuquote
Loading