public void Not_Supported()
{
engine.AddFunction("LASTINSERTID", FunctionHelper.NotSupported);
IFunction func = (IFunction)sql.Val(() => SqlExp.Function("LASTINSERTID"));
Exception ex = Assert.Throws <ClauseNotSupportedException>(() => engine.Compile(func));
Assert.Equal("Function \"LASTINSERTID\" is not supported in this engine.", ex.Message);
}
public void Coalesce_Object_Overload()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Coalesce(person.Name, person.Salary));
QueryResult result = engine.Compile(func);
Assert.Equal("COALESCE(\"person\".\"Name\", \"person\".\"Salary\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Count_Distinct()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.CountDistinct(person.Name));
QueryResult result = engine.Compile(func);
Assert.Equal("COUNT(DISTINCT \"person\".\"Name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void OrElse_Operator_Value(int id, string name)
{
Person person = new Person()
{
Id = 1, Name = "abcd"
};
Assert.Equal(person.Id == id || person.Name == name,
sql.Val(() => SqlExp.Val(person.Id == id || person.Name == name)));
}
public void Conditional_Operator_Value(string name, string value)
{
Person person = new Person()
{
Name = name, SurName = name
};
Assert.Equal(person.Name != null ? person.SurName : value,
sql.Val(() => SqlExp.Val(person.Name != null ? person.SurName : value)));
}
public void Count_Distinct()
{
engine.AddFunction(FunctionName.Concat, FunctionHelper.ConcatOr);
Person person = new Person();
Exception ex = Assert.Throws <InvalidOperationException>(() => SqlExp.CountDistinct(person.Name));
Assert.Equal("Only for expressions.", ex.Message);
}
public void Concat()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Concat(person.Name, person.SurName));
QueryResult result = engine.Compile(func);
Assert.Equal("\"PERSON\".\"NAME\" || \"PERSON\".\"SURNAME\"", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Trim()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Trim(person.Name));
QueryResult result = engine.Compile(func);
Assert.Equal("TRIM(\"PERSON\".\"NAME\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void ConcatOr_Invalid()
{
engine.AddFunction(FunctionName.Concat, FunctionHelper.ConcatOr);
IFunction func = (IFunction)sql.Val(() => SqlExp.Concat());
Exception ex = Assert.Throws <CompileException>(() => engine.Compile(func));
Assert.Equal("Invalid function \"CONCAT\", wrong number of parameters.", ex.Message);
}
public void Coalesce()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Coalesce(person.Name, person.SurName));
QueryResult result = engine.Compile(func);
Assert.Equal("COALESCE([person].[Name], [person].[SurName])", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void AvgDistinct()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.AvgDistinct(person.Salary));
QueryResult result = engine.Compile(func);
Assert.Equal("AVG(DISTINCT `person`.`Salary`)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Lower()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Lower(person.Name));
QueryResult result = engine.Compile(func);
Assert.Equal("LOWER(`person`.`Name`)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Ceiling()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Ceiling(person.Salary));
QueryResult result = engine.Compile(func);
Assert.Equal("CEILING(\"person\".\"Salary\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Concat()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Concat(person.Name, person.SurName));
QueryResult result = engine.Compile(func);
Assert.Equal("CONCAT(\"person\".\"Name\", \"person\".\"SurName\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Cast()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Cast(person.Salary, sql.Type("VARCHAR")));
QueryResult result = engine.Compile(func);
Assert.Equal("CAST(\"person\".\"Salary\" AS VARCHAR)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Upper()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Upper(person.Name));
QueryResult result = engine.Compile(func);
Assert.Equal("UPPER(\"person\".\"Name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Expression_Method_Nested()
{
Person person = null;
IOperator op = sql.Op(() => SqlExp.IsNull(person.Address.Street));
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"AddressStreet\" IS NULL", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void SumDistinct()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.SumDistinct(person.Salary));
QueryResult result = engine.Compile(func);
Assert.Equal("SUM(DISTINCT \"person\".\"Salary\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Expression_Method_ForeignKey()
{
Person person = null;
IOperator op = sql.Op(() => SqlExp.IsNotNull(person.Department.Id));
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"DepartmentId\" IS NOT NULL", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Expression_Method_Nested_Deep()
{
Person2 person = null;
IOperator op = sql.Op(() => SqlExp.IsNotNull(person.Address.City.Country.Name));
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"AddressCityCountryName\" IS NOT NULL", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void RTrim()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.RTrim(person.Name));
QueryResult result = engine.Compile(func);
Assert.Equal("TRIM(TRAILING FROM `person`.`Name`)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Expression_Method_Null()
{
Person person = null;
IOperator op = sql.Op(() => SqlExp.Like(person.Name, null));
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"Name\" LIKE NULL", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Avg()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Avg(person.Salary));
QueryResult result = engine.Compile(func);
Assert.Equal("AVG(\"PERSON\".\"SALARY\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Sum()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Sum(person.Salary));
QueryResult result = engine.Compile(func);
Assert.Equal("SUM([person].[Salary])", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void LTrim()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.LTrim(person.Name));
QueryResult result = engine.Compile(func);
Assert.Equal("LTRIM([person].[Name])", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Round()
{
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Round(person.Salary));
QueryResult result = engine.Compile(func);
Assert.Equal("ROUND(`person`.`Salary`)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
/// <summary>
/// 自定义数据库查询.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="expression"></param>
/// <returns></returns>
public IEnumerable <T> Query <T>(Action <SqlExp <T> > expression)
{
using (var conn = DbConnFactory.Open(DbName))
{
var exp = SqlExp <T> .GetSqlExp(conn);
expression(exp);
return(conn.Select(exp.GetExp()));
}
}
public static PagedResult <TResult> PagedQuery <TEntity, TResult>(this IDbConnection db, int pageSize, int pageNumber, Action <SqlExp <TResult> > action, Action <SqlExp <TEntity> > subAction,
IDbTransaction trans = null, int?commandTimeout = null) where TEntity : class where TResult : class
{
var sqllam = new SqlExp <TEntity>(db.GetAdapter());
subAction?.Invoke(sqllam);
var sqlLamMain = new SqlExp <TResult>(db.GetAdapter());
sqlLamMain.SubQuery(sqllam);
action?.Invoke(sqlLamMain);
var countSqlam = new SqlExp <TResult>(db.GetAdapter(), true);
countSqlam.SubQuery(sqllam);
action?.Invoke(countSqlam);
countSqlam = countSqlam.Count <TResult>(sqlLamMain);
int countRet = 0;
var sqlString = countSqlam.SqlString;
try
{
DebuggingSqlString(sqlString);
countRet = db.Query <int>(sqlString, countSqlam.Parameters, trans, commandTimeout: commandTimeout).FirstOrDefault();
}
catch (Exception ex)
{
DebuggingException(ex, sqlString);
throw new DapperLamException(ex.Message, ex, countSqlam.SqlString)
{
Parameters = countSqlam.Parameters
};
}
var sqlstring = sqlLamMain.QuerySubPage(pageSize, pageNumber);
try
{
DebuggingSqlString(sqlstring);
var retlist = db.Query <TResult>(sqlstring, sqlLamMain.Parameters, trans, commandTimeout: commandTimeout);
return(new PagedResult <TResult>(retlist, countRet, pageSize, pageNumber));
}
catch (Exception ex)
{
DebuggingException(ex, sqlstring);
throw new DapperLamException(ex.Message, ex, sqlstring)
{
Parameters = sqlLamMain.Parameters
};
}
}
public void TrimTrailing_Invalid()
{
engine.AddFunction(FunctionName.RTrim, FunctionHelper.TrimTrailing);
Person person = null;
IFunction func = (IFunction)sql.Val(() => SqlExp.Function("RTRIM", person.Name, ",", ","));
Exception ex = Assert.Throws <CompileException>(() => engine.Compile(func));
Assert.Equal("Invalid function \"RTRIM\", wrong number of parameters.", ex.Message);
}
public void Expression_Method_Column()
{
Person person = null;
Department dept = null;
IOperator op = sql.Op(() => SqlExp.Not(person.Department.Id == dept.Id));
QueryResult result = engine.Compile(op);
Assert.Equal("NOT \"person\".\"DepartmentId\" = \"dept\".\"Id\"", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}