数据未永久保存到SQL表 [英] Data not saving permanently to SQL table

问题描述

我在Visual Studio Express 2012中为WPF应用程序使用C＃。我按照教程找到了这里。
我创建了一个 testDb.mdf 基于本地服务的数据库。我打开应用程序，输入文本，点击添加，然后数据添加到数据库中。我只知道这一点，因为我将一个字段设置为主键并且是唯一的。如果我再次尝试添加相同的内容，则会收到一条错误消息，指出它已经存在。

I am using C# for a WPF application in Visual Studio Express 2012. I followed the tutorial found here. I created a testDb.mdf local 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.

退出应用程序后，数据库中未显示任何内容。我输入的数据不见了。为什么数据不是永久性的？

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

连接字符串：

<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>

推荐答案

这是常见的情况。您有一个使用替换字符串 | DataDirectory | 的连接字符串。在桌面应用程序中，该目录通常与程序运行所在的目录相同。在Visual Studio中，您的程序在BIN\DEBUG（或x86变体）目录中运行。因此，Visual Studio将您的MDF文件从项目目录复制到BIN\DEBUG文件夹。您将记录添加到此副本，而不是项目文件夹中的记录。但是，Visual Studio Server Explorer窗口具有一个指向Project Folder数据库的连接，该连接当然仍然为空。

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.

您可以向Server Explorer添加另一个连接。指向文件夹BIN\DEBUG并检查您的数据库是否已更新。

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.

要使事情复杂化，有一个属性 Copy到与MDF文件关联的输出目录。如果将此属性设置为始终复制，则每次在Visual Studio中启动新会话时，该文件就会再次从项目文件夹复制到输出目录（BIN\DEBUG）用一个新的空副本覆盖已经存在的副本。因此，第一次运行成功，第二次失败。

您观察到的症状清楚表明了这种情况。

To complicate the matter, there is the property Copy to the Output Directory associated with the MDF file. If this property is set to Copy Always everytime 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.

只需更改属性复制到输出目录到如果更新则复制，代码运行良好。

（Peraphs现在还为时过早，但是请记住将查询更改为参数化查询。按原样，您只需在txtName文本框中插入单引号（例如 O'Malley ）即可破坏代码，更不用说 Sql注入 hack）

Simply change the property Copy to the Output directory to 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 Injection hack)

这篇关于数据未永久保存到SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！