Executing Stored Procedures

There are three different kinds of helpers for calling stored procedures:

• ExecuteStoredProcedureReader<T>
  retrieve a result set as a List of a known type

• ExecuteStoredProcedureReader
  retrieve a result set as generic DbDataReader

• ExecuteStoreProcedureNonQuery
  Call a stored procedure that doesn't return any result data

Returning Entity Data

A common scenario is to return a result set and turning the result data into a list of objects of a specified type.

using (var context = new WebStoreContext())
{
    IEnumerable<Customer> customers = context.Db.ExecuteStoredProcedureReader<Customer>("GetCustomers",
        // named parameter requires CreateParameter
        context.Db.CreateParameter("@cCompany","W%"));

    Assert.IsNotNull(customers, "Customers should not be null: " + context.Db.ErrorMessage);
    Assert.IsTrue(customers.Count() > 0, "Customer count should be greater than 0");
}

Returning a DataReader

In other scenarios you might return data that can't be mapped to an existing type or you might just be interested in one value out of a large list. In that scenario you'll want a generic way to access the data without the overhead of converting everything to a type. This code returns a DataReader you can use to iterate over the data.

using (var context = new WebStoreContext())
{
    DbDataReader reader = context.Db.ExecuteStoredProcedureReader("GetCustomers",
        context.Db.CreateParameter("@cCompany", "W%"));

    Assert.IsNotNull(reader, "Reader should not be null: " + context.Db.ErrorMessage);
    Assert.IsTrue(reader.HasRows, "Reader should have rows");

    while (reader.Read())
    {
        var company = reader["Company"] as string;
        var entered = (DateTime) reader["Entered"];
        Console.WriteLine(company + " " + entered.ToString("d"));
    }
}

Non-Query Results and returning Output and Return Values

You can also call stored procedures that don't return any data and just perform some sort of processing. Often times these procs return output or return parameters.

Although I'm using a Non-Query example here, you can also capture output and return parameters from the ExecuteStoredProcedureReader methods in the same way as shown below.

The following example demonstrates calling a stored procedure that returns both an Output and Return value (same value actually) but has no data set result:

using (var context = new WebStoreContext())
{
    var countParm = context.Db.CreateParameter("@nCount", 0,
        parameterDirection: System.Data.ParameterDirection.Output);

    var returnValueParm = context.Db.CreateParameter("@returnValue", 0,
        parameterDirection: System.Data.ParameterDirection.ReturnValue);
    
    int result = context.Db.ExecuteStoredProcedureNonQuery("GetCustomerCount",
        // named parameter requires CreateParameter
        context.Db.CreateParameter("@cCompany", "W%"),
        countParm, returnValueParm);

    Assert.IsFalse(result == -1, "result shouldn't be -1. " + context.Db.ErrorMessage);

    Console.WriteLine("Count value: " + countParm.Value);
    Console.WriteLine("Return Value: " + returnValueParm.Value);
}