C#實現(xiàn)定義一套中間SQL可以跨庫執(zhí)行的SQL語句(案例詳解)
目前數(shù)據(jù)的種類非常多,每種數(shù)據(jù)都支持sql語句,但是大家發(fā)現(xiàn)沒有每種數(shù)據(jù)的SQL都有自己的語法特性,都是SQL語句都沒有一個特定的語法標準,導(dǎo)致開發(fā)人員在開發(fā)的過程中無法任意選庫(如果用自己不熟的庫都會要學習一遍SQL語法),主要是學習成本太高
那么有沒有一種工具能夠統(tǒng)一下sql語法規(guī)則,只要學一種sql語句就可以跨庫執(zhí)行,讓開發(fā)人員不用學習每種庫的SQL語句呢?
下面就給大家介紹一款開源工具 hisql源碼 hisql介紹
hisql查詢樣例
單表查詢
根據(jù)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'
大家可以會問 明明是 "select * from HTest01 where CreateTime>='2022-02-17 09:27:50' and CreateTime<='2022-03-22 09:27:50'"
用的 *
查詢 生成的sql語句為什么會直接列出所有字段呢? 熟HiSql的網(wǎng)友都應(yīng)該知道這里其實隱藏了一個功能就是用戶可以自定義字段排序在系統(tǒng)表 Hi_FieldModel
中可配置
group by查詢
hisql 默認支持的常用函數(shù) max
, count
, min
, sum
, avg
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"
having 查詢
上次有網(wǎng)友說having 實現(xiàn)起來很麻煩通過正則表達式解析很難實現(xiàn),我只能說很多網(wǎng)友對正則表達式的理解還不是很深,正則表達式已經(jīng)應(yīng)用于種類型的編譯器中,下面就來演示一下Having
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
join 多表查詢
hisql inner join 和 in 操作語法
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生成的sqlserver 的sql 如下的所示
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生成mysql的sql如下所示
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生成postgresql 的sql下所示
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 分頁 只要在日常查詢上增加 Take()
每頁顯示多少數(shù)據(jù) Skip()
顯示第幾頁
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) ").Take(2).Skip(2).ToSql();
hisql生成的sqlserver 的sql 如下的所示
select [TabName] from ( select ROW_NUMBER() OVER(Order by [a].[FieldType] ASC) AS _hi_rownum_, [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) ) as hi_sql where hi_sql._hi_rownum_ BETWEEN (2-1)*2+1 and 2*2 order by _hi_rownum_ asc
hisql生成mysql的sql如下所示
select `TabName` from ( select ROW_NUMBER() OVER(Order by `a`.`FieldType` ASC) AS `_hi_rownum_`, `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) ) as hi_sql order by `_hi_rownum_` asc limit 2,2
hisql生成postgresql 的sql下所示
select "TabName" from ( select ROW_NUMBER() OVER(Order by "a"."FieldType" ASC) AS "_hi_rownum_", "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) ) as hi_sql order by "_hi_rownum_" asc limit 2 OFFSET 2
hisql 實現(xiàn)參數(shù)化
參數(shù)化可以有效的防注入,通過前端可以拼接的hisql語句通過參數(shù)化的方式傳入, 這樣就解決了注入問題
string sql1= sqlClient.HiSql("select * from hi_tabmodel where tabname=@tabname ", new { TabName="H_test" ,FieldName="DID"}).ToSql(); string sql2= sqlClient.HiSql("select * from hi_tabmodel where tabname=@tabname or TabType in( @TabType)", new { TabName="H_test" , TabType =new List<int> { 1,2,3,4} }).ToSql(); string sql3 = sqlClient.HiSql("select * from hi_tabmodel where tabname=@tabname ", new Dictionary<string, object> { { "TabName", "H_test" } }).ToSql();
鏈式查詢
hisql除了提供hisql語法外還提供鏈式查詢方法如下所示
string sql = sqlClient.Query("Hi_FieldModel").As("A").Field("A.FieldType") .Join("Hi_TabModel").As("B").On(new HiSql.JoinOn() { { "A.TabName", "B.TabName" } }) .Where("A.TabName='GD_UniqueCodeInfo'").Group(new GroupBy { { "A.FieldType" } }) .Sort("A.FieldType asc", "A.TabName asc") .Take(2).Skip(2) .ToSql();
hisql語句和鏈式查詢混用
string sql = sqlClient.HiSql("select A.FieldType from Hi_FieldModel as A ") .Where("A.TabName='GD_UniqueCodeInfo'").Group(new GroupBy { { "A.FieldType" } }) .Sort("A.FieldType asc", "A.TabName asc") .Take(2).Skip(2) .ToSql();
到此這篇關(guān)于C#實現(xiàn)定義一套中間SQL可以跨庫執(zhí)行的SQL語句的文章就介紹到這了,更多相關(guān)C#跨庫執(zhí)行SQL語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
C#實現(xiàn)將聊天數(shù)據(jù)發(fā)送加密
這篇文章主要為大家詳細介紹了如何利用C#實現(xiàn)將聊天數(shù)據(jù)發(fā)送加密的功能,文中的示例代碼講解詳細,對我們學習C#有一定的幫助,感興趣的小伙伴可以跟隨小編一起了解一下2022-12-12C#中TreeView實現(xiàn)適合兩級節(jié)點的選中節(jié)點方法
這篇文章主要介紹了C#中TreeView實現(xiàn)適合兩級節(jié)點的選中節(jié)點方法,實例分析了C#中TreeView節(jié)點操作的相關(guān)技巧,具有一定參考借鑒價值,需要的朋友可以參考下2015-09-09