Simple.Data Ranges

In this blog I am going to show you how you can use FindAll using ranges that makes use of the BETWEEN operator. The BETWEEN operator (in SQL) is used in a WHERE clause to select a range of data between two values.

Lets look at finding users by Id:-

var db = Database.OpenNamedConnection("dbConnection");
var list = db.User.FindAllById(10002.to(10005));

foreach (var item in list)
  Console.WriteLine(string.Concat(item.Id, " ", item.Username,
    " ", item.Password, " ", item.DateCreated, " ", 
    item.RoleId));

This actually produces the following SQL:-

select
  User.Id,
  User.Username,
  User.Password,
  User.DateCreated,
  User.RoleId
from
  User
WHERE
  User.Id BETWEEN 10002 AND 10005

and returns the following data:-

OK so far so good but what about if we want to get all users that have a RoledId of 1 to 2. This is where the dyanamic features of .net4 and Simple.Data comes alive. All I need to do is change FindAllById to FindAllByRoleId:-

var list = db.User.FindAllByRoleId(1.to(2));

foreach (var item in list)
  Console.WriteLine(string.Concat(item.Id, " ", item.Username,
    " ", item.Password, " ", item.DateCreated, " ", 
    item.RoleId));

The SQL where clause will now be

WHERE User.RoleId BETWEEN 1 AND 2

and returns the following data:-

OK great but what about date ranges?

var list = db.User
  .FindAllByDateCreated("2011-11-20".to("2011-11-22 17:00"));

foreach (var item in list)
  Console.WriteLine(string.Concat(item.Id, " ", item.Username,
    " ", item.Password, " ", item.DateCreated, " ", 
    item.RoleId));

The SQL where clause will now be

WHERE User.DateCreated
  BETWEEN '2011-11-20  00:00:00' 
    AND '2011-11-22  17:00:00'

and returns the following data:-

Great can we also make user of using the BETWEEN operator for strings? you bet:-

var list = db.User.FindAllByUsername("User2".to("User4"));

The SQL where clause will now be

WHERE User.Username BETWEEN 'User2' AND 'User4'

You can also use FindAllBy... range for arrays, this will make use of the IN operator (in SQL) which allows you to specify multiple values in a WHERE clause, more to follow...