private void SelectPaged()
{
string procName = Prefix + "GETPAGED" + Suffix;
string fileName = procName + ".sql";
var writer = CreateProcedureWriter(procName);
writer.AddParameter(ParameterName("currentPage"), ColumnDataType("INT"));
writer.AddParameter(ParameterName("pageSize"), ColumnDataType("INT"));
writer.AddOutputParameter(ParameterName("total"), ColumnDataType("INT"));
writer.BeginProcedure(true);
//returns two result sets- paged, and count of total
writer.AddQuerySql(SqlWriter.SelectPageSql());
writer.AddSql(SqlWriter.CountSql(ParameterName("total")));
var txt = writer.End();
WriteFile(fileName, txt);
WriteSignature(writer.Signature());
}
public void RunCountSql()
{
//arrange
var writer = new SqlWriter(_table, _sqlType);
var sql = writer.CountSql();
int count;
//run generated sql
using (var con = _factory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
con.Open();
//this returns an int in SqlServer and MySQL, a long in SQLite and a decimal(!) in Oracle
count = Convert.ToInt32(cmd.ExecuteScalar());
}
}
//assert
Assert.IsTrue(count > 0, "There should be some categories (this test may fail if database table is empty)");
}
public void TestGeneratedSqlForCount()
{
//arrange
var table = LoadCategoriesFromNorthwind();
var writer = new SqlWriter(table, SqlType.SqlServer);
var sql = writer.CountSql();
var factory = DbProviderFactories.GetFactory(ProviderName);
int count;
//run generated sql
using (var con = factory.CreateConnection())
{
con.ConnectionString = ConnectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
con.Open();
count = (int)cmd.ExecuteScalar();
}
}
//assert
Assert.IsTrue(count > 0, "There should be some categories (this test may fail if database table is empty)");
}