使用通用存储库和存储过程 [英] Using Generic Repository and Stored Procedures

问题描述

我正在处理现有的应用程序，首先使用Generic Repo模式和EF6数据库。
我正在调用一个存储过程，它返回一个复杂的类型，它不是我实体模型中现有的实体，因此我不确定要给出什么类型。

I am working on an existing application the uses the Generic Repo pattern and EF6 database first. I am calling a stored proc that returns a complex type that is not an existing entity in my entity models and therefore I am not sure what type to give.

这是我的服务层调用sp的方式

This is how my sp is being called from my service layer

_unitOfWork.Repository<Model>()
            .SqlQuery("sp_Get @FromDateTime, @ToDateTime, @CountyId",
                         new SqlParameter("FromDateTime", SqlDbType.DateTime) { Value = Request.FromDateTime },
                         new SqlParameter("ToDateTime", SqlDbType.DateTime) { Value = Request.TripToDateTime },
                         new SqlParameter("CountyId", SqlDbType.Int) { Value = Convert.ToInt32(Request.County) }
           ).ToList();

我在数据层中创建一个实体以映射到哪个存储过程的最佳方法返回复杂类型。
如果是需要自定义映射，或者仅仅是创建Entity类的情况

Do I create an Entity in my data layer to map to or what is the best approach for stored procedures returning complex types. If so is there custom mapping needed or is it just a case of creating the Entity class

谢谢

推荐答案

如果您有一个具有这些字段的实体，可以像上面所示的那样调用SqlQuery方法，否则，我建议创建一个新类来映射结果：

If you have an entity with those fields you can call SqlQuery method as you show above, if not, then I suggest creating a new class to map the result:

public class Result
{
    public int CountyId { get; set; }

    public DateTime FromDateTime { get; set; }

    public DateTime ToDateTime { get; set; }
}

我不知道在你的情况下如何实现UnitOfWork模式，但我认为您可以访问您的上下文。在您的 UnitOfWork 类中，您可以创建一个类似的方法：

I don't know how is implemented the UnitOfWork pattern in your case, but I assume that you have access to your Context. In your UnitOfWork class you could create a generic method like this:

public class UnitOfWork 
{
    private YourContext Context { get; set; }

    public DbRawSqlQuery<T> SQLQuery<T>(string sql, params object[] parameters)
    {
       return Context.Database.SqlQuery<T>(sql, parameters);
    }
}

这样，你可以执行你的商店程序，因为我显示如下：

This way, you can execute your store procedures as I show below:

var result= _unitOfWork.SqlQuery<Result>("sp_Get @FromDateTime, @ToDateTime, @CountyId",
                     new SqlParameter("FromDateTime", SqlDbType.DateTime) { Value = Request.FromDateTime },
                     new SqlParameter("ToDateTime", SqlDbType.DateTime) { Value = Request.TripToDateTime },
                     new SqlParameter("CountyId", SqlDbType.Int) { Value = Convert.ToInt32(Request.County) }
       ).ToList();

这篇关于使用通用存储库和存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！