SQL server Pagination

Hi Readers

This post is about getting sql server results in a paginated manner,

Lets see the requirement
Simply when some one is requesting for a list of data you dont want to retrieve all records from the database and show it to the end user, this will affect the performance of the application and the user will be getting thousands on rows in a single request, think of the network traffic that can make.
So the solution will be getting only a subset of data, that is by pages. Show 1st 10 records of data then show the next 10.

This is easy to do when it you need to show the data that is already ordered by the primary key. you can use it to get the set of records easily, but what if its a dataset that you are showing for a search result, then the order will not be there.

Let see a way to achieve this.
The datasource used for this example is from the Adventure works database.
 We are using the product table in production schema.

Lets take the first 10 rows
ProductID    Name
1        Adjustable Race
2        Bearing Ball
3        BB Ball Bearing
4        Headset Ball Bearings
316    Blade
317    LL Crankarm
318    ML Crankarm
319    HL Crankarm
320    Chainring Bolts
321    Chainring Nut

There are two ways to achieve this
1st way is to use the offset keyword (Available from SQL server 2012)
2nd way is to use the row number and get results

Method 1
Offset - Fetch Method Introduced with SQL Server 2012
This method used the new clause that is offset and fetch
Here we'll have to first use the order by to order the results and fetch the records we need.

SELECT  [ProductID]
      ,[Name]
FROM [AdventureWorks2012].[Production].[Product]
ORDER BY Name
OFFSET N ROWS
FETCH NEXT M ROWS ONLY

This code skips the first 'N' number of rows and returns the next 'M' number of rows.You can use this for pagination by setting the N to the count that you have already read and the items per page as M.

Method 2
Ranking the results
This method simply ranks the results and you can use the rank to get the result set you want using a where clause.

SELECT T.*
FROM
(
    SELECT *,ROW_NUMBER() OVER (ORDER BY NAME DESC) AS [rank]
    FROM [AdventureWorks2012].[Production].[Product]
) AS T
WHERE T.[rank]>((@PageNo*@Count)-@Count) and T.[rank]<(@PageNo*@Count)+1

Here the @PageNo is the number of the page you need details for and the @Count is the number of items per page.

These are simple techniques that i used in one of my projects.

Happy Coding :)
-Guruparan-

Syncing tables using Merge in SQL Server

Hi Readers,

In this post I'll explain how to use the 'Merge' statement in sql server to Sync two tables.
For this we'll need two tables, one will act as the source and the other one will act as the destination.

First create the required tables

CREATE TABLE SourceTable
(
 ID INT IDENTITY PRIMARY KEY,
 Name VARCHAR(100)
);

CREATE TABLE DestinationTable
(
 ID INT PRIMARY KEY,
 Name VARCHAR(100)
);

Then insert some data to the source table

INSERT INTO SourceTable VALUES('George');
INSERT INTO SourceTable VALUES('Saman');
INSERT INTO SourceTable VALUES('Kamal');
INSERT INTO SourceTable VALUES('Nimal');
INSERT INTO SourceTable VALUES('Sunil');
INSERT INTO SourceTable VALUES('Mark');
INSERT INTO SourceTable VALUES('Bill');
INSERT INTO SourceTable VALUES('Paul');
INSERT INTO SourceTable VALUES('Sean');
INSERT INTO SourceTable VALUES('Shane');
INSERT INTO SourceTable VALUES('Elvis');

Now we'll come to the Merge Statement

MERGE DestinationTable D
USING SourceTable S
ON D.ID=S.ID 
WHEN MATCHED AND D.Name!=S.Name THEN UPDATE SET D.Name=S.Name
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT(ID,Name) VALUES(S.ID,S.Name);

As this is the first time , all the rows will be inserted
So we'll do some changes to the table (insert, update , delete)

DELETE FROM SourceTable WHERE Name='Bill';
UPDATE SourceTable SET Name='Mark2' WHERE Name= 'Mark';
INSERT INTO SourceTable VALUES('Alex');

Now if we run the merge statement again we can see that both the tables are synced.
You can schedule it to a job so that the tables will be in sync.

Google maps - locations live update from SQL Server

Hi Readers This post is about showing the locations (Longitude and Latitude) values stored in a SQL server database in a google map. this will automatically update the location lively. For this I'm using a ASP .Net website . Languages used will be C# Linq and javascript. First we'll create the database

--Create Database
CREATE DATABASE MapSample
USE MapSample

--Create Table
CREATE TABLE Locations
(
 ID INT PRIMARY KEY IDENTITY(1,1),
 Longitude FLOAT,
 Latitude FLOAT,
 Bounce INT,
 [Description] VARCHAR(50)
);

Here the bounce column is to make a marker bounce and the description column is to show the message when the marker is clicked.
Now lets see the javascript explanation

        //The array to hold the markers this will be used to clear the markers for every refresh
        var markersArray = [];

        //The map object
        var map;
        //Longitude of the center point
        var longt = 80.727539;
        //Latitude of the center point
        var lat = 7.373362;

        //Make the update method run once in every 10 seconds
        self.setInterval("updateMap()", 10000);

        //initialize the google map
        function initialize() {
            //Options for the map object
            var myOptions = {
                zoom: 8,
                center: new google.maps.LatLng(lat, longt),
                mapTypeId: google.maps.MapTypeId.ROADMAP
            };

            //create the map object
            map = new google.maps.Map(document.getElementById('map_canvas'),myOptions);
        }

        //method to clear the markers in the map
        function clearOverlays() {
            if (markersArray) {
                for (var i = 0; i < markersArray.length; i++) {
                    //remove the marker from the map
                    markersArray[i].setMap(null);
                }
                //set the marker array as null
                markersArray = [];
            }
        }

        //show the map in the web page
        google.maps.event.addDomListener(window,  load , initialize);

        //The update method that is used to insert markers to the map
        function updateMap() {

            //clear the markers that are currently in the map so that new once can be added
            clearOverlays();

            //loop to set the markers
            for (var i = 0; i < lonarray.length; i++) {

                //get the longtitude from the longtitude array this array is set from the C# code 
                longt = lonarray[i];
                //get the latitude from the latitude array this array is set from the C# code
                lat = latarray[i];
                //set a title
                var title =  test ;

                //create the location object
                var location = new google.maps.LatLng(lat, longt);
                //create the marker
                var marker1 = new google.maps.Marker({ position: location, title: title });
                //set whether the marker should bounce or not
                if (bounce[i] == 1) {
                    marker1.setAnimation(google.maps.Animation.BOUNCE);
                }

                //set the information of the overlay which will be displayed when marker is clicked
                var infoContent = infoarray[i];
                setContent(marker1, infoContent);

                //add the marker to the marker array so that it can be cleared
                markersArray.push(marker1);

                //add the marker to the map
                marker1.setMap(map);

            }
            //clear the array contents so that new locations can be loaded
            infoarray = [];
            latarray = [];
            lonarray = [];
            emergencies = [];
        }

        //the method used to set the onclick method and info window
        function setContent(marker,message) {

            var infowindow = new google.maps.InfoWindow({
                content: message
            });

            google.maps.event.addListener(marker,  click , function() {
                infowindow.open(map, marker);
            });
        }

Now we'll go to the C# code that will be used to set the array contents
Here i'm using an update panel and timer control which refreshes it every 10 seconds (Please download and see the HTML part to see the code)
This will be the code behind file

 protected void UpdatePanel1_Load(object sender, EventArgs e)
        {
            //Linq is used to load the table to the code
            DataClassesDataContext data = new DataClassesDataContext();

            //Select all from the table
            List lst = (from u in data.Locations select u).ToList();

            //add the table contents to the javascript array so that new locations will be loaded
            foreach (Location item in lst)
            {
                ScriptManager.RegisterArrayDeclaration(UpdatePanel1, "infoarray", "'"+item.Description.ToString()+"'");
                ScriptManager.RegisterArrayDeclaration(UpdatePanel1, "lonarray", item.Longitude.ToString());
                ScriptManager.RegisterArrayDeclaration(UpdatePanel1, "latarray", item.Latitude.ToString());
                ScriptManager.RegisterArrayDeclaration(UpdatePanel1, "bounce", item.Bounce.ToString()); 
            }
        }

        protected void Timer1_Tick(object sender, EventArgs e)
        {
            //update the update panel every 10 seconds
            UpdatePanel1.Update();
        }

You can download the source files HERE
Hope this is useful
Happy Coding.