SubSonic Paging Syntax Help

Question

I am struggling to get a subsonic select query to work, I am writing a paging method and tried the following

Select ns = new Select(maincolumns.ToArray());
ns.PageSize = 10;   ** Error Here **
ns.PageIndex = 1;   ** And Error Here **
ns.Where("IsLive").IsEqualTo(true);
ns.And("Title").Like("%" + SearchTerm + "%");
ns.OrderAsc("RentalExVat");
return ns.ExecuteDataSet().Tables[0];

Now it doesn't recognise ns.PageSize Or ns.PageIndex, the rest of the query works fine?? I see I need to use the new 'Query' tool to be able to use these two, but I can't figure out the Query syntax??

any syntax help appreciated

Answer 1

SubSonic's query syntax is 'fluent', so in your code sample, the Where clause is not be applied to your query. This snippet may work better:

Select ns = new Select(maincolumns.ToArray());
ns = ns.Where("IsLive").IsEqualTo(true)
       .And("Title").Like("%" + SearchTerm + "%")
       .OrderAsc("RentalExVat")
       .Paged(1, 10); // paging is set here
return ns.ExecuteDataSet().Tables[0];

Also, make sure your 'SearchTerm' has been SQL escaped (or use an alternate calling pattern) or you are vulnerable to SQL injection.