CommandBuilder and SqlTransaction to insert/update a row
- by Jesse
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?
