Sql server 2012 OFFSET use

Hello,

Today i want to share sql server 2012 new features OFFSET Fetch next example.

It is working fast more than our row_number function. It will show you difference when data in lacs and your query is complex like use inner join more than two tables and also use some sub querys.

Use below query to get more idea for fetch next function.

DECLARE @tblTemp TABLE   
(   
    ID INT,  
    firstName NVARCHAR(100),
    lastName NVARCHAR(100)
);
  
INSERT INTO @tblTemp VALUES(1,'Shikhar', 'Dhawan')  
INSERT INTO @tblTemp VALUES(2,'Rohit', 'Sharma')  
INSERT INTO @tblTemp VALUES(3,'Kumar', 'Sangakara')  
INSERT INTO @tblTemp VALUES(4,'Jonathan', 'Trott')  
INSERT INTO @tblTemp VALUES(5,'Virat', 'Kohli')
INSERT INTO @tblTemp VALUES(6,'Ms', 'Dhoni')
INSERT INTO @tblTemp VALUES(7,'Ravindra', 'Jadeja')
INSERT INTO @tblTemp VALUES(8,'Ravichandran', 'Ashiwn')
INSERT INTO @tblTemp VALUES(9,'Mitchell', 'Mccleanaghan')
INSERT INTO @tblTemp VALUES(10,'James', 'Anderson')
INSERT INTO @tblTemp VALUES(11,'Ishant', 'Sharma')
INSERT INTO @tblTemp VALUES(12,'Joe', 'Root')

DECLARE @pageIndex INT = 0,
	@pageSize INT = 5,
	@sortField NVARCHAR(50) = 'firstName',
	@order BIT = CAST(1 AS BIT)

SELECT * FROM @tblTemp
ORDER BY 
	CASE WHEN (@sortField ='firstName' AND  @order=CAST(1 AS BIT)) THEN firstName END ASC,
	CASE WHEN (@sortField ='firstName' AND @order=CAST(0 AS BIT)) THEN firstName END DESC,
	CASE WHEN (@sortField ='lastName' AND @order=CAST(1 AS BIT)) THEN lastName END ASC,
	CASE WHEN (@sortField ='lastName' AND @order=CAST(0 AS BIT)) THEN lastName END DESC
OFFSET (@pageIndex * @pageSize) ROWS
FETCH NEXT @pageSize ROWS ONLY

Remember Fetch next will only work with order by clause. You have to set order by to work with offset and fetch next.

Here in this query i cover multiple order issue of offset fetch next. If you want to use dynamic sortfield in offset than use case statement in order by clause.

enjoy new feature of sql server 2012.

comma separated values by column in sql

Hello All,

Sometimes we need comma separated values by columnName(e.g By Category, By ID, By Name etc) in sql query.

Generally we are making a user defined function for above problem. If we have large amount of data and there are multiple joins in query then it degrades the performance.

So how can we increase the performance of our query and how can we get comma separated values by columnName.

we can achieve this by using “FOR XML PATH“.

Lets take an example.

we have a table with column ID and Name.

Table contains below data

ID    Name
2    sachin
4    yuvraj
1    saurav
3    kohli
4    sharma
3    dravid
1    gambhir
3    raina
4    karthik
2    sehvag

Now we need to display it as

ID    Names
1     saurav, gambhir
2     sachin, sehvag
3     kohli, dravid, raina
4     yuvraj, sharma, karthik

see below query with temporary table variable.

DECLARE @tblTest TABLE 
( 
    ID INT,
    name NVARCHAR(100)
); 

INSERT INTO @tblTest VALUES(2,'sachin')
INSERT INTO @tblTest VALUES(4,'yuvraj')
INSERT INTO @tblTest VALUES(1,'saurav')
INSERT INTO @tblTest VALUES(3,'kohli')
INSERT INTO @tblTest VALUES(4,'sharma')
INSERT INTO @tblTest VALUES(3,'dravid')
INSERT INTO @tblTest VALUES(1,'gambhir')
INSERT INTO @tblTest VALUES(3,'raina')
INSERT INTO @tblTest VALUES(4,'karthik')
INSERT INTO @tblTest VALUES(2,'sehvag')

SELECT a.ID, 
    (SELECT SUBSTRING( 
        (SELECT ', '+ b.name 
        FROM @tblTest b  
        WHERE a.ID = b.ID 
        FOR XML PATH('')) 
        ,2 
        ,4000) 
    ) Names
FROM @tblTest a 
GROUP BY a.ID

Hope this will help you.

How to split comma separated string in sql

Hello all,

I want to share one sql function to get comma separated string. you can use any separator in function.

CREATE FUNCTION [dbo].[fnSplit](
  @string NVARCHAR(4000),
  @seperator NVARCHAR(5)
)
RETURNS @tbl_IDs TABLE(
 id NVARCHAR(4000)
)
AS
BEGIN
DECLARE
  @currentStr NVARCHAR(4000)
  SET @currentStr = @string
  WHILE(CHARINDEX(@seperator, @currentStr)>0)
    BEGIN
      DECLARE
      @comaIndex INT,
      @currentValue NVARCHAR(4000)
      SET @comaIndex = CHARINDEX(@seperator, @currentStr)
      SET @currentValue = SUBSTRING(@currentStr,1,@comaIndex-1)
      SET @currentValue = LTRIM(RTRIM(@currentValue))
      SET @currentStr = RIGHT(@currentStr, LEN(@currentStr) - @comaIndex)
      IF @currentValue<>''
        BEGIN
          INSERT INTO @tbl_IDs(id) VALUES(@currentValue)
        END
    END
  SET @currentStr = LTRIM(RTRIM(@currentStr))
  IF @currentStr<>''
    BEGIN
      INSERT INTO @tbl_IDs(id) VALUES(@currentStr)
    END  
  RETURN
END

above function is useful to get comma separated string. For e.g.

DECLARE @strValue NVARCHAR(1000)
SET @strValue = 'test,my,comma,separated,string'
SELECT
	ID AS Output
FROM 
	fnSplit(@strValue,',')

Output
test
my
comma
separated
string

How to insert link in part of image

We can add link on whole picture by using anchor tag but if we have a big picture with several buttons and we need different links on all buttons then it is not possible to insert link by using anchor tag.

Generally people divide the image in different parts is not a best solution for this problem.

You can do it by using html image map feature. This is simple and smart solution for this problem.

Syntax:

<img src="imagepath" usemap="#demoexample" slt="image map example">
<map name="demoexample ">
<area shape="rect" coords="x1,y1,x2,y2"  href="#" alt="rect">
<area shape="circle" coords="x,y,radius"  href="#" alt="circle">
<area shape="polygon" coords="x1,y1,x2,y2,..,xn,yn"  href="#" alt="poly">
</map>

You can define three different shapes in area attribute of image map
1. Rect : you need two different co-ordinates. Top right and bottom left.
x1,y1,x2,y2 indicates left, top, right, bottom corner of the rectangle

2. circle : you need centre co-ordinate and radius size for circle.
x,y,radius indicates cenre and radius co-ordinates

3. Polygon : If you want link in your image and you cannot use above area(rect & circle) then you can use this custom area attributes in your map.
x1,y1,x2,y2,..,xn,yn indicates your custom area of image.

Here I have defined mapname because sometimes we use map for multiple image so it will identify which image goes with which map.

Timeout settings in IIS and web.config

Hello all,

We all are facing timeout issue in iis or in our site. Sometimes client need more time to respond. So, we have to extend site timeout.

Timeout has Session Timeout and Idle Timeout.
Session timeout is set from web.config and from iis too.

To set from web.config write following code.


....

....

Default timeout is 20 minutes if you want more than that set in your session state. Timeout takes minutes. Here i have set 60 minutes.

Idle timeout only set from iis.
To set idle timeout go to iis
-> Application Pools
-> right click on your pools
-> select advance settings
-> go to process model
-> set value in Idle time-out.

Default value is 20 you can set it as you wish. I have added image for more idea.
idle-timeout-iis-settings

How to make custom error page in asp.net

Hello,

In asp.net application, sometimes we need to show custom error page to user.
We can set it by using “custom error” tag of web.config file.

There are three option in custom error tag
1. On
2. Off
3. Remote Only
The “custom error” tag must be placed within <system.web> tags.

If we set it to “Off” then ASP.NET uses its default error page for both local and remote users in case of an error.

<configuration>
   <system.web>
      <customErrors mode="Off"/>
   </system.web>
</configuration>

If we set it to “On” then ASP.NET uses user defined custom error page for both local and remote users in case of an error.

<configuration>
   <system.web>
      <customErrors mode="On" defaultRedirect="errorPage.aspx" />
   </system.web>
</configuration>

If we set it to “Remoteonly” then ASP.NET error page is shown only to local users.

if we set it to “On” or “RemoteOnly” then we need to specify the defaultRedirect attribute. This attribute contains the error page to which the user will be redirected when any error occurs in our web application.

Additionally you can take custom error handling a step further by using error codes with specific error pages. The customErrors section contains error elements that associate particular error codes with error pages.
Please check below code

<configuration>
   <system.web>
      <customErrors mode="RemoteOnly" defaultRedirect="errorPage.aspx">
         <error statusCode="403" redirect="error403.aspx" />
         <error statusCode="404" redirect="erro404.aspx" />
      </customErrors>
   </system.web>
</configuration>

Create Simple WCF Service

Hello All,

Today, I want to tell you how to Create Simple WCF Service.

To create WCF service right click on project and add new item -> WCF service. It will create .svc file. Remember to declare Factory tag in .svc file. When you creating service if your web.config is read only it will ask to make it writable. It will not write anything in web.config.

Factory="System.ServiceModel.Activation.WebScriptServiceHostFactory"

Now, go to your .cs file. It has created two files one for sampleClassFunction and another is interface file. We can code in both way write in sampleClassFunction or in interface. I prefer to write in class direct. So, i have remove interface from my class. In cs class file add following code

[AspNetCompatibilityRequirements
    (RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
[ServiceContract(Namespace = "")]
public class sampleClassFunctions
{
    [OperationContract]
    public string GetFirstString(string sample)
    {
        return sample;
    }
}

Now, declare ScriptManager in .aspx.

    
        
            
        
    

To call this service from your code use following code(I am calling it from my js).

function sample()
{
    function getFirstStringCallback(data)
    {
         alert(data);
    }

    sampleClassFunctions.GetFirstString("My First WCF Service", getFirstStringCallback);
}

That’s it. Enjoy your WCF service….

How to apply CSS class for a div from code behind in asp.net

Hello,

As you know that, we can set style and class for div element as below

Now I would like to change the class name for a div from code-behind.

Please note that I have set runat=server in div elements. now i am going to add attributes(class/style) in div tag.

Now you need to write below code in your code behind event

dvID.Attributes.Add("class", "youclassname")

same way, you can set any attributes of style.
Hope this will help.

send email using gmail credentials in asp.net

we often require email sending functionality in our web application.
If we don’t have smtp server for sending mails then we can send mails by using gmail smtp server.

Here I will explain how to send email using gmail credentials in asp.net.

protected void btnSubmit_Click(object sender, EventArgs e)
{
  try
  {
    MailMessage Msg = new MailMessage();
    Msg.From = new MailAddress("fromemailaddress");
    Msg.To.Add("toemailaddress");
    Msg.Subject = "Define your subject line";
    Msg.Body = "Content of body";

    SmtpClient smtp = new SmtpClient();
    smtp.Host = "smtp.gmail.com";
    smtp.Port = 587;
    smtp.Credentials=new System.Net.NetworkCredential("gmailID","gmailpassword");
    smtp.EnableSsl = true;
    smtp.Send(Msg);
  }
  catch (Exception ex)
  {
    //catch exception here
  }
}

Note:You need to add System.Net.Mail namespace on your page.

Reset control value in js

Today, i want to share one js function with all of you. Which is useful to reset controls value.

function resetControl(element)
{
    try
    {
        switch (Utilities.GetTagName(element).toLowerCase())
        {
            case "":
                {
                    break;
                }
            case "input":
                {
                    switch ((element.type + "").toLowerCase())
                    {
                        case "":
                            {
                                element.value = "";
                                break;
                            }
                        case "text":
                            {
                                element.value = "";
                                break;
                            }
                        case "password":
                            {
                                element.value = "";
                            }
                    }
                    break;
                }
            case "select":
                {
                    Utilities.deselectAllOptions(element);
                    if (element.options.length > 0)
                    {
                        element.options[0].selected = (!element.multiple);
                    }
                    break;
                }
            case "textarea":
                {
                    element.value = "";
                    break;
                }
        }
    }
    catch (ex)
    {
        return;
    }
    return;
};

above function is useful to reset html controls.