Paging and Sorting on SQL Server and Oracle using Sql.Net

using System;
using System.Data;
using System.Data.SqlClient;
using Reeb.SqlOM;
using Reeb.SqlOM.Render;

namespace PagingSampleProject
{
	class Test
	{
		SqlConnection connection;

		[STAThread]
		static void Main(string[] args)
		{
			Test test = new Test();
			test.AllProducts(1, 2, "name", true);
			test.ParametrizedQuery(0, 2, "name", true);
		}

		Test()
		{
			connection = new SqlConnection("your database here");
			connection.Open();
		}

		void AllProducts(int pageNum, int pageSize, string orderCol, bool asc)
		{
			SelectQuery query = new SelectQuery();
			query.Columns.Add(new SelectColumn("*"));
			query.FromClause.BaseTable = FromTerm.Table("products");
			query.OrderByTerms.Add(new OrderByTerm(orderCol, (asc) ? OrderByDirection.Ascending : OrderByDirection.Descending));

            SqlServerRenderer renderer = new SqlServerRenderer();
			string rowCountSql = renderer.RenderRowCount(query);
			int rowCount = Convert.ToInt32(new SqlCommand(rowCountSql, connection).ExecuteScalar());
			string pageSql = renderer.RenderPage(pageNum, pageSize, rowCount, query);
			IDataReader data = new SqlCommand(pageSql, connection).ExecuteReader();
			data.Close();
		}

		void Example_1_1(string orderCol, bool asc)
		{
			FromTerm tProducts = FromTerm.Table("products");
			FromTerm tCategories = FromTerm.Table("categories");

			SelectQuery query = new SelectQuery();
			query.Columns.Add(new SelectColumn("productName"));
			query.Columns.Add(new SelectColumn("categoryName"));
			query.FromClause.BaseTable = tProducts;
			query.FromClause.Join(JoinType.Inner, tProducts, tCategories, "categoryId", "categoryId");
			query.OrderByTerms.Add(new OrderByTerm(orderCol, (asc) ? OrderByDirection.Ascending : OrderByDirection.Descending));	
		}

		void ParametrizedQuery(int pageNum, int pageSize, string orderCol, bool asc)
		{
			int categoryId = 1;

			//Create the query
			SelectQuery query = new SelectQuery();
			query.Columns.Add(new SelectColumn("*"));
			query.FromClause.BaseTable = FromTerm.Table("products");
			query.OrderByTerms.Add(new OrderByTerm(orderCol, (asc) ? OrderByDirection.Ascending : OrderByDirection.Descending));

			//Set filter
			query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(categoryId), SqlExpression.Field("productId"), CompareOperator.Equal));

			//Prepare renderer and SqlCommand
			SqlServerRenderer renderer = new SqlServerRenderer();
			SqlCommand command = new SqlCommand();
			command.Connection = connection;
			command.Parameters.Add("@param1", categoryId);

			//Render & Execute
			command.CommandText = renderer.RenderRowCount(query);
			int rowCount = (int)command.ExecuteScalar();
			command.CommandText = renderer.RenderPage(pageNum, pageSize, rowCount, query);
			IDataReader data = command.ExecuteReader();
			
			data.Close();
		}

	}
}