Method returns -1 instead of the number of updated rows

c# entity-framework-core

Question

I'm trying to do a bulk update in my database by executing a stored procedure. I'm calling the DbContext.Database.ExecuteSqlCommand method like so:

_context.Database.ExecuteSqlCommand("EXECUTE TestProcedure");

In this crude test, my stored procedure looks like this:

BEGIN
    UPDATE TestTable
    SET MyColumn = 'changed'
END

Select @@ROWCOUNT

When I execute the SProc in SSMS directly, I get the number of rows returned. However, if I do it via the code posted above, I ALWAYS get a result of -1 (even when the SProc executes correctly - which I can verify in my table).

When I change the code to this:

_context.Database.ExecuteSqlCommand("UPDATE TestTable SET MyColumn = "changed");

I then get the correct number of updated rows. Since my actual code is more verbose, I'd like to use a SProc, but I need an accurate number of rows affected.

How can I use the SProc?

1
0
11/28/2017 6:22:25 PM

Accepted Answer

Database.ExecuteSqlCommand returns the number of rows affected, not the result of any SELECT... statements inside the SQL.

If you want to return the result sets, you will need to use Database.SqlQuery instead.

EDIT: As this is Entity Framework Core, the SqlQuery method does not exist (yet, it is planned). However, you could do something like this which grabs the raw connection and uses reflection to return the data. It's not particularly efficient but should work:

public static class DbContextExtensionss
{
    public static IEnumerable<TEntity> ExecSQL<TEntity>(this DbContext context, string query) where TEntity : new()
    {
        using (var connection = context.Database.GetDbConnection())
        using (var command = connection.CreateCommand())
        {
            command.CommandText = query;
            command.CommandType = CommandType.Text;

            if(connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            using (var result = command.ExecuteReader())
            {
                while (result.Read())
                {
                    var entity = new TEntity();
                    foreach (PropertyInfo prop in typeof(TEntity).GetProperties())
                    {
                        if (!object.Equals(result[prop.Name], DBNull.Value))
                        {
                            prop.SetValue(entity, result[prop.Name], null);
                        }
                    }
                    yield return entity;
                }
            }
        }
    }
}   

And you would use it like this:

public class TestClass
{
    public int RowCount { get; set; }
}

var results = _context.ExecSQL<>("EXECUTE TestProcedure");

foreach(var testClass in results)
{
    Console.WriteLine(testClass.RowCount);
}

NOTE: you would need to change the stored procedure to name the column it returns to match the class:

SELECT @@ROWCOUNT AS [RowCount]
2
11/29/2017 2:26:46 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow