T-SQL Dynamic Paged Query Techniques

Here we will see how to write dynamic paged query using Derived Table and CTE.

Simple paging query using Derived Table:

CREATE PROCEDURE [dbo].[HrEmployee_GetPaged]

AS

BEGIN

      SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select * from

( select  row_number() over(order by EmployeeId) as Row,* from HrEmployee ) as e

where e.Row between 21 and 30 
END

Dynamic paging query using Derived Table:

CREATE PROCEDURE [dbo].[HrEmployee_GetPaged]

      @StartRowIndex        int,

      @RowPerPage       int,

      @WhereClause      nvarchar(4000),

      @SortColumn       nvarchar(128),

      @SortOrder        nvarchar(4)

AS

BEGIN

      SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

      SET @StartRowIndex = isnull(@StartRowIndex, -1)

      SET @RowPerPage = isnull(@RowPerPage, -1)

      SET @WhereClause = isnull(@WhereClause, '')

      SET @SortColumn = isnull(@SortColumn, '')

      SET @SortOrder = isnull(@SortOrder, '')

DECLARE @SQL nvarchar(4000)

      IF (@WhereClause != '')

      BEGIN

            SET @WhereClause = 'WHERE ' + char(13) + @WhereClause

      END

      IF (@SortColumn != '')

      BEGIN

            SET @SortColumn = 'ORDER BY ' + @SortColumn

            IF (@SortOrder != '')

                  BEGIN

                        SET @SortColumn = @SortColumn + ' ' + @SortOrder

                  END

      END

      SET @SQL = 'SELECT * FROM (SELECT *,

                        ROW_NUMBER() OVER ('+ @SortColumn +')AS Row

                        FROM  [HrEmployee]

                        '+ @WhereClause +'

                        ) as E

                         

                        WHERE E.Row between '+ CONVERT(nvarchar(10), @StartRowIndex) +' And ('+ CONVERT(nvarchar(10), @StartRowIndex+ @RowPerPage-1) +')'

--    print @SQL

EXEC sp_executesql @SQL

END

Simple paging query using CTE:

CREATE PROCEDURE [dbo].[HrEmployee_GetPaged]

AS

BEGIN

      SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

WITH e AS (select  row_number() over(order by EmployeeId) as Row,* from HrEmployee )

select * from e where e.Row between 21 and 30
END

Dynamic paging query using CTE:

CREATE PROCEDURE [dbo].[HrEmployee_GetPaged]

      @StartRowIndex        int,

      @RowPerPage       int,

      @WhereClause      nvarchar(4000),

      @SortColumn       nvarchar(128),

      @SortOrder        nvarchar(4)

AS

BEGIN

      SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

      SET @StartRowIndex = isnull(@StartRowIndex, -1)

      SET @RowPerPage = isnull(@RowPerPage, -1)

      SET @WhereClause = isnull(@WhereClause, '')

      SET @SortColumn = isnull(@SortColumn, '')

      SET @SortOrder = isnull(@SortOrder, '')

DECLARE @SQL nvarchar(4000)

      IF (@WhereClause != '')

      BEGIN

            SET @WhereClause = 'WHERE ' + char(13) + @WhereClause

      END

      IF (@SortColumn != '')

      BEGIN

            SET @SortColumn = 'ORDER BY ' + @SortColumn

            IF (@SortOrder != '')

                  BEGIN

                        SET @SortColumn = @SortColumn + ' ' + @SortOrder

                  END

      END

      SET @SQL = 'WITH E AS (

                        SELECT ROW_NUMBER() OVER ('+ @SortColumn +')AS Row,  *

                        FROM  [HrEmployee]

                        '+ @WhereClause +'

                        )

                        SELECT *

                        FROM E

                        WHERE E.Row between '+ CONVERT(nvarchar(10), @StartRowIndex) +' And ('+ CONVERT(nvarchar(10), @StartRowIndex+ @RowPerPage-1) +')'

--    print @SQL

EXEC sp_executesql @SQL

END

 

Earn money from Twitter

Passing value and auto refresh parent window from popup

Here I will show how to pass value from popup window to parent window and refresh parent window while closing popup. This is a common task and often asked in asp.net forum. In this example, I have taken two forms:

Default4.aspx >Its Master page is  MasterPage.master (This is parent page)
Default5.aspx (This is child page)

I have taken UpdatePanel in parent page to check whether it is working properly with UpdatePanel. Let us see codes of Default4.aspx:

HTML:

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true"

    CodeFile="Default4.aspx.cs" Inherits="Default4" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">

    <script type="text/javascript">

        function OpenPopup() {

            window.open("Default5.aspx", "Popup", "scrollbars=no,resizable=no,width=500,height=250");

            return false;

        }

    script>

asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">

    <div>

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">

            <ContentTemplate>

                <div>

                    <table width="80%">

                        <tr>

                            <td>

                                <asp:Label ID="Label1" runat="server" Text="Value from popup:">asp:Label>

                            td>

                            <td>

                                <asp:TextBox ID="txtOpenner" runat="server">asp:TextBox>

                                 <asp:Button ID="Button1" runat="server" Text="Popup" OnClientClick="OpenPopup()" />

                                <asp:Label ID="Label2" runat="server" Text="">asp:Label>

                            td>

                        tr>

                    table>

                div>

            ContentTemplate>

        asp:UpdatePanel>

    div>

asp:Content>

Design preview: 

When user click on Popup button popup window will be displayed and the textbox will show value sent from popup window.

Code behind:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class Default4 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        int count = 0;

        if (!IsPostBack)

        {

            Session["Count"] = 0;

        }

        if (IsPostBack)

        {

            Session["Count"] = count = int.Parse(Session["Count"].ToString())+1;

            Label2.Text = "This page is postedback " + count.ToString() + " times";

        }

    }

}

Here I am counting how many time PostBack event is occurring in parent page and showing in Label2. I am doing this to clearly understand that parent page is refreshed clearly.



Now, Let us see codes of Default5.aspx:

HTML:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default5.aspx.cs" Inherits="Default5" %>

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 runat="server">

    <title>title>

    <script language="javascript" type="text/javascript">

        function SendValue() {

            var val = document.getElementById('<%=TextBox1.ClientID%>').value;

            window.opener.document.getElementById("ctl00_ContentPlaceHolder1_txtOpenner").value = val;

            window.close();

            window.opener.document.forms(0).submit();

        }

    script>

head>

<body>

    <form id="form1" runat="server">

    <div>

        <table width="100%">

            <tr>

                <td>

                    <asp:Label ID="Label1" runat="server" Text="Give your name here:">asp:Label>

                td>

                <td>

                    <asp:TextBox ID="TextBox1" runat="server">asp:TextBox>

                     <asp:Button ID="btnSend" runat="server" Text="Send to opener" OnClientClick="SendValue()" />

                td>

            tr>

        table>

    div>

    form>

body>

html>

Code behind:

using System;

public partial class Default5 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

}

Earn money from Freelancer.com:

Show client side alert message from server side code even when update panel is used

Developers might want to display client side message with information from server side to their users when they have completed some execution at server side. People may try in different way for this purpose.
For example:
Response.Write() method with JavaScript code inside the method:

string mes = "Hello Dhaka";
        Response.Write("<script language=\"javascript\"  type=\"text/javascript\">alert('" + mes + "');</script>");

or ClientScript.RegisterStartupScript() method:

string message = "<script language=\"javascript\"  type=\"text/javascript\">alert('Hello Dhaka');</script>";
        if (!ClientScript.IsStartupScriptRegistered("mes"))
        {
            ClientScript.RegisterStartupScript(this.GetType(), "mes", message);
        }

But these code doesn't work when you use update panel in your page. So better solution is to use ScriptManager.RegisterStartupScript() method. This works whether update panel is used or not. So let's see the code snippet below:

string message = string.IsNullOrEmpty(TextBox1.Text) ? "Please give a text to textbox." : "You have given: " + TextBox1.Text.Trim();
        string script = "<script language=\"javascript\"  type=\"text/javascript\">alert('" + message + "');</script>";
        ScriptManager.RegisterStartupScript(Page, this.GetType(), "AlertMessage", script, false);

With this code snippet you can display a modal message that their data was saved or updated successfully or not.

Truncate table when referenced by a FOREIGN KEY

If you try to truncate a table that is referenced by foreign key you usually get the following error:

Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.

To avoid this error the easiest way to use DELETE without where clause and RESEED the identity:

DELETE FROM AccVoucher
DBCC CHECKIDENT (AccVoucher, RESEED, 0)

Highlight gridview row on mouse hover in asp.net

Gridview control is a customizable and flexible control used to display data in tabular format. It has some nice features. But lacks of some client side features that makes web users happy. We can easily add these features with few lines of code.

For example, a common task is to highlight gridview row on mouse over which is not provided with gridview control. Here we will see how easily we can do the task.

In order to change gridview row color we need to add/remove style attributes to that specific row using JavaScript onmouseover and onmouseout client event. We can do it on RowDataBound or RowCreated gridview event.

Code Snippet:

protected void gvHrEmploye_RowDataBound(object sender, GridViewRowEventArgs e)

        {

            if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)

            {

                // when mouse is over the row, save original color to new attribute, and change it to highlight color

                e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");

                // when mouse leaves the row, change the bg color to its original value  

                e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");

            }

        }

or,

protected void gvHrEmploye_RowCreated(object sender, GridViewRowEventArgs e)

        {

            if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)

            {

                // when mouse is over the row, save original color to new attribute, and change it to highlight color

                e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");

                // when mouse leaves the row, change the bg color to its original value  

                e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");

            }

        }

It works properly even if you set AlternatingRowStyle property or the row is previously selected.

How it works: