public void ConcurrencyTest() { QueryBuilder.DefaultInstance = new QueryBuilder.SQLite(); using (IDbConnection connection = GetSqliteDbConnection()) { var createTableSql = DDLAttribute.GenerateCreateSQL <ConcurrencyTestTable>(); connection.Execute(createTableSql); var testData = new ConcurrencyTestTable { Key = "1", Data = "xxx", UpdatedAt = new DateTime(2019, 5, 1, 0, 0, 0), Version = 2 }; connection.Select <ConcurrencyTestTable>(() => testData); Assert.AreEqual( "select \"Key\", \"Data\", \"UpdatedAt\", \"Version\" from \"ConcurrencyTestTable\" where \"Key\"=@Key and \"UpdatedAt\"=@UpdatedAt and \"Version\"=@Version", ExecutedSQL); connection.Update(testData); Assert.AreEqual( "update \"ConcurrencyTestTable\" set \"Data\"=@Data, \"UpdatedAt\"=CURRENT_TIMESTAMP, \"Version\"=@Version where \"Key\"=@Key and \"UpdatedAt\"=@UpdatedAt", ExecutedSQL); connection.Delete(testData); Assert.AreEqual( "delete from \"ConcurrencyTestTable\" where \"Key\"=@Key and \"UpdatedAt\"=@UpdatedAt and \"Version\"=@Version", ExecutedSQL); } }
public void Ver0_9Test() { QueryBuilder.DefaultInstance = new QueryBuilder.SQLite(); using (IDbConnection connection = GetSqliteDbConnection()) { var createTableSql = DDLAttribute.GenerateCreateSQL <Member>(); connection.Execute(createTableSql); // Get only specific columns specified by type IReadOnlyList <Ver0_9TestColumns> ver09list1 = connection.Select <Member, Ver0_9TestColumns>(); // Get only specific columns #2 (group by) IReadOnlyList <Ver0_9TestColumns2> ver09list2 = connection.Select <Member, Ver0_9TestColumns2>(); } }
public void ToSqlTest() { 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 != ToSql.Like("%TEST%") || r.Name == ToSql.Between("1", "5") || DateTime.Now < r.CreatedAt)); } }
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>(); } }
public void UpsertTest() { QueryBuilder.DefaultInstance = new QueryBuilder.SQLite() { MultiInsertRowsPerQuery = 2 }; using (IDbConnection connection = GetSqliteDbConnection()) { var createTableSql = DDLAttribute.GenerateCreateSQL <Dept>(); connection.Execute(createTableSql); // insert records ------------------- var insertData = new[] { new Dept { Code = 110, Name = "Business" }, new Dept { Code = 210, Name = "Accounting" }, new Dept { Code = 220, Name = "Finance" }, new Dept { Code = 230, Name = "Purchasing" }, }; int insertMulti = connection.InsertRows(insertData); var inserted = connection.Select <Dept>(); Assert.AreEqual(4, inserted.Count); foreach (var rec in inserted) { Trace.WriteLine(rec.ToString()); } // upsert records ------------------- Thread.Sleep(1000); var upsertRow = new Dept { Code = 230, Name = "Buying" }; int upsertSingle = connection.InsertOrUpdate(upsertRow); Thread.Sleep(1000); var upsertData = new[] { new Dept { Code = 110, Name = "Sales" }, new Dept { Code = 120, Name = "Marketing" }, new Dept { Code = 130, Name = "Publicity" }, }; int upsertMulti = connection.InsertOrUpdateRows(upsertData); var upserted = connection.Select <Dept>(); // returns 6 rows (1 rows updated, 2 rows inserted) Assert.AreEqual(6, upserted.Count); foreach (var rec in upserted) { Trace.WriteLine(rec.ToString()); } // SQL generation according to the dialect of each DBMS ------------------- Trace.WriteLine("<Oracle>"); var oracleBuilder = new QueryBuilder.Oracle() { MultiInsertRowsPerQuery = 2 }; foreach (var sql in oracleBuilder.BuildMultiInsert(insertData)) { Trace.WriteLine(sql); } Trace.WriteLine(oracleBuilder.BuildUpsert <Dept>()); foreach (var sql in oracleBuilder.BuildMultiUpsert(upsertData)) { Trace.WriteLine(sql); } Trace.WriteLine("<SqlServer>"); var mssqlBuilder = new QueryBuilder.SqlServer() { MultiInsertRowsPerQuery = 2 }; Trace.WriteLine(mssqlBuilder.BuildUpsert <Dept>()); foreach (var sql in mssqlBuilder.BuildMultiUpsert(upsertData)) { Trace.WriteLine(sql); } Trace.WriteLine("<MySql>"); var mysqlBuilder = new QueryBuilder.MySql() { MultiInsertRowsPerQuery = 2 }; Trace.WriteLine(mysqlBuilder.BuildUpsert <Dept>()); foreach (var sql in mysqlBuilder.BuildMultiUpsert(upsertData)) { Trace.WriteLine(sql); } } }