C# - 使用DataAdapter从一个DataTable更新SQL表格 - SQL表格没有更新

Question

我使用 select * from 从 Excel 电子表格中将数据导入到 DataTable dt 中，现在想把这些值更新到 SQL 表中。SQL 表是通过从原始的 Excel 电子表格手动导入到 SQL 中而创建的，并且已经有了一个主键。用户更新电子表格后，我需要更新 SQL 值。我将 dt.RowState 设置为修改状态，希望能够触发更新操作。虽然没有报错，但是 SQL 表没有被更新。之前的测试显示我的 SQL 权限和连接都是正常的，我可以修改该表。

connectionToSQL = new SqlConnection(SQLConnString);
connectionToSQL.Open();
var cmd = new SqlCommand("SELECT * FROM TAGS$",connectionToSQL);                 
var da = new SqlDataAdapter(cmd);
var b = new SqlCommandBuilder(da);
foreach (DataRow r in dt.Rows)
{
    r.SetModified();
}
da.Update(dt);   

- cjjeeper

---

你考虑过使用SQL Profiler来查看是否有任何SQL实际上发送到了SQL服务器吗？ - Conrad Frix

我没有修改dt中的值。请重新阅读问题。Excel中的dt中的值与SQL表中的值不同。foreach循环手动将行状态设置为“已修改”，以便调用UPDATE。 - cjjeeper

Alex，请再仔细看一下整个问题。问题实际上是让datatable看起来像已经被修改，以便对所有行执行更新。 - cjjeeper

我可能有点迟钝，但你实际上并没有对数据库做任何操作；只是对Excel文件进行了修改。此外，变量DT在代码片段的范围内不存在。请发布整个代码。看起来你只是试图修改Excel，而不是SQL数据库。 - user220583

检查您的行是否具有版本信息，并查看它们是否实际上具有原始版本和修改版本。如果没有，您的更新将无法工作。如果所有数据实际上都存在于表中（因此您仅修改行而不插入它们），则可以通过使用AcceptChanges方法来伪造必要的条件。因此，r.SetModified()，然后r.AcceptChanges()，然后再次r.SetModified。 - B H

Answer 1

试试这个：

using System.Data;
using System.Data.SqlClient;
using System;
namespace Q308507 {
    class Class1 
    {
        static void Main(string[] args) 
        {
            SqlConnection cn = new SqlConnection();
            DataSet CustomersDataSet = new DataSet();
            SqlDataAdapter da;
            SqlCommandBuilder cmdBuilder;
            // Set the connection string of the SqlConnection object
            // to connect to the SQL Server database in which you
            // created the sample table.
            cn.ConnectionString =
            "Server=server;Database=northwind;UID=login;PWD=password;";
            cn.Open();      
            // Initialize the SqlDataAdapter object by specifying a
            // Select command that retrieves data from the sample table.
            da = new SqlDataAdapter("select * from CustTest order by CustId", cn);
            // Initialize the SqlCommandBuilder object to automatically
            // generate and initialize the UpdateCommand,
            // InsertCommand, and DeleteCommand properties
            // of the SqlDataAdapter.
            cmdBuilder = new SqlCommandBuilder(da);
            // Populate the DataSet by running the Fill method
            // of the SqlDataAdapter.
            da.Fill(CustomersDataSet, "Customers");
            // Display the Update, Insert, and Delete commands
            // that were automatically generated
            // by the SqlCommandBuilder object.
            Console.WriteLine(
                "Update command Generated by the Command Builder : ");
            Console.WriteLine(
                "==================================================");
            Console.WriteLine(
                cmdBuilder.GetUpdateCommand().CommandText);
            Console.WriteLine("         ");
            Console.WriteLine(
                "Insert command Generated by the Command Builder : ");
            Console.WriteLine(
                "==================================================");
            Console.WriteLine(cmdBuilder.GetInsertCommand().CommandText);
            Console.WriteLine("         ");        
            Console.WriteLine(
                "Delete command Generated by the Command Builder : ");
            Console.WriteLine(
                "==================================================");
            Console.WriteLine(cmdBuilder.GetDeleteCommand().CommandText);
            Console.WriteLine("         ");
            // Write out the value in the CustName field before
            // updating the data using the DataSet.
            Console.WriteLine("Customer Name before Update : " +
                CustomersDataSet.Tables["Customers"].Rows[0]["CustName"]);
    
            // Modify the value of the CustName field.
            CustomersDataSet.Tables["Customers"].Rows[0]["CustName"] = "Jack";
            // Post the data modification to the database.
            da.Update(CustomersDataSet, "Customers");        
            Console.WriteLine("Customer Name updated successfully");
            // Close the database connection.
            cn.Close();
            // Pause
            Console.ReadLine();
        }
    }
}

- Keith Costa

---

这并不适用。我不会修改我的数据表中的值。它们与 SQL 表格不同，因为受源 Excel 工作表中用户输入的影响。 - cjjeeper

问题实际上是让数据表看起来像已经被修改，以便对所有行执行更新。 - cjjeeper

Answer 2

我认为SqlCommandBuilder生成的自动生成的SqlCommand在您的情况下并不真正适用（如果我正确理解了问题）。在SqlCommandBuilder生成的SQL Update语句的WHERE子句中，所有列的值都与它们在DataRow中的原始值进行比较。如果目标数据库中的原始值与其不匹配，则不会更新任何行。

这个链接可能有助于理解SqlCommandBuilder：

http://msdn.microsoft.com/en-us/library/ms971491.aspx

从那个链接中，尝试理解："adCriteriaAllCols"，因为这是SqlCommandBuilder使用的内容。我猜想你想要"AdCriteriaKey"的行为。

可能的解决方案之一是不使用SqlCommandBuilder，而是自己编写INSERT/UPDATE/DELETE SqlCommands，并将它们附加到SqlDataAdapter.InsertCommand、UpdateCommand和DeleteCommand中。这里有一些示例代码：http://support.microsoft.com/kb/308055。

编辑：.Net版本2.0及以上的SqlCommandBuilder具有ConflictOption属性。默认使用的是：CompareAllSearchableValues。尝试使用：OverwriteChanges，这将导致在SQL语句中生成的WHERE子句仅比较主键值。

- Moe Sisko

---

是的，我正在朝这个方向努力。我目前正在寻找一些旧代码，其中我“认为”我已经使用Excel和数据库完成了我现在尝试做的事情。但它必须是OleDbAdapter，我想知道是否有区别。Alex建议使用来自Excel的数据覆盖我的SQL中选择的数据表，这可能是（稍微）更有效的方法。 - cjjeeper

顺便说一句，你是100%正确的。我已经在文本可视化器中审查了更新命令，它不会更新任何内容。但是，我似乎被记忆所困扰，好像之前做过这件事。如果我找到并使其正常工作，我会发布代码的。 - cjjeeper

实际上，可能可以让CommandBuilder仅基于主键进行更新。尝试：sqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges - Moe Sisko

Answer 3

我试着发表评论，但被告知重新阅读问题。所以我重新阅读了一遍，但没有帮助 :) 在你的示例中，很少有代码将dt（你说这是从Excel填充的）与数据库连接起来。你有变量connectionToSQL、cmd、da和b。它们都连接到一个数据库。然后你遍历dt，但它并没有连接到数据库。这就是为什么在我的评论中，我要求提供修改dt行的示例源代码，因为我假设你会在某个地方拥有它，以期望更改会从Excel（填充dt）跳转到你的数据库。

我看到你正在调用da.Update(dt);尝试从数据库打开一个新数据集，并遍历dt中的行，将更改应用于你的新数据集中的行。据我所知 - 而且那里没有太多的代码 - 没有发出命令，因为数据适配器知道dt的内部行不是来自其数据源。这是我的猜测。

- Alex Norcliffe

---

谢谢Alex。我想我理解了你的思维过程。让我试着添加一些更多的信息。Excel中的更改是由最终用户在Excel内进行的。他们保存更改并关闭工作簿，然后将其交给我。我然后运行我的程序并从Excel工作簿中选择*到dt。此时，dt是一个具有某些值的数据表。它未被修改。dt中的值与从原始Excel导入的SQL表中的值不同。 - cjjeeper

所以我在内存中有一个未修改的datatable，但我想将它发送到SQL，因为“我”知道数据是（或可能）不同的。因此，我遍历它将行状态设置为修改状态，以调用Update命令来处理表格。这是我在表格上唯一做的工作。所以我的问题是，即使我已经将所有行设置为“修改”状态，更新命令也没有做任何事情。（我可以在这里粘贴所有ImportExcel代码，但这很多，并且除了用所有内容填充一个工作表的dt之外没有做任何事情。 - cjjeeper

谢谢澄清 - 我已经明白了，但我的观点是您正在隐式更改所有这些行的后备存储区。只标记行为已修改，然后向使用另一个源的DataAdapter发出整个数据集的更新不会（就我所知）发出更新操作。因此，我建议（部分作为诊断操作）从目标数据源创建一个单独的数据集，并在循环中在发出更新之前将值复制过去。 - Alex Norcliffe

啊，我现在明白你的意思了。是的，理论上应该可以工作。 - cjjeeper

我将行设置为“添加”（使用循环），而不是将行设置为“修改”，然后da.update按预期工作。 - cjjeeper