ToB企服应用市场:ToB评测及商务社交产业平台

标题: c# 实现定义一套中间SQL可以跨库执行的SQL语句 [打印本页]

作者: 徐锦洪 时间: 2022-8-22 11:10
标题: c# 实现定义一套中间SQL可以跨库执行的SQL语句
c# 实现定义一套中间SQL可以跨库执行的SQL语句

目前数据的种类非常多，每种数据都支持sql语句，但是大家发现没有每种数据的SQL都有自己的语法特性，都是SQL语句都没有一个特定的语法标准，导致开发人员在开发的过程中无法任意选库（如果用自己不熟的库都会要学习一遍SQL语法），主要是学习成本太高
那么有没有一种工具能够统一下sql语法规则，只要学一种sql语句就可以跨库执行，让开发人员不用学习每种库的SQL语句呢？
下面就给大家介绍一款开源工具 hisql源码 hisql介绍
hisql查询样例

单表查询

根据hisql语句通过ToSql()方法生成目标sql的原生sql

var _sql = sqlClient.HiSql("select * from HTest01 where CreateTime>='2022-02-17 09:27:50' and CreateTime<='2022-03-22 09:27:50'").ToSql();

复制代码

hisql生成的sqlserver 的sql 如下的所示

select [htest01].[SID],[htest01].[UName],[htest01].[Age],[htest01].[Salary],[htest01].[Descript],[htest01].[CreateTime],[htest01].[CreateName],[htest01].[ModiTime],[htest01].[ModiName] from [HTest01] as [HTest01]
where [htest01].[CreateTime] >= '2022-02-17 09:27:50.000' and [htest01].[CreateTime] <= '2022-03-22 09:27:50.000'

复制代码

hisql生成mysql的sql如下所示

select `htest01`.`SID`,`htest01`.`UName`,`htest01`.`Age`,`htest01`.`Salary`,`htest01`.`Descript`,`htest01`.`CreateTime`,`htest01`.`CreateName`,`htest01`.`ModiTime`,`htest01`.`ModiName` from `htest01` as `htest01`
where `htest01`.`CreateTime` >= '2022-02-17 09:27:50.000' and `htest01`.`CreateTime` <= '2022-03-22 09:27:50.000'

复制代码

hisql生成postgresql 的sql下所示

select "htest01"."SID","htest01"."UName","htest01"."Age","htest01"."Salary","htest01"."Descript","htest01"."CreateTime","htest01"."CreateName","htest01"."ModiTime","htest01"."ModiName" from "HTest01" as "htest01"
where "htest01"."CreateTime" >= '2022-02-17 09:27:50.000' and "htest01"."CreateTime" <= '2022-03-22 09:27:50.000'

复制代码

join 多表查询

hisql inner join 和 in 操作语法

string sql = sqlClient.HiSql("select FieldName, count(FieldName) as NAME_count,max(FieldType) as FieldType_max from Hi_FieldModel group by FieldName").ToSql();

复制代码

hisql生成的sqlserver 的sql 如下的所示

select [hi_fieldmodel].[FieldName],count(*) as NAME_count,max([hi_fieldmodel].[FieldType]) as FieldType_max from [Hi_FieldModel] as [Hi_FieldModel]
group by [hi_fieldmodel].[FieldName]

复制代码

hisql生成mysql的sql如下所示

select `hi_fieldmodel`.`FieldName`,count(*) as NAME_count,max(`hi_fieldmodel`.`FieldType`) as FieldType_max from `Hi_FieldModel` as `hi_fieldmodel`
group by `hi_fieldmodel`.`FieldName`

复制代码

hisql生成postgresql 的sql下所示

select "hi_fieldmodel"."FieldName",count(*) as NAME_count,max("hi_fieldmodel"."FieldType") as FieldType_max from "Hi_FieldModel" as "hi_fieldmodel"
group by "hi_fieldmodel"."FieldName"

复制代码

分页

hisql 分页只要在日常查询上增加Take() 每页显示多少数据 Skip() 显示第几页

string sql_having = sqlClient.HiSql("select FieldName, count(FieldName) as NAME_count,max(FieldType) as FieldType_max from Hi_FieldModel group by FieldName having count(FieldName) > 1").ToSql();

复制代码

hisql生成的sqlserver 的sql 如下的所示

select [hi_fieldmodel].[FieldName],count(*) as NAME_count,max([hi_fieldmodel].[FieldType]) as FieldType_max from [Hi_FieldModel] as [Hi_FieldModel]
group by [hi_fieldmodel].[FieldName]
having count(*) > 1

复制代码

hisql生成mysql的sql如下所示

select `hi_fieldmodel`.`FieldName`,count(*) as NAME_count,max(`hi_fieldmodel`.`FieldType`) as FieldType_max from `Hi_FieldModel` as `hi_fieldmodel`
group by `hi_fieldmodel`.`FieldName`
having count(*) > 1

复制代码

hisql生成postgresql 的sql下所示

select "hi_fieldmodel"."FieldName",count(*) as NAME_count,max("hi_fieldmodel"."FieldType") as FieldType_max from "Hi_FieldModel" as "hi_fieldmodel"
group by "hi_fieldmodel"."FieldName"
having count(*) > 1

复制代码

hisql 实现参数化

参数化可以有效的防注入，通过前端可以拼接的hisql语句通过参数化的方式传入，这样就解决了注入问题

var sql = sqlClient.HiSql("select a.tabname from hi_fieldmodel as a inner join Hi_TabModel as b on a.tabname =b.tabname inner join Hi_TabModel as c on a.tabname=c.tabname where a.tabname='h_test' and a.FieldType in (11,41,21) ").ToSql();

复制代码

链式查询

hisql除了提供hisql语法外还提供链式查询方法如下所示

select [a].[TabName] from [Hi_FieldModel] as [a]
inner join [Hi_TabModel] as [b] on [a].[TabName] = [b].[TabName] inner join [Hi_TabModel] as [c] on [a].[TabName] = [c].[TabName]
where [a].[TabName] = 'h_test' and [a].[FieldType] in (11,41,21)

复制代码

hisql语句和链式查询混用

select `a`.`TabName` from `Hi_FieldModel` as `a`
inner join `Hi_TabModel` as `b` on `a`.`TabName` = `b`.`TabName` inner join `Hi_TabModel` as `c` on `a`.`TabName` = `c`.`TabName`
where `a`.`TabName` = 'h_test' and `a`.`FieldType` in (11,41,21)

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)