sql - C# local database not updating when using |DataDirectory|

Question

I am using a local database and for some reason when I use |DataDirectory|, the database doesn't update when I add/delete

SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|HomeDB.mdf;Integrated Security=True");  
SqlDataAdapter da = new SqlDataAdapter();

DataTable dt = new DataTable();
DataSet ds = new DataSet();

BUT if I use the following directory it does work

SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=F:ProjectHome_DatabaseHomeDB.mdf;Integrated Security=True");  
SqlDataAdapter da = new SqlDataAdapter();

DataTable dt = new DataTable();
DataSet ds = new DataSet();

Anyone may know why?

Answer 1

|DataDirectory| variable has a value set by the .NET Framework based on the OS.

I did a quick experiment with this as following.

class Program
{
    static void Main(string[] args)
    {
        var dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory");

        Console.WriteLine(dataDirectory);

        Console.ReadKey();
    }
}

When I ran above code, it displayed nothing. Upon debugging I figured that the dataDirectory variable has value null.

Then I tried to use it to create database connection and open it.

 SqlConnection conn = new SqlConnection(@"Data Source=.;AttachDbFilename=|DataDirectory|HomeDB.mdf;Integrated Security=True");

 conn.Open();

This code failed at conn.Open(); with following error.

An attempt to attach an auto-named database for file D:ChetanSandboxconsoleapp1ConsoleApp1inDebugHomeDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

As you can see from the error that, because '|DataDirectory| is null, the application tries to locate the .mdf file at binDebug folder under the project directory which is basically the location from where the exe is running.

So if you want the |DataDirectory| have different value, you first need to change it before using it. As following.

class Program
{
    static void Main(string[] args)
    {
        var dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory");

        //Changing DataDirectory value.
        AppDomain.CurrentDomain.SetData("DataDirectory", "C:\DataFiles");

        Console.WriteLine(dataDirectory);

        SqlConnection conn = new SqlConnection(@"Data Source=.;AttachDbFilename=|DataDirectory|HomeDB.mdf;Integrated Security=True");

        conn.Open();


        Console.ReadKey();
    }
}

With this code, I noticed that the value "C:\DataFiles" was used to locate HomeDB.mdf.

If you read This it explains the order in which values of |DataDirectory| are expanded.

This explains about how to customize the value of |DataDirectory|.

Now coming to your issue of data not being reflected.

In your case, |DataDirectory| has null value, so it is connecting to the HomeDB.mdf file located at the binDebug or binRelease folder making changes there while you are looking at F:ProjectHome_DatabaseHomeDB.mdf to verify the changes.

You are not seeing the error which I am seeing because the .mdf file is copied to the executable location when you are building the project.

So solution to your problem is to change the value of |DataDirectory| using AppDomain.CurrentDomain.SetData("DataDirectory",<<yourvalue>>); method.

EDIT:

If the location of .mdf file is fix relative to the executable file of the project, you can build the value for |DataDirectory| at runtime and assign it.

Let say, you have a folder Database at the same location as the exe and Database folder has HomeDB.mdf file. So first you need to find the path from where the exe is running and append Database to is and assign it to |DataDirectory|.

//Get the current path from where the exe is running.
var currentDirectory = AppDomain.CurrentDomain.BaseDirectory;

//Build path to Database folder
var databasePath = currentDirectory + "Database";

//Assign it to DataDirectory
AppDomain.CurrentDomain.SetData("DataDirectory", databasePath);

var dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory");

Console.WriteLine(dataDirectory);

//Use DataDirectory to SQL Connection.
SqlConnection conn = new SqlConnection(@"Data Source=.;AttachDbFilename=|DataDirectory|HomeDB.mdf;Integrated Security=True");

conn.Open();

This would work without any issue no matter from where you are running the application. It will work as long as you have Database folder and HomeDB.mdf file in that folder.

EDIT END

You also might want to consider putting the connection string in the configuration file, instead of hard-coding it into the code itself.

I hope I was able to explain the point clearly and this would help you to resolve your issue.