Insert or Update SQL Table with DataTable

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.

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.

Is there any way to simply do this only in my WinForm Application?

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]

---

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 TRUNCATE statement for speed and efficiency

Use ADO.NET SqlBulcCopy class to bulk insert data from ADO.NET table to SQL table using WriteToServer method.

No views or stored procedures involved, just pure TSQL and .NET code.

---

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.

So here is the example code (complete 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();
                }
            }
        }
    }
}

链接地址: http://www.djcxy.com/p/14734.html

上一篇: 与RadioButton一起添加EditText

下一篇: 用DataTable插入或更新SQL表