Pages

Monday, April 29, 2013

String concatenation and issue related to NULL values

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

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

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.

Correct use of System.Web.HttpResponse.Redirect

Try very, very hard to avoid using Response.Redirect(url), instead, use Response.Redirect(url, false). Response.Redirect(url), after writing a 302 redirect response to the response buffers, calls Response.End. This is very expensive. The alternative, Response.Redirect(url, false) is fast, but unlike Response.Redirect(url), the lines of code which follow the call to Response.Redirect(url, false) will be executed. More on this later, but first, let me tell you about the horrors of Response.End.
Ok, so 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, thereby short circuiting the pipeline. This is usually all you need.
So to summarize...
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();
}

Prevent the browser from caching the ASPX page

​use this code to prevent the browser from caching the ASPX page.
It will work for IE, Mozilla, and Chrome. I didn't checked the below code for other browsers.

<%@ Page language="c#" AutoEventWireup="true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
  <head>
    <title>HttpCachePolicy - SetNoStore - C# Example</title>
    <script runat="server">
      void Page_Load(Object sender, EventArgs e) 
      {
        // Prevent the browser from caching the ASPX page
        Response.Cache.SetNoStore();

        // Display the DateTime value.
        Label1.Text = DateTime.Now.ToLongTimeString();
      }
    </script>
  </head>
  <body>
    <form id="Form1" method="post" runat="server">
      <h3>HttpCachePolicy - SetNoStore - C# Example</h3>

      <p>Click the Submit button a few times, and then click the Browser's Back button.<br />
      You should get a "Warning: Page has Expired" error message.</p>

      <p>Time:  <asp:Label id="Label1" runat="server" Font-Bold="True" ForeColor="Red" /></p>

      <asp:Button id="Button1" runat="server" Text="Submit" />
    </form>
  </body>
</html>

Paging and sorting in sql server

create table #Student(id int identity(1,1), name nvarchar(100), birthdate datetime)
declare @PageSize int=5
declare @MinRowNumber int=1
declare @MaxRowNumber int= @MinRowNumber + @PageSize-1
declare @SortExpression nvarchar(100)='Name' ---'birthdate' Pass here column name.
declare @SortOrder nvarchar(100)='D' --'A' pass here sort order D- for descending and A for ascending.
insert #Student
select 'Viru','1986-12-07'
union all
select 'Sachin','1960-01-31'
union all
select 'Gambhir','1950-06-23'
union all
select 'Shreekant','1983-10-04'
union all
select 'Kapil','1981-03-17'
union all
select 'Yuvi','1986-03-02'
union all
select 'Dhoni','1987-12-05'
union all
select 'Zak','1975-08-17'
union all
select 'Bhajji','2007-08-06'
union all
select 'Ashwin','2010-04-22'
union all
select 'Ojha','2012-02-26'
union all
select 'Ravindra','2012-10-04'
SELECT RowNumber, name, birthdate,id FROM
(select *,ROW_NUMBER() OVER (ORDER BY
CASE WHEN @SortExpression = 'Name' AND @SortOrder='D' THEN Name END DESC,
CASE WHEN @SortExpression = 'Name' AND @SortOrder='A' THEN Name END ASC,
CASE WHEN @SortExpression = 'birthdate' AND @SortOrder='D' THEN birthdate END DESC,
CASE WHEN @SortExpression = 'birthdate' AND @SortOrder='A' THEN birthdate END ASC
) AS RowNumber from #Student) AS Student
WHERE RowNumber BETWEEN @MinRowNumber AND @MaxRowNumber
drop table #student

Monday, March 18, 2013

Get users information through active directory

Get users information through active directory. You need to add System.DirectoryServices dll reference in your asp.net project. Add following code in default.aspx page.
it automatically finds user name from Page.User.Identity.Name. We have added some properties like: sAMAccountName it only load those properties which we have mentioned in our code.


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.DirectoryServices;
using System.Runtime.InteropServices;
using System.Security.Authentication;
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SearchResultCollection users;
        string loginUserName = "";
        if (string.IsNullOrEmpty(Page.User.Identity.Name) == false)
        {
            loginUserName = Page.User.Identity.Name.ToString().Substring(Page.User.Identity.Name.ToString().LastIndexOf("\\") + 1);
        }
        using (DirectoryEntry Root = new DirectoryEntry())
        {
            //Establish connection to current loged on users Active Directory
            using (DirectorySearcher Searcher = new DirectorySearcher(Root))
            {
                //Start at the top              
                Searcher.Filter = "(&(objectCategory=person)(anr=" + loginUserName.ToUpper().Trim() + "))";
                Searcher.SearchScope = SearchScope.Subtree;
                //Start at the top and keep drilling down
                Searcher.PropertiesToLoad.Add("sAMAccountName");
                //Load User ID
                Searcher.PropertiesToLoad.Add("displayName");
                //Load Display Name
                Searcher.PropertiesToLoad.Add("givenName");
                //Load Users first name
                Searcher.PropertiesToLoad.Add("sn");
                //Load Users last name
                Searcher.PropertiesToLoad.Add("distinguishedName");
                //Users Distinguished name
                Searcher.PropertiesToLoad.Add("telephoneNumber");
                //Ext. Number
                Searcher.PropertiesToLoad.Add("ipPhone");
                //7D Phone Number
                Searcher.PropertiesToLoad.Add("mobile");
                //Cell Phone Number
                Searcher.PropertiesToLoad.Add("mail");
                //Cell Phone Number
                Searcher.Sort.PropertyName = "sn";
                //Sort by last name
                Searcher.Sort.Direction = System.DirectoryServices.SortDirection.Ascending;
                //A-Z
                users = Searcher.FindAll();
                {
                    //Users contains our searh results
                    //MsgBox(users.Count)
                    //If it's zero then no matches were found
                    if (users.Count > 0)
                    {
                        //goes throug each user in the search results
                        foreach (SearchResult User in users)
                        {
                           // ResultPropertyCollection rcol = User.Properties;
                            ResultPropertyCollection props = User.Properties;
                            foreach (string prop in props.PropertyNames )
                            {
                                ResultPropertyValueCollection values = props[prop];
                                foreach (string val in values)
                                {
                                    //if (prop=="mail")
                                    //    Response.Write(val);
                                    Response.Write("<br>"+prop + "= " + val);
                                }
                            }  
                        }
                    }
                    else
                    {
                        // Store the DataTable in ViewState.
                        return;
                    }
                }
            }
        }
    }
}