DataAdapter.Update returns 0 and doesn't update the table in SQL Server.
Short one today.
Sometimes there are reports that the DataAdapter.Update(datatable) method returns 0 and that the backing table in the database doesn’t get updated.
How come? On the client side we can clearly see that our added and modified rows have taken effect, ie. in the datatable all looks ok.
Well, the most common reason is that AcceptChanges() have been called on the DataTable prior to the Update. Why does this matter you may ask.
What happens when you call the Update method on the DataAdapter is that each row in the DataTable is checked for its RowState (Added, Modfied etc) and if there is a
change to it, ie. the RowState is not Unchanged, the appropriate action is taken. If a row has been added, an insert is triggered and if a modification has taken place, then an update is triggered.
Since I’m better with examples than words, let’s do it that way. First create a table with some rows in SQL Server:
create table TestTable (id int primary key, fname nvarchar(50), lname nvarchar(50))
insert into TestTable values (1, 'Mike', 'Spike')
insert into TestTable values (2, 'John', 'Johnson')
insert into TestTable values (3, 'Pete', 'Peterson')
--drop table TestTable
Then create a new C# Console application and run the following code:
static void Main(string[] args)
{
string cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";
string sql = "SELECT id, fname, lname FROM TestTable";
using (SqlConnection con = new SqlConnection(cs))
{
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataTable dt = new DataTable("Persons");
da.Fill(dt);
Console.WriteLine("Rows are untouched...");
ViewDataTableRowStatus(dt);
// Modify a row.
Console.WriteLine("Modifying the first row...");
dt.Rows[0][1] = "Jake";
ViewDataTableRowStatus(dt);
// Add a row.
Console.WriteLine("Adding a row");
dt.Rows.Add(new object[]{100, "Paul", "Paulson..."});
ViewDataTableRowStatus(dt);
// Now, if calling AcceptChanges() all rows will be set to Unchanged.
Console.WriteLine("Calling AcceptChanges()...");
dt.AcceptChanges();
ViewDataTableRowStatus(dt);
// This means that when we call update on the DataAdapter, it will return 0
// since no rows has actuall been sent to the database. (first we need to build the INSERT and UPDATE commands)
SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.InsertCommand = builder.GetInsertCommand();
da.UpdateCommand = builder.GetUpdateCommand();
int rows = da.Update(dt);
Console.WriteLine("Updated rows: {0}", rows);
}
}
private static void ViewDataTableRowStatus(DataTable dt)
{
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("Id:{0}\tFirstName: {1}\tRowState: {2}", dr[0].ToString(), dr[1].ToString(), dr.RowState.ToString());
}
Console.WriteLine();
}
and run it, the output should be:
Rows are untouched...
Id:1 FirstName: Mike RowState: Unchanged
Id:2 FirstName: John RowState: Unchanged
Id:3 FirstName: Pete RowState: Unchanged
Modifying the first row...
Id:1 FirstName: Jake RowState: Modified
Id:2 FirstName: John RowState: Unchanged
Id:3 FirstName: Pete RowState: Unchanged
Adding a row
Id:1 FirstName: Jake RowState: Modified
Id:2 FirstName: John RowState: Unchanged
Id:3 FirstName: Pete RowState: Unchanged
Id:100 FirstName: Paul RowState: Added
Calling AcceptChanges()...
Id:1 FirstName: Jake RowState: Unchanged
Id:2 FirstName: John RowState: Unchanged
Id:3 FirstName: Pete RowState: Unchanged
Id:100 FirstName: Paul RowState: Unchanged
Updated rows: 0
So this should illustrate that when the AccepChanges() method is called, then all the rows in the DataTable is set to Unchanged. And as such, they will not be updated to the database.
Try to comment out the AcceptChanges() call, and the added and modified row should be sent to the database.
Some more info here:
".NET Framework Class Library - DataAdapter.Update Method"
https://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx
".NET Framework Class Library - DataTable.AcceptChanges Method"
https://msdn.microsoft.com/en-us/library/system.data.datatable.acceptchanges.aspx