使用DataTable插入或更新SQL表

Question

首先，我不能使用任何存储过程或视图。
我知道这似乎有些不利于生产力，但这不是我的规定。

我有一个DataTable，其中填充了数据。它的结构与我的SQL表相同。

ID - NAME

目前SQL表中有一些数据，但现在我需要用DataTable中的所有数据更新它。如果ID匹配，则需要更新SQL表，否则将其添加到唯一列表中。

是否有办法仅在我的WinForm应用程序中完成此操作？

到目前为止，我已经做到了：

SqlConnection sqlConn = new SqlConnection(ConnectionString);
            SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT * FROM {0}", cmboTableOne.SelectedItem), sqlConn);
            using (new SqlCommandBuilder(adapter))
            {
                try
                {
                    adapter.Fill(DtPrimary);
                    sqlConn.Open();
                    adapter.Update(DtPrimary);
                    sqlConn.Close();
                }
                catch (Exception es)
                {
                    MessageBox.Show(es.Message, @"SQL Connection", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }

数据表：

        DataTable dtPrimary = new DataTable();

        dtPrimary.Columns.Add("pv_id");
        dtPrimary.Columns.Add("pv_name");

        foreach (KeyValuePair<int, string> valuePair in primaryList)
        {
            DataRow dataRow = dtPrimary.NewRow();

            dataRow["pv_id"] = valuePair.Key;
            dataRow["pv_name"] = valuePair.Value;

            dtPrimary.Rows.Add(dataRow);

SQL：

CREATE TABLE [dbo].[ice_provinces](
    [pv_id] [int] IDENTITY(1,1) NOT NULL,
    [pv_name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ice_provinces] PRIMARY KEY CLUSTERED 
(
    [pv_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

- TheGeekZn

---

@KaiHartmann 不，你不需要这样做：http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlcommandbuilder.aspx - Geeky Guy

好的，那么可能是你的数据表中没有设置主键？你能展示一下数据表的创建过程吗？ - Kai Hartmann

@Renan 主键已经存在。 - TheGeekZn

@Renan 帖子已更新。SQL表已经有了主键。 - TheGeekZn

让我们在聊天中继续这个讨论：http://chat.stackoverflow.com/rooms/34047/discussion-between-newambition-and-kai-hartmann - TheGeekZn

Answer 1

由于您将从具有所有数据的数据表更新现有值并插入新值，因此我认为以下方法可能最适合您：

1. 删除 SQL 表中的所有现有数据（可以使用 TSQL TRUNCATE 语句以提高速度和效率）。
2. 使用 ADO.NET SqlBulkCopy 类使用WriteToServer 方法批量将数据从 ADO.NET 表插入到 SQL 表中。

没有涉及视图或存储过程，只是纯粹的 TSQL 和 .NET 代码。

- Yuriy Galanter

---

编程容易但可能不太适合扩展。如果记录不是太多，这应该可以正常工作。 - Michael Todd

@MichaelTodd 是的，但我认为 TRUNCATE/Bulk copy 的组合应该对数十万条记录很有效。但这甚至不应该发生，因为 OP 将所有数据保存在内存中的 DataTable 中，（希望）这不应该太多。 - Yuriy Galanter

那是一个非常不优雅、采用蛮力的解决方案。 - Geeky Guy

@YuriyGalanter 哈哈，你的实用主义得到了我的认可。 - Geeky Guy

@MichaelTodd - 最大条目数约为1500..这个解决方案可行吗 :) - TheGeekZn

Answer 2

这是我尝试的方法，至少能够更新数据库中的记录。它与你的解决方案不同之处在于，在执行Fill()后，它将值应用于DataTable。如果有需要更新的记录，你必须在DataTable中手动搜索，这是一个缺点。

另一件事是，我意识到，如果你没有正确设置MissingSchemaAction，DataTable不会从数据库继承表模式。

因此，这里是完整的ConsoleApplication示例代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace SQLCommandBuilder
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnectionStringBuilder ConnStringBuilder = new SqlConnectionStringBuilder();
            ConnStringBuilder.DataSource = @"(local)\SQLEXPRESS";
            ConnStringBuilder.InitialCatalog = "TestUndSpiel";
            ConnStringBuilder.IntegratedSecurity = true;

            SqlConnection sqlConn = new SqlConnection(ConnStringBuilder.ConnectionString);

            SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT * FROM {0}", "ice_provinces"), sqlConn);
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;   // needs to be set to apply table schema from db to datatable

            using (new SqlCommandBuilder(adapter))
            {
                try
                {
                    DataTable dtPrimary = new DataTable();                                   

                    adapter.Fill(dtPrimary);

                    // this would be a record you identified as to update:
                    dtPrimary.Rows[1]["pv_name"] = "value";

                    sqlConn.Open();
                    adapter.Update(dtPrimary);
                    sqlConn.Close();
                }
                catch (Exception es)
                {
                    Console.WriteLine(es.Message);
                    Console.Read();
                }
            }
        }
    }
}

- Kai Hartmann