Stored Procedure with Service Pattern Examples

Create Stored Procedure Interface

public interface INorthwindStoredProcedures
{
    IEnumerable<CustomerOrderHistory> CustomerOrderHistory(string customerID);
    int CustOrdersDetail(int? orderID);
    IEnumerable<CustomerOrderDetail> CustomerOrderDetail(string customerID);
    int EmployeeSalesByCountry(DateTime? beginningDate, DateTime? endingDate);
    int SalesByCategory(string categoryName, string ordYear);
    int SalesByYear(DateTime? beginningDate, DateTime? endingDate);
}

Create partial DataContext and implement Stored Procedure interface

public partial class NorthwindContext : INorthwindStoredProcedures
{
    public IEnumerable<CustomerOrderHistory> CustomerOrderHistory(string customerID)
    {
        var customerIDParameter = customerID != null ?
            new SqlParameter("@CustomerID", customerID) :
            new SqlParameter("@CustomerID", typeof (string));

        return Database.SqlQuery<CustomerOrderHistory>("CustOrderHist @CustomerID", customerIDParameter);
    }

    public int CustOrdersDetail(int? orderID)
    {
        var orderIDParameter = orderID.HasValue ?
            new SqlParameter("@OrderID", orderID) :
            new SqlParameter("@OrderID", typeof (int));

        return Database.ExecuteSqlCommand("CustOrdersDetail @OrderId", orderIDParameter);
    }

    public IEnumerable<CustomerOrderDetail> CustomerOrderDetail(string customerID)
    {
        var customerIDParameter = customerID != null ?
            new SqlParameter("@CustomerID", customerID) :
            new SqlParameter("@CustomerID", typeof (string));

        return Database.SqlQuery<CustomerOrderDetail>("CustOrdersOrders @CustomerID", customerIDParameter);
    }

    public int EmployeeSalesByCountry(DateTime? beginningDate, DateTime? endingDate)
    {
        var beginningDateParameter = beginningDate.HasValue ?
            new SqlParameter("@Beginning_Date", beginningDate) :
            new SqlParameter("@Beginning_Date", typeof (DateTime));

        var endingDateParameter = endingDate.HasValue ?
            new SqlParameter("@Ending_Date", endingDate) :
            new SqlParameter("@Ending_Date", typeof (DateTime));

        return Database.ExecuteSqlCommand("EmployeeSalesByCountry @Beginning_Date, @Ending_Date", beginningDateParameter, endingDateParameter);
    }

    public int SalesByCategory(string categoryName, string ordYear)
    {
        var categoryNameParameter = categoryName != null ?
            new SqlParameter("@CategoryName", categoryName) :
            new SqlParameter("@CategoryName", typeof (string));

        var ordYearParameter = ordYear != null ?
            new SqlParameter("@OrdYear", ordYear) :
            new SqlParameter("@OrdYear", typeof (string));

        return Database.ExecuteSqlCommand("SalesByCategory @CategoryName, @OrdYear", categoryNameParameter, ordYearParameter);
    }

    public int SalesByYear(DateTime? beginningDate, DateTime? endingDate)
    {
        var beginningDateParameter = beginningDate.HasValue ?
            new SqlParameter("@Beginning_Date", beginningDate) :
            new SqlParameter("@Beginning_Date", typeof (DateTime));

        var endingDateParameter = endingDate.HasValue ?
            new SqlParameter("@Ending_Date", endingDate) :
            new SqlParameter("@Ending_Date", typeof (DateTime));

        return Database.ExecuteSqlCommand("SalesByYear @Beginning_Date, @Ending_Date", beginningDateParameter, endingDateParameter);
    }
}

Implement Service Interface for Stored Procedure Service

This step is for best practices, not required.

public interface IStoredProcedureService
{
    IEnumerable<CustomerOrderHistory> CustomerOrderHistory(string customerID);
    int CustOrdersDetail(int? orderID);
    IEnumerable<CustomerOrderDetail> CustomerOrderDetail(string customerID);
}

Create Service to Expose Procedure Calls

This step is for best practices, not required.

public class StoredProcedureService : IStoredProcedureService
{
    private readonly INorthwindStoredProcedures _storedProcedures;

    public StoredProcedureService(INorthwindStoredProcedures storedProcedures)
    {
        _storedProcedures = storedProcedures;
    }

    public IEnumerable<CustomerOrderHistory> CustomerOrderHistory(string customerID)
    {
        return _storedProcedures.CustomerOrderHistory(customerID);
    }

    public int CustOrdersDetail(int? orderID)
    {
        return _storedProcedures.CustOrdersDetail(orderID);
    }

    public IEnumerable<CustomerOrderDetail> CustomerOrderDetail(string customerID)
    {
        return _storedProcedures.CustomerOrderDetail(customerID);
    }
}

If Using DI & IoC Framework, Register Bindings

public static void RegisterTypes(IUnityContainer container)
{
    container
        .RegisterType<IDataContextAsync, NorthwindContext>(new PerRequestLifetimeManager())
        .RegisterType<IUnitOfWorkAsync, UnitOfWork>(new PerRequestLifetimeManager())
        .RegisterType<IRepositoryAsync<Customer>, Repository<Customer>>()
        .RegisterType<IRepositoryAsync<Product>, Repository<Product>>()
        .RegisterType<IProductService, ProductService>()
        .RegisterType<ICustomerService, CustomerService>()
        .RegisterType<INorthwindStoredProcedures, NorthwindContext>(new PerRequestLifetimeManager())
        .RegisterType<IStoredProcedureService, StoredProcedureService>();
}

Consume Procedures in your Application

public class ProcedureController : ApiController
{
    private readonly IStoredProcedureService _storedProcedureService;

    public ProcedureController(IStoredProcedureService storedProcedureService)
    {
        _storedProcedureService = storedProcedureService;
    }

    [HttpGet]
    public HttpResponseMessage CustomerOrderHistory(string customerID)
    {
        var customerOrderHistory = _storedProcedureService.CustomerOrderHistory(customerID);
        return Request.CreateResponse(HttpStatusCode.OK, customerOrderHistory);
    }

    [HttpGet]
    public HttpResponseMessage CustomerOrderDetail(string customerId)
    {
        var customerOrderDetails = _storedProcedureService.CustomerOrderDetail(customerId);
        return Request.CreateResponse(HttpStatusCode.OK, customerOrderDetails);
    }
}