Insert or Update SQL Table with DataTable

Question

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]

Your current code should do the trick. Is it failin with an error, or failing silently? Is this ASP.NET? — Geeky Guy
– Geeky Guy, Commented Jul 23, 2013 at 14:44
@Renan, it is currently just adding the values to the end of the previous table. - Yes, i can use linq — TheGeekZn
– TheGeekZn, Commented Jul 23, 2013 at 14:45
Look here: msdn.microsoft.com/en-us/library/awzk4kc1.aspx, you need to specify at least the other sql statement for updating data, including the where clause, by which a record is uniquely identified. — Kai Hartmann
– Kai Hartmann, Commented Jul 23, 2013 at 14:51
@KaiHartmann no you don't: msdn.microsoft.com/en-us/library/… — Geeky Guy
– Geeky Guy, Commented Jul 23, 2013 at 14:52

Comfortably Numb · Accepted Answer · 2013-07-23 15:16:01Z

3

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.

answered Jul 23, 2013 at 15:16

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael Todd Over a year ago

Easy to program but may not scale very well. If there aren't too many records ever, this should work fine.

Comfortably Numb Over a year ago

@MichaelTodd true, but I beleive TRUNCATE/Bulk copy combination should hold well for hundreds of thousands of records. But it shouldn't even come to this since OP keeps all data in in-memory DataTable and (hopefully) that shouldn't be much.

Geeky Guy Over a year ago

That is a very inelegant, brute forcing solution.

Geeky Guy Over a year ago

@YuriyGalanter Heh. +1 for your pragmatism.

TheGeekZn Over a year ago

@MichaelTodd - The maximum entries is about 1500..Will this solution a go :)

|

Kai Hartmann · Accepted Answer · 2013-07-24 11:25:56Z

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();
                }
            }
        }
    }
}

Collectives™ on Stack Overflow

Insert or Update SQL Table with DataTable

2 Answers 2

7 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Related