public void _12_CreateSProc()
{
DBDatabase db = ConnectDb();
this.CreateCustomTable(db);
try
{
DBParam name = DBParam.Param("name", DbType.String, 50);
DBParam desc = DBParam.Param("desc", DbType.String, 150);
DBQuery ins = DBQuery.InsertInto(TableName).Fields(TblCol2, TblCol3).Values(name, desc);
char one = 'A';
char two = 'a';
TestContext.WriteLine(ins.ToSQLString(db));
for (int i = 0; i < 26; i++)
{
int c = (int)one;
c += i;
string offset = ((char)c).ToString() + two.ToString();
name.Value = offset;
desc.Value = "Description of " + offset;
db.ExecuteNonQuery(ins);
}
int count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName)));
Assert.AreEqual(26, count);
DBQuery q = DBQuery.Create.StoredProcedure("finditemsincol2")
.WithParam("p1", DbType.String, 50, ParameterDirection.Input)
.As(
DBQuery.SelectAll().From(TableName)
.WhereField(TblCol2, Compare.Like, DBParam.Param("p1"))
);
TestContext.WriteLine("Execute Procedure: " + q.ToSQLString(db));
db.ExecuteNonQuery(q);
TestContext.WriteLine("Created the new stored procedure");
DBQuery exec = DBQuery.Exec("finditemsincol2").WithParamValue("p1", DbType.String, 50, "A%");
count = 0;
TestContext.WriteLine(exec.ToSQLString(db));
db.ExecuteRead(exec, reader =>
{
while (reader.Read())
{
count++;
Assert.IsTrue(reader[TblCol2].ToString().StartsWith("A"));
}
TestContext.WriteLine("Executed the stored procedure and read '" + count.ToString() + "' rows");
});
Assert.AreEqual(1, count);
}
finally
{
try
{
DBQuery drop = DBQuery.Drop.StoredProcedure("finditemsincol2");
db.ExecuteNonQuery(drop);
TestContext.WriteLine("Sucessfully dropped the stored procedure");
}
catch
{
TestContext.WriteLine("DROP PROCEDURE failed");
}
this.DropCustomTable(db);
}
}