SQL 语法服务

  ISyntaxProvider 提供对 SQL 语法、函数的解析。在每一种数据库中，SQL 语法和内置函数存在不同的差异，因此使用该扩展服务可以兼容这些差异。

以下是几个比较重要的属性和方法：


1、参数前缀

  ParameterPrefix 用于标识参数化的名称前缀，如 SqlServer 使用符号 @、MySql 使用符号 ?，而 Oracle 使用 :。


2、界定符

  Delimiter 是一对符号，放置在表名称或字段名称的前后，一般在名称中有空格，或与保留关键词冲突时使用。如 SqlServer 使用符号 [ ]、MySql 使用 ，Oracle中 使用 " "。


3、自增编号标识

  IdentityColumn 用于获取自增长编号的语句，如 SqlServer 里为 IDENTITY(1, 1)，MySql 里为 AUTO_INCREMENT。


4、查询当前自增编号

  IdentitySelect 用于获取自增长编号的语句，如 SqlServer 里为 SELECT @@IDENTITY，MySql 里为 SELECT LAST_INSERT_ID()。


5、伪查询表名称

  FakeSelect 是一个伪查询的表名称，即不针对任何表的查询，如 Oracle 比较特殊，为 from dual;，而其他类型的数据库都是空字符串。


6、SQL 分页语法

  Segment 方法为每一种数据库提供 SQL 分页格式，使用该方法传递一个 IDataSegment 来格式化分页查询的 SQL。如下所示：

[TestMethod]
public void TestSegmentPager()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var syntax = db.Provider.GetService<ISyntaxProvider>();
        var segment = new DataPager(20, 1);
        var sql = "select * from products order by productname";

        Console.WriteLine(syntax.Segment(sql, segment));
    }
}

  输出的 SQL 如下：

  • 当数据库为 SqlServer 时
SELECT T.* FROM 
(
        SELECT T.*, ROW_NUMBER() OVER (order by productname) AS ROW_NUM 
        FROM (select * from products) T
) T 
WHERE ROW_NUM BETWEEN 21 AND 40
  • 当数据库为 SQLite 时
SELECT T.* FROM 
(
        select * from products order by productname
) T LIMIT 20 OFFSET 20

7、字段创建语法

  每一种数据库的字段类型可能都不一样，该方法根据 DbType 和字段长度来构建一个字符串。如下所示：

[TestMethod]
public void TestCreateColumn()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var syntax = db.Provider.GetService<ISyntaxProvider>();
        Console.WriteLine("{0}长{1}:{2}", DbType.AnsiString, 50, syntax.Column(DbType.AnsiString, 50));
        Console.WriteLine("{0}:{1}", DbType.Binary, syntax.Column(DbType.Binary));
        Console.WriteLine("{0}:{1}", DbType.Int32, syntax.Column(DbType.Int32));
        Console.WriteLine("{0}:{1}", DbType.Int64, syntax.Column(DbType.Int64));
        Console.WriteLine("{0}:{1}", DbType.SByte, syntax.Column(DbType.SByte));
        Console.WriteLine("{0}:{1}", DbType.Single, syntax.Column(DbType.Single));
        Console.WriteLine("{0}:{1}", DbType.String, syntax.Column(DbType.StringFixedLength));
    }
}

  输出的 SQL 如下：

  • 当数据库为 SqlServer 时
AnsiString长50：VARCHAR(50)
Binary：VARBINARY(8000)
Int32：INT
Int64：BIGINT
SByte：TINYINT
Single：REAL
String：NCHAR(255)
  • 当数据库为 SQLite 时
AnsiString长50：TEXT 
Binary：BLOG 
Int32：INT
Int64：BIGINT
SByte：INTEGER
Single：NUMERIC
String：TEXT

8、字段类型转换语法

  每一种数据库的数据类型转换函数都可能不一样。该方法将字段转换为指定的 DbType 类型。如下所示：

public void ConvertColumn()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var syntax = db.Provider.GetService<ISyntaxProvider>();
        Console.WriteLine("转换为{0}:{1}", DbType.AnsiString, 
                syntax.Convert("fireasy", DbType.AnsiString));
        Console.WriteLine("转换为{0}:{1}", DbType.DateTime, 
                syntax.Convert("fireasy", DbType.DateTime));
        Console.WriteLine("转换为{0}:{1}", DbType.Int16, 
                syntax.Convert("fireasy", DbType.Int16));
        Console.WriteLine("转换为{0}:{1}", DbType.Int32, 
                syntax.Convert("fireasy", DbType.Int32));
        Console.WriteLine("转换为{0}:{1}", DbType.Int64, 
                syntax.Convert("fireasy", DbType.Int64));
    }
}

  输出的 SQL 如下：

  • 当数据库为 SqlServer 时
转换为AnsiString：CAST(fireasy AS VARCHAR)
转换为DateTime：CAST(fireasy AS DATETIME)
转换为Int16：CAST(fireasy AS SMALLINT)
转换为Int32：CAST(fireasy AS INT)
转换为Int64：CAST(fireasy AS BIGINT)
  • 当数据库为 SQLite 时
转换为AnsiString：CAST(fireasy AS TEXT)
转换为DateTime：CAST(fireasy AS DATETIME)
转换为Int16：CAST(fireasy AS INTEGER)
转换为Int32：CAST(fireasy AS INTEGER)
转换为Int64：CAST(fireasy AS INTEGER)

9、空值转换语法

  这好比在C#里写 var obj = obj1 ?? obj2语句一样，在 SQL 里也可以写类似的语句，将空值转换为后续的值，但每一种数据库之间也存在差异。如下所示：

[TestMethod]
public void TestCoalesce()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var syntax = db.Provider.GetService<ISyntaxProvider>();
        var sql = "select " + syntax.Coalesce("companyname", "contactname", "'computer'") + " from customers";
        Console.WriteLine(sql);
        database.ExecuteNonQuery(new SqlCommand(sql));    }
}

  输出的 SQL 如下：

  • 当数据库为 SqlServer 时
select COALESCE(companyname, contactname, 'computer') from customers
  • 当数据库为 Oracle 时
select NVL(companyname, NVL(contactname, 'computer')) from customers

10、字符串函数

  StringSyntax 定义了以下方法：

函数 说明
Substring 取左边第n个字符串
Length 返回长度
IndexOf 判断在字符串中的位置
ToLower 转换为小写
ToUpper 转换为大写
TrimStart 去除前端的空格
TrimEnd 去除后端的空格
Trim 去除两端的空格
Replace 替换字符串
Concat 将多个字符串连接起来
Reverse 反转字符串
Remove 移除指定的字符
PadLeft 在左边补齐字符
PadRight 在右边补齐字符

  下面的示例演示如何取字符串的长度：

[TestMethod]
public void TestStringSyntax()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var syntax = db.Provider.GetService<ISyntaxProvider>();
        var sql = "select " + syntax.String.Length("productname") + " from products";
        Console.WriteLine(sql);
    }
}

  输出的 SQL 如下：

  • 当数据库为 SqlServer 时
select LEN(productname) from products
  • 当数据库为 Oracle 时
select LENGTH(productname) from products

11、日期函数

  DateTimeSyntax 定义了以下方法：

函数 说明
Now 返回当前时间
UtcNow 返回当前UTC时间
Year 取日期中的年份
Month 取日期中的月份
Day 取日期中的天数
Hour 取时间中的小时
Minute 取时间中的分钟
Second 取时间中的秒数
Millisecond 取时间中的毫秒数
DayOfWeek 取本周的第第几
DayOfYear 取本年中的第几天
WeekOfYear 取本年中的第几周
AddYears 在日期上添加n年
AddMonths 在日期上添加n月
AddDays 在日期上添加n天
DiffDays 求两个日期相差的天数
DiffHours 取两个时间相差的小时数
DiffMinutes 取两个时间相差的分钟数
DiffSeconds 取两个时间相差的秒数

  下面的示例演示如何取时间中的分钟：

[TestMethod]
public void TestDateTimeSyntax()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var syntax = db.Provider.GetService<ISyntaxProvider>();
        var sql = "select " + syntax.DateTime.Minute("orderdate") + " from orders";
        Console.WriteLine(sql);
    }
}

  输出的 SQL 如下：

  • 当数据库为 SqlServer 时
select DATEPART(MI, orderdate) from orders
  • 当数据库为 Oracle 时
select TO_NUMBER(TO_CHAR(orderdate,'MM')) from orders

12、数学函数

  MathSyntax 定义了以下方法：

函数 说明
Add 与运算
Or 或运算
Modulo 求余
ExclusiveOr 异或运算
Ceiling 取最小整数
Round 四舍五入
Truncate 取整
Floor 取最大整数
Log 以e为底的对数
Log10 以10为底的对数
Exp e的指定次冪
Abs 取绝对值
Negate 取反
Power n次方(冪)
Sqrt 二次开方
Sin 正弦值
Cos 余弦值
Tan 正切值
Asin 反正弦值
Acos 反余弦值
Atan 反正切值
Sign 返回符号
LeftShift 左移n位
RightShift 右移n位

  下面的示例演示如何求余：

[TestMethod]
public void TestMathSyntax()
{
    using (var db = DatabaseFactory.CreateDatabase())
    {
        var syntax = db.Provider.GetService<ISyntaxProvider>();
        var sql = "select " + syntax.Math.Modulo("unitprice", 2) + " from products";
        Console.WriteLine(sql);
    }
}

  输出的 SQL 如下：

  • 当数据库为 SqlServer 时
select (unitprice % 2) from products
  • 当数据库为 Oracle 时
select MOD(unitprice, 2) from products
0
0
157

« 上一篇：数据库插件服务之 标识生成服务

» 下一篇：数据库提供者的配置及扩展

 

你可以对此发表评论或反馈，以便我们更好地完善此内容和功能：

» 你目前是游客，请先登录

Copyright © 2024 帆易动力  滇ICP备15005781-1