是的,您可以在上面使用 Entity Framework 4 和 LINQ,它生成参数化查询并执行它,这就是选项。
另一种选择是(我做了几次)创建一个基类/接口,比方说:
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:
}
然后你可以创建你的具体行动:
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
}
}
很容易创建具体的行动!
然后,为了使其更容易,创建一个 ExecutionManager,其关注的是如何获取连接并执行操作:
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 并将数据库输出转换为一些有意义的结果。
祝你好运 :)