Ideal Ordeal? Deal.

to break is human; to fix is also human

Ordeal: Strongly Typed DataSet Connection String

Posted by Raj Kashyap on Wednesday, December 9, 2009

When designing Strongly Typed DataSets, Visual Studio’s designer wants you to specify a database Connection String, which is then shared by all TableAdapters for database connectivity. The connection string gets stored as a read-only property in the Application Settings configuration. You cannot change it at run-time if you want to connect to a different data source. In the real-world, even the simplest application needs to connect to different data sources – development, test and production. While we certainly need the RAD and IntelliSense benefits of using strongly typed datasets during development, we also need the ability to change the connection at run-time, whenever we need to. Here we shall see how to work around this inadequacy. This is Ideal Ordeal.

Applies to: .NET 3.5, Visual Studio 2008



Contents
1____In the DataSet Designer
1.1________Hierarchical Update
1.2________Connection String
1.3________TableAdapter Properties
2____In the Code Editor
2.1________Add Runtime Connection String to Application Settings
2.2________Data Tier
3____That’s all folks!





In the DataSet Designer


Hierarchical Update


Hierarchical Update = True

.........

Create a new DataSet and bring up the DataSet designer. View the Properties list and make sure the Hierarchical Update property is set to True. This tells the designer to create the TableAdapterManager class.

TableAdapterManager is a new class in .NET 3.5 and the Visual Studio 2008 DataSet designer creates this class in a new namespace under your application’s root namespace.

I have named my root namespace ConfigHack and my DataSet, MyDataSet. The DataSet designer defines the TableAdapterManager class in the ConfigHack.MyDataSetTableAdapters namespace. We will be using this class to change the connection string at run-time.

For this walk-through, I am using a local Access database, MyDatabase.mdb, and then illustrate how we can change the connection string at run-time to connect to a remote database server. The database contains two tables, Customer and BlackList.

Customer   BlackList
ID <==> CustomerID
Name   StartDate
Email   EndDate
LastUsedIP   IPAddress
    Reason

We will add these two tables to our strongly typed dataset.

Right-click on the DataSet designer and choose “Add>TableAdapter…”. This will bring up the “TableAdapter Configuration Wizard”. As the fist step, you will be asked to specify a Connection String. Go ahead and create a new connection string pointing to the local Access database file, MyDatabase.mdb. Click next.



Connection String


In the second step you will be asked to save the connection string to the application’s configuration file. Enter a name for the connection string and click next. I named mine “ConnectionString”, which is made available as a read-only Application Settings property:

ConfigHack.Properties.Settings.Default.ConnectionString
Save Connection String

.........

The person who built the wizard interface for the designer seems to have had the right idea, as you can see in this figure, where it is says, “Storing connection strings in your application configuration file eases maintenance and deployment”.

Nice.

But it is probably a different person who decided to store the connection string as a read-only application scope property.

How does it “ease maintenance and deployment” if it can never be changed dynamically at run-time?

Click next and move on to specify a query as shown below and complete the setup for the Customer table.

SELECT ID, Name FROM Customer
ORDER BY Name

Repeat the above steps, reuse the same connection string and add a TableAdapter for the BlackList table. In the query let it search for a given Customer ID.

SELECT * FROM BlackList
WHERE CustomerID = @customerid
AND EndDate > GetDate()
ORDER BY StartDate

Now back in the designer, you can setup the relationship between the two tables.



TableAdapter Properties


Click and select the Customer TableAdapter and look at its Properties list. You can see the connection string and all the SQL commands. I renamed the Fill() method to GetCustomers() which will populate the MyDataSet.Customer table.

Table Adapter Properties

.........



In the Code Editor


Add Runtime Connection String to Application Settings


Depending on the needs of an application, the choice of connecting to data sources could be driven by various run-time factors. Information from input parameters, user permissions & preferences, environment variables, registry settings, run-time availability of a data source, business rules – one or a combination of these and other factors might determine the appropriate connection string at run time.

In this walk-through, I have a simple console application, ConfigHack.exe, which gets the connection information from command line parameters. We can construct the entire connection string from these parameter values, for example, to a Sybase server, like shown here:

Provider=Sybase.ASEOLEDBProvider.2;Persist Security Info=True;Initial Catalog=devDB;Password=Pa$$w0rd;User ID=user007;Server Name=devServer;Server Port Address=108

In a somewhat more complicated scenario, you may not want to build entirely new connection string, but just modify certain values within the currently used connection string. The source code provided with this posting deals with such a scenario. Once the connection string is ready, it is added to Application Settings:

Properties.Settings.Default.Properties.Add(
    new System.Configuration.SettingsProperty(
        "RuntimeConnectionString",
        String.Empty.GetType(),
        new System.Configuration.LocalFileSettingsProvider(),
        false,
        newConnectionString,
        System.Configuration.SettingsSerializeAs.String,
        new System.Configuration.SettingsAttributeDictionary(),
        false,
        false));

New property RuntimeConnectionString is added to the Application Settings. In line 7, newConnectionString is a StringBuilder which contains the run-time connection string value. Also notice in line 6, I specified false for the isReadOnly boolean parameter.

NOTE: There is a bug in the SettingsProperty constructor or Properties.Add() method, which basically ignores the isReadOnly parameter. That is, you will be able to modify a read-only setting!



Data Tier


In the application’s data tier, once the TableAdapterManager is instantiated, I call the private SetConnectionString() method, which reads the new Application Settings RuntimeConnectionString value and assigns it to the TableAdapterManager.Connection.ConnectionString property:

using ConfigHack.MyDataSetTableAdapters;

// ...

// Read the runtime connection string value
string connectionString = Properties.Settings.Default.Properties["RuntimeConnectionString"].DefaultValue.ToString();

// Update table adapter manager's connection string
this.tableAdapterManager.Connection.ConnectionString = connectionString;

NOTE: Remember to include the namespace as as shown above in line 1.

Now that we have changed the TableAdapterManager’s connection string, all tables should connect and work just fine, right?

No. Not yet.

.NET 3.5 TableAdapterManager has a bug. Changing the connection string for the Manager is supposed to change it for all underlying TableAdapters. But it is changed only for the very first TableAdapter, other TableAdapters still use the old connection. So, we will have to change the connection string for every TableAdapter, until Microsoft fixes this bug.

this.tableAdapterManager.CustomerTableAdapter.Connection.ConnectionString = connectionString;
this.tableAdapterManager.BlackListTableAdapter.Connection.ConnectionString = connectionString;


That’s all folks!


The work around explained here avoids several “problematic solutions” – like directly editing designer generated code, modifying the .config file at run-time etc. Also using this work around you can change the connection anytime you want to. Here we basically have a two-step strategy, once you dynamically build your connection string at run-time:

1. Add a new property in the Application Settings to store the run-time connection string.
2. Read this new property in your data tier and assign to the TableAdapterManager before accessing the DataSet.

Also, keep in mind, anytime the application Reload()s the properties from config file, you may have to redo adding the run-time connection string property.

For the complete source code, please see below. WordPress does not allow uploading of ZIP files, so I am not able to publish the entire application. I hope this information was useful to you. If you have questions or if you notice any errors, please let me know.

Source Code
C#: ConfigHack-C#.pdf
VB: Coming Soon



Bug Tracking:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=510914
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=519333
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=519338

Related links:
http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7483b816-be7a-4204-a4d3-cfb14b2aae26/
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/392e5774-3f43-486a-a5c9-5bb26772f489/
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=113388
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=305701
http://weblogs.asp.net/rajbk/archive/2007/05/26/changing-the-connectionstring-of-a-wizard-generated-tableadapter-at-runtime-from-an-objectdatasource.aspx







About these ads

14 Responses to “Ordeal: Strongly Typed DataSet Connection String”

  1. liuwenfei said

    how laughing! please use ‘Now()’ in query of access!
    if you don’t know it,i doubt about your interrogative!

    • Thanks for the correction. I was actually using Sybase and fairly large tables, but just for the blog I thought it will be easier to explain using Access and two simple tables. So, such mistakes do happen. Hence the tag line.

      Hey, I am glad in this new year I made at least one person feel better about themselves. You just helped me fulfill my resolution already.

      Happy new year to you too…

  2. Peter said

    Hi Raj,

    This issue has been driving me nuts and your posting is REALLY helpful. Thanks for taking time to post it. But I’m a bit of a newbie… Can I ask a couple of questions?

    It seems to me that the real meat is :

    this.tableAdapterManager.Connection.ConnectionString = connectionString;

    but what is “this” here?

    Is an instantiation of my strongly type data set?

    I’ve been doing stuff like :

    TestTableTableAdapter ta = new TestDataSetTableAdapters.TestTableTableAdapter();
    string result = ta.SomeScalarQuery();

    i.e., to my knowledge, I’m NOT explicitly instantiating my dataset. So I’m therefore not sure how to apply your solution.

    Or do I have to do a bit of re-factoring?

    Thanks again,
    Peter

    • Hi Peter, Let me try to answer though I don’t fully understand.

      To understand how it works, you may want to write your own sample app using the source code I have published (ConfigHack-C#.pdf) which you will find at the end of the blog.

      “this” is the instance of the class in which you have declared your typed-dataset and TAM. In the code sample “this” is the TierData instance.

      If you are using a typed-dataset, you must instantiate it somewhere. I don’t understand when you say “to my knowledge, I’m NOT explicitly instantiating my dataset”. I think you are just using TableAdapters and filling DataTables. If that is the case, you are not using typed-datasets. So this posting is not applicable to your scenario.

      You wouldn’t use a typed-dataset if all you need is just one single value selected from the database. It will be too much overhead. It is fine to use if you already have few tables in your typed-dataset and you need just one more value from some other table. Then you could select the value like this:

      // assuming: testTableAdapterManager = new TestTableAdapterManager();
      this.testTableAdapterManager.TestTableTableAdapter.Fill(this.TestDataSet.TestTable);
      string result = this.TestDataSet.TestTable.Rows[0][0].ToString();
      

      HTH

      • Peter said

        Hi Raj,

        Thanks you for taking the time to repsond. You’re right, of course, I was and continue to be confused! I thought that because I’d created them with the dataset designer, my table adapters etc were a part of “the dataset”. But I have another example where I am pretty sure I AM using a typed dataset :-) – I will study your pdf and apply it to that example.

        Thanks again,
        Peter

  3. K.Prabakaran said

    Hi,
    i want to modify the connection string through application. After i modify the connection string i will save using the below method.
    My.MySettings.Default.Save()
    My.Settings.Save()
    After modified the settings,i closed appplication and open the same application the settings are the old one. New setting will not permanently store the app.config.
    what i do for resolve the issue.

    • Hi Prabakaran, Those Save() methods are not for writing to the config file. I don’t think there is a nice way to write to the config file at runtime, at least not that I know of. You can create your own settings file that you can update at runtime. You will need to use System.IO to do that. Others reading this here may have a better answer.

  4. Ed said

    Ok, I am reading the write-up and it says “In the real-world, even the simplest application needs to connect to different data sources – development, test and production.” but I don’t see any explanation on how to change the data source (my problem exactly). I can change the connection string for strongly typed datasets to point to a Development or Production environment in the same SQL Server world. However, if I want to make my application flexible and connect to either MS Access or MS SQL Server, I don’t see how this code changes the Data Source, Server Name and Connection String on a strongly typed dataset. What am I missing?

    • Hi Ed, To keep the illustration simple in the sample code I used just a Sybase connection. If you need to connect to different data sources, you can modify the logic in the ConfigHack class to build entirely new connection string, as applicable to your scenario. But there is an even better way to do this; I recommend using the DbProviderFactory and the DbConnectionStringBuilder classes. Please see:

      http://msdn.microsoft.com/en-US/library/t9f29wbk%28v=VS.80%29.aspx

      http://msdn.microsoft.com/en-US/library/wda6c36e%28v=VS.80%29.aspx

      http://msdn.microsoft.com/en-us/library/ms254947%28VS.80%29.aspx

      HTH

      • Ed said

        Hi Raj:

        Ok, it looks like the MSDN examples DbProviderFactory reflects the same examples as in the book and that is to ignore pre-defined datasets/data adapters and build your own on the fly. This would require a major re-write of my application at this point. Well, it was worth reviewing the examples.

      • Glen said

        Hi,

        Just for the sake of clarity:

        I have a strongly typed dataset using tableAdapaters created using the VS designer connected to a VistaDB database and appropriate connection string.

        Now I want to use the same dataset except connect to a MS SQL database that has identical tables as the VistaDB database.

        By changing the connection string and provider before using the dataset/tableAdapters at runtime as described in this article (or the DbProviderFactory) then the same dataset and tableAdapters can be utilised.

        Is that the case?

      • Glen,

        That is correct. As I had mentioned in an earlier comment, if you need to connect to different data sources, you can modify the logic in the ConfigHack class to build entirely new connection string, as applicable to your scenario.

        HTH

  5. Ed said

    Thanks Raj for the URL’s. I read “Professional Design Patterns in VB.NET” and understand the concept of Factory Design Patterns but the examples don’t use .NET Data Providers connected to pre-defined datasets with their own generated data adapters. Since my application is already written using a SQL Server data source, I really don’t want to re-invent the wheel and I was sure someone had already been down this road. I want to make this DB transition as simple as possible in the code as well as the user experience.

  6. liangxinhui said

    Thanks a lot!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: