基于AScript的SQL脚本语言发布啦！

一、介绍支持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 age10; 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.EvalIEnumerabledynamic(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 age10; 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.EvalIEnumerablestring(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 age10; 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.EvalIEnumerablePerson(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.AddTypePerson(); 13 script.Context.AddTypeAddressInfo(); 14 var list script.EvalIEnumerabledynamic(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 ListPerson(); 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 Age28 where Age25; 2 var list new ListPerson 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 Age25; 2 var list new ListPerson 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 ListPerson(); 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 ListPerson(); 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.EvalIEnumerabledynamic(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_levelA 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.EvalIEnumerabledynamic(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.EvalIEnumerabledynamic(s).ToList(); 17 Console.WriteLine(JsonConvert.SerializeObject(list, Formatting.Indented)); 18 }我们通过SQL脚本来操作DbContext实现SQL to LINQ再由LINQ转SQLEF 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 ExpressionFuncPerson, bool condition script.LambdaPerson, bool(conditionString, p); 10 // 条件过滤 11 var result context.Persons.Where(condition).ToList(); 12 }2数据层只提供了IQuerableT可是应用层我手痒想写SQL怎么办那就用SQL to LINQ吧结束语SQL动态脚本也内置了一些函数比如获取当前时间NOW()或者GETDATE()字符串拼接CONCAT计算长度LEN(字符串或列表)或者LENGTH(字符串或列表)数学函数ABS/POW/ROUND/FLOOR/SIN/COS等