Every time I try and update a row via SqlDataAdapter.Update() in Mono, I get:
Unhandled Exception: System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 records. at System.Data.Common.DbDataAdapter.Update (System.Data.DataRow[] dataRows, Sy stem.Data.Common.DataTableMapping tableMapping) [0x00000]
The related C# code is:
IDbConnection conn = new SqlConnection(DB_CONN);
DataSet ds = new DataSet();
conn.Open();
IDbCommand command = conn.CreateCommand();
command.CommandText = "SELECT * FROM TestTable";
SqlDataAdapter adapter = new SqlDataAdapter((SqlCommand)command);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Fill(ds);
ds.Tables[0].TableName = "TestTable";
ds.Tables[0].Rows[0]["testInt"] = 5;
adapter.Update(ds, "TestTable");
After logging the query as it hits SQL Server 2008, it shows:
exec sp_executesql N'UPDATE [TestUpdate] SET [id] = @p1, [testInt] = @p2 WHERE (([id] = @p3) AND ((@p4 = 1 AND [testInt] IS NULL) OR ([testInt] = @p5)))', N'@p1 int, @p2 int, @p3 int, @p4 int, @p5 int', @p1=1, @p2=5, @p3=1, @p4=NULL, @p5=NULL
The database is a simple test to debug this issue, composed of two columns: an integer id column (pk) and integer testInt column, with nulls allowed. The code works fine unless the testInt value is NULL, in which case the exeption is thrown.
UPDATE [TestUpdate]
SET [id] = 1,
[testInt] = 5
WHERE (([id] = 1)
AND ((NULL = 1 AND [testInt] IS NULL)
OR ([testInt] = NULL)))
It appears @p4 should be 1 in this scenario, as to apply the IS NULL check, as opposed to NULL which results in an = NULL check (which I believe would fail if the value was NULL).
Does this looks like a Mono issue to anyone else, or am I just doing something silly/wrong?