Solving development problems  |  About this blog

This Transact-SQL Store Procedure allows you to do paging on your SQL table by providing only two parameters:

• Current page
• Record to display per page

It returns chosen records and also total count as output parameter if you need to display this information or calculate how many pages you have in paging control.

CREATE PROCEDURE [dbo].[YourProcedureName]
  @CurrentPage int,
  @RecordsPerPage int,
  @Count int OUTPUT
AS
BEGIN
  -- The number of rows affected by the different commands
  -- does not interest the application, so turn NOCOUNT ON
  SET NOCOUNT ON

  -- Determine the first record and last record
  DECLARE @FirstRecord int, @LastRecord int

  SELECT @FirstRecord = (@CurrentPage - 1) * @RecordsPerPage
  SELECT @LastRecord = (@CurrentPage * @RecordsPerPage + 1)
  SET @Count = (SELECT COUNT(*) FROM YourTable);

  WITH TempResult as
  (
    SELECT	ROW_NUMBER() OVER(ORDER BY Field1 DESC) as RowNumber,
            Field1,
            Field2
    FROM    YourTable
  )
  SELECT  TOP (@LastRecord - 1) *
  FROM    TempResult
  WHERE   RowNumber > @FirstRecord AND
          RowNumber < @LastRecord

  -- Turn NOCOUNT back OFF
  SET NOCOUNT OFF
END

Classic LINQ Max and Min functions have the same problem as First function – exception can occur, so we use FirstOrDefault which returns null instead of exception.

So, this is how you can extend Min and Max functions into MinOrDefault and MaxOrDefault;

public static class IEnumerableExtensions
{
  public static int MinOrDefault(this IEnumerable source,
    Func selector, int defaultValue)
  {
    if (source.Any())
      return source.Min(selector);

    return defaultValue;
  }

  public static int MaxOrDefault(this IEnumerable source,
    Func selector, int defaultValue)
  {
    if (source.Any())
      return source.Max(selector);

    return defaultValue;
  }
}

Usage example:

  YourDataContext db = new YourDataContext();
  int maxVisits = db.SomeEntityTables.MaxOrDefault(x => x.SomeFieldYouSearchForMaximum, 0);

Typical scenario: you got your list of items from database but you want to remove some items from it (items that match additional criterion).

It is simple as this (i.e. assuming that your list is named yourItems):

yourItems.RemoveAll(x => x.ItemPropertyId != null);

Random Sort

Consider the below Person class:

public class Person
{
    public int Id
    {
        get;
        set;
    }
    public string Name
    {
        get;
        set;
    }
}

This is how you can randomly sort the List object:

List list = new List();

list.Add(new Person { Id = 1, Name = "Davolio Nancy" });
list.Add(new Person { Id = 2, Name = "Fuller Andrew" });
list.Add(new Person { Id = 3, Name = "Leverling Janet" });
list.Add(new Person { Id = 4, Name = "Peacock Margaret" });
list.Add(new Person { Id = 5, Name = "Buchanan Steven" });
list.Add(new Person { Id = 6, Name = "Suyama Michael" });
list.Add(new Person { Id = 7, Name = "King Robert" });
list.Add(new Person { Id = 8, Name = "Callahan Laura" });
list.Add(new Person { Id = 9, Name = "Dodsworth Anne" });

list = list.OrderBy(x => Guid.NewGuid()).ToList();

Or you can use this approach:

Random random = new Random();
list = (from x in list
       let r = random.Next()
       orderby r
       select x).ToList();

Linq allows to handle data easily. Linq to SQL tools are integrated in Visual Studio but is limited to can generate code only for  certain databases. To create ORM from .sdf database file read on…

What to do?
Copy next script to a file with .bat extension.

Change DATABASE_NAME, DATABASE_PASSWORD and NAMESPACE according to your needs.

@echo off
rem Input parameters
rem DATABASE_NAME should NOT contain spaces
set DATABASE_NAME=MyDatabase
set DATABASE_PASSWORD=mypass
set NAMESPACE=MyNamespace.Database

rem Set full path to SqlMetal.exe
rem For 64-bit Windows
set SQLMETAL="%ProgramW6432%\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe"
rem For 32-bit Windows
if "%ProgramW6432%"=="" set SQLMETAL="%ProgramFiles%\Microsoft SDKs\Windows\v6.0A\Bin\SqlMetal.exe"

rem Other parameters
set DATABASE_PATH="%CD%\%DATABASE_NAME%.sdf"
set OUTPUT_CS_PATH="%CD%\%DATABASE_NAME%.cs"
set OUTPUT_DBML_PATH="%CD%\%DATABASE_NAME%.dbml"
set CONNECTION_STRING="Data Source=\"%DATABASE_PATH%\"; Password=\"%DATABASE_PASSWORD%\";"

rem Check and run sqlmetal
if not exist %SQLMETAL% goto error-missing-sqlmetal
%SQLMETAL% /conn:%CONNECTION_STRING% /dbml:%OUTPUT_DBML_PATH% /namespace:%NAMESPACE% /context:%DATABASE_NAME%DataContext /pluralize
%SQLMETAL% /conn:%CONNECTION_STRING% /code:%OUTPUT_CS_PATH% /namespace:%NAMESPACE% /context:%DATABASE_NAME%DataContext /pluralize
goto end

:error-missing-sqlmetal
echo Error: cannot find sqlmetal.exe
echo Search location: %SQLMETAL%
goto end

:end

Example

If the database is stored in C:\db\Website.sdf then copy script into C:\db\make.bat. Change DATABASE_NAME to Website and run the script. Script should generate C:\db\Website.cs and C:\db\Website.dbml files. Include the generated .cs file into your project.

Using the generated code

class Program
{
  static void Main()
  {
    //Suppose that Website.sdf has Page table with CreationDate column of type DateTime
    WebsiteDataContext db = new WebsiteDataContext(@"C:\db\Website.sdf");
    var list = from p in db.Pages
                 where p.CreationDate > DateTime.Today
                 selet p;

    //Show number of pages that match Linq query
    Console.WriteLine(list.Count() + " pages created today.");
  }
}