如何从使用exec()的存储过程中创建复杂类型? [英] How to create a complex type from a stored procedure that uses exec()?

问题描述

我想根据动态构造并使用exec()执行的查询创建一个复杂类型以在实体管理器中使用.是否有可能?;由于我正在编写过滤器，如果不可能的话，您会怎么做?

I want to create a complex type to use within an entity manager from a query constructed dynamically and executed with exec(). Is it possible?; since I'm writing a filter, what would you do instead if it is not possible?

此外，我正在使用linq进行评估，但是该过滤器需要许多表及其寄存器，因此效率是一个问题.

Also, I'm evaluating using linq, but the filter needs many tables and their registers, therefore efficiency is a concern.

谢谢...

推荐答案

是的，您可以在顶部使用Entity Framework 4和LINQ，它会生成参数化查询并执行它，这是选项.

Yes, you can use Entity Framework 4 and LINQ on top, it generates the parametrized query and executes it, that's the option.

另一个选择是(并且我做了几次)来创建基类/接口，比如:

Another option is (and I did several times) to create a base class/interface, let's say:

public interface IExecutable
{
    void Execute(IConnection connection);
}
public interface IExecutable<TResult> : IExecutable
{
    TResult Result { get; }
}

public abstract ActionBase<TResult> : IExecutable<TResult>
{
    protected void AddParameter(....);

    protected IDataReader ExecuteAsReader(string query) {
        //create a DB Command, open transaction if needed, execute query, return a reader.
    }

    protected object ExecuteAsScalar(string query) {
        //....
    }

    //the concrete implementation
    protected abstract TResult ExecuteInternal();

    IExecutable.Execute(IConnection connection) {
        //keep the connection
        this.Result = ExecuteInternal();
    }

    //another common logic: 

}

然后您可以创建自己的具体操作:

Then you can create your concrete actions:

public sealed class GetUsersAction : ActionBase<<IList<User>>
{
    //just a constructor, you provide it with all the information it neads
    //to be able to generate a correct SQL for this specific situation
    public GetUsersAction(int departmentId) {
        AddParameter("@depId", departmentId);
    }

    protected override IList<User> ExecuteInternal() {
        var command = GenerateYourSqlCommand();

        using(var reader = ExecuteAsReader(command)) {
            while(reader.Read) {
                //create your users from reader
            }
        }
        //return users you have created
    }
}

非常容易创建具体动作！

Very easy to create concrete actions!

然后，为了使其变得更加简单，创建一个ExecutionManager，其关注点是如何获得连接并执行操作:

Then, to make it even easier, create an ExecutionManager whose concern is how to get the connection and execute the action:

public sealed ExecutionManager() {

    TResult Execute<TResult>(IExecutable<TResult> action) {
        var connection = OhOnlyIKnowHowTOGetTheConnectionAnfHereItIs();
        action.Execute(connection);
        return action.Result;
    }
}

现在就使用它:

var getUsersAction = new GetUsersAction(salesDepartmentId);

//it is not necessary to be a singletone, up to you
var users = ExecutionManager.Instance.Execute(getUsersAction);

//OR, if you think it is not up to ExecutionManager to know about the results:
ExecutionManager.Instance.Execute(getUsersAction);
var users = getUsersAction.Result

使用这种简单的技术，将所有连接/命令/执行逻辑从具体动作移到基类中确实很容易，而具体动作所关心的只是生成SQL，并将数据库输出转换为有意义的结果.

Using this simple technique it is really easy to move all the connection/command/execution logic away from the concrete actions into the base class, and the concrete actions' concerns are just generating SQLs and converting database output into some meaningful results.

祝你好运:)

这篇关于如何从使用exec()的存储过程中创建复杂类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！