Can't connect to localdb when using [DataDirectory] in Windows Forms C# .NET Core

Question

I installed VS2019 and started training, I'm trying to connect to my DB using [DataDirectory] the DB File is in my Debug folder I'm using the following Conn string cstr = @"server=(localdb)\MSSQLLocalDB;AttachDbFilename=[DataDirectory]\test.mdf;Integrated Security = True;"; but I'm getting this error

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

but it will work if I change [datadirectory] with another fixed location like D:\Test.mdf

My Code:

string cstr;
SqlConnection cnn;
cstr = @"server=(localdb)\MSSQLLocalDB;AttachDbFilename=[DataDirectory]\Test.mdf;Integrated Security = True;";
cnn = new SqlConnection(cstr);
cnn.Open();
MessageBox.Show("Connection Open  !");
cnn.Close();

Please help.

Answer 1

finally found my solution , i was using System.Data.SqlClient which doesn't support |DataDirectory| in: AttachDbFilename= . the new SqlClient Provider package : Microsoft.Data.SqlClient is what should be used from now on which supports AppDomains and |DataDirectory| macro in AttachDbFilename=

for using the System.Data.SqlClient the DB location in the connection string should be retrieved using : AppDomain.CurrentDomain.BaseDirectory so the connection string for System.Data.SqlClient Should be like this :

cstr = @"server=(localdb)\MSSQLLocalDB;AttachDbFilename="+ AppDomain.CurrentDomain.BaseDirectory+"Database.mdf ; Integrated Security = True";

this should fix the problem

Answer 2

The substitution string is read from the current AppDomain.

ADO.NET |DataDirectory| where is this documented?

But in .Net Core the AppDomain is a discontinued technology

MS .Net blog | Porting to .NET Core

App Domains

Why was it discontinued? AppDomains require runtime support and are generally quite expensive. While still implemented by CoreCLR, it’s not available in .NET Native and we don’t plan on adding this capability there.

And I tested it and that string substitution did not work with .Net Core 3.1

So you have to implement that substituion by yourself or read the data from an external config file.

Answer 3

I believe the problem is really about things not working how they used to. I have seen all sorts of answers regarding how to setup a connection with SQL Server LocalDB.

Here is my solution to get things back in VS 2022, .NET 6 and EF Core 6.

Use a connection string with both Initial Catalog and AttachDbFilename. Also, include a macro named [DataDirectory] to expand later on:

"Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MyDatabase;AttachDbFilename=[DataDirectory]\\MyDatabase.mdf;"

Use the following code to obtain the actual connection string:

string connStr = config.GetConnectionStringWithExpand("MyDatabaseConnection")

public static class ExtensionMethods
{
    public static string GetConnectionStringWithExpand(this IConfiguration config, string name)
    {
        string projectDirectory = Environment.CurrentDirectory;
        string dataDirectory = Path.Combine(projectDirectory, "App_Data");
        Directory.CreateDirectory(dataDirectory);

        string connStr = config.GetConnectionString(name);
        return connStr.Replace("[DataDirectory]", dataDirectory);
    }
}

When running the application from VisualStudio, the App_Data folder will be placed in the project folder, otherwise next to the entry point.