Database Paging and Sorting with CSLA Part 1 - The Database

Part 1 - The Database

I will be writing a multi-part article on how I implemented databse paging and sorting with CSLA business objects for an ASP.NET application.  As a disclaimer, I am sure there are other and potentially better solutions, but I wanted to offer up my solution in hope that it will help others. 

The Problem - When dealing with large amounts of data, it is inefficient to retrieve thounsands of records from a database only to display 10 - 20 records on your UI.  The default paging within a DataGrid does just this.  In addition, each time a user "sorts" a column on a datagrid, all of the records are again being retrieved from the databse.  An alternative to the approach I am using is to use caching within your application to address these issues.

The Solution - I will create a small ASP.NET application, similar to the Project Tracker solution provided with the CSLA.NET framework.  The application I am building is meant to hold "Information", anything from Birthdates to interesting websites, and allows the user to search, sort, add, update, and remove "Information" items.  The homepage will contain a GridView that displays all of the information items contained within the database.  My solution uses a SQL Server 2005 database.  In order to create a layered, Object Oreinted application that is performant, I am using CSLA.Net business objects with Database paging and sorting.

The Database - I am using SQL Server 2005 as my database.  SQL 2005 provides a new function ROW_NUMBER() that along with CTE's provides an easy and performant way to select a subset of data for paging.  The below stored procedure will be called from my application which will pass all of the necessary parameters.  Note that this procedure uses dynamic sql.  You should only use dynamic sql if you fully understand SQL Injection attacks and how to protect your code from them.  In addition, SQL Server will not cache the query execution plan for dynamic sql, so there is a performance trade off.  In this case, being able to limit the nunber of records returned to the middle tier will be worthwhile once your table becomes large with many records.  Also, using EXEC sp_executesql will allow the query plan to be cached, but in my case, since I am also sorting, I would not be able to paramaterize the order by using sp_executesql. 

What is next...
In my next article I will explain what needs to be done within your CSLA business objects to call this stored procedure.  My final article will expain how to hook this functionality up to a GridView in an ASP.Net page.

CREATE PROCEDURE [dbo].[GetInfoList]


@PageIndex int,

@NumberOfRecords int,

@Sort varchar(200)





DECLARE @Sql varchar(5000);

SELECT @Sql = ' SELECT InfoList.InfoId, InfoList.Subject, InfoList.CategoryName,

InfoList.Description, InfoList.LastModified FROM


SELECT i.InfoId, i.Subject, c.CategoryName,

SUBSTRING(i.Description,0,30) Description, i.LastModified,

+ @Sort + ') AS RowNum

FROM dbo.Info i

JOIN dbo.Category c on c.CategoryId = i.CategoryId ) InfoList '


SELECT @Sql = @Sql + ' WHERE InfoList.RowNum BETWEEN

( ' + CAST(@PageIndex AS varchar(10)) + ' * ' + CAST(@NumberOfRecords AS varchar(10)) + ' + 1 ) AND (( '

+ CAST(@PageIndex AS varchar(10)) + ' + 1 ) * ' + CAST(@NumberOfRecords AS varchar(10)) + ' ) '

SELECT @Sql = @Sql + ' ORDER BY ' + @Sort;


--Return total number of records for paging

SELECT COUNT(*) TotalRowCount FROM dbo.Info;


Unable to build CSLA.NET

The below error occurred when trying to build the 2.1.4 solution with visual studio 2005 on a machine that also had orcas installed:

"The application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem"

I had previously downloaded the Orcas beta and installed on the same machine.  I ended up having to uninstall Orcas, then uninstall visual studio 2005, and re-install VS 2005.  After the re-install I was then able to build the csla solution.


If you recently downloaded the latest ajax toolkit (1.0.10606.0) from Microsoft, you may receive the below error:

 System.Configuration.ConfigurationErrorsException: Could not load file or assembly VsWebSite.Interop

 I was able to eliminate the error by removing the below line from the web.config:

<add assembly="VsWebSite.Interop, Version=, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies>


I have been considering using Rockford Lhotka's CSLA framework for an upcoming ASP.NET project.  I was curious if many people are using this framework and what their experiences have been?

SQL Server 2005 64 bit Linked Server Issue

If you get the below error when trying to create a linked server from a 2005 64 bit instance to a 2000 32 bit instance, you can try the follwoing steps to correct:

  • Execute Instcat.sql on the 2000 instance, which is part of the latest SQL Server 2000 SP 4
  • Change your linked server syntax from servername.db.schema.table to SELECT * FROM OPENQUERY(servername, "...")
One of the causes of this error is a missing stored procedure that the 64 bit instance is looking for.

OLE DB provider "SQLNCLI" for linked server "servername" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "servername" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "servername". The provider supports the interface, but returns a failure code when it is used.