C# 使用 DataTable 插入或更新 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17813422/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert or Update SQL Table with DataTable
提问by TheGeekZn
Firstly, I can't use any stored procedures or views.
I know this may seem counter-productive, but those aren't my rules.
首先，我不能使用任何存储过程或视图。
我知道这可能会适得其反，但这不是我的规则。
I have a DataTable, filled with data. It has a replicated structure to my SQL table.
我有一个DataTable, 充满了数据。它有一个复制到我的 SQL 表的结构。
ID - NAME
ID - NAME
The SQL table currently has a bit of data, but I need to now update it with all the data of my DataTable. It needs to UPDATE the SQl Table if the ID's match, or ADD to the list where it's unique.
SQL 表目前有一些数据，但我现在需要用我的DataTable. 如果 ID 匹配，则需要更新 SQL 表，或者添加到唯一的列表中。
Is there any way to simply do this only in my WinForm Application?
有没有办法只在我的 WinForm 应用程序中简单地做到这一点？
So far, I have:
到目前为止，我有：
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:
数据表：
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]
回答by Yuriy Galanter
Since you're going update existing values and insert new ones from datatable that has ALL the data anyway I think the following approach might work best for you:
由于您要更新现有值并从包含所有数据的数据表中插入新值，因此我认为以下方法可能最适合您：
- Delete all existing data from the SQL Table (you can use TSQL TRUNCATEstatement for speed and efficiency
- Use ADO.NET
SqlBulcCopyclass to bulk insert data from ADO.NET table to SQL table using WriteToServermethod.
- 从 SQL 表中删除所有现有数据（为了速度和效率，您可以使用 TSQL TRUNCATE语句
- 使用 ADO.NET
SqlBulcCopy类使用WriteToServer方法将数据从 ADO.NET 表批量插入到 SQL 表。
No views or stored procedures involved, just pure TSQL and .NET code.
不涉及视图或存储过程，只是纯 TSQL 和 .NET 代码。
回答by Kai Hartmann
This is my try on this, by which I was able to at least update the records in the database. It differs from your solution so far, that it applies values to the DataTable after Fill() has been executed. You would have to search manually in the DataTable if there are records you have to update, that's the drawback. Another thing is, I realized, that DataTable does not inherit the table schema from database, if you haven't set MissingSchemaAction properly.
这是我对此的尝试，通过它我至少能够更新数据库中的记录。到目前为止，它与您的解决方案不同，它在执行 Fill() 后将值应用于 DataTable。如果您必须更新记录，则必须在 DataTable 中手动搜索，这是缺点。另一件事是，我意识到，如果您没有正确设置 MissingSchemaAction，DataTable 不会从数据库继承表架构。
So here is the example code (complete ConsoleApplication):
所以这是示例代码（完整的 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();
}
}
}
}
}
