基于AScript的SQL脚本语言发布啦！-Seo优化-塔城地区网站建设公司

一、介绍

支持SqlServer/MySql基础语法和数据类型：

支持SELECT查询语法：FROM/WHERE/LEFT JOIN/RIGHT JOIN/INNER JOIN/GROUP BY/ORDER BY/LIMIT
支持INSERT插入语法
支持UPDATE修改语法
支持DELETE删除语法
支持创建存储过程：Sqlserver/MySql语法都支持
支持创建表
支持定义变量
字段名、关键字不区分大小写
支持调用外部方法和变量
已内置常用数据类型：tinyint/smallint/int/bigint/decimal/float/real/double/bit/char/nchar/varchar/nvarchar/text/datetime

不支持：

存储过程暂不支持OUT参数
SELECT查询不支持*号
SELECT查询不支持聚合函数

二、安装

1 install-package AScript 2 install-package AScript.Lang.Sql

三、使用说明

1、注册语言

1 Script.Langs.Set("sql", SqlLang.Instance); 2 // 可全局设置为默认语言 3 // Script.Langs.Set("sql", SqlLang.Instance, setDefault: true);

2、单表查询

select多个字段，生成匿名类型

1 var s = @"select Name, Age from list where age=10"; 2 var list = new[] { new Person("tom", 15), new Person("jim", 10), new Person("san", 20), new Person("qin", 10) }; 3 var script = new Script(); 4 script.Context.Langs = new[] { "sql" }; 5 script.Context.SetVar("list", list); 6 var result = script.Eval<IEnumerable<dynamic>>(s).ToList(); 7 Assert.AreEqual(2, result.Count); 8 Assert.AreEqual("jim", result[0].Name); 9 Assert.AreEqual(10, result[0].Age); 10 Assert.AreEqual("qin", result[1].Name); 11 Assert.AreEqual(10, result[1].Age);

select一个字段

1 var s = @"select Name from list where age=10"; 2 var list = new[] { new Person("tom", 15), new Person("jim", 10), new Person("san", 20), new Person("qin", 10) }; 3 var script = new Script(); 4 script.Context.Langs = new[] { "sql" }; 5 script.Context.SetVar("list", list); 6 var result = script.Eval<IEnumerable<string>>(s).ToList(); 7 Assert.AreEqual(2, result.Count); 8 Assert.AreEqual("jim", result[0]); 9 Assert.AreEqual("qin", result[1]);

3、单独from语句

1 var s = @"from list where age=10"; 2 var list = new[] { new Person("tom", 15), new Person("jim", 10), new Person("san", 20), new Person("qin", 10) }; 3 var script = new Script(); 4 script.Context.Langs = new[] { "sql" }; 5 script.Context.SetVar("list", list); 6 var result = script.Eval<IEnumerable<Person>>(s).ToList(); 7 Assert.AreEqual(2, result.Count); 8 Assert.AreEqual("jim", result[0].Name); 9 Assert.AreEqual(10, result[0].Age); 10 Assert.AreEqual("qin", result[1].Name); 11 Assert.AreEqual(10, result[1].Age);

4、多表查询

1 string s = @" 2 var q1 = new[] { new Person('tom', 20), new Person('jim', 25), new Person('san', 18), new Person('kit', 30) }.AsQueryable(); 3 var q2 = new[] { new AddressInfo('jim', 'a'), new AddressInfo('cc', 'b'), new AddressInfo('tom', 'c'), new AddressInfo('ee', 'd') }.AsQueryable(); 4 @lang sql 5 select a.Name, a.Age, b?.Address, case a.Age when 25 then 1 else 2 end as Level 6 from q1 as a 7 left join q2 as b on a.Name = b.UserName 8 where a.age > 22 9 order by a.age desc 10 "; 11 var script = new Script(); 12 script.Context.AddType<Person>(); 13 script.Context.AddType<AddressInfo>(); 14 var list = script.Eval<IEnumerable<dynamic>>(s).ToList(); 15 Assert.AreEqual(2, list.Count); 16 Assert.AreEqual("kit", list[0].Name); 17 Assert.AreEqual(30, list[0].Age); 18 Assert.AreEqual(2, list[0].Level); 19 Assert.IsNull(list[0].Address); 20 Assert.AreEqual("jim", list[1].Name); 21 Assert.AreEqual(25, list[1].Age); 22 Assert.AreEqual("a", list[1].Address); 23 Assert.AreEqual(1, list[1].Level);

5、INSERT

执行insert语句返回插入记录的数量。

1 var s = @"insert into list (Name, Age) values ('tom', 20), ('jim', 25)"; 2 var list = new List<Person>(); 3 var script = new Script(); 4 script.Context.Langs = new[] { "sql" }; 5 script.Context.SetVar("list", list); 6 Assert.AreEqual(2, script.Eval(s)); 7 Assert.AreEqual(2, list.Count); 8 Assert.AreEqual("tom", list[0].Name); 9 Assert.AreEqual(20, list[0].Age); 10 Assert.AreEqual("jim", list[1].Name); 11 Assert.AreEqual(25, list[1].Age);

6、UPDATE

执行update语句返回修改记录的数量。

1 var s = @"update list set Age=28 where Age<25"; 2 var list = new List<Person> 3 { 4 new Person("jim", 18), 5 new Person("tom", 20), 6 new Person("lily", 30) 7 }; 8 var script = new Script(); 9 script.Context.Langs = new[] { "sql" }; 10 script.Context.SetVar("list", list); 11 var count = (int)script.Eval(s); 12 Assert.AreEqual(2, count); 13 Assert.AreEqual(28, list[0].Age); 14 Assert.AreEqual(28, list[1].Age); 15 Assert.AreEqual(30, list[2].Age);

7、DELETE

执行delete语句返回删除记录的数量。

1 var s = @"delete from list where Age<25"; 2 var list = new List<Person> 3 { 4 new Person("jim", 18), 5 new Person("tom", 20), 6 new Person("lily", 30) 7 }; 8 var script = new Script(); 9 script.Context.Langs = new[] { "sql" }; 10 script.Context.SetVar("list", list); 11 var count = (int)script.Eval(s); 12 Assert.AreEqual(2, count); 13 Assert.AreEqual(1, list.Count); 14 Assert.AreEqual("lily", list[0].Name);

8、SqlServer存储过程

如果有多条语句，则使用begin..end，只有一条语句可以不写begin..end。

1 var s = @" 2 CREATE PROCEDURE AddPerson 3 @name VARCHAR, 4 @age INT 5 AS 6 BEGIN 7 INSERT INTO list (name, age) VALUES(@name, @age) 8 END 9 EXEC AddPerson 'tom', 20 10 "; 11 var list = new List<Person>(); 12 var script = new Script(); 13 script.Context.Langs = new[] { "sql" }; 14 script.Context.SetVar("list", list); 15 Assert.AreEqual(1, script.Eval(s)); 16 Assert.AreEqual(1, list.Count); 17 Assert.AreEqual("tom", list[0].Name); 18 Assert.AreEqual(20, list[0].Age);

9、MySql存储过程

如果有多条语句，则使用begin..end，只有一条语句可以不写begin..end。

1 var s = @" 2 CREATE PROCEDURE AddPerson(@name VARCHAR, @age INT) 3 BEGIN 4 DECLARE @name2 VARCHAR 5 DECLARE @age2 INT 6 SET @name2 = @name + '2' 7 SET @age2 = @age + 10 8 INSERT INTO list (name, age) VALUES(@name, @age),(@name2, @age2) 9 END 10 CALL AddPerson('tom', 20)"; 11 var list = new List<Person>(); 12 var script = new Script(); 13 script.Context.Langs = new[] { "sql" }; 14 script.Context.SetVar("list", list); 15 Assert.AreEqual(2, script.Eval(s)); 16 Assert.AreEqual(2, list.Count); 17 Assert.AreEqual("tom", list[0].Name); 18 Assert.AreEqual(20, list[0].Age); 19 Assert.AreEqual("tom2", list[1].Name); 20 Assert.AreEqual(30, list[1].Age);

10、创建函数

1 var s = @" 2 CREATE FUNCTION Calc(@a INT, @b INT) 3 RETURNS INT 4 BEGIN 5 DECLARE @sum INT 6 SET @sum = @a * @b + @a + @b 7 RETURN @sum 8 END 9 SELECT Calc(2, 3) 10 "; 11 var script = new Script(); 12 script.Context.Langs = new[] { "sql" }; 13 var result = script.Eval(s); 14 Assert.AreEqual(11, result);

11、创建表

表字段支持主键（PRIMARY KEY)、自增（IDENTITY(1,1)或者AUTO_INCREMENT）、默认值（DEFAULT）、可空（NULL）、非空（NOT NULL）、最大长度定义。

简单示例：

1 var s = @" 2 CREATE TABLE person (name varchar, age int) 3 INSERT INTO person (name, age) VALUES ('tom', 20),('jim', 25),('san', 18) 4 UPDATE person SET age = 30 WHERE name = 'tom' 5 DELETE FROM person WHERE Name = 'jim' 6 SELECT Name,age FROM person WHERE age > 22 7 "; 8 var script = new Script(); 9 script.Context.Langs = new[] { "sql" }; 10 var list = script.Eval<IEnumerable<dynamic>>(s).ToList(); 11 Assert.AreEqual(1, list.Count); 12 Assert.AreEqual("tom", list[0].Name); 13 Assert.AreEqual(30, list[0].age);

复杂示例：

1 var s = @" 2 CREATE TABLE student_scores ( 3 id INT PRIMARY KEY AUTO_INCREMENT, 4 name VARCHAR(50), 5 subject VARCHAR(20), 6 score INT NOT NULL DEFAULT 0, 7 class_level VARCHAR(10) 8 ); 9 10 INSERT INTO student_scores (name,subject,score,class_level) VALUES 11 ('张三','数学',85,'A'), 12 ('李四','数学',92,'B'), 13 ('王五','数学',78,'A'), 14 ('赵六','数学',45,'C'); 15 16 SELECT 17 id, 18 name, 19 score, 20 CASE 21 WHEN score >= 90 THEN '优秀' 22 WHEN score >= 80 THEN '良好' 23 WHEN score >= 60 THEN '及格' 24 ELSE '不及格' 25 END AS basic_grade, 26 CASE 27 WHEN score >= 90 THEN 28 CASE WHEN class_level='A' THEN '顶尖' ELSE '优秀' END 29 WHEN score >= 80 THEN '潜力' 30 ELSE '需加强' 31 END AS advanced_grade 32 FROM student_scores; 33 "; 34 var script = new Script(); 35 script.Context.Langs = new[] { "sql" }; 36 var list = script.Eval<IEnumerable<dynamic>>(s).ToList(); 37 Assert.AreEqual(4, list.Count); 38 Assert.AreEqual(1, list[0].id); 39 Assert.AreEqual("张三", list[0].name); 40 Assert.AreEqual(85, list[0].score); 41 Assert.AreEqual("良好", list[0].basic_grade); 42 Assert.AreEqual("潜力", list[0].advanced_grade); 43 Assert.AreEqual(2, list[1].id); 44 Assert.AreEqual("李四", list[1].name); 45 Assert.AreEqual(92, list[1].score); 46 Assert.AreEqual("优秀", list[1].basic_grade); 47 Assert.AreEqual("优秀", list[1].advanced_grade); 48 Assert.AreEqual(3, list[2].id); 49 Assert.AreEqual("王五", list[2].name); 50 Assert.AreEqual(78, list[2].score); 51 Assert.AreEqual("及格", list[2].basic_grade); 52 Assert.AreEqual("需加强", list[2].advanced_grade); 53 Assert.AreEqual(4, list[3].id); 54 Assert.AreEqual("赵六", list[3].name); 55 Assert.AreEqual(45, list[3].score); 56 Assert.AreEqual("不及格", list[3].basic_grade); 57 Assert.AreEqual("需加强", list[3].advanced_grade);

12、SQL to LINQ

项目中我们经常使用LINQ to SQL，如果反过来SQL to LINQ是啥样呢？

1 using (var context = new TestSqliteContext()) 2 { 3 var s = @" 4 select 5 p.Id, 6 p.Name, 7 p.Age, 8 a.Address as MyAddress, 9 case p.Age when 20 then 1 when 22 then 2 else 3 end as Level 10 from context.Persons as p 11 left join context.AddressInfos as a on p.Id = a.UserId 12 "; 13 var script = new Script(); 14 script.Context.Langs = new[] { "sql" }; 15 script.Context.SetVar("context", context); 16 var list = script.Eval<IEnumerable<dynamic>>(s).ToList(); 17 Console.WriteLine(JsonConvert.SerializeObject(list, Formatting.Indented)); 18 }

我们通过SQL脚本来操作DbContext，实现SQL to LINQ，再由LINQ转SQL（EF Core/FreeSql/SqlSugar等ORM）执行数据库查询操作。

生成的SQL语句如下：

1 SELECT "p"."Id", "p"."Name", "p"."Age", "a"."Address" AS "MyAddress", CASE 2 WHEN "p"."Age" = 20 THEN 1 3 WHEN "p"."Age" = 22 THEN 2 4 ELSE 3 5 END AS "Level" 6 FROM "Persons" AS "p" 7 LEFT JOIN "AddressInfos" AS "a" ON "p"."Id" = "a"."UserId" 8 ORDER BY "p"."Age" DESC

当然，也可以使用SQL脚本对DbContext进行新增、修改、删除操作，同上面的INSERT/UPDATE/DELETE示例。

四、应用场景

1）前端动态查询条件和排序方式：

将前端传过来的条件和排序拼接为SQL字符串，然后生成Lambda表达式，如下：

1 // 前端传name、op(=,>,<等比较符)、age 2 using (var context = new TestSqliteContext()) 3 { 4 // 拼接条件字符串 5 var conditionString = "p.name like %"+name+"% and p.age" + op + age; 6 var script = new Script(); 7 script.Context.Langs = new[] { "sql" }; 8 // 编译条件 9 Expression<Func<Person, bool>> condition = script.Lambda<Person, bool>(conditionString, "p"); 10 // 条件过滤 11 var result = context.Persons.Where(condition).ToList(); 12 }

2）数据层只提供了IQuerable<T>可是应用层我手痒想写SQL怎么办？

那就用SQL to LINQ吧！