Blog Post

EF Execute stored procedure

Monday, January 25, 2016 5:54 AM

There is multiple way of executing stored procedures

Usual way of executing stored procedures I have come across:

 

        public void ExecPsUsingDbo(int id, int userId)
        {
            using (var dbconnection = new SqlConnection(_context.Database.Connection.ConnectionString))
            {
                using (var tmsCommand = new SqlCommand("dbo.example", dbconnection))
                {
                    try
                    {
                        if (tmsConnection.State == ConnectionState.Closed)
                        {
                            tmsConnection.Open();
                        }

                        tmsCommand.CommandType = CommandType.StoredProcedure;

                        tmsCommand.Parameters.Add(new SqlParameter("@ParameterId", SqlDbType.Int)).Value = id;                        
                        tmsCommand.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int)).Value = userId;

                        tmsCommand.ExecuteReader();
                    }
                    catch (SqlException exception)
                    {
                        throw;
                    }
                }
            }
        }

It has couple issues, the main one is that I need to create new connection everytime I do execute the stored procedure.

this adds couple ms to seconds to every execution.

Executing SP using Entity Framework context

I have started to use this implementation. Even if you use the SP you need to be aware how does the EF works.

 

using System;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;

namespace Examples
{
    public class SpExample
    {
        private readonly DbContext _context;

        public SpExample(DbContext context)            
        {
            _context = context;
        }

        public void ExecSpUsingEfContext(int id, int userId)
        {
            var idParameter = new SqlParameter("@ParameterId", SqlDbType.Int) { Value = id };
            var userIdSqlParameter = new SqlParameter("@UserId", SqlDbType.Int) { Value = userId };

            try
            {
                var result = _context.Database.ExecuteSqlCommand(
                    TransactionalBehavior.EnsureTransaction,
                    "exec dbo.example @ParameterId, @UserId",
                    idParameter,                    
                    userIdSqlParameter);

                if (result == 0)
                {
                    throw new Exception("Failed to execute sql sp");
                }
            }
            catch (SqlException exception)
            {
                ////var message = exception.Message;
                throw;
            }
        }
    }
}

 

Context and stored procedure issue.

Lets take example of class:

public class User{
    public int UserId{get;set;}
    public string Name{get;set;}
    public string Surname{get;set;}
}

Now lets have stored procedure that updates name of user.

If you execute stored procedure that updates the record, the context is out of date and your might get exception.

 

The way to refresh object context is by command

 this._context.Entry(entity).Reload();
Blog
Products
Error pages
Links and tools