Skip to content

9.18 Sql 高级代理

📝 模块更新日志 新特性*

+ 新增 **`Sql` 高级拦截支持返回 `IEnumerable<T>` 和 `T[]` 类型值** 4\.8\.7\.5 ⏱️2023\.03\.07 [f2ca2d3](https://gitee.com/dotnetchina/Furion/commit/f2ca2d303ea06febcc3a50df56ed03895e43c639)

查看变化过去版本如果返回对象类型只支持 List<T>，T 和 Tuple<>，现已支持 IEnumerable<T>、T[] 和 Tuple<> 混合体。

public interface ISql : ISqlDispatchProxy  
{  
    [SqlExecute("select * from person")]  
    Person[] GetPersons();  

    [SqlExecute("select * from person")]  
    IEnumerable<Person> GetPersons2();  

    // 更复杂的组合  
    [SqlExecute(@"  
select * from person where id = 1;  
select * from person;  
select * from person where id > 0;  
select * from person where id > 0;  
")]  
    (Person, List<Person>, Person[], IEnumerable<Person>) GetPersons();  
}

  • 突破性变化

    • 调整 EFCore 的 [Interceptor] 特性名称为 [SqlInterceptor] 4.9.5.9 ⏱️2024.09.18 7d4c575

9.18.1 关于 Sql 代理​

Sql 代理是 Furion 框架中对 Sql 操作一个非常重要的概念，通过这种方式可以大大提高 Sql 书写效率，而且后期极易维护。

Sql 代理属于 Furion 框架中一个高级功能。

9.18.2 了解 ISqlDispatchProxy​

ISqlDispatchProxy 接口是 Furion 实现被代理接口的唯一依赖，任何公开的接口一旦集成了 ISqlDispatchProxy 接口，那么这个接口就是被托管拦截的 Sql 操作接口。

简单定义一个 Sql 代理接口

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
    }  
}

一旦这个接口继承了 ISqlDispatchProxy，那么它就会动态创建接口实例，而且支持依赖注入/控制反转获取实例。

9.18.3 开始领略 Sql 代理​

下面我将通过多个例子来演示 Sql 代理的用法，为什么推荐这种方式操作 Sql。

支持各种方式获取实例：

9.18.3.1 构造函数方式​

private readonly ISql _sql;  
public FurionService(ISql sql)  
{  
    _sql = sql;  
}

9.18.3.2 方法参数注入​

public async Task<List<PersonDto>> GetAll([FromServices] ISql, string keyword)  
{  
}

9.18.3.3 Db.GetSqlDispatchProxy<ISql>()​

var sql = Db.GetSqlDispatchProxy<ISql>();

9.18.4 Sql 操作​

9.18.4.1 返回 DataTable​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        // 执行sql并传入参数，基元类型  
        [SqlExecute("select * from person where id >@id and name like @name")]  
        DataTable GetPerson(int id, string name);  

        // 执行sql并传入参数，对象类型  
        [SqlExecute("select * from person where id >@id and name like @name")]  
        DataTable GetPerson(MyParam paras);  

        // 执行存储过程 sql，支持设置参数类型  
        [SqlExecute("exec PROP_NAME @id", CommandType = CommandType.StoredProcedure)]  
        DataTable GetPerson(int id);  

        // 支持多数据库操作  
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]  
        DataTable GetPerson();  

        // 异步方式  
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator))]  
        Task<DataTable> GetPersonAsync();  
    }  
}

关于参数Sql 代理参数查找规则：

如果方法的参数是 基元类型（或 string、值类型），则自动将这些类型组合成 Dictionary<string, object> 作为 Sql 参数。命令参数可使用方法同名参数加 @ 符号。

如果方法的参数是 类类型，那么自动遍历该类公开实例属性生成 DbParameter[] 数组，每一个属性名都将是命令参数，大部分数据库是不区分大小写，个别数据库除外，如 Sqlite，如：

public class MyModel  
{  
    public int Id {get;set;}  
    public string Name {get; set;}  
}

那么 sql 语句可以直接使用属性名作为参数：

select * from person where id > @id and name = @name;

9.18.4.2 返回 List<T>​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        // 执行sql并传入参数，基元类型  
        [SqlExecute("select * from person where id >@id and name like @name")]  
        List<Person> GetPerson(int id, string name);  

        // 执行sql并传入参数，对象类型  
        [SqlExecute("select * from person where id >@id and name like @name")]  
        List<Person> GetPerson(MyParam paras);  

        // 执行存储过程 sql，支持设置参数类型  
        [SqlExecute("exec PROP_NAME @id", CommandType = CommandType.StoredProcedure)]  
        List<Person> GetPerson(int id);  

        // 支持多数据库操作  
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator)]  
        List<Person> GetPerson();  

        // 异步方式  
        [SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator)]  
        Task<List<Person>> GetPersonAsync();  
    }  
}

9.18.4.3 返回 DataSet​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        // 执行sql并传入参数，基元类型  
        [SqlExecute(@"  
            select * from person where id >@id and name like @name;  
            select top 10 * from student where Id >@id;")]  
        DataSet GetData(int id, string name);  

        // 执行sql并传入参数，对象类型  
        [SqlExecute(@"  
            select * from person where id >@id and name like @name;  
            select top 10 * from student where Id >@id;")]  
        DataSet GetData(MyParam paras);  

        // 执行存储过程 sql，支持设置参数类型  
        [SqlExecute(@"  
            exec PROP_NAME @id;  
            select * from person;", CommandType = CommandType.StoredProcedure)]  
        DataSet GetData(int id);  

        // 支持多数据库操作  
        [SqlExecute(@"  
            select * from person;  
            select * from student;"), SqlDbContextLocator(typeof(MySqlDbContextLocator)]  
        DataSet GetData();  

        // 异步方式  
        [SqlExecute(@"  
            select * from person;  
            select * from student;  
            select 1;"), SqlDbContextLocator(typeof(MySqlDbContextLocator)]  
        Task<DataSet> GetDataAsync();  
    }  
}

9.18.4.4 返回 Tuple<T1,...T8>​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        // 执行sql并传入参数，基元类型  
        [SqlExecute(@"  
            select * from person where id >@id and name like @name;  
            select top 10 * from student where Id >@id;")]  
        (List<Person>,List<Student>) GetData(int id, string name);  

        // 执行sql并传入参数，对象类型  
        [SqlExecute(@"  
            select * from person where id >@id and name like @name;  
            select top 10 * from student where Id >@id;")]  
        (List<Person>,List<Student>) GetData(MyParam paras);  

        // 执行存储过程 sql，支持设置参数类型  
        [SqlExecute(@"  
            exec PROP_NAME @id;  
            select * from person;", CommandType = CommandType.StoredProcedure)]  
        (List<Person>,List<Student>) GetData(int id);  

        // 支持多数据库操作  
        [SqlExecute(@"  
            select * from person;  
            select * from student;"), SqlDbContextLocator(typeof(MySqlDbContextLocator)]  
        (List<Person>,List<Student>) GetData();  

        // 异步方式  
        [SqlExecute(@"  
            select * from person;  
            select * from student;  
            select 1;"), SqlDbContextLocator(typeof(MySqlDbContextLocator)]  
        Task<(List<Person>,List<Student>,List<int>)> GetDataAsync();  

        // 自 v3.7.3+ 版本支持返回单个类类型参数  
        [SqlExecute(@"  
            select * from person where id =@id;  
            select * from person")]  
        (Person, List<Person>) GetData(int id); // 注意返回值是 `(Person, List<Person>)` 组合  
}

9.18.4.5 返回 单行单列​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlExecute("select Name from person where id = @id")]  
        string GetValue(int id);  

        [SqlExecute("select age from person where id = @id")]  
        int GetValue(int id);  

        [SqlExecute("select Name from person where id = @id")]  
        Task<string> GetValueAsync(int id);  
    }  
}

9.18.4.6 无返回值​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlExecute("insert into person(Name,Age) values(@name,@age)")]  
        void Insert(MyParam dto);  

        [SqlExecute("delete from person where id = @id")]  
        void Delete(int id);  

        [SqlExecute("update person set name=@name where id=@id")]  
        void Update(int id, string name);  
    }  
}

9.18.4.7 返回单个类类型参数​

版本说明以下内容仅限 Furion 3.7.1 + 版本使用。

public interface ISql : ISqlDispatchProxy  
{  
    // 自 v3.7.3+ 版本支持返回单个类类型参数  
    [SqlExecute("select * from person where id=@id")]  
    Person GetPerson(int id);  
}

9.18.4.8 返回受影响行数​

版本说明以下内容仅限 Furion 4.4.5 + 版本使用。

需要在 [SqlExcuete] 特性中标记 RowEffects = true 且返回值是 int 或者 Task<int>。

public interface ISql : ISqlDispatchProxy  
{  
    // 同步  
    [SqlExecute("update person set age = 30 where id = {id}", RowEffects = true)]  
    int Update(int id);  

    // 异步  
    [SqlExecute("update person set age = 30 where id = {id}", RowEffects = true)]  
    Task<int> UpdateAsync(int id);  
}

9.18.4.9 返回 IEnumerable 或 Array 类型​

版本说明以下内容仅限 Furion 4.8.7.5 + 版本使用。

public interface ISql : ISqlDispatchProxy  
{  
    [SqlExecute("select * from person")]  
    Person[] GetPersons();  

    [SqlExecute("select * from person")]  
    IEnumerable<Person> GetPersons2();  

    // 更复杂的组合  
    [SqlExecute(@"  
select * from person where id = 1;  
select * from person;  
select * from person where id > 0;  
select * from person where id > 0;  
")]  
    (Person, List<Person>, Person[], IEnumerable<Person>) GetPersons();  
}

9.18.5 存储过程 操作​

9.18.5.1 返回 DataTable​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlProcedure("PROC_Name")]  
        DataTable GetPersons(MyParam dto);  

        [SqlProcedure("PROC_Name")]  
        DataTable GetPersons(int id);  

        [SqlProcedure("PROC_Name")]  
        DataTable GetPersons(int id, string name);  
    }  
}

9.18.5.2 返回 List<T>​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlProcedure("PROC_Name")]  
        List<Person> GetPersons(MyParam dto);  

        [SqlProcedure("PROC_Name")]  
        List<Person> GetPersons(int id);  

        [SqlProcedure("PROC_Name")]  
        List<Person> GetPersons(int id, string name);  
    }  
}

9.18.5.3 返回 DataSet​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlProcedure("PROC_Name")]  
        DataSet GetData(MyParam dto);  

        [SqlProcedure("PROC_Name")]  
        DataSet GetData(int id);  

        [SqlProcedure("PROC_Name")]  
        DataSet GetData(int id, string name);  
    }  
}

9.18.5.4 返回 Tuple(T1,...T8)​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlProcedure("PROC_Name")]  
        (List<Person>, List<Student>) GetData(MyParam dto);  

        [SqlProcedure("PROC_Name")]  
        (List<Person>, List<Student>) GetData(int id);  

        [SqlProcedure("PROC_Name")]  
        (List<Person>, List<Student>, Person, int) GetData(int id, string name);  

        // 自 v3.7.3+ 版本支持返回单个类类型参数  
        [SqlProcedure(@"PROC_Name)]  
        (Person, List<Person>) GetData(int id); // 注意返回值是 `(Person, List<Person>)` 组合  
    }  
}

9.18.5.5 返回 单行单列​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlProcedure("PROC_Name")]  
        object GetValue(MyParam dto);  

        [SqlProcedure("PROC_Name")]  
        string GetValue(int id);  

        [SqlProcedure("PROC_Name")]  
        int GetValue(int id, string name);  
    }  
}

9.18.5.6 无返回值​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlProcedure("PROC_Name")]  
        void GetValue(MyParam dto);  

        [SqlProcedure("PROC_Name")]  
        void GetValue(int id);  

        [SqlProcedure("PROC_Name")]  
        void GetValue(int id, string name);  
    }  
}

9.18.5.7 带 OUTPUT/RETURN 返回​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlProcedure("PROC_Name")]  
        ProcedureOutputResult GetOutput(ProcOutputModel pams);  

        [SqlProcedure("PROC_Name")]  
        ProcedureOutputResult GetOutput(ProcOutputModel pams);  

        [SqlProcedure("PROC_Name")]  
        ProcedureOutputResult<(List<Person>, List<Student>)> GetOutput(ProcOutputModel pams);  
    }  
}

9.18.5.8 返回单个类类型参数​

版本说明以下内容仅限 Furion 3.7.1 + 版本使用。

public interface ISql : ISqlDispatchProxy  
{  
    // 自 v3.7.3+ 版本支持返回单个类类型参数  
    [SqlProcedure("PROC_Name")]  
    Person GetPerson(int id);  
}

9.18.5.9 返回 IEnumerable 或 Array 类型​

版本说明以下内容仅限 Furion 4.8.7.5 + 版本使用。

public interface ISql : ISqlDispatchProxy  
{  
    [SqlProcedure("PROC_Name")]  
    Person[] GetPersons();  

    [SqlProcedure("PROC_Name")]  
    IEnumerable<Person> GetPersons2();  

    // 更复杂的组合  
    [SqlProcedure("PROC_Name")]  
    (Person, List<Person>, Person[], IEnumerable<Person>) GetPersons();  
}

9.18.6 函数 操作​

using Furion.DatabaseAccessor;  

namespace Furion.Application  
{  
    public interface ISql : ISqlDispatchProxy  
    {  
        [SqlFunction("FN_Name")]    // 标量函数  
        string GetValue(MyParam dto);  

        [SqlProcedure("FN_Name")]   // 表值函数  
        List<Person> GetPersons(int id);  
    }  
}

补充说明Sql 代理会自动判断返回值然后自动执行特定函数类型。

9.18.7 Sql 模板替换​

在最新的 1.18.3 版本中提供了模板替换功能，如：

[SqlExecute("select * from person where id > {id} and name like {name} and age > {user.Age}")]  
List<Person> GetPerson(int id, string name, User user);

两者区别模板字符串有别于命令参数替换，模板字符串采用 { } 方式，运行时直接替换为实际的内容， @ 而是转换成 DbParameter 参数。

9.18.8 切换数据库​

Sql 代理方式的支持三种切换数据库的方式：

9.18.8.1 单个方法方式​

主要通过在方法上贴 [SqlDbContextLocator] 特性

[SqlExecute("select * from person"), SqlDbContextLocator(typeof(MySqlDbContextLocator)]  
List<Person> GetPerson();

9.18.8.2 接口方式​

在接口中贴 [SqlDbContextLocator] 特性，此方式下，接口所有方法将采用指定的数据库执行。

[SqlDbContextLocator(typeof(MySqlDbContextLocator)]  
public interface ISql : ISqlDispatchProxy  
{  
    [SqlFunction("FN_Name")]    // 标量函数  
    string GetValue(MyParam dto);  

    [SqlProcedure("FN_Name")]   // 表值函数  
    List<Person> GetPersons(int id);  
}

9.18.8.3 运行时 .Change 方法切换​

除了以上两种 静态 配置方式，Furion 框架还提供 动态 方式，如：

// 将 sql 代理数据库切换成特定数据库  
_sql.Change<MySqlDbContextLocator>();  
_sql.GetPerson();  

// 多次切换  
_sql.Change<OracleDbContextLocator>();  
_sql.GetPerson();  

// 还支持重置数据库上下文定位器为初始状态  
_sql.ResetIt();  
_sql.GetPerson();

关于优先级问题.Change<> 优先级大于 方法贴 [SqlDbContextLocator] 大于 接口贴 [SqlDbContextLocator]。

默认情况下，不指定 DbContextLocator 属性，则为 MasterDbContextLocator。

9.18.9 Sql 代理拦截​

更名说明在 Furion 4.8.5.9+ 版本之后，[Interceptor] 更名为 [SqlInterceptor]。

在 Furion v2.13 + 版本新增了 Sql 代理拦截功能，可以篡改特定方法或所有代理方法实际执行的参数，如 sql语句、参数、执行对象等等。

若在 Sql 代理中实现拦截功能，必须满足两个条件：

  • 方法必须是 static 静态方法且返回值为 void 且只有一个 SqlProxyMethod 参数
  • 方法必须贴 [Interceptor] 特性

如：

public interface ISql : ISqlDispatchProxy  
{  
    [SqlFunction("FN_Name")]  
    string GetValue(MyParam dto);  

    [SqlProcedure("FN_Name")]  
    List<Person> GetPersons(int id);  

    [SqlExecute("select name from person", InterceptorId = "GetPersonsByName")] // 通过 InterceptorId 解决方法名重载问题  
    Task<List<string>> GetPersons();  

    // 只拦截 GetValue 方法  
    [Interceptor(nameof(GetValue))]  
    static void 拦截1(SqlProxyMethod method)  
    {  
        method.FinalSql += " where id > 1"; // 篡改最终执行 sql  
    }  

    // 拦截 GetValue 和 GetPersons 方法  
    [Interceptor(nameof(GetValue), nameof(GetPersons))]  
    static void 拦截2(SqlProxyMethod method)  
    {  
        method.FinalSql += " where id > 1"; // 篡改最终执行 sql  
    }  

    [Interceptor("GetPersonsByName")]   // 对应上面的 InterceptorId 配置  
    static void 解决方法名重载拦截(SqlProxyMethod method)  
    {  
        // 。。。  
    }  

    [Interceptor]  
    static void 全局拦截(SqlProxyMethod method)  
    {  
        // 这里会拦截所有的方法  
    }  
}

9.18.10 设置超时时间​

[Timeout(1000)]  
public interface ISql : ISqlDispatchProxy  
{  
    [SqlFunction("FN_Name"), Timeout(500)]   // 单位秒  
    string GetValue(MyParam dto);  
}

9.18.11 反馈与建议​

与我们交流给 Furion 提 Issue。