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
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 }
);
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 );
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"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With