Help with connection string

Question

So I'm trying to connect to my database at the specified location, and the connection is established as long as the db at the same location specified at DataSource field, but what if I tried to distribute my application, the file path will change and will lead to errors I want to avoid. Here is my connstring:

string connstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\PC1\Documents\Visual Studio 2008\Projects\Test\Test\bin\Debug\MyDatabase01.accdb;Persist Security Info=true";

Is there anyway I can define DataSource location to be at the same folder?.

Answer 1

If you can't use a relative path in connection string, you can generate it at runtime something like:

string connstring = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=true", Path.Combine(Directory.GetCurrentDirectory(), "MyDatabase01.accdb"));

Answer 2

There are a few things I can think of to do:

1. Store the database next to the application and then use a relative file path in the connection string (this makes use of a substitution string built into ADO.Net - see here for more info):

   string connstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\MyDatabase01.accdb;Persist Security Info=true";
   

2. Store your connection string in a configuration file. This can then be changed when your application is run on a machine where the database is in a different place.

3. Have your application prompt for the location of the database on first use and then save that location to as settings file to use in the connection string.

If you are distributing your database with the application, option 1 is the best. If not, I would go for option 3.