C#實(shí)現(xiàn)定義一套中間SQL可以跨庫(kù)執(zhí)行的SQL語(yǔ)句(案例詳解)

更新時(shí)間：2022年07月04日 14:35:46 作者：tansar

這篇文章主要介紹了C#實(shí)現(xiàn)定義一套中間SQL可以跨庫(kù)執(zhí)行的SQL語(yǔ)句，主要包括hisql查詢樣例、group by查詢、鏈?zhǔn)讲樵兗癶isql語(yǔ)句和鏈?zhǔn)讲樵兓煊玫膕ql語(yǔ)句，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下

目前數(shù)據(jù)的種類(lèi)非常多，每種數(shù)據(jù)都支持sql語(yǔ)句，但是大家發(fā)現(xiàn)沒(méi)有每種數(shù)據(jù)的SQL都有自己的語(yǔ)法特性，都是SQL語(yǔ)句都沒(méi)有一個(gè)特定的語(yǔ)法標(biāo)準(zhǔn)，導(dǎo)致開(kāi)發(fā)人員在開(kāi)發(fā)的過(guò)程中無(wú)法任意選庫(kù)（如果用自己不熟的庫(kù)都會(huì)要學(xué)習(xí)一遍SQL語(yǔ)法），主要是學(xué)習(xí)成本太高

那么有沒(méi)有一種工具能夠統(tǒng)一下sql語(yǔ)法規(guī)則，只要學(xué)一種sql語(yǔ)句就可以跨庫(kù)執(zhí)行，讓開(kāi)發(fā)人員不用學(xué)習(xí)每種庫(kù)的SQL語(yǔ)句呢？

下面就給大家介紹一款開(kāi)源工具 hisql源碼 hisql介紹

hisql查詢樣例

單表查詢

根據(jù)hisql語(yǔ)句通過(guò)ToSql()方法生成目標(biāo)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'

大家可以會(huì)問(wèn) 明明是 "select * from HTest01 where CreateTime>='2022-02-17 09:27:50' and CreateTime<='2022-03-22 09:27:50'" 用的 * 查詢生成的sql語(yǔ)句為什么會(huì)直接列出所有字段呢？熟HiSql的網(wǎng)友都應(yīng)該知道這里其實(shí)隱藏了一個(gè)功能就是用戶可以自定義字段排序在系統(tǒng)表 Hi_FieldModel 中可配置

group by查詢

hisql 默認(rèn)支持的常用函數(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)友說(shuō)having 實(shí)現(xiàn)起來(lái)很麻煩通過(guò)正則表達(dá)式解析很難實(shí)現(xiàn)，我只能說(shuō)很多網(wǎng)友對(duì)正則表達(dá)式的理解還不是很深，正則表達(dá)式已經(jīng)應(yīng)用于種類(lèi)型的編譯器中，下面就來(lái)演示一下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 操作語(yǔ)法

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)

分頁(yè)

hisql 分頁(yè) 只要在日常查詢上增加 Take() 每頁(yè)顯示多少數(shù)據(jù) Skip() 顯示第幾頁(yè)

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 實(shí)現(xiàn)參數(shù)化

參數(shù)化可以有效的防注入，通過(guò)前端可以拼接的hisql語(yǔ)句通過(guò)參數(shù)化的方式傳入，這樣就解決了注入問(wèn)題

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();

鏈?zhǔn)讲樵?/h2>

hisql除了提供hisql語(yǔ)法外還提供鏈?zhǔn)讲樵兎椒ㄈ缦滤?/p>

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語(yǔ)句和鏈?zhǔn)讲樵兓煊?/h2>

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#實(shí)現(xiàn)定義一套中間SQL可以跨庫(kù)執(zhí)行的SQL語(yǔ)句的文章就介紹到這了,更多相關(guān)C#跨庫(kù)執(zhí)行SQL語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: