CommandBuilder and SqlTransaction to insert/update a row

Question

I can get this to work, but I feel as though I'm not doing it properly.

The first time this runs, it works as intended, and a new row is inserted where "thisField" contains "doesntExist"

However, if I run it a subsequent time, I get a run-time error that I can't insert a duplicate key as it violate the primary key "thisField".

static void Main(string[] args)
{           
    using(var sqlConn = new SqlConnection(connString) )
    {
        sqlConn.Open();
        var dt = new DataTable();

        var sqlda = new SqlDataAdapter("SELECT * FROM table WHERE thisField ='doesntExist'", sqlConn);
        sqlda.Fill(dt);

        DataRow dr = dt.NewRow();

        dr["thisField"] = "doesntExist"; //Primary key

        dt.Rows.Add(dr);

        //dt.AcceptChanges(); //I thought this may fix the problem. It didn't.

        var sqlTrans = sqlConn.BeginTransaction();

        try
        {
            sqlda.SelectCommand = new SqlCommand("SELECT * FROM table WITH (HOLDLOCK, ROWLOCK) WHERE thisField = 'doesntExist'", sqlConn, sqlTrans);

            SqlCommandBuilder sqlCb = new SqlCommandBuilder(sqlda);

            sqlda.InsertCommand = sqlCb.GetInsertCommand();
            sqlda.InsertCommand.Transaction = sqlTrans;

            sqlda.DeleteCommand = sqlCb.GetDeleteCommand();
            sqlda.DeleteCommand.Transaction = sqlTrans;

            sqlda.UpdateCommand = sqlCb.GetUpdateCommand();
            sqlda.UpdateCommand.Transaction = sqlTrans;

            sqlda.Update(dt);

            sqlTrans.Commit();
        }
        catch (Exception)
        {
            //...
        }
    }
}

Even when I can get that working through trial and error of moving AcceptChanges around, or encapsulating changes within Begin/EndEdit, then I begin to experience a "Concurrency violation" in which it won't update the changes, but rather tell me it failed to update 0 of 1 affected rows.

Is there something crazy obvious I'm missing?

Answer 1

I was able to over come my problems by manually deleting the row(s) after filling the datatable. I'm unable to use dt.Clear(), as doing so still results in an exception due to trying to insert a record with a duplicate primary key.

sqladapter.Fill(dt);

foreach( DataRow d in dt.Rows )
    d.Delete();

DataRow dr = dt.NewRow();

dr.BeginEdit();
//update dr
dr.EndEdit();

dt.Rows.Add(dr);

The above will successfully let me insert a new row, and update a pre-existing row. Removing the foreach will generate the "Cannot insert duplicate key in object," so I feel I've at least found a solution; although, I feel as though it's not the solution.

Hope this helps someone.