public void ReturnSimpleScalar()
{
ExecuteSQL("CREATE TABLE test(id INT)");
ExecuteSQL("INSERT INTO test VALUES (1)");
SqlResult r = GetSession(true).SQL("SELECT * FROM test").Execute();
Assert.True(r.Next());
Assert.Equal(1, r[0]);
Assert.False(r.NextResult());
}
public void Bind()
{
ExecuteSQL("CREATE TABLE test(id INT, letter varchar(1))");
for (int i = 1; i <= 10; i++)
{
ExecuteSQLStatement(GetSession(true).SQL("INSERT INTO test VALUES (?, ?), (?, ?)")
.Bind(i, ((char)('@' + i)).ToString())
.Bind(++i, ((char)('@' + i)).ToString()));
}
SqlResult result = ExecuteSQLStatement(GetSession(true).SQL("select * from test where id=?").Bind(5));
Assert.True(result.Next());
Assert.That(result.Rows, Has.One.Items);
Assert.AreEqual(5, result[0]);
Assert.AreEqual("E", result[1]);
}
public void Bind()
{
ExecuteSQL("CREATE TABLE test(id INT, letter varchar(1))");
for (int i = 1; i <= 10; i++)
{
GetSession(true).SQL("INSERT INTO test VALUES (?, ?), (?, ?)")
.Bind(i, ((char)('@' + i)).ToString())
.Bind(++i, ((char)('@' + i)).ToString())
.Execute();
}
SqlResult result = GetSession(true).SQL("select * from test where id=?").Bind(5).Execute();
Assert.True(result.Next());
Assert.Equal(1, result.Rows.Count);
Assert.Equal(5, result[0]);
Assert.Equal("E", result[1]);
}
public void TestPaging()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
if (sf.HasCapability(Capability.Paging))
{
// first verify that data set is ok for test
IList lists = MailingList.ListAll;
Assert.IsNotNull(lists, "Test case invalid if row count is below 3.");
Assert.IsTrue(lists.Count >= 3, "Test case invalid if row count is below 3.");
// try with 1 row limit
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(MailingList));
sb.SetRowLimit(1);
SqlResult sr = Broker.Execute(sb.GetStatement(true));
// verify result
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 1 was not limited.");
MailingList ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[0] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
// get next result
sr = sr.Next();
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 2 was not limited.");
ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[1] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
// get next result
sr = sr.Next();
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 3 was not limited.");
ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[2] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
// try the previous again
sr = sr.Previous();
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 4 was not limited.");
ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[1] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
//Try Page Numbers..
sr = sr.Page(2);
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 5 was not limited.");
ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[1] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
sr = sr.Page(1);
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 6 was not limited.");
ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[0] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
sr = sr.Next();
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 7 was not limited.");
ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[1] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
sr = sr.Page(3);
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 8 was not limited.");
ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[2] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
//Test for Page 0 - should return first page
sr = sr.Page(0);
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected.");
Assert.AreEqual(1, sr.RowsContained, "Result set 9 was not limited.");
ml = ObjectFactory.GetInstance(typeof(MailingList), sr) as MailingList;
Assert.AreEqual((lists[0] as MailingList).Id, ml.Id, "Wrong row(s) retrieved.");
//Test for invalid page - should return no rows
sr = sr.Page(1 + (lists.Count / sr.Statement.RowLimit));
Assert.IsTrue(sr.ErrorCode == 0 && sr.RowsContained == 0);
}
}
public bool MoveNext()
{
return(_sqlResult.Next());
}