Repository pattern to execute a stored procedure using Entity Framework

Question

I'm trying to use repository pattern for my vsto project.

How do I use a repository pattern to execute a stored procedure? I'm using Entity Framework. Any link for code sample would really useful

Answer 1

To your generic repository add

public IEnumerable<T> ExecWithStoreProcedure(string query, params object[] parameters)
{
        return _context.Database.SqlQuery<T>(query, parameters);
}

And then you can call it with any unitofwork/repository like

IEnumerable<Products> products = 
             _unitOfWork.ProductRepository.ExecWithStoreProcedure(
             "spGetProducts @bigCategoryId",
             new SqlParameter("bigCategoryId", SqlDbType.BigInt) { Value = categoryId } 
      );

Answer 2

A non generic solution in your repository would be:

private int ExecWithStoreProcedure(string query, params object[] parameters)
{
   return _context.Database.ExecuteSqlCommand("EXEC " +  query, parameters);
}

And then a few typical examples of use:

var param = new SqlParameter("SomethingToCheck", SqlDbType.NVarChar) { Value = shortCode };            
var result = ExecWithStoreProcedure("mySchema.myStoredProc @SomethingToCheck", param);

with multiple parameters:

var param1 = new SqlParameter("SomeCode", SqlDbType.VarChar) { Value = shortCode };
var param2 = new SqlParameter("User", SqlDbType.VarChar) { Value = userName };
var result = ExecWithStoreProcedure("mySchema.myStoredProc @SomeCode, @User",  param1, param2 );

Answer 3

this link guided me. [Link]

But when you execute stored procedure you have to put "exec" informant of SP name Eg: if sp is "sp_aa"

string should be "exec sp_aa"