I'm using context.Database.ExecuteSql to update a table. The update with where clause is executed correctly and the record is updated. However the method returns 2 for rowcount instead of 1. When I execute the update statement in SSMS, the result rowcount returned is 1. Can someone provide insight on this?
string query =
string.Format("update {0} set Description = '{1}', Code = '{2}', LastUpdatedBy = '{3}', LastUpdatedDate = '{4}' where ID = {5};",
tableName,
description,
code,
lastUpdatedBy,
lastUpdatedDate,
ID);
int rowCount = 0;
string message = string.Empty;
using (DBContext context = new DBContext())
{
rowCount = context.Database.ExecuteSqlCommand(TransactionalBehavior.EnsureTransaction, query);
}
return rowCount == 0 ? //this return 2 instead of 1.
new SaveResult(SaveResult.MessageType.Error, string.Format("There was an error updating a record in the {0} table.", tableName), "Index") :
new SaveResult(SaveResult.MessageType.Success, string.Format("The update of {0} was successful.", tableName), "Index");
This returns rowcount = 1 in SSMS:
update zAddressTypes
set Description = 'Current', Code = '101122', LastUpdatedBy = 'user', LastUpdatedDate = '10/20/2014 12:17:26 PM'
where ID = 1;
DECLARE @RowCount INTEGER = @@ROWCOUNT;
select @RowCount;
The rowcount is being returned separately. What you are seeing here is the exit status of the query.
ExecuteSqlCommand
return value is for the status of query not for row count. You may want to look into using a datareader or something similar to return the rowcount.