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