I'm trying to add a Data Connection to a Project and point it to a database that will be in a dynamic location (based on where the user installs the software). There are two things I can't seem to do...

1) Make the Data Connection specific to the Project (or even the Form). It looks like the Data Connection is configured at the "Visual Studio" level, so even when my Project is closed, I can still see that Data Connection in the server explorer. I don't want this. I only need this connection for this project, so I'd like to create it "inside" the project. All of the other data objects (Data Source, DataSet, DataAdapter, etc) are specific to a Class (i.e. Form), so why isn't the "Data Connection"? If there's a way to add the data connection without using the VS2005 wizards, so that it still shows up in the list of available Data Connections, that would be ideal, because of my next issue...

2) I need to make the data connection's path dynamic. The user could install the app anywhere, and while coding, the database will be in the "bin" folder, so I need the path for the data connection to be set to "Application.StartupPath"... not a fixed location. Again, if there's some way to code the Data Connection, instead of using the wizards, that would solve this issue.

Usually I do all of my data connections in code. This is the first attempt at using the VS wizards for creating/working with database connections. Obviously, it's not going too hot so far.

WATYF

Hello WATYF,

After configuring data in a project with a wizard or dialog box the connections are saved in the application settings - right click the project in Solution Explorer and select Properties - the connection strings are on teh Settingspage/tab.

Data connections are simply connection strings so you can dynamically create the correct connection string and assign it to the TableAdapter, or whatever objects you are using that contain connections.

You can also store more than 1 connection in application settings.

Take a look at the following topics:

How to: Save a Connection String

How to: Edit a Connection String

Connection Strings (ADO.NET)

Hope that helps....

Thanks... but the problem is not with creating or editing it. The problem is with making it dynamic.

For example, I created a Data Connection and then a DataSource/DataSet and then dragged some fields onto a form which created a BindingSource and TableAdapter. This all works fine. The problem is, I need the path to the database to be set at run-time.

So the app starts and I set the connection to point to Application.StartupPath, so the connection is always referring to a db in the folder where they install the software.

I was able to find some settings for the connection string in the DataSet's Designer.vb file and make that dynamic, but there's still an .xsd file that has the path hard coded in it, and that's stopping me from being able to set it up dynamically.


I like the benefits of being able to work with contols without having to code every connection to the db, but I also need the interaction with the db to be dynamic. I can't just say "here's the path/configuration of my db" and expect it to be the same every time the app runs.


WATYF

Well... apparently, changing the hard-coded path on DataSet.Designer.vb isn't going to work. Any time you reconfigure the dataset, it resets the path back to the hard-coded value.

How on earth do people use local databases for the back end of distributed applications if they can't make the path dynamic? Is there something obvious that I'm missing, or is this a huge oversight on the design of VS.NET?

WATYF

Is your databasea .mdf or .mdb file - or ?

If so, when you select the file in Solution Explorer, what is the setting of the 'Copy to Output Directory' property (in the properties window)?

And are you simply trying to have the connection point to where the user installs your app - or are you trying to change the connection based on some user action when your app is running?

It's a VistaDB file, but frankly, that's irrelevent. It could be any portable db format and the issue would remain the same. And I'm not even talking about the disribution method yet (Copy to Output Directory probably won't be what I use, but regardless).

I'm talking about trying to have the connection point to where the user installs the app.

I did figure this out, though. The problem lies with the fact that in order to use the handy "drag and drop" capabilities of VS.NET, you have to let it to a whole lot of configuration up front (including the connection string, but even more than that, the database structure itself... tables, columns, etc).

The main issues arrise if your app is distributed with a portable db, or if you make changes to the db template (add a column, etc).

In the case of the latter, you have to re-configure the data source using the wizard and let it pick up any changes you've made to the db (new tables, new columns, etc). Of course, certain changes cause big problems... I tried changing the name of a table and all *** broke lose. Had to end up deleting the data source entirely and all of the accompanied objects and starting over. But in most cases, simply walking through the wizard again will work.

In the case of a portable db, you need your app to be able to point to the db wherever it resides once the software is installed (which will most definintely not be the same place it resided while you were designing the software). To do this, I had to create a New oledb Connection object during form load, use a connection string that pointed to Application.StartupPath, and apply it to each TableAdapter that got created by the wizards/vs.net.

So, for example, when I create a Data Source and drag some fields from a table onto a form, I get a new TableAdapter and BindingSource for that table. If I drag fields from a different table onto that same form, I get another set of TableAdapter/BindingSource. So when the form loads, I need to create a new oledb connection object and apply it to TableAdapter1.Connection and TableAdapter2.Connection, and it will connect to the db using the path I provide at run-time.

WATYF