.NET windows application with relative connection string in app.config

Question

I am working on a project and it is a Windows based .NET application. I want to make a relative path so that when I move my files to another computer, I won't set up a new connection again.

So far, here's what I have done.

In my app.config:

<connectionStrings> 
    <add name="ConString"
         connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\dbOffense.mdf;Integrated Security=True;Connect Timeout=30"
         providerName="System.Data.SqlClient" /> 
</connectionStrings>

In my program. The code is not in any function. It is globally declared.

Dim constr As String=ConfigurationManager.ConnectionStrings("ConString").ConnectionString()
Dim con As New SqlConnection(constr)

My database dbOffense.mdf is located in the same folder as my Windows application OffenseDatabase. It is not in the folder where the .exe file of my application is.

So far, all that I've read doesn't really work at all.

I have tried moving my dbOffense.mdf to bin/Debug/Database directory. Then whenever I remove and set up a new connection/data source in my .NET application, the dataset is generated in the same folder with my Windows application OffenseDatabase instead of the same folder where dbOffense.mdf is.

Please can someone help?

Answer 1

You actually don't have a problem but you're trying to create one. Local data files just work as they are but some people don't realise and make their lives more difficult. Here's how it works.

You add the data file to your project and it gets saved in the project folder. Like all the other files in the project folder, it is a SOURCE file. It does not get used at run time. If you want to make any changes to your database schema or default data, you do so in that source file. If you generate a typed DataSet or Entity Framework model or the like then you do it from that file.

When you build your project, the files that get used at run time are placed in the output folder. For a Debug build, that is generally 'bin\Debug' under the project folder and for a Release build it is generally 'bin\Release'. Your source data file is copied and placed in that output folder along with the EXE and any other output files. It is that copy that you connect to at run time, NOT the source file. One obvious advantage of this is that, when it comes time to deploy, your Release output will contain a nice clean copy of your source file rather then your having to clean up a file that you were using for debugging.

By default, the Copy To Output Directory property of the data file is set to Copy Always. What that means is that, every time you make a change to your code and build again, the old copy in the 'bin\Debug' folder will be overwritten. This is not what a lot of people want. Many, if not most, people want their test data to persist between builds. In that case, you simply set that property to Copy If Newer and then a new copy will only be made when you make changes to the source file.

So, in short, your connection will work as it is unless you have done something to break the normal workings of things. The "|DataDirectory|" place-holder wil resolve to the correct folder at run time. Just make sure that your source file has been added to the project and the only thing that you may need to do is change the Copy To Output Directory property.