Friday, October 22, 2010

Implementing SKIP and TAKE in SQLServer

I wanted to see how far I could push the envelop to implement custom paging using just SQL Server.
Suppose you have a stored procedure that does wonderful things, but returns a lot of data. So you want to page that data but want to make the paging happen as far back in the data retrieval cycle as possible.   So if your data is coming from a SQL Server database, you can apply the pattern described below:

In order to make the paging minimally invasive to already working stored procedures, create a new stored procedure which will return the paged record set and the total number of records.  Here it us:

create proc usp_SkipTake(
 @columnDefs varchar(1000), --the column names and dataTypes in the exact same order as the stored procedure which will be paged. 
  --example: firstName varchar(50), lastname varchar(50)
 @sprocWithInputParams varchar(1000),  --this is the stored procedure which will be paged. 
 @skip int, --the number of records to skip 
 @take int, --the number of records to return
 @orderBy varchar(255)=null   --allows you to specify the sort order of the data. This is the only new feature your old stored procedure will need to implement to fit this design pattern.
 )
as Begin
 declare @p varchar(8000)
 declare @lastRecord varchar(9) 
 set @lastRecord = isnull(@skip,0) + case when isnull(@take,0)=0 then 1000000 else @take end
 set @p = 'create table #tmptbl  (' + @columnDefs  + ', _rowNo int identity)' +
  'insert into #tmptbl exec ' + @sprocWithInputParams + '
  select * from #tmptbl 
   where (_rowNo > ' + convert(varchar(9), @skip) + ' AND _rowNo <= ' + @lastRecord  + ')
  select recordCount=count (_rowNo) from #tmptbl 
  drop table #tmptbl'
 exec(@p)
End

Note that if you supply the @take value of 0, it will default to getting the first million records.  This was an arbitrary number.  I assumed that would be the same as retrieving all records for the vast majority of use cases.

A note about performance: I found that this approach will likely double the amount of time it takes to return data from the database. Coming up next, we will examine how caching can help improve performance.

Tuesday, October 19, 2010

Creating JSON-enabled WCF services in

This is worth a look. Creating JSON-enabled WCF services in .NET 3.5 – an even simpler approach
setting Factory="System.ServiceModel.Activation.WebScriptServiceHostFactory" in your .svc file
I've done this in Framework 4.0 and it's supported there too.

ASP.NET AJAX Toolkit

http://www.asp.net/ajaxlibrary/download.ashx

Android Developer Guide

http://developer.android.com/guide/

Friday, October 15, 2010

GetJar: An alternate Android Marketplace

http://www.getjar.com/

Applying Style-Sheets based on acreen size

<!--for screens less than 480 px-->
<link href="Style1.css" rel="stylesheet" type="text/css" media="screen and (max-width:480px)" />

<!--for screens less between 480 px and 600px-->
    <link href="Style2.css" rel="stylesheet" type="text/css" media="screen and (min-width:480px) and (max-width:600px)" />

<!--for screens greater than 600px-->
    <link href="Style3.css" rel="stylesheet" type="text/css" media="screen and (min-width:600px)" />

More on this here.

I wonder what would happen if the screen size is exactly 480. Does max-width include 480 or does it stop at 480? Will post an update if and when I come across the answer.

Wednesday, October 13, 2010

Getting Querystring Parameter Value from Javascript

function querySt(ji) {
 hu = window.location.search.substring(1);
 gy = hu.split("&");
 for (i=0;i<gy.length;i++) {
  ft = gy[i].split("=");
  if (ft[0] == ji) {
   return ft[1];
  }
 }
}
//if you need to get param value from a URL such as 
//this one: http://domain.com?param=value
var koko = querySt("param");
I found this code at this site.

Tuesday, October 12, 2010

Code Conversion

If you ever have a code snippet in VB.NET but need it to be in C# (or the other way around), you've got to see this site.

I was elated to find this because it saves a lot of time.




Thursday, October 07, 2010

Excellent post on protecting usernames/passwords

When sending the username and password over the wire in clear-text is not an option, and you don't want to use SSL.  Try the excellent suggestion on this site: http://pajhome.org.uk/crypt/md5/index.html