Paging in MS SQL server. (An alternative to Limit in MySQL)

  If you are a .NET developer , then you might know the paging capabilities of ASP.NET data 

controls like Gridview. But the main problem associated with this paging is that , each time a 

new page is requested all the records are transferred from the data server. If the data server 

and your application is in different physical locations, then it causes a performance dropdown.

So it will be always better if we can do the paging in SQL server. In MySQL this is 

straight forward and needs no complex queries.

SELECT * FROM tablename ORDER BY key LIMIT 10 OFFSET 100 .. simple.

But in MS SQL the keyword Limits or its alternative is not available.

So we need to find our own solution for this.

LIMIT in MSSQL - solution using ROW_NUMBER()

ROW_NUMBER()  Returns the sequential number of a row in a result set, starting at 1 for the first 

row.

 Let's consider an example.

Suppose you have a table.

CREATE TABLE [dbo].[JobDetails](

[ControlID] [int] NOT NULL,

[JobDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Priority] [int] NULL,

[CreatedOn] [datetime] NULL,

 CONSTRAINT [PK_JobDetails] PRIMARY KEY CLUSTERED 

(

[ControlID] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And records..

ALTER TABLE dbo.JobDetails NOCHECK CONSTRAINT ALL

-- Insert new records

INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (1, 

'2008-10-10 00:00:00', 'Job Description 1', 10)

INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (2, 

'2008-11-10 00:00:00', 'Job Description 2', 10)

INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (3, 

'2008-10-10 00:00:00', 'Job Description 3', 11)

INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (4, 

'2008-10-10 00:00:00', 'Job Description 4', 12)

INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (5, 

'2008-10-10 00:00:00', 'Job Description 5', 9)

-- Enable all check on table

ALTER TABLE dbo.JobDetails CHECK CONSTRAINT ALL

Now , I need to include paging for this records. I have to get the job order by Priority and 

CreatedOn Date. So here is the code to do that.

DECLARE @PageNumber int

SET @PageNumber = 1

DECLARE @PageSize int

SET @PageSize = 3

SELECT *

FROM

(SELECT ROW_NUMBER() OVER(ORDER BY Priority asc , CreatedOn desc ) AS 

sequenceNum,

*

FROM JobDetails 

-- WHERE ...(you can include where condition here if u need)

)temp

WHERE temp.sequenceNum BETWEEN ((@PageNumber-1)*@PageSize)+1 AND (@PageNumber * 

@PageSize)

Hope this will be useful for you. If you want to see a live implementation of this query, you 

can see the following page in a job site. Click here to view demo

Updating Only Non NULL parameters in Stored Procedure

All the developers who worked on SQL server surely will have encountered the following situation atleast once.
"Update only the columns with non null values in parameter in a row."
I will explain this.
Suppose you have a users table. You are using a single stored procedure to update all the columns in table.
suppose now you need to change only the field 'Name' using this stored procedure. The other parameters will be empty. Then u can use the ISNULL property to update the table.
Here is an example.
CREATE PROC [dbo].[usp_UserDetailsUpdate]
@ControlID int,
@EmailId varchar(255)=NULL,
@LoginId varchar(255)=NULL,
@Name varchar(255)=NULL
AS
UPDATE [dbo].[UserDetails]
SET
[EmailId] = ISNULL(@EmailId, EmailId),
[LoginId] = ISNULL(@LoginId,LoginId),
[Name] = ISNULL(@Name, [Name]
WHERE [ControlID] = @ControlID

StringBuilder Vs String Concatenations

StringBuilder or String Concatenation.. Which is better ?

How they work ?

String is immutable while StringBuilder is mutable.
After a string object is created its value cannot be changed. Then how
name ="jimmy"+"augustine" is possible ? When we assign this , we actually discard the old string object and create a new string.

But StringBuilder is mutable. StringBuilder class provides methods to change its contents at anytime.StringBuilder has a very useful method "append" to add new strings to the end of the existing string. StringBuilder internally reserves a certain amount of memory(Buffer). When a new string is added to the StringBuilder, the new string is just copied to the existing buffer. If the buffer is not enough to fit the new string , then a new buffer is created to fit the new string.

My Performance Test ( StringBuilder VS Concatenation):

Here is a simple performance test which demonstrate the power of stringBuilder over string concatenation.

DateTime start;
start = DateTime.Now;
StringBuilder strBuilder = new StringBuilder();

for (int i = 0; i < strbuilderexecutiontime =" DateTime.Now" builder ="" start =" DateTime.Now;" str =" string.Empty;">for (int i = 0; i <>
{
str = str + i;
}
strBuilderExecutionTime =DateTime.Now - start ;
Console.WriteLine("Exection Time for Concatenation =" + strBuilderExecutionTime.ToString());

Output :

Execution Time for string builder =00:00:00.0312500
Execution Time for Concatenation =00:01:18.5937500


When looking at the output of the code , it is very clear that StringBuilder is much more efficient than the regular concatenation.

Is there any disadvantage for StringBuilder when comparing with String concatenation ?

Is StringBuilder always a better Option than concatenation ? No... Because creating a StringBuilder object using the constructor will take time. So if there is only few concatenations ( say less than 5 apprx) , then no need to go for StringBuilder.


Conclusion:

If the number of concatenations is more , then use StringBuilder. But if the number of concatenations is very few then go for ordinary "+" concatenations.
If the number of concatenations is more than 8, then in all the situations StringBuilder is faster than String concatenations.

Web Config file in ASP.net

How to add a web.config file?

1) Create a new website by selecting File->New->Website from the Menu

Visual Studio 2005 will create a basic website with a default page for you.

2) Click on the Website ->Add New Item from the menu or press Ctrl+Shift+A

A new pop up window will be displayed. Select Web Configuration File from the popUp.

3) Now you can see a Web.config file added to your project.

The default web.config file will look something like this.(I removed all the comments from web.config)

We can now use the web.config file. Now lets store a value in web.config file and read that from our application.

Reading a value from web.config.

1) Open web.config file

2) Add a new key in web.config file under AppSettings.

Now the web.config file will look like this.

3) Now We can read this value from our aspx page.

For reading a value from web.config file, we can use the ConfigurationManager class.

Write the following code in page load event .

String Name = ConfigurationManager.AppSettings.Get("Name");

Response.Write("Value of Name from web.config is : " + Name);

4) Now run your sample website. Done !

Storing a Connection String in web.config.

Storing your connection string in web.config file when you are using visual studion 2005 is very easy.

You can add a new connection string under the tag

Here is a sample web.config.

You can read the connection string using the following code.

String connectionString = ConfigurationManager.ConnectionStrings["TestConnectionString"].ToString();

Response.Write("Value of TestConnectionString from web.config is : " + connectionString);