C# Visual Studio: |DataDirectory| keyword in connection string conflicts mdf files

Question

Working on a windows form application project with 2 developers. Sharing source codes through tfs, have replaced the data directory path by |DataDirectory| keyword as the data directories are different in each PC. (C:\Users\username\Documents\Visual Studio 20XX\Projects\solution folder\solution folder)

Have used this relative path in connection string as

class ConnectionManager
{
        public static SqlConnection dbcon()
        {
            string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\systemdb.mdf;Integrated Security=True";
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            return con;
        }
}

The problem is when the C# application sends data into SQL Server database, it shows that data has been added into the database, but actually the database is not updated.

Found out that the connection string conflicts the .mdf file located in the root folder with the .mdf located in bin\debug. SELECT queries work fine when using |DataDirectory|. INSERT query does not work when using |DataDirectory|, but works fine when using the actual directory path instead of |DataDirectory|.

How to solve this problem? Requirements are:

1. Should not need to modify directory paths each time after getting the latest version from tfs, needs something like |DataDirectory| to remain always.

2. Connection string should not have a conflict with the .mdf files in root folder and the bin\debug folder.

Answer 1

The whole AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

The real solution in my opinion would be to

1. install SQL Server Express (and you've already done that anyway)

2. install SQL Server Management Studio Express

3. create your database in SSMS Express, give it a logical name (e.g. systemdb)

4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

   Data Source=.\\SQLEXPRESS;Database=systemdb;Integrated Security=True
   

   and everything else is exactly the same as before...

Also see Aaron Bertrand's excellent blog post Bad habits to kick: using AttachDbFileName for more background info.