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>();
}
}
