Wednesday, May 19, 2010

How to auto-register types in Castle Windsor

This is how you can use Castle Windsor's fluent interface to auto-register all the types within one or more assemblies:

private static void RegisterTypes(WindsorContainer container)
{
        var assemblies = GetAssembliesToRegister();
 
        foreach (var assembly in assemblies)
        {
            container.Register(
                    AllTypes.Pick().FromAssemblyNamed(assembly)
                    .If(x => x.IsPublic)
                    .If(x => x.GetInterfaces().Length > 0)
                    .Configure(x => x.LifeStyle.Transient)
                    .WithService.FirstInterface()
                    );
        }
}
private static List GetAssembliesToRegister()
{
        var assemblies = new List();
 
        assemblies.Add("DemoApp.DataAccess");
 assemblies.Add("DemoApp.BusinessLogic");
 
        return assemblies;
}

Sunday, May 9, 2010

Paging in SQL Server 2005/2008

In order to do paging in SQL Server 2005 or later, we need to use a Common Table Expression (CTE). Below is a practical example.

Regular SQL query without paging:


select *
from Person.Contact
order by FirstName, LastName;

Query as a CTE without paging:


with Persons as
(
 select *
 from Person.Contact
)
select *
from Persons
order by FirstName, LastName;

Query as a CTE with paging, retrieving row number 41 to 60:


with PagedPersons as
(
 select *,
  row_number() over (order by FirstName, LastName) as RowNumber
 from Person.Contact
)
select *
from PagedPersons
where RowNumber between 41 and 60
order by FirstName, LastName;

Convert between PageNumber/RowsPerPage and FirstRow/LastRow:


declare @PageNumber int
declare @RowsPerPage int
declare @FirstRow int
declare @LastRow int

set @PageNumber = 3
set @RowsPerPage = 20

set @LastRow = @PageNumber * @RowsPerPage
set @FirstRow = @LastRow - @RowsPerPage + 1;

with PagedPersons as
(
 select *,
  row_number() over (order by FirstName, LastName) as RowNumber
 from Person.Contact
)
select *
from PagedPersons
where RowNumber between @FirstRow and @LastRow
order by FirstName, LastName;