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]

Answer 1

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:

1. Delete all existing data from the SQL Table (you can use TSQL TRUNCATE statement for speed and efficiency
2. 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.