Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
 
Add your own
alternative version

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

, 21 Feb 2005
This article presents an alternative solution to paging large datasets using Open Source Sql.Net library.
pagingsqlnet_sampleproject.zip
pagingSqlNet_sampleProject
bin
App.ico
PagingSampleProject.csproj.user
Reeb.SqlOM.dll
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();
		}

	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Alon_Catz

United States United States
No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411019.1 | Last Updated 21 Feb 2005
Article Copyright 2005 by Alon_Catz
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid