c# 实现定义一套中间SQL可以跨库执行的SQL语句
<h1 id="c-实现定义一套中间sql可以跨库执行的sql语句">c# 实现定义一套中间SQL可以跨库执行的SQL语句</h1><p>目前数据的种类非常多，每种数据都支持sql语句，但是大家发现没有每种数据的SQL都有自己的语法特性，都是SQL语句都没有一个特定的语法标准，导致开发人员在开发的过程中无法任意选库（如果用自己不熟的库都会要学习一遍SQL语法），主要是学习成本太高</p>
<p>那么有没有一种工具能够统一下sql语法规则，只要学一种sql语句就可以跨库执行，让开发人员不用学习每种库的SQL语句呢？</p>
<p>下面就给大家介绍一款开源工具 hisql源码 hisql介绍</p>
<h2 id="hisql-官方群">hisql 官方群</h2>
<p><img src="https://img2022.cnblogs.com/blog/891276/202207/891276-20220705091507650-1108981672.png"></p>
<p>为了更好的服务于真正使用hisql的用户，进群的伙伴必须是在github或gitee 上star了hisql项目或进行过捐的伙伴的方能进群</p>
<h2 id="hisql查询样例">hisql查询样例</h2>
<h3 id="单表查询">单表查询</h3>
<p>根据hisql语句通过ToSql()方法生成目标sql的原生sql</p>
<pre><code class="language-c#">
var _sql = sqlClient.HiSql("select * from HTest01 whereCreateTime>='2022-02-17 09:27:50' and CreateTime<='2022-03-22 09:27:50'").ToSql();
</code></pre>
<p>hisql生成的sqlserver 的sql 如下的所示</p>
<pre><code class="language-sql">select.,.,.,.,.,.,.,.,. from as
where . >= '2022-02-17 09:27:50.000' and . <= '2022-03-22 09:27:50.000'
</code></pre>
<p>hisql生成mysql的sql如下所示</p>
<pre><code class="language-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'
</code></pre>
<p>hisql生成postgresql 的sql下所示</p>
<pre><code class="language-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'
</code></pre>
<p>大家可以会问 明明是<code>"select * from HTest01 whereCreateTime>='2022-02-17 09:27:50' and CreateTime<='2022-03-22 09:27:50'"</code> 用的<code>*</code>查询 生成的sql语句为什么会直接列出所有字段呢？ 熟HiSql的网友都应该知道这里其实隐藏了一个功能就是用户可以自定义字段排序在系统表<code>Hi_FieldModel</code> 中可配置</p>
<h2 id="group-by查询">group by查询</h2>
<p>hisql 默认支持的常用函数 <code>max</code>,<code>count</code>,<code>min</code>,<code>sum</code>,<code>avg</code></p>
<pre><code class="language-c#">string sql = sqlClient.HiSql("select FieldName, count(FieldName) as NAME_count,max(FieldType) as FieldType_max from Hi_FieldModelgroup by FieldName").ToSql();
</code></pre>
<p>hisql生成的sqlserver 的sql 如下的所示</p>
<pre><code class="language-sql">select.,count(*) as NAME_count,max(.) as FieldType_max from as
group by .
</code></pre>
<p>hisql生成mysql的sql如下所示</p>
<pre><code class="language-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`
</code></pre>
<p>hisql生成postgresql 的sql下所示</p>
<pre><code class="language-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"
</code></pre>
<h3 id="having-查询">having 查询</h3>
<p>上次有网友说having 实现起来很麻烦通过正则表达式解析很难实现，我只能说很多网友对正则表达式的理解还不是很深，正则表达式已经应用于种类型的编译器中，下面就来演示一下Having</p>
<pre><code class="language-c#">string sql_having = sqlClient.HiSql("select FieldName, count(FieldName) as NAME_count,max(FieldType) as FieldType_max from Hi_FieldModelgroup by FieldName having count(FieldName) > 1").ToSql();
</code></pre>
<p>hisql生成的sqlserver 的sql 如下的所示</p>
<pre><code class="language-sql">select.,count(*) as NAME_count,max(.) as FieldType_max from as
group by .
having count(*) > 1
</code></pre>
<p>hisql生成mysql的sql如下所示</p>
<pre><code class="language-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
</code></pre>
<p>hisql生成postgresql 的sql下所示</p>
<pre><code class="language-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
</code></pre>
<h3 id="join-多表查询">join 多表查询</h3>
<p>hisql inner join 和 in 操作语法</p>
<pre><code class="language-c#">var sql = sqlClient.HiSql("select a.tabname from hi_fieldmodel as a inner join Hi_TabModel asb 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();
</code></pre>
<p>hisql生成的sqlserver 的sql 如下的所示</p>
<pre><code class="language-sql">select. from as
inner join as on . = . inner join as on . = .
where . = 'h_test' and . in (11,41,21)
</code></pre>
<p>hisql生成mysql的sql如下所示</p>
<pre><code class="language-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)
</code></pre>
<p>hisql生成postgresql 的sql下所示</p>
<pre><code class="language-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)
</code></pre>
<h3 id="分页">分页</h3>
<p>hisql 分页 只要在日常查询上增加<code>Take()</code> 每页显示多少数据 <code>Skip()</code> 显示第几页</p>
<pre><code class="language-c#">var sql = sqlClient.HiSql("select a.tabname from hi_fieldmodel as a inner join Hi_TabModel asb 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();
</code></pre>
<p>hisql生成的sqlserver 的sql 如下的所示</p>
<pre><code class="language-sql">select from (
select ROW_NUMBER() OVER(Order by . ASC) AS _hi_rownum_, . from as
inner join as on . = . inner join as on . = .
where . = 'h_test' and . 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
</code></pre>
<p>hisql生成mysql的sql如下所示</p>
<pre><code class="language-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
</code></pre>
<p>hisql生成postgresql 的sql下所示</p>
<pre><code class="language-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
</code></pre>
<h2 id="hisql-实现参数化">hisql 实现参数化</h2>
<p>参数化可以有效的防注入，通过前端可以拼接的hisql语句通过参数化的方式传入， 这样就解决了注入问题</p>
<pre><code class="language-c#"> 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();
</code></pre>
<h2 id="链式查询">链式查询</h2>
<p>hisql除了提供hisql语法外还提供链式查询方法如下所示</p>
<pre><code class="language-c#">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();
</code></pre>
<h2 id="hisql语句和链式查询混用">hisql语句和链式查询混用</h2>
<pre><code class="language-c#">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();
</code></pre><br><br>
来源：https://www.cnblogs.com/tansar/p/16442741.html
頁:
[1]