为什么一个数据库失败保存更改？ [英] Why saving changes to a database fails?

问题描述

我在一个控制台应用程序下面的C＃code。

I have following C# code in a console application.

每当我调试应用程序并运行QUERY1（这将插入一个新值到数据库），然后运行query2（用于显示数据库中的所有条目），我可以看到新的条目我插清晰。然而，当我在数据库关闭应用程序，并检查表（在Visual Studio），就消失了。我不知道它为什么不节能。

Whenever I debug the application and run the query1 (which inserts a new value into the database) and then run query2 (which displays all the entries in the database), I can see the new entry I inserted clearly. However, when I close the application and check the table in the database (in Visual Studio), it is gone. I have no idea why it is not saving.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlServerCe;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string fileName = "FlowerShop.sdf";
                string fileLocation = "|DataDirectory|\\";
                DatabaseAccess dbAccess = new DatabaseAccess();
                dbAccess.Connect(fileName, fileLocation);

                Console.WriteLine("Connected to the following database:\n"+fileLocation + fileName+"\n");
                string query = "Insert into Products(Name, UnitPrice, UnitsInStock) values('NewItem', 500, 90)";
                string res = dbAccess.ExecuteQuery(query);
                Console.WriteLine(res);

                string query2 = "Select * from Products";
                string res2 = dbAccess.QueryData(query2);
                Console.WriteLine(res2);
                Console.ReadLine();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                Console.ReadLine();
            }
        }
    }

    class DatabaseAccess
    {
        private SqlCeConnection _connection;

        public void Connect(string fileName, string fileLocation)
        {
            Connect(@"Data Source=" + fileLocation + fileName);
        }

        public void Connect(string connectionString)
        {
            _connection = new SqlCeConnection(connectionString);
        }

        public string QueryData(string query)
        {
            _connection.Open();
            using (SqlCeDataAdapter da = new SqlCeDataAdapter(query, _connection))
            using (DataSet ds = new DataSet("Data Set"))
            {
                da.Fill(ds);
                _connection.Close();
                return ds.Tables[0].ToReadableString(); // a extension method I created
            }
        }

        public string ExecuteQuery(string query)
        {
            _connection.Open();
            using (SqlCeCommand c = new SqlCeCommand(query, _connection))
            {
                int r = c.ExecuteNonQuery();
                _connection.Close();
                return r.ToString();
            }
        } 
    }

编辑：忘了提，我使用SQL Server精简版4和VS2012前preSS

Forgot to mention that I am using SQL Server Compact Edition 4 and VS2012 Express.

推荐答案

这是一个相当普遍的问题。您使用 | DataDirectory目录| 替换字符串。这意味着，在调试应用程序在Visual Studio环境中，应用程序使用的数据库位于项目的子文件夹 BIN \ DEBUG 文件夹中（或x86变体） 。而这个效果很好，你不必连接到数据库，使更新操作的任何类型的错误。

It is a quite common problem. You use the |DataDirectory| substitution string. This means that, while debugging your app in the Visual Studio environment, the database used by your application is located in the subfolder BIN\DEBUG folder (or x86 variant) of your project. And this works well as you don't have any kind of error connecting to the database and making update operations.

但是，你退出调试会话，你看通过Visual Studio的服务器资源管理器数据库。该窗口有一个不同的连接字符串（可能指向您的数据库项目文件夹的副本）。你搜索你的表，你看不到的变化。

But then, you exit the debug session and you look at your database through the Visual Studio Server Explorer. This window has a different connection string (probably pointing to the copy of your database in the project folder). You search your tables and you don't see the changes.

那么问题变得更糟。如果您列出您的项目文件和物业之间的数据库文件复制到输出目录设置为复制始终，那么，每次重新启动调试会话时，原始数据库文件是从项目文件夹到输出文件夹（BIN \ DEBUG），因此您的previous更改将丢失复制。现在，还您的应用程序没有看到之前所做的更改。

Then the problem get worse. If you have your database file listed between your project files and the property Copy to Output directory is set to Copy Always, then, every time you restart a debug session, the original database file is copied from the project folder to the output folder (BIN\DEBUG) and thus your previous changes are lost. Now, also your application doesn't see the changes made before.

您可以停止这个问题更改属性复制到输出目录到复制如果较新或绝不能复制。你也可以更新服务器资源管理器您的ConnectionString看看你的数据库的工作副本或创建第二个连接。第一个仍然指向在项目文件夹，而第二个点在bin \ debug文件夹数据库的数据库。通过这种方式，你可以保持原有的数据库可供部署的目的和模式的变化，同时，随着第二个连接，你可以看看你的编码工作的有效成果。

You could stop this problem changing the property Copy To Output Directory to Copy If Newer or Never Copy. Also you could update your connectionstring in the Server Explorer to look at the working copy of your database or create a second connection. The first one still points to the database in the project folder while the second one points to the database in the BIN\DEBUG folder. In this way you could keep the original database ready for deployment purposes and schema changes, while, with the second connection you could look at the effective results of your coding efforts.

修改的 MS-访问数据库用户的特别警示。看着你的表的简单行为改变你的数据库的修改日期此外，如果你不写或改变任何东西。所以标记复制如果较新踢和数据库文件复制到输出目录。随着进入更好的使用复制决不。

EDIT Special warning for MS-Access database users. The simple act of looking at your table changes the modified date of your database ALSO if you don't write or change anything. So the flag Copy if Newer kicks in and the database file is copied to the output directory. With Access better use Copy Never.

这篇关于为什么一个数据库失败保存更改？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！