SQL Express connection string: mdf file location relative to application location

Question

I am using SQL Express databases as part of a unit test project in c#. My databases is located here:

./Databases/MyUnitTestDB.mdf

I would like to use a relative path or variable in the app.config rather than having my connection string defined as:

AttachDbFilename=C:\blah\blah\blah\yea\yea\yea\MyApplication\Databases\MyUnitTestDB.mdf 

I have seen the use of |DataDirectory| but am I correct in thinking this is only applicable to web applications?

I want to control this in the application configuration file, as in production the application uses a hosted sql database.

Gayelord

Answer 1

Thanks everyone, I used a combination of your responses.

In my app.config file my connection string is defined as follows

<add name="MyConnectionString"
    connectionString="Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Database=MyDatabaseForTesting;Trusted_Connection=Yes;" />

In my unit test class I set the DataDirectory property using the following

[TestInitialize]
public void TestInitialize()
{
    AppDomain.CurrentDomain.SetData("DataDirectory", System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Databases"));

    // rest of initialize implementation ...
}

Gayelord

To learn more about the DataDirectory term: msdn.microsoft.com/en-us/library/cc716756.aspx (look for "DataDirectory", it's at the end of the document) – Donovan

Answer 2

Yes, |DataDirectory| Web application to select the App_Data directory of the web application.

In a not web application, depending on .NET Framework, it could be used and also changed using AppDomain.SetData

But you have other two posiblities to create the connection:

1.- Use a relative path:

String con ="... AttachDbFilename=Databases\MyUnitTestDB.mdf ... ";

2.- get the application path and add to the String.
In c# Windows Application you can use Application.StartupPath

 String con= " ... AttachDbFilename=" + Application.StartupPath + "\Databases\MyUnitTestDB.mdf ... ";

Depending on the applicaiton type or launch mode you got different properties. Ex:

• Application.StartupPath -- The start path of the exe application that starts the application

• Application.ExecutablePath -- the start path an name of the exe application that stats the application But to use Application you need to include System.Windows.Forms that is not included for example into console applications.

• System.IO.Path.GetDirectoryName( System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) -- This gets the path from the current assembly "dll,exe,..." Is not affected by application type, path changes,... Always return the directory when the Assemby resides.

• Environment.CurrentDirectory -- the current directory. This can be changed for example if you navigate into folders.

You can find more about the different connection string options here http://www.connectionstrings.com/sql-server-2005

Wanwand

Thanks Dubas, option 1 is what I want to use however I cannot get this working. – Gayelord

Option 1 depends on your executing path. If your UInit test are executing with a different path that your application probably the use of a relative path gives you to a different path that the expected path. – Wanwand

@Wanwand |DataDirectory| variable isn't only for Web Applications, you can user it anywhere – Onanism

Answer 3

I have spent a whole day googling to work this through and finally have a clue from this

Here is my solution:
1. Use |DataDirectory| in connection string

<add name="NorthwindConnectionString" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDbFilename=|DataDirectory|\Northwind.mdf;User Instance=True" providerName="System.Data.SqlClient" />

2.Set DataDirectory in ClassInitialize

[ClassInitialize()]
public static void MyClassInitialize(TestContext testContext)
{
    string baseDir = AppDomain.CurrentDomain.BaseDirectory;
    int index = baseDir.IndexOf("TestResults");
    string dataDir = baseDir.Substring(0, index) + System.Reflection.Assembly.GetExecutingAssembly().GetName().Name;
    AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
}

Upsurge

Answer 4

I'm building a simple Windows Forms App with VS2010 with C#3.0. Also using SQL Express 2008 RC2.

I am able to use: |DataDirectory|MyDb.mdf in the connection string alone without changing anything else. The |DataDirectory| points to the location of my .exe file.

I will think that this would be the first thing all of you would try, so that is why I'm specifying my VS and SQL version. Or maybe it is new to C#3.0.

My complete Connection String:

"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|App_Data\ThumbsUpPlayer.mdf;Database=ThumbsUpPlayer;Trusted_Connection=Yes;"

Note that I have added a "App_Data" folder to my application, because I'm used to the Db in that folder, the folder is not recognised by VS.

Thinnish

Perfect solution. Thnx. – Lodi

Answer 5

I don't have Visual Studio here, but what about:

using System.IO;
using System.Windows.Forms;

string appPath = Path.GetDirectoryName(Application.ExecutablePath);
AttachDBFilme = appPath + "\\MyUnitTestDB.mdf"

Copernicus

I would suggest something along that line, perhaps used together with ADO.NET's connection string builders to put together the final connection string. – Lindly

Answer 6

I did the following. Hopefully it helps someone.

AppDomain.CurrentDomain.SetData("DataDirectory", System.IO.Path.GetFullPath(AppDomain.CurrentDomain.BaseDirectory + "../../App_Data"));

Symbolic

Answer 7

Dynamic Path in SQL Server Connection

SqlConnection  con="Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Database=MyDatabaseForTesting;Trusted_Connection=Yes;" ;

Surreptitious