wpf 数据未永久保存到 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23496393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Data not saving permanently to SQL table
提问by mkmitchell
I am using C# for a WPF application in Visual Studio Express 2012. I followed the tutorial found here.
I created a testDb.mdflocal Service-based database. I open the application, enter text, hit add, and the data adds to the db. I only know this because I have the one field setup as a primary key and unique. If I try to add the same thing again I get an error saying it already exists.
我在 Visual Studio Express 2012 中将 C# 用于 WPF 应用程序。我按照此处找到的教程进行操作。我创建了一个testDb.mdf本地基于服务的数据库。我打开应用程序，输入文本，点击添加，然后数据添加到数据库中。我只知道这一点，因为我将一个字段设置为主键并且是唯一的。如果我再次尝试添加相同的内容，我会收到一条错误消息，指出它已经存在。
When I exit my application nothing shows in the database. The data I entered is gone. Why is the data not permanent?
当我退出我的应用程序时，数据库中没有任何显示。我输入的数据不见了。为什么数据不是永久的？
Here is the code I'm using for my button click:
这是我用于单击按钮的代码：
private void Add_Click(object sender, RoutedEventArgs e)
{
SqlConnection cn = new SqlConnection(global::testdb.Properties.Settings.Default.testDBConnectionString);
try
{
string sql = "INSERT INTO Test (TestInsert) Values('" + txtName.Text + "')";
SqlCommand cmd = new SqlCommand(sql, cn);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Added new record", "Message", MessageBoxButton.OK);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Error", MessageBoxButton.OK);
}
finally
{
cn.Close();
}
}
Connection String:
连接字符串：
<connectionStrings>
<add name="testdb.Properties.Settings.testDBConnectionString"
connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\testDB.mdf;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
回答by Steve
It is a common scenario. You have a connection string that uses the substitution string |DataDirectory|. In a desktop application this directory is usually the same directory where your program runs. Inside Visual Studio, your program runs in the BIN\DEBUG (or x86 variant) directory. Thus the Visual Studio copies your MDF file from the project directory to the BIN\DEBUG folder. You add records to this copy, not to the one in the project folder. However, the Visual Studio Server Explorer window has a connection that points to the Project Folder database that, of course, remains empty.
这是一个常见的场景。您有一个使用替换字符串|DataDirectory|的连接字符串 . 在桌面应用程序中，此目录通常与程序运行的目录相同。在 Visual Studio 中，您的程序在 BIN\DEBUG（或 x86 变体）目录中运行。因此，Visual Studio 将您的 MDF 文件从项目目录复制到 BIN\DEBUG 文件夹。您将记录添加到此副本，而不是项目文件夹中的记录。但是，Visual Studio Server Explorer 窗口有一个指向 Project Folder 数据库的连接，当然，它仍然是空的。
You could add another connection to the Server Explorer pointing to the folder BIN\DEBUG and check that your database has been updated or not.
您可以向服务器资源管理器添加另一个指向文件夹 BIN\DEBUG 的连接，并检查您的数据库是否已更新。
To complicate the matter, there is the property Copy to the Output Directoryassociated with the MDF file. If this property is set to Copy Alwayseverytime you start a new session within Visual Studio, the file is copied again from the project folder to the output directory (BIN\DEBUG) overwriting the copy already there with a new empty one. So the first run succeds, the second one fails.
The symptoms that you observed are a clear sign of this situation.
更复杂的是，有一个Copy to the Output Directory与 MDF 文件相关的属性。如果此属性设置为Copy Always每次在 Visual Studio 中启动新会话时，文件会再次从项目文件夹复制到输出目录 (BIN\DEBUG)，并用新的空副本覆盖已有的副本。所以第一次运行成功，第二次运行失败。
您观察到的症状是这种情况的明显迹象。
Simply change the property Copy to the Output directoryto Copy if newer, the code works well.
(Peraphs it is too early, but remember to change your query to a parameterized query. As is, you could break your code simply inserting a single quote in the txtName textbox like O'Malley, not to mention the Sql Injectionhack)
只需将属性更改Copy to the Output directory为Copy if newer，代码运行良好。
（Peraphs 现在还为时过早，但请记住将您的查询更改为参数化查询。照原样，您可以简单地在 txtName 文本框中插入一个单引号来破坏您的代码，例如O'Malley，更不用说Sql 注入hack）
回答by inkieweb
I think your issue is not with the insert code. It's with the way you're checking the database/table yourself. Particularly because you say you're getting primary key errors so something's being added to the table.
我认为您的问题不在于插入代码。这与您自己检查数据库/表的方式有关。特别是因为你说你得到了主键错误，所以一些东西被添加到表中。
Are you sure you're refreshing your view of the table properly? Are you sure you're checking the righttable in the right database?
您确定正确刷新了表格视图吗？您确定要检查正确数据库中的正确表吗？
回答by Apollo SOFTWARE
Make sure your initial catalog is set in your connection string, and be sure you are pointing to the right server/ instance of SQL. You may have multiple instances of SQL Server on the same server or whatever DB Server you are using. Also ensure you're going to the right table, of course.
确保在连接字符串中设置了初始目录，并确保指向正确的服务器/SQL 实例。您可能在同一台服务器或您使用的任何 DB Server 上有多个 SQL Server 实例。当然，还要确保你去的是正确的桌子。
using (SqlConnection cn = new SqlConnection(@"Persist Security Info=False;Integrated Security=true;Initial Catalog=testDB;server=(local)"))
{
string sql = "INSERT INTO Test (TestInsert) Values('" + txtName.Text + "')";
SqlCommand cmd = new SqlCommand(sql, cn);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Added new record", "Message", MessageBoxButton.OK);
}
Check in SQL Server Studio that the database exists. I don't recommend using the filename mdf in your code directly using AttachDbFilename. Use the initial catalog.
在 SQL Server Studio 中检查数据库是否存在。我不建议直接使用 AttachDbFilename 在代码中使用文件名 mdf。使用初始目录。
回答by Tanver Hasan
Well, i found the solution. I solved it by installing SSDT(SQL server data tools) for visual studio. Install it according to your visual studio version. Go to following link to download the SSDT for visual studio. https://msdn.microsoft.com/en-us/mt186501
嗯，我找到了解决方案。我通过为 Visual Studio 安装 SSDT（SQL 服务器数据工具）解决了这个问题。根据您的 Visual Studio 版本安装它。转到以下链接以下载 Visual Studio 的 SSDT。 https://msdn.microsoft.com/en-us/mt186501
回答by clevergenius
In the following of the Steve's answer you can get your database full path in properties window and substitute it with |DataDirectory| in string connection.
在史蒂夫的回答的以下内容中，您可以在属性窗口中获取数据库的完整路径并将其替换为 |DataDirectory| 在字符串连接中。
your code will be like
你的代码会像
conn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=G:\MhD\c#\phonebook\phonebook\phonebook\tellbook.mdf;Integrated Security=True";
