Insert or Update SQL Table with DataTable
asp.net, c#, sql-server
Solution
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.
Problem
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] ```