Search This Blog

Executing Stored Procedure with Entity Framework

The following is the description of executing stored procedure with Entity Framework.
It describes two separate methods for reading data and inserting/updating data through stored procedure.

The following is the code.

1. Fetching data

static List ExecuteReadStoredProcedure(string storedProcName, Dictionary parameters)
        {
            using (var entityContext = new ImplementedDbContext())
            {
                var storedProcedureParams = GetStoredProcedureParameters(storedProcName, parameters);
                return entityContext.Database.SqlQuery(storedProcedureParams.StoredProcedureName,
                    storedProcedureParams.Parameters.ToArray()).ToList();              
            }
        }
2. Insert/Update

static int ExecuteInsertUpdateStoredProcedure(string storedProcName, Dictionary parameters)
        {
            using (var entityContext = new ImplementedDbContext())
            {
                var storedProcedureParams = GetStoredProcedureParameters(storedProcName, parameters);
                return entityContext.Database.ExecuteSqlCommand(storedProcedureParams.StoredProcedureName,storedProcedureParams.Parameters.ToArray());
            }
        }

3. Helper method
private static StoredProcedureParams GetStoredProcedureParameters(string storedProcedureName,Dictionary parameters) {
            StringBuilder query = new StringBuilder();
            var listOfParameters = new List();

            query.Append(storedProcedureName + " ");
            var count = 0;

            foreach (var p in parameters)
            {
                count++;
                query.Append(p.Key);
                if (count != parameters.Count)
                {
                    query.Append(",");
                }

                listOfParameters.Add(new SqlParameter(p.Key, p.Value));
            }

            return new StoredProcedureParams() {
                StoredProcedureName = query.ToString(),
                Parameters = listOfParameters
            };
        }

 4. Object Parameter

 class StoredProcedureParams {
            public string StoredProcedureName { get; set; }
            public List Parameters { get; set; }
        }

5.  Implementing Read
var dictParameters = new Dictionary();
                dictParameters.Add("@parameter1",value of parameter 1);
                dictParameters.Add("@parameter2",value of parameter 2);

                var readResponse = ExecuteReadStoredProcedure("Your-Read-StoredProcedureName", dictParameters);
5.1 Response Object
public class MyResponse
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

6. Implementing Insert/Update
var dictParametersCreateEmailTemp = new Dictionary();
                dictParametersCreateEmailTemp.Add("@parameter1","value");
                dictParametersCreateEmailTemp.Add("@parameter2", "value");
                dictParametersCreateEmailTemp.Add("@parameter3", "value");
                dictParametersCreateEmailTemp.Add("@parameter4", "value");

                var insertResponse = ExecuteInsertUpdateStoredProcedure("Your-Insert-StoredProcedureName", dictParametersCreateEmailTemp);