Library that parses SQL written as so-called 2-Way-SQL
This is a paser, not an o/r mapper
It expects to pass parsed SQL, generated parameters into
Dapper ,
EntityFramework ,
EntityFrameworkCore,etc
.NET Standard 2.0
- Register Config
// in entry point
ConfigContainer.AddDefault(
DbConnectionKind.SqlServer, // A kind of DB connection
() => new SqlParameter() // A delegate for create IDbDataParameter instance
);- Create 2-Way-SQL file
SELECT
t0.BusinessEntityID
, t1.FirstName
, t1.MiddleName
, t1.LastName
, t0.BirthDate
, t0.MaritalStatus
, t0.Gender
, t0.HireDate
FROM HumanResources.Employee t0
INNER JOIN Person.Person t1
ON t0.BusinessEntityID = t1.BusinessEntityID
WHERE
/*%if MiddleNames != null && MiddleNames.Count > 0 */
t1.MiddleName IN /* MiddleNames */('M')
/*%end*/
/*%if BirthDateFrom != null && BirthDateTo != null */
AND t0.BirthDate BETWEEN /* BirthDateFrom */'1980-01-01' AND /* BirthDateTo */'1990-01-01'
/*%end*/
/*%if FirstName != null && FirstName != "" */
AND t1.FirstName LIKE /* @StartsWith(FirstName) */'A%'
/*%end*/
ORDER BY
t0.BusinessEntityID- execute SQL
// Model to pass to SQL file
public class SqlCondition
{
public List<string> MiddleNames { get; set; }
public DateTime? BirthDateFrom { get; set; }
public DateTime? BirthDateTo { get; set; }
public string FirstName { get; set; }
}
var condition = new SqlCondition
{
BirthDateFrom = new DateTime(1980, 1, 1),
BirthDateTo = new DateTime(1990, 1, 1)
};
var parser = new SqlParser("path/to/SelectEmployees.sql", condition);
var result = parser.Parse();
Console.WriteLine(result.ParsedSql);
Console.WriteLine(result.DebugSql);
using (var context = new EmployeesContext())
{
var resultList = context.Employees
.AsNoTracking()
.FromSql(result.ParsedSql, result.DbDataParameters.Cast<object>().ToArray())
.ToList();
}-
Since SQL parsing is porting the code of DOMA, SQL comments etc. are basically compatible with DOMA
-
However, there are the following differences
expand,populate,forare not supported- Built-in function name, type
- Self-made built-in functions are not supported
-
Supported built-in functions
| Return type | Function name and parameters | Description |
|---|---|---|
| string | @Escape(string text) | Indicates to escape for LIKE operation. The return value is a string with the input value escaped. The escape is done using the default escape character ($). If you pass null as an argument, it returns null. |
| string | @Escape(string text, char escapeChar) | Indicates to escape for LIKE operation. The return value is a string with the input value escaped. Escape is performed using the escape character specified in the second argument. If you pass null as the first argument, it returns null. |
| string | @StartsWith(string text) | Indicates to perform a forward match search. The return value is a string after escaping the input value and appending a wildcard. The escape is done using the default escape character ($). If you pass null as an argument, it returns null. |
| string | @StartsWith(string text, char escapeChar) | Indicates to perform a forward match search. The return value is a string after escaping the input value and appending a wildcard. Escape is performed using the escape character specified in the second argument. If you pass null as the first argument, it returns null. |
| string | @Contains(string text) | Indicates that an intermediate match search is to be performed. The return value is a string with the input value escaped and wildcards given before and after. Escape is done using the default escape character ($). If you pass null as an argument, it returns null. |
| string | @Contains(string text, char escapeChar) | Indicates that an intermediate match search is to be performed. The return value is a string with the input value escaped and wildcards given before and after. Escape is performed using the escape character specified in the second argument. If you pass null as the first argument, it returns null. |
| string | @EndsWith(string text) | Indicates to perform a backward match search. The return value is a string with the input value escaped and preceded by a wildcard. Escape is done using the default escape character ($). If you pass null as an argument, it returns null. |
| string | @EndsWith(string text, char escapeChar) | Indicates to perform a backward match search. The return value is a string with the input value escaped and preceded by a wildcard. Escape is performed using the escape character specified in the second argument. If you pass null as the first argument, it returns null. |
| DateTime | @TruncateTime(DateTime dateTime) | Indicates to truncate the time part. The return value is a new date with the time portion truncated. If you pass null as an argument, it returns null. |
| DateTimeOffset | @TruncateTime(DateTimeOffset dateTimeOffset) | Indicates to truncate the time part. The return value is a new date with the time portion truncated. If you pass null as an argument, it returns null. |
- The names
StartsWith,Contains,EndsWithcome from EntityFramework
- It's available on nuget
dotnet add package EasySqlParser
or
Install-Package EasySqlParser
https://github.com/stroheim/EasySqlParser.Examples
https://stroheim.github.io/EasySqlParser-Doc/
This product is based on the following source code