关闭应用程序时，将从数据库中删除所有数据 [英] All data from database is deleted when closing the application
问题描述
创建本地数据库文件然后将应用程序连接到该文件的正确方法是什么？即使您更改项目文件夹的位置，我也希望它能正常工作。
What is the proper way to create a local-database file and then connect the app to it ? I want it to work even if you change the location of the project folder.
正确地知道我在做什么：项目->添加新项目->基于服务的数据库然后创建一个，然后转到数据->添加新的数据源，添加创建的数据库，然后得到连接字符串。
Right know what I do is: Project -> Add new item -> Service-based Database and I create one and then I go to Data -> Add new Data Source, I add the created database and I get the connection string.
好，一切都好，我可以连接到它，但我希望在关闭应用程序时（但不总是）从数据库中删除所有数据。
Ok, all good, I can connect to it as I wish BUT all my data is erased from the database when I close the application (not always).
例如，此代码：
SqlConnection c = new SqlConnection(@"DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB.mdf;Integrated Security=True;User Instance=True");
c.Open();
SqlCommand cmd;
cmd = new SqlCommand("CREATE TABLE Persons (id int primary key, nume char(20), age int)");
cmd.ExecuteNonQuery();
cmd = new SqlCommand("INSERT INTO Persons VALUES (@id, @name, @age)", c);
cmd.Parameters.AddWithValue("@id", 1);
cmd.Parameters.AddWithValue("@name", "Catalin");
cmd.Parameters.AddWithValue("@age", 20);
cmd.ExecuteNonQuery();
我第一次运行它来创建表并向其中添加一个项目，然后，如果我第二次运行而不使用sqlcommand创建表person，它告诉我没有Persons对象，但是如果我第二次运行具有相同代码的项目，它告诉我已经存在一个Persons对象。 。
I run it first time to create the table and add an item to it and then, then if I run it the second time without the sqlcommand to create the table persons, it tells me that there is no Persons object, BUT if I run the second time the project with the same code, it tells me that there is already a Persons object...
我正在使用Visual C＃Express Edition 2010。
I am using Visual C# Express Edition 2010.
推荐答案
整个 User Instance和AttachDbFileName = 方法都有缺陷-充其量！在Visual Studio中运行应用程序时，它将在 .mdf 文件周围（从 App_Data 目录复制到输出目录-通常是 .\bin\debug -您运行应用的位置），最有可能是您的 INSERT 可以正常工作-但您最终只能看到错误的.mdf文件！
The whole User Instance and 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!
如果您要坚持使用这种方法，请尝试在 myConnection.Close（）调用中设置断点-然后使用SQL Server Mgmt Studio Express检查 .mdf 文件-我几乎确定您的数据在那里。
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
-
安装SQL Server Express（并且您已经完成了此操作）
install SQL Server Express (and you've already done that anyway)
安装SQL Server Management Studio Express
install SQL Server Management Studio Express
在 SSMS Express 中创建数据库，并为其提供逻辑名称（例如 MyDatabase ）
create your database in SSMS Express, give it a logical name (e.g. MyDatabase)
使用其逻辑数据库名称（在服务器上创建时提供）进行连接-请勿与物理数据库文件和用户实例混为一谈。在这种情况下，您的连接字符串将类似于：
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=MyDatabase;Integrated Security=True
其他所有内容与以前完全相同。 ..
and everything else is exactly the same as before...
另请参阅亚伦·贝特朗（Aaron Bertrand）的精彩博客文章要消除的坏习惯：使用AttachDbFileName 获取更多背景信息。
Also see Aaron Bertrand's excellent blog post Bad habits to kick: using AttachDbFileName for more background info.
这篇关于关闭应用程序时，将从数据库中删除所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
