public void Tutorial()
{
int?val1 = 100; // (bound to @IntCol)
Select <T>(t => t.IntCol == val1);
Select <T>(t => t.IntCol != val1);
Select <T>(t => t.IntCol < val1);
Select <T>(t => t.IntCol > val1);
Select <T>(t => t.IntCol <= val1);
Select <T>(t => t.IntCol >= val1);
int?val2 = null;
Select <T>(t => t.IntCol == val2);
Select <T>(t => t.IntCol != val2);
Select <T>(t => t.IntCol == t.OtherCol);
string[] inValues = { "111", "222", "333" }; // (bound to @TextCol)
Select <T>(t => t.TextCol == SqlExpr.In(inValues));
Select <T>(t => t.TextCol != SqlExpr.In(inValues));
Select <T>(t => t.IntCol != SqlExpr.In(new[] { 1, 2, 3 }));
string likeValue = "%test%"; // (bound to @TextCol)
Select <T>(t => t.TextCol == SqlExpr.Like(likeValue));
Select <T>(t => t.TextCol != SqlExpr.Like(likeValue));
int b1 = 1; // (bound to @IntCol)
int b2 = 99; // (bound to @P01)
Select <T>(t => t.IntCol == SqlExpr.Between(b1, b2));
Select <T>(t => t.TextCol == "111" && t.IntCol < 200);
Select <T>(t => t.TextCol == "111" || t.IntCol < 200);
Select <T>(t => !(t.TextCol == "111" || t.IntCol < 200));
string text1 = "111";
string text2 = null;
Select <T>(t => text1 == null || t.TextCol == text1);
Select <T>(t => text1 != null && t.TextCol == text1);
Select <T>(t => text2 == null || t.TextCol == text2);
Select <T>(t => text2 != null && t.TextCol == text2);
Select <T>(t => t.TextCol == SqlExpr.In <string>("select text from otherTable where..."));
Select <T>(t => t.IntCol == SqlExpr.In <int?>("select text from otherTable where..."));
Select <T>(t => SqlExpr.Eval("exists(select * from otherTable where...)"));
var idText = "userIdText";
var pwText = "passswordText";
var salt = "hashsalt";
Select <T>(t => SqlExpr.Eval("id=", idText, " AND pw=CRYPT(", pwText, ", pw)"));
Select <T>(t => t.TextCol == SqlExpr.Eval <string>("CRYPT(", pwText, ", pw)"));
Select <T>(t => t.TextCol == SqlExpr.Eval <string>("CRYPT(", pwText, ",", salt, ")"));
}
public void SqlExprTest()
{
QueryBuilder.DefaultInstance = new QueryBuilder.SQLite();
using (IDbConnection connection = GetSqliteDbConnection())
{
var createTableSql = DDLAttribute.GenerateCreateSQL <Member>();
connection.Execute(createTableSql);
var list1 = connection.Select <Member>(r =>
(r.Name == ToSql.In(new[] { "A", "B" }) ||
r.Name != SqlExpr.Like("%TEST%") ||
r.Name == SqlExpr.Between("1", "5") ||
DateTime.Now < r.CreatedAt));
var list2 = connection.Select <Member>(r =>
(r.Id == SqlExpr.In <int>("SELECT MAX(id) FROM Members") ||
SqlExpr.Eval("EXISTS(SELECT * FROM Members m2 WHERE id=sqlite_version())") ||
r.Id == SqlExpr.Eval <int>("MAX(", 1, ",", 2, ",", 3, ")")
));
}
}