Implementing Custom Paging, Sorting and Dynamic query in sql

This post explains and demonstrates how to implement following things

1. Custom Paging: Returning a set of records for the given page number and page size
   
2. Dynamic query: Querying based on the where string passed thru parameter
   
3. Dynamic sorting: Sorting based on the sort expression passed thru parameter
   
4. SP_ExecuteSql: Executing sql statements included within parameters
   
5. Common Table Expression: Similar to table variable
6. function row_number(): returns the current row number in a result set

The following code is to create the stored procedure GetCustomersByPage. This stored procedure accepts the parameters page size, page number to return the page, where string (It must start with 'and' keyword if a condition is added. Ex: " and CompanyName like '%soft%' " or empty is accepted but applies no filter), sort expression to get the sorted records and total count which is useful when implementing the paging logic in user interface.

The sample sproc is written for NorthWind database. If you have NorthWind database installed then you can run this sproc and see how it works.

create procedure GetCustomersByPage
@PageSize int,
@PageNumber int,
@WhereString varchar(max),
@SortExpression varchar(200),
@TotalCount int output
as
begin

declare @FirstRecord int
declare @LastRecord int


set @FirstRecord = (((@PageNumber - 1) * @PageSize) + 1)
set @LastRecord = (@PageNumber * @PageSize)

declare @sql nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition=N'@TotalCount int OUTPUT'

set @sql = 'select @TotalCount = count(*) from dbo.Customers where 1=1 '+@WhereString+';
with PagedCustomers as (select row_number() over(order by '+@SortExpression+') as RowNumber, * from dbo.Customers where 1=1 '+@WhereString+')
SELECT * from PagedCustomers where RowNumber between '+cast(@FirstRecord as varchar)+'and '+cast(@LastRecord as varchar)

exec sp_executesql @sql, @ParmDefinition, @TotalCount=@TotalCount output

return @TotalCount
end

Return multiple result sets using IMultipleResults in linq c#

As we know that LINQ introduced a new feature which lets us to get multiple result sets from the stored procedure. But there is one important which all should know about IMultipleResults. The order of the GetResult's should be same as the order of the select statements in the sproc. This must be followed in order to avoid unexpected errors. I spent many hours on this to get rid of this problem.

For better understanding please go through the following

The following tsql code shows how the spoc which returns multiple result sets look like

CREATE PROCEDURE GetMultipleResults
@SomeID int
AS
BEGIN
SELECT * FROM SomeTable1 where SomeColumn1=@SomeID
SELECT * FROM SomeTable2 where SomeColumn2=@SomeID
SELECT * FROM SomeTable3 where SomeColumn3=@SomeID
SELECT * FROM SomeTable4 where SomeColumn4=@SomeID
END

The below code shows how to get the IMultipleResult object by calling the above sproc using LINQ

[Function(Name = "dbo.SPROCName")]
[ResultType(typeof(ResultSet1))]
[ResultType(typeof(ResultSet2))]
[ResultType(typeof(ResultSet3))]
[ResultType(typeof(ResultSet4))]
public IMultipleResults SomeMethod([Parameter(DbType = "INT")] int? SomeID
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), SomeID);
return ((IMultipleResults)(result.ReturnValue));
}


The below code shows how to read the result set from the object of IMultipleResult type

public void SomeOtherMethod(int SomeID)
{
DataContext1 context = new DataContext1 (dbConnString);
IMultipleResults results = context.SomeMethod(SomeID);

ResultSet1 resultSet1= results.GetResult().FirstOrDefault();
IEnumerable resultSet2 = results.GetResult();
IEnumerable resultSet3 = results.GetResult();
ResultSet4 resultSet4= results.GetResult().FirstOrDefault();
}

If you observe the above code you can notice that the method is reading individual result sets from the IMultipleResult object in the same sequence as the sproc returns the results. This is very important that the order of the IMultipleResults.GetResult() statements for the result sets should be same as the order of the select statements in sproc. If the order is different then you will get runtime errors or invalid objects and many more issues.

Exception handling in Sql server 2000

When I was working on a project, I had written a sproc which runs in sql server 2005. I had used Try Catch blocks to handle the exceptions and roll back the transaction for any exception. My script was working fine with sql-2005. But when I had to deploy on production, I noticed that we had sql server 2000 on which our legacy database was sitting. When I ran my script it was failing to create the sproc due to try catch blocks. After doing a while research on it I found that sql server 2000 deals with exceptions in different way.

When any exception is raised then @@ERROR will be set to a non zero value. If you want to check whether exception has araised you should check

@@ERROR <>0 -> Exception occurred

@@ERROR =0 -> No Exception

To handle exceptions in sql server 2000 we need to check the condition @@ERROR <>0 for every insert/update/delete statement and based on that we need to roll back the transaction if used.

Example:

BEGIN TRAN

DELETE EMP WHERE EmployeeType = ‘ProjectManager’

IF (@@ERROR <>0)

GOTO HandleError;

UPDATE EMP SET Salary = Salary * 1.30, EmployeeType = ‘ProjectManager’ WHERE EmployeeType = ‘TeamLead’

IF (@@ERROR <>0)

GOTO HandleError;

COMMIT TRANS

RETURN;

HandleError:

ROLLBACK TRAN

RETURN;