Typed DataSet connection - required to have one in the .xsd file?

Question

In the .xsd file for a typed DataSet in .NET, there's a <Connections> section that contains a list of any data connections I've used to set up the DataTables and TableAdapters.

There are times when I'd prefer not to have those there. For instance, sometimes I prefer to pass in a connection string to a custom constructor and use that rather than look for one in settings, .config, etc.

But it seems like if I remove the connection strings from that section (leaving it empty), or remove the section entirely, the DataSet code-generation tool freaks out. Whereas if I don't remove them, the DataSet gripes when I put it in a different project because it can't find the settings for those connection strings.

Is there any way I can tell a typed DataSet not to worry about any connections? (Obviously I'll have to give it a connection if I change any TableAdapter SQL or stored procs, but that should be my problem.)

Answer 1

This has bugged me for a long time as well and I did some testing recently. Here is what I came up with. For the record, I'm using VS 2008 SP1.

• Datasets will store connection string information whether you want them to or not.
• You can make sure datasets won't store passwords in their connection strings.
• You can either have the connection string stored in the 'connections' section the .xsd file or you can have it stored in the app.config file and referenced from the 'connections' section of the .xsd file. Either way, the 'connections' section appears to be a requirement.

If you find a way to make it work, please post here. Currently, I store the connection string in the xsd file and make sure I'm not storing the password. Then, when I use it it code, I always create my own connection and never use one stored in the dataset.

Answer 2

In a similar case I stored the connection string for the dataset in a settings file / app.config (default behaviour in Visual Studio when creating a dataset) with integrated security to a local development database. In order to be able to access this from external projects, I changed the settings file to be public instead of the default internal (in the visual designer). This resulted in code like (in Properties\Settings.Designer.cs):

public sealed partial class Settings
{
    [...]
    [global::System.Configuration.DefaultSettingValueAttribute
            ("Data Source=.;Initial Catalog=MyDb;Integrated Security=True;")]
    public string MyConnectionString
    {
        get
        {
            return ((string)(this["MyConnectionString"]));
        }
    }
}

Then I manually modified the property to include a public setter as well:

public string MyConnectionString
{
    get
    {
        return ((string)(this["MyConnectionString"]));
    }
    set
    {
        this["MyConnectionString"] = value;
    }
}

Finally I set the connection string from an external project like:

MyDataAccessLibrary.Properties.Settings.Default.MyConnectionString = ...

Answer 3

The best way to do this will be to create a connection at runtime with your preferred connection string and assign it TableAdapter.Connection. This has worked for me in the past.

Secondly, if you remove the <connections> section from xsd it will surely throw an error becuase it is not only used at runtime but it is also used by the visual studio dataset designer at design-time to fetch the database schema of the underlying database so that you can design the Typed Dataset in the designer. So <connections> better be there for the dataset designer! :)

Answer 4

Using the TableAdapterManager might work for you. Please read more at: http://rajmsdn.wordpress.com/2009/12/09/strongly-typed-dataset-connection-string/

Answer 5

To Answer your question,

You don't have to specify a database name in your connection string to make the dataset IDE work. You ONLY have to specify a working SERVER.

We use the following connection string to point to our local server without specifying a database.

<add name="MyConnectionString" connectionString="data source=.;Integrated Security=SSPI" providerName="System.Data.SqlClient" />

This will allow you to work with datasets, change queries, edit/delete columns, without having to have a connection to a real database. Some features will still require you to specify a database, but you'll be able to do a lot more than before. Give it a shot.