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