|| DataDirectory |时，C#本地数据库未更新 [英] C# local database not updating when using |DataDirectory|

问题描述

我正在使用本地数据库，由于某些原因，当我使用 | DataDirectory | 时，添加/删除数据库不会更新

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:\Project\Home_Database\HomeDB.mdf;Integrated Security=True");  
SqlDataAdapter da = new SqlDataAdapter();

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

有人知道为什么吗?

推荐答案

| DataDirectory | 变量具有由.NET Framework基于OS设置的值.

|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();
    }
}

当我运行上面的代码时，它什么也没显示.调试后，我发现 dataDirectory 变量的值为 null .

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();

此代码在 conn.Open(); 处失败，并出现以下错误.

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

尝试为文件D:\ Chetan \ Sandbox \ consoleapp1 \ ConsoleApp1 \ bin \ Debug \ HomeDB.mdf附加自动命名的数据库失败.存在具有相同名称的数据库，或者无法打开指定的文件，或者该文件位于UNC共享上.

An attempt to attach an auto-named database for file D:\Chetan\Sandbox\consoleapp1\ConsoleApp1\bin\Debug\HomeDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

从错误中可以看到，由于' | DataDirectory | 为空，因此应用程序尝试在 bin \ Debug下找到 .mdf 文件.文件夹，位于项目目录下，该目录基本上是exe运行的位置.

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

因此，如果希望 | DataDirectory | 具有不同的值，则首先需要对其进行更改，然后再使用它.如下.

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();
    }
}

使用此代码，我注意到值"C:\\ DataFiles" 用于查找 HomeDB.mdf .

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

如果您阅读此说明 | DataDirectory | 的值的扩展顺序.

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

此说明如何自定义 | DataDirectory | 的值.

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

现在您的数据问题没有得到反映.

Now coming to your issue of data not being reflected.

在您的情况下， | DataDirectory | 具有空值，因此它正在连接到位于 bin \ Debug 的 HomeDB.mdf 文件.>或 bin \ Release 文件夹在那里进行更改，而您正在查看 F:\ Project \ Home_Database \ HomeDB.mdf 来验证更改.

In your case, |DataDirectory| has null value, so it is connecting to the HomeDB.mdf file located at the bin\Debug or bin\Release folder making changes there while you are looking at F:\Project\Home_Database\HomeDB.mdf to verify the changes.

您没有看到我看到的错误，因为在构建项目时将.mdf文件复制到可执行位置.

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.

因此，解决您的问题的方法是使用 AppDomain.CurrentDomain.SetData("DataDirectory"，<< yourvalue>>))更改 | DataDirectory | 的值.方法.

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

如果 .mdf 文件的位置相对于项目的可执行文件是固定的，则可以在运行时构建 | DataDirectory | 的值并进行分配.

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.

比方说，您在与 exe 相同的位置有一个文件夹 Database ，并且 Database 文件夹具有 HomeDB.mdf 文件.因此，首先您需要找到运行 exe 的路径，并将 Database 附加到is并将其分配给 | DataDirectory | .

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();

无论您从何处运行应用程序，此操作都不会出现任何问题.只要您具有 Database 文件夹和该文件夹中的 HomeDB.mdf 文件，该文件便会起作用.

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.

编辑结束

您可能还需要考虑将连接字符串放入配置文件中，而不是将其硬编码为代码本身.

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.

这篇关于|| DataDirectory |时，C#本地数据库未更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！