Esempio n. 1
0
 public void ErrorTest1()
 {
     Assert.ThrowsException <ArgumentException>(() =>
     {
         Select <T>(t => t.OtherCol == SqlExpr.Eval <int?>("errortest", 1, "引数5でエラーになることを確認", false, 3.5));
     });
 }
Esempio n. 2
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, ")"));
        }
Esempio n. 3
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, ")")
                                                       ));
            }
        }
Esempio n. 4
0
        public void Tutorial()
        {
            QueryBuilder.DefaultInstance = new QueryBuilder.SQLite();

            using (IDbConnection connection = GetSqliteDbConnection())
            {
                // optional : create table -----------
                var createTableSql = DDLAttribute.GenerateCreateSQL <Member>();
                connection.Execute(createTableSql);
                // ->  create table Members
                //     (
                //      "Id" INTEGER, -- identity
                //      "Name",
                //      Phone_No,
                //      "CreatedAt",
                //      "UpdatedAt",
                //      primary key( "Id")
                //     )
                var tableInfoTsv = DDLAttribute.GenerateTableDefTSV <Member>();
                Trace.WriteLine(tableInfoTsv);


                // select 1 record -------------------
                Member select1 = connection.Select(
                    () => new Member {
                    Id = 5
                });

                Member select2 = connection.Select(
                    () => new Member {
                    Id = 6
                },
                    r => new { r.Id, r.Name });

                // (for update etc.)
                Member selectForUpdate = connection.Select(
                    () => new Member {
                    Id = 7
                },
                    otherClauses: "--FOR UPDATE"); // SQLite doesn't support "FOR UPDATE", so commented out

                var targetMember = new Member {
                    Id = 8, Name = "LockTest"
                };
                var lockedMember = connection.Select(
                    () => targetMember,            // where [Key] or [ConcurrencyCheck] is set
                    otherClauses: "--FOR UPDATE"); // SQLite doesn't support "FOR UPDATE", so commented out

                // (with SqlExpr)
                Member select3 = connection.Select(
                    () => new Member {
                    Id = SqlExpr.Eval <int>("(SELECT MAX(id) FROM Members)")
                }
                    );

                // select records --------------------
                IReadOnlyList <Member> list1 = connection.Select <Member>();

                IReadOnlyList <Member> list2 = connection.Select <Member>(
                    r => r.Name == "TEST");

                IReadOnlyList <Member> list3 = connection.Select <Member>(
                    r => r.Name != "TEST",
                    r => new { r.Id, r.Name });

                IReadOnlyList <Member> list4 = connection.Select <Member>(
                    r => r.Tel != null,
                    $"ORDER BY {nameof(Member.Name)} LIMIT 5 OFFSET 10");

                IReadOnlyList <Member> list5 = connection.Select <Member>(
                    r => r.Tel != null,
                    r => new { r.Id, r.Name },
                    $"ORDER BY {nameof(Member.Name)} LIMIT 5 OFFSET 10");

                // count -----------------------------

                ulong count1 = connection.Count <Member>();

                ulong count2 = connection.Count <Member>(
                    r => (r.Id >= 3 && r.Id <= 9));


                // insert ----------------------------
                var rec1 = new Member {
                    Name = "InsertTest", Tel = "177"
                };
                int insert1 = connection.Insert(rec1);

                var rec2 = new Member {
                    Name = "ParticularColumnOnly1", CreatedAt = null
                };
                int insert2 = connection.Insert(rec2,
                                                r => new { r.Name, r.CreatedAt });

                var rec3 = new Member {
                    Name = "IdentityTest", Tel = "7777"
                };
                int insert3 = connection.InsertAndRetrieveId(rec3);
                Trace.WriteLine("insertedID=" + rec3.Id); // -> 3

                int insertX = connection.Insert(
                    () => new Member {
                    Id = 888, Name = "ParticularColumnOnly2"
                });

                // (with SqlExpr)
                string nameExample = "SqlExpr.Eval Usage Example";
                int    insertX2    = connection.Insert(
                    () => new Member {
                    Name = SqlExpr.Eval <string>("Upper(", nameExample, ")")
                });

                // insert records -------------------
                int insertMulti = connection.InsertRows(new[] {
                    new Member {
                        Name = "MultiInsert1", Tel = null
                    },
                    new Member {
                        Name = "MultiInsert2", Tel = "999-999-9999"
                    },
                    new Member {
                        Name = "MultiInsert3", Tel = "88-8888-8888"
                    },
                });

                // update record ---------------------
                rec1 = connection.Select(() => new Member {
                    Id = 1
                });
                rec1.Name = "Updatetest";
                int update1 = connection.Update(rec1);

                rec2.Id  = 2;
                rec2.Tel = "6666-66-6666";
                int update2 = connection.Update(rec2, r => new { r.Tel });

                int update3 = connection.Update(
                    () => new Member {
                    Name = "updateName"
                },
                    r => r.Tel == "55555-5-5555");

                // (with SqlExpr)
                int update4 = connection.Update(
                    () => new Member {
                    Name = SqlExpr.Eval <String>("SUBSTR(name,", 1, ",", 4, ")")
                },
                    r => r.Tel == "55555-5-5555");

                // delete record
                var delRec = new Member {
                    Id = 999, Name = "XXXX"
                };
                int delete1 = connection.Delete(delRec);

                int delete2 = connection.Delete <Member>(
                    r => r.Name == null);

                // truncate
                connection.Truncate <Member>();
            }
        }