在你的通用存储库中添加
public IEnumerable<T> ExecWithStoreProcedure(string query, params object[] parameters)
{
return _context.Database.SqlQuery<T>(query, parameters);
}
然后您可以使用任何unitofwork/repository调用它,例如
IEnumerable<Products> products =
_unitOfWork.ProductRepository.ExecWithStoreProcedure(
"spGetProducts @bigCategoryId",
new SqlParameter("bigCategoryId", SqlDbType.BigInt) { Value = categoryId }
);
在您的代码库中,一个非通用的解决方案是:
private int ExecWithStoreProcedure(string query, params object[] parameters)
{
return _context.Database.ExecuteSqlCommand("EXEC " + query, parameters);
}
然后是一些典型的使用示例:
var param = new SqlParameter("SomethingToCheck", SqlDbType.NVarChar) { Value = shortCode };
var result = ExecWithStoreProcedure("mySchema.myStoredProc @SomethingToCheck", param);
带有多个参数:
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 );