Ejemplo n.º 1
0
        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, ")"));
        }
Ejemplo n.º 2
0
        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, ")")
                                                       ));
            }
        }