Making your aspx page IE compatible

We found that in some cases our site is showing some weird UI behavior, It was working fine when we are opening our site on clients local machine with http://localhost but when we use the machine name instead of the localhost like (http://machinename ) we are getting a broken UI page.

When we observer the page in IE developer tool (F12) we found that IE 10 is not able to understand the type of document and because of this we are getting the UI error.

To fix this problem we have just added a meta tag after title tag in our pages so that browser can detect correct Browser mode and document Mode

<meta http-equiv="X-UA-Compatible" content="IE=100" />

This way you can change the browser compatibility mode.

Happy Coding!!

Self Signed Certificate

Following link has described how to create self signed certificate for development/Testing purpose
http://www.robbagby.com/iis/self-signed-certificates-on-iis-7-the-easy-way-and-the-most-effective-way/

SelfCert Exe Download location:
https://skydrive.live.com/?cid=3c8d41bb553e84f5&id=3C8D41BB553E84F5!175&authkey=yeHVTUTVzGE$

javascript don't execute after partial postback

If you are doing partial postback in your page then code on $(document).ready() function will not execute.

You will experience when page loads then your javascript functionality works but after partial postback your code is not working

For resolve this issue you need to write code in following format.

Write general code in SetLocalValues() function which you want to call on $(document).ready() function

var prm = Sys.WebForms.PageRequestManager.getInstance();prm.add_endRequest(function () {//Write your code here//This function executes after partial postback so you need to call $(document).ready() functions code here});
$(document).ready(function () {        SetLocalValues();});
var prm = Sys.WebForms.PageRequestManager.getInstance();prm.add_endRequest(function () {        SetLocalValues();});function SetLocalValues() {//Write your code}

This way i have resolved javascript issue.

Happy Coding!!

String concatenation issue related to NULL values and solution for it

I am writing this blog to avoid issues related to string concatenation.

“+” is used to concatenate strings, prior to 2012. As you can see in the query below, two strings are being concatenated. Unfortunately this query doesn’t return expected results and it returns NULL.

To fix that, select query should be rewritten to replace null.

In Sql Sever 2012, Concat() is clearly a simpler way to concatenate strings. It improves code readability and returns the expected results since it automatically (implicitly) converts null to blank space.

CREATE TABLE #temp (

emp_name nvarchar(200) NOT NULL,

emp_middlename nvarchar(200) NULL,

emp_lastname nvarchar(200) NOT NULL

);

INSERT INTO #temp VALUES( 'Something', NULL, ' US' );

INSERT INTO #temp VALUES( 'Something', NULL, ' India' );

SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result FROM #temp;

DROP TABLE #temp

You can use concat function instead of "+" operator.

Happy Coding!!

Working with ROW_NUMBER, sort expression and sort order without dynamic query

create table #Cricketers(

ID int identity(1,1),

Name nvarchar(255),

Comments nvarchar(255)

)

insert into #Cricketers

select 'David Warner','T20-opening batsman'

union all

select 'R Ashwin','All rounder'

union all

select 'Virender Sehwag','Test-opening batsman'

union all

select 'Rahul Dravid','Former Captain'

union all

select 'MS Dhoni','Captain, Team India'

declare @OrderByDirection NVARCHAR(200)='A'

declare @OrderBy NVARCHAR(200)='Comments'

select *,ROW_NUMBER() OVER (

ORDER BY CASE WHEN @OrderBy = 'Name'

AND @OrderByDirection = 'D'

THEN Name END DESC,

CASE WHEN @OrderBy = 'Name'

AND @OrderByDirection != 'D'

THEN Name END,

CASE WHEN @OrderBy = 'ID'

AND @OrderByDirection = 'D'

THEN ID END DESC,

CASE WHEN @OrderBy = 'ID'

AND @OrderByDirection != 'D'

THEN ID END,

CASE WHEN @OrderBy = 'Comments'

AND @OrderByDirection = 'D'

THEN Comments END DESC,

CASE WHEN @OrderBy = 'Comments'

AND @OrderByDirection != 'D'

THEN Comments END

) AS RowNumber

from #Cricketers

drop table #Cricketers


This way you can write the query. We use CASE.. WHEN to work with multiple conditions.

Happy Coding!!

SQL Parameter sniffing

​SQL Parameter sniffing

For last few days we were having a problem with a few stored procedures that stop functioning for some set of values using SQL Server 2012. The stored procedure work correctly with one set of parameters, while with another set would timeout and fail.

The initial way that we bypassed this problem was that when this circumstance was found, we would alter the stored procedure and this seemed to fix it. As you can imagine it wasn't an ideal situation for an enterprise application because the problem would need to be found and reported before we would even know it existed but we struggled to find any proposed solutions to the problem.

After much investigation it was suggested that this could be caused by parameter sniffing. SQL Server does this by tracking what happens to the parameters passed into the stored procedure when creating the stored procedure and working out the execution plan. So the execution plan generated by SQL server was good most of the time but the plan would contain situations where the procedure would fail.

Below is a stored procedure that allows parameter sniffing. This is not one of the stored procedures that did have the problem, but a stored procedure where parameter sniffing occurs:

ALTER PROCEDURE [dbo].[ReportJobSeekerDetails]

@MarketPlaceID BIGINT,

@RoleName NVARCHAR(20),

@CountryId SMALLINT = NULL,

@StateId BIGINT = NULL,

@UserStatus TINYINT = NULL,

@RegStartDate DATETIME = NULL,

@RegEndDate DATETIME = NULL,

@StartRowNumber INT,

@PageSize INT,

@SortExpression NVARCHAR(100)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

SET NOCOUNT OFF;

IF @PageSize > 100

SET @PageSize = 100

SELECT COUNT(*) 'RowCount'

FROM

aspnet_users au

INNER JOIN aspnet_membership am ON au.userID = am.userid

INNER JOIN aspnet_usersinroles ur ON ur.userID = au.UserId

INNER JOIN UserPublicProfile upp ON upp.UserID = ur.ID

LEFT JOIN Country c ON upp.CountryID = c.Id

LEFT JOIN State s ON upp.StateID = s.ID

WHERE (@CountryId IS NULL OR c.Id = @CountryId)

AND (@StateId IS NULL OR upp.StateId = @StateId)

AND (@UserStatus IS NULL OR am.IsLockedOut = @UserStatus)

AND (@RegStartDate IS NULL OR am.CreateDate > @RegStartDate)

AND (@RegEndDate IS NULL OR am.CreateDate < @RegEndDate)

END

With the following addition of variables it prevents SQL Server from having the ability to perform parameter sniffing:

ALTER PROCEDURE [dbo].[ReportJobSeekerDetails]

@MarketPlaceID BIGINT,

@RoleName NVARCHAR(20),

@CountryId SMALLINT = NULL,

@StateId BIGINT = NULL,

@UserStatus TINYINT = NULL,

@RegStartDate DATETIME = NULL,

@RegEndDate DATETIME = NULL,

@StartRowNumber INT,

@PageSize INT,

@SortExpression NVARCHAR(100)= NULL,

@TotalCount INT OUTPUT

AS

BEGIN

DECLARE @CountryIdLocal SMALLINT = @CountryId

DECLARE @StateIdLocal BIGINT = @StateId

DECLARE @UserStatusLocal TINYINT = @UserStatus

DECLARE @RegStartDateLocal DATETIME = @RegStartDate

DECLARE @RegEndDateLocal DATETIME = @RegEndDate

SELECT @TotalCount = COUNT(au.UserName)

FROM dbo.aspnet_users au

INNER JOIN dbo.aspnet_membership am ON au.userID = am.userid

INNER JOIN dbo.aspnet_usersinroles ur ON ur.userID = au.UserId

INNER JOIN UserPublicProfile upp ON upp.UserID = ur.ID

LEFT JOIN Country c ON upp.CountryID = c.Id

LEFT JOIN State s ON upp.StateID = s.ID

WHERE (@CountryIdLocal IS NULL OR c.Id = @CountryIdLocal)

AND (@StateIdLocal IS NULL OR upp.StateId = @StateIdLocal)

AND (@UserStatusLocal IS NULL OR am.IsLockedOut = @UserStatusLocal)

AND (@RegStartDateLocal IS NULL OR am.CreateDate > @RegStartDateLocal)

AND (@RegEndDateLocal IS NULL OR am.CreateDate < @RegEndDateLocal)

END

By implementing the small change demonstrated above on a couple of troublesome stored procedures, this fixed our problem with our stored procedures randomly not working with certain parameters. This proved that the problem was caused by parameter sniffing and left me wondering how many other people are out there with the same problem.

Happy Coding!!

difference between Response.Redirect(url) and Response.Redirect(url, false)

Avoid using Response.Redirect(url), instead, use Response.Redirect(url, false):

I have faced the issue, In our project generalize redirect code was written and it is called from other places. We just pass the redirect url to that generalize function. At few places there was a code after redirect function call. we don't wanted that code to be executed if redirect is happened and this call to redirect is based on particular condition. I started to check why it is happening then i came to know about the actual working of response.redirect()

 Response.Redirect(url): 

        After writing a 302 redirect response to the response buffers, calls Response.End. This is very expensive. 

Response.Redirect(url, false): 

   It is fast, but unlike Response.Redirect(url), the lines of code which follow the call to Response.Redirect(url, false) will be executed.

Horrors of Response.End:

     what if you don't want the lines of code to execute after you redirect? Well, one way to accomplish this is to call HttpApplication.CompleteRequest(), which is accessible from the HttpContext. e.g., call calling Context.ApplicationInstance.CompleteRequest(). It's not the same as aborting the thread, which truly does prevent all subsequent lines of code form running. The lines of code that follow the call to CompleteRequest() will execute, but as soon as the current page or module that calls this completes, the pipeline will jump ahead to the EndRequest event.

BAD:

Response.Redirect(url);

GOOD:

Response.Redirect(url, false);

Context.ApplicationInstance.CompleteRequest();

GOOD:

void Application_Error(object sender, EventArgs e)

{

Server.ClearError();

Response.Redirect(url, false);

Context.ApplicationInstance.CompleteRequest();

}

I hope this helps to understand why to use Response.Redirect(url, false) over Response.Redirect(url).

Happy Coding!!