INSERT语句影响1行，但是VS Server Explorer显示它实际上并未更新SQL CE数据库 [英] INSERT statement affects 1 row, but the VS Server Explorer shows that it doesn't actually update the SQL CE database

问题描述

我已经检查了其他Stack Overflow问题以找到答案，但我只是无法找到解决方案.我正在尝试将字符串数据插入到SQL CE(本地数据库)中.

我可以从数据库中读取数据，但是无论是对数据进行硬编码还是尝试使用参数添加数据，都无法插入数据.

要修改的表称为 users .它具有3列: ID ， name ， email .

这是我正在使用的代码:

 字符串连接= Properties.Settings.Default.LocalDBConnectionString;使用(var con = new SqlCeConnection(connection)){con.Open();//(我也尝试添加参数，但是无法正常工作.)使用(var com = new SqlCeCommand("INSERT INTO users(name，email)" +值(N'jimmy'，N'[e-mail]')"，con)){int numRowsAffected = com.ExecuteNonQuery();//这会返回受影响的1行"，但实际上不会更新数据库Console.WriteLine(numRowsAffected);Console.ReadKey();}} 

尽管控制台输出 1 ，但是由于受影响的是1行，因此当我返回数据库并进行检查时，它没有插入新数据.

我正在关注本教程，但是只有读取有效，插入或写入无效工作.

我还发现了类似的堆栈溢出问题，但是使用参数仍然无法使其正常工作.

---

我的连接字符串是:

  Data Source = | DataDirectory | \ LocalDB.sdf 

我不知道如何找到构建设置"，它是我在控制台应用程序解决方案中创建的本地数据库，它存储在项目目录中.

我可以使用调试"配置吗?

---

这是我发现的另一个示例 :

  SqlCeConnection con =新的SqlCeConnection(Properties.Settings.Default.LocalDBConnectionString);con.Open();SqlCeCommand comInsert =新的SqlCeCommand("INSERT INTO users(name，email)VALUES('value 1'，'value 2')"，con);comInsert.ExecuteNonQuery();SqlCeCommand comSelect =新的SqlCeCommand("SELECT * FROM users"，con);SqlCeDataReader reader = comSelect.ExecuteReader();而(reader.Read()){Console.WriteLine("{0} {1}"，reader ["name"]，reader ["email"]));Console.ReadKey();}con.Close(); 

我刚刚复制并粘贴了它，并更改了主要变量.此示例临时插入数据，但是当我转到Visual Studio的服务器资源管理器并查看表时，它尚未更新.

解决方案

您确定 INSERT 进入的数据库和您在Server Explorer中查看的数据库是否是同一数据库?/p>

记住，我们在这里谈论的是基于文件的数据库(SQL Server CE ).因此，可能是您正在 INSERT 插入数据库副本，然后查看(未更改的)原始文件.

• 数据库文件是否在您的解决方案中?如果是这样，是否将其复制到输出目录(例如 bin \ Debug )?检查相应解决方案项的属性，如下面的屏幕截图所示(抱歉，它是德语的):

• 连接字符串中的 DataDirectory 到底指向何处?如果不确定，暂时将其替换为绝对的东西，看看是否能解决您的问题.

请确保您的连接字符串确实指向您正在使用服务器资源管理器查看的数据库！

I've checked other Stack Overflow questions to find answers, but I just can't find a solution to this. I am trying to insert string data into a SQL CE (local database).

I can read from the database, but I cannot insert data, regardless of whether I hard-code it or try to add it with parameters.

The table to be modified is called users. It has 3 columns: ID, name, email.

Here is the code I am using:

string connection = Properties.Settings.Default.LocalDBConnectionString;

using (var con = new SqlCeConnection(connection))
{
    con.Open();

    // (I also tried adding with parameters but couldn't get it working.)
    using (var com = new SqlCeCommand("INSERT INTO users (name, email) " +
                                      "VALUES (N'jimmy', N'[e-mail]')", con))
    {
        int numRowsAffected = com.ExecuteNonQuery();

        // This returns '1 row affected' but doesn't actually update the database
        Console.WriteLine(numRowsAffected);
        Console.ReadKey();
    }
}

Although the Console outputs 1, because 1 row is affected, when I go back into the database and check, it has not inserted the new data.

I was following this tutorial, but only the Read works, the Insert or write doesn't work.

I also found this similar Stack Overflow question, but using parameters, still couldn't get it to work.

---

Edit:

My connection string is:

Data Source=|DataDirectory|\LocalDB.sdf

I don't know how to find the Build Settings, it is a Local Database I created within the console application solution, It is stored in the project directory.

I am in "Debug" configuration if that helps?

---

Here is another example I found on the internet:

SqlCeConnection con = new SqlCeConnection(Properties.Settings.Default.LocalDBConnectionString);
con.Open();

SqlCeCommand comInsert = new SqlCeCommand ("INSERT INTO users(name, email) VALUES('value 1', 'value 2')", con);
comInsert.ExecuteNonQuery();

SqlCeCommand comSelect = new SqlCeCommand("SELECT * FROM users", con);
SqlCeDataReader reader = comSelect.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine("{0} {1}", reader["name"], reader["email"]);
    Console.ReadKey();
}

con.Close();

I just copied and pasted this in, and changed the main variables. This example temporarily inserts the data, but when I go to Visual Studio's Server Explorer and view the table, it hasn't updated.

解决方案

Are you sure that the database you INSERT into and the database you're looking at with Server Explorer are the same one?

Remember, we're talking about a file-based DB here (SQL Server CE). So it could be that you're INSERTing into a copy of your DB and then look at the (unchanged) original.

• Is the DB file part of your solution? If so, does it get copied to the output directory (e.g. bin\Debug)? Check the properties of the corresponding solution item, like in the screenshot below (sorry for it being in German):

• Where exactly does DataDirectory in the connection string point to? If you're not sure, replace it with something absolute for the time being and see if that solves your problem.

Make double sure that your connection string really points to the database you're looking at with Server Explorer!

这篇关于INSERT语句影响1行，但是VS Server Explorer显示它实际上并未更新SQL CE数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！