Paging Records Using SQL Server 2005 Database - ROW_NUMBER Function

SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications.  ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.

If I want the first page of 10 records from my log file sorted by Date DESC, I can use the ROW_NUMBER FUNCTION as follows:

SELECT  Description, Date
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 1 AND Row <= 10

The second page of 10 records would then be as follows:

SELECT  Description, Date
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 11 AND Row <= 20

If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20:

SELECT  Description, Date
FROM     (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC)
             AS Row, Description, Date FROM LOG)
            AS LogWithRowNumbers
WHERE  Row >= 11 AND Row <= 20

We can rap this up in a Stored Procedure as follows:

CREATE PROCEDURE dbo.ShowLog
    @PageIndex INT, 
    @PageSize INT 
AS

BEGIN 

WITH LogEntries AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description 
FROM LOG)

SELECT Date, Description
FROM LogEntries 
WHERE Row between 
(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize


END 

It is only available in SQL Server 2005, but it is a heck of a lot easier and more intuitive than creating temp tables and using other stored procedures that I have used in the past.  However, if you want to target your application for SQL Server 2000 use, I would stick with a record paging solution that works for both SQL Server 2005 and SQL Server 2000 Databases.

Have a nice day... 'N happy Coding :)

Increase Performance in asp.net application

For every enterprise level application the key to make that application success is the responsiveness of application. ASP.NET also offers great deal of the features for developing web based enterprise application but some times due to avoiding best practice to write application the performance of application performance of application is not so fast as it should be. Here are the some use full suggestion to make your application super fast.

1. Always set debug=”false” in web.config production environment.
2. Always set trace=”false” in web.config production environment
3. If you are using asp.net 2.0 or higher version then always use precompiled version of your code and also prefer web application project over website. If you are using website then always publish it and then upload that precompiled version of your site in production environment.
4. Always compile your project in Release Mode before uploading application to production environment.
5. Decrease your html kb as much as you can for that use tables less html using div’s and if possible then do not give big name to your control it will increase your html kb as asp.net uses client Id to differentiate all the controls. If you are creating custom controls then you can overwrite your clientid and uniqueId.
6. Use cache api as much as possible it will decrease your server roundtrip and boost application performance. ASP.NET 2.0 or higher version provides functionality called sqlcachedependancy for your database caching. It will validate cache with your database operation like insert,update and delete and if not possible with it then use the file base caching.
7. Remove blank spaces from your html it will increase your kb. You can use regular expression to remove white spaces. I will post the code for removing white spaces next posts.
8. For asp.net 2.0 and higher version use master pages. It will increase your performance.
9. Prefer database reader over dataset unless and until you have specific reason to use database.
10. Use ADO.NET asynchronous calls for ado.net methods. asp.net 2.0 or higher version is supporting your performance. If you are using same procedure or command multiple time then use ADO.NET Prepare command it will increase your performance.
11. Do IIS performance tuning as per your requirement.
12. Disable view state for your controls if possible. If you are using asp.net 2.0 or higher version then use asp.net control state instead of view state. Store view state in session or database by overriding the default methods for storing view state.
13. User Server.Transfer instead of response.redirect.
14. Always use inproc session state if possible.
15. Use Ajax for your application wisely. Lots of Ajax calls for a page will also decrease your performance.
16. Measure your application performance with tools like redgate profiler,firebug and whyslovw from yahoo.
17. User System.Text.StringBuilder for string concatenation its 4 times more faster then the normal strings.
18. Right JavaScript in .Js files and place it as bottom of the application.
19. Use Separate CSS files for styling of application.
20. User database paging over normal paging while displaying huge amount of data.
21. Call web service from java script instead of server side. Use asynchronous calls to call a web method from web service.

Have a nice day... 'N happy Coding :)

Difference between int.Parse and Convert.ToInt32

Both int.Parse and Convert.ToInt32 are used to convert string into the integer but Only difference between them is to Convert.ToInt32 handle null and returns ‘0’ as output and int.parse is not going to handle NULL and will give a Argument Null Exception. Here is the example for that both are almost same except handling null.

        string convertToInt = "12";
        string nullString = null;
        string maxValue = "32222222222222222222222222222222222";
        string formatException = "12.32";

        int parseResult;

        // It will perfectly convert interger
        parseResult = int.Parse(convertToInt);

        // It will raise Argument Null Exception
        parseResult = int.Parse(nullString);

        //It willl raise Over Flow Exception
        parseResult = int.Parse(maxValue);

        //It will raise Format Exception
        parseResult = int.Parse(formatException);


        //For Convert.ToInt32

        //It will perfectly convert integer
        parseResult = Convert.ToInt32(convertToInt);

        //It will ouput as 0 if Null string is there
        parseResult = Convert.ToInt32(nullString);

        //It will raise Over Flow Exception
        parseResult = Convert.ToInt32(maxValue);

        //It will raise Format Exception
        parseResult = Convert.ToInt32(formatException);

Hope this will help you understand the better but still there is third option available called int.TryParse which can handle all kind of exception and return result as Output Parameter.

Have a nice day... 'N happy Coding :)

Check Database connection using C# or VB.NET

As a programmer, one of the most common tasks in your application is to connect to the database. Here’s a very simple way to check your database connection before you start any task or use this as a service to check the connection and pause tasks if the connection breaks.

C#

private static bool DBConnectionStatus()
{
    try
    {
        using (SqlConnection sqlConn =
            new SqlConnection("YourConnectionString"))
            {
                sqlConn.Open();
                return (sqlConn.State == ConnectionState.Open);
            }
    }
    catch (SqlException)
    {
        return false;
    }
    catch (Exception)
    {
        return false;
    }
}

VB.NET (Converted Code)

Private Function DBConnectionStatus() As Boolean
    Try
        Using sqlConn As New SqlConnection("YourConnectionString")
                sqlConn.Open()
                Return (sqlConn.State = ConnectionState.Open)
        End Using
    Catch e1 As SqlException
        Return False
    Catch e2 As Exception
        Return False
    End Try
End Function

The code above opens a connection to the database and returns a boolean depending on the database status.

Have a nice day... 'N happy Coding :)

Advantages and Disadvantages in Output Caching in asp.net

Recently one of my friend ask about output cache so I decided to put a post about how output cache works and what is the advantages and disadvantage of using output cache. Output cache is a technique to cache generated response from the asp.net pages or controls. Output Caching increases the performance drastically by reducing server round trips. We can use @OutputCache directive to controls output caching for a page or controls.

The @OutputCache includes following attributes.

• Duration: This attribute will explain how long output cache will be there for a page or control. It can be set in seconds. If you set 60 then it will not going to generate response from server until 60 second It will generate response from the cache it self. Here is example of duration where it will set 60 second for page.

<%@ OutputCache Duration="60" %>  

• VaryByParam: This attribute will determine cache entries based on get or post parameters. It will vary cache based on get or post parameters suppose you set product Id query string as VaryByParam it will create a different cache based on product Id. Following is a example how you can set the VaryByParam based on Product Id. 

<%@ OutputCache Duration="Seconds" VaryByParam="ProductId"%>

• Location: This attribute will specify where the Item will be cached. Here are options available for that.
  
  • Any: The output cache can be located at any browser from where request is generated or Server where request is processed or Proxy server participating in request.
  • Client: The output cache will be located on browser client from where request is generated.
  • Downstream: The output cache can be stored in any HTTP 1.1 cache-capable devices other than the origin server. This includes proxy servers and the client that made the request.
  • Server: The output cache will stored in the server where generated request will be processed.
  • ServerAndClient: The output cache will generated either on Browser where request generated or on server where generated request will be processed. Proxy servers are not allowed for this.
  • None: None specifies that output cache will be disabled for this controls or Page.

Here is example of location.

<%@ outputcache duration="10"  Location="Server" %>

• VaryByCustom: This attribute is for different browsers where request is generated this means it will generate new instance of cache based on different browser versions.

<%@ OutputCache Duration="Seconds" VaryByCustom="Browser" %> 

• VaryByHeader: This attribute allows to determine different instances of cache based on the headers. Here is example for it.

<%@ OutputCache VaryByHeader="Accept-Language" %> 

Note: If you specify the output cache it will not fire server side events like click or selected index changed etc. So make sure the controls that you used in output cache will not have this kind of controls Or you have to handle this in other scenarios.

Have a nice day... 'N happy Coding :)

Difference between sliding expiration and absolute expiration in ASP.NET

ASP.net cache is great feature through which we can increase the performance of the our web application via reducing server round trips to database. We can cache any serializable data into the cache. There are so many ways to cache data but one of the simplest way to cache data like insert data into cache object.

Here we must need to validate cache if any data is changed and there are so many ways from where we can set dependency to validate the cache like files,SQL Cache Dependency etc. We also can validate cache or expire via setting time to duration to its object. Like after the defined time our cache will expire and then it will again put new fresh data into the cache. This is called as time base expiration. We can put this kind of expiration via two way.

1. Absolute Expiration
2. Sliding Expiration

Absolute Expiration

Absolute expiration means It will expire cache after some time period set at the time of activating cache. This will be absolute expiration whether cache will be used or not It will expire the cache. This type of expiration used to cache data which are not frequently changing.

Sliding Expiration

Sliding expiration means It will expire cache after time period at the time of activating cache if any request is not made during this time period. This type of expiration is useful when there are so many data to cache. So It will put those items in the cache which are frequently used in the application. So it will not going to use unnecessary memory.

Here is the example how we can set the sliding expiration and absolute expiration.

string cacheData = "The data to be cached";
//Absolute Expiration
Cache.Insert("AbsoluteCacheKey", cacheData, null,
DateTime.Now.AddMinutes(1), System.Web.Caching.Cache.NoSlidingExpiration);
//Sliding Expiration
Cache.Insert("SlidingExpiration", cacheData, null,
System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(1));

In above example I have created the a string to be cache that can be any serialized data. In the absolute expiration you can see that it will expires after one minute whether its accessed or not. While in sliding expiration it will expire cache if cache is not accessed within specified time.

Have a nice day... 'N happy Coding :)

Rounding Numbers in JavaScript

A very common question while dealing with Math methods in JavaScript is to round numbers. JavaScript provides 3 rounding methods: round(), ceil() and floor()

round() – rounds a number up if decimal part is >= .5, else rounds down

ceil() - rounds a number up to the nearest whole number and removes decimal

floor() – rounds a number down to the nearest whole number and removes decimal

Here’s an example:

<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Math Rounding</title>
</head>
<body>
<script type="text/javascript">

function callRounding(num) {
document.write("Original value: " + num + "</br>");
document.write("Using round(): " + Math.round(num) + "</br>");
document.write("Using ceil(): " + Math.ceil(num) + "</br>");
document.write("Using floor(): " + Math.floor(num) + "</br>");
}

</script>
<input id="btn1" type="button" value="Example 1"
onClick="callRounding(2.12)" />
<input id="btn2" type="button" value="Example 2"
onClick="callRounding(2.62)" />
<input id="btn3" type="button" value="Example 3"
onClick="callRounding(-2.62)" />
</body>
</html>

hope it help u someway's...
Have a nice day... 'N happy Coding :)