Code to Create Zip file and Download using DotNetZip Library

Below is the code to create zip file on the server and download it

first download the DotNetZip Library from the below link

http://dotnetzip.codeplex.com/

Add the reference of Ionic.zip.dll  within your application.

Add below using statements to the Program



using Ionic.Zip;
using Ionic.Zlib;
using Ionic.BZip2;
using System.IO;
using System.Net;



Below is the sample code to create zip files.
Dataset being passed in below method has the URL's of the files to be added in to the zip file


  public void ZIPfiles(DataSet ds,String FileName)
    {
        try
        {
            Response.Clear();
            // no buffering - allows large zip files to download as they are zipped
            Response.BufferOutput = false;
            // String ReadmeText = "Dynamic content for a readme file...\n" +
            // DateTime.Now.ToString("G");
            string archiveName = String.Format(FileName + ".zip",
                                              DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
            Response.ContentType = "application/zip";
            Response.AddHeader("content-disposition", "attachment; filename=" + archiveName);

            ZipFile zip = new ZipFile();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {


                string appPath = System.AppDomain.CurrentDomain.BaseDirectory;
                //  string appPath = HttpContext.Current.Request.ApplicationPath;
                String filePath = ds.Tables[0].Rows[i]["ImageURL"].ToString().Remove(0, 1).ToString();
                // add a file entry into the zip, using content from a string
                zip.AddFile(appPath + filePath);
           
                // compress and write the output to OutputStream
            }

            zip.Save(System.AppDomain.CurrentDomain.BaseDirectory + "DateWiseOrder.zip");

            //download the above Zipped file from the server
            //String filePath1 = System.Web.HttpContext.Current.Server.MapPath("~/" +"DateWiseOrder.zip");

            //HyperLink1.NavigateUrl = Server.MapPath("~/")+"DateWiseOrder.zip";

            try
            {
                string strURL =  "DateWiseOrder.zip";
                WebClient req = new WebClient();
                HttpResponse response = HttpContext.Current.Response;
                response.Clear();
                response.ClearContent();
                response.ClearHeaders();
                response.Buffer = true;
                response.AddHeader("Content-Disposition", "attachment;filename=\"" + Server.MapPath(strURL) + "\"");
                byte[] data = req.DownloadData(Server.MapPath(strURL));
                response.BinaryWrite(data);
                response.End();
            }
            catch (Exception ex)
            {
            }
       
        }

        catch (Exception ex)
        {


        }
   
    }

Code to Copy Bulk Data from One Table to another using SqlBulkCopy

Below code sample is how to copy bulk data from one table to another in sqlServer from ASP.NET (C#)

//Create SqlBulkCopy Object
 SqlBulkCopy sbc = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MyDbConn"].ToString()); 


//Code to copy Data from Source to Destination

  try
            {
                //Read Data from Source
                SqlDataReader dr = sqlobject.RunQueryReturnRedaer("Select * from Orders");


                sbc.DestinationTableName = "BackUpOrders";
                sbc.WriteToServer(dr);




            }
            catch (Exception ex)
            {


                evtlog.WriteError(ex.Message);
            }




//Read Data from Source

public SqlDataReader RunQueryReturnRedaer(string strSql)
    {
        SqlCommand cmd = new SqlCommand(strSql, connObj);
        createConnection();
        SqlDataReader rdr = cmd.ExecuteReader();
        return rdr;
    }

Details View Row Invisible from Code Behind

Below is the sample code if you want to make some rows invisible in details view

//Method to Bind details View from DB

 public void GetUserProfile()
    {
        DataSet ds = sqlobj.RunQueryReturnDataSetonLoginDB("exec [GetServiceUsage] '" + Session["UserId"].ToString() + "'" + "," + 0);
        if (ds != null)
        {
            if (ds.Tables[0] != null) 
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dView.DataSource = ds.Tables[0];
                    dView.DataBind();


                }


            }


        }




    }


//Make Specific rows which have cell text ="0" invisible

    if (dView != null)
            {
                DataRowView row = dView.DataItem as DataRowView;
                
                    if (row  != null)
                    {
                       //Row 1
                        if (row["Testrow"].ToString() == "0" )
                        {
                            dView.Rows[0].Visible = false;


                        }


                      //Row 2
                        if (row["Testrow2"].ToString() == "0" )
                        {
                            dView.Rows[1].Visible = false;


                        }


                    }
                
            }

Dynamically Create Link Button and Add Command event

Below is the code which you can write to create Dynmic Link Button in C#

    LinkButton  hl = new LinkButton ();
                        hl.Text = "Delete Check";
                        String D = ds.Tables[0].Rows[i]["Date"].ToString();
                     //   hl.Click += new EventHandler(this.MyButtonHandler);
                        hl.Command += new CommandEventHandler(this.MyButtonHandler);
                        hl.CommandArgument = ds.Tables[0].Rows[i]["ID"].ToString();
                        hl.ID = "DeleteLink" + i.ToString();


                        div3.Controls.Add(hl);
                        div3.Controls.Add(new LiteralControl("<br/>"));


                        ViewState["DataSet"] = ds;




                    }
                    ViewState["recordsCount"] = ds.Tables[0].Rows.Count.ToString();


                }


//Event Handler which executes on Click on the Link Button



 void MyButtonHandler(object sender, CommandEventArgs e)
        {
            string  ID = e.CommandArgument.ToString();
             //Business Logic Comes Here
            }


       }


This Code works perfect when you are creating dynamic Link Buttons On page Load.


But In My case I wanted these links to be created on Some Search Button Click .
and When I was clicking on the Link Button I saw that Event Handler code is not executing because you have to add them on Page_Load when you are dynamically creating them , but here I got the Hack of it .


You recreate the Same Hyperlink in PageLoad also on post back with same "ID"(Link Button ID)
So then I wrote below code on the page load , and it worked well.

protected void Page_Load(object sender, EventArgs e)
    {
               if (IsPostBack)
                {
                    if (ViewState["recordsCount"] != null)
                    {
                        for (int i = 0; i < int.Parse(ViewState["recordsCount"].ToString()); i++)
                        {


                            LinkButton hl = new LinkButton();
                            hl.ID = "DeleteLink" + i.ToString();
                            div3.Controls.Add(hl);
                            hl.Command += new CommandEventHandler(this.MyButtonHandler);
                            DataSet ds1 = (DataSet)ViewState["DataSet"];
                            hl.CommandArgument = ds1.Tables[0].Rows[i]["ID"].ToString();
                               
                        }
                    }
                }




The reason of using ViewState here is my command argument for each link was a cell of a DataSet which I was binding from Data Base . So I saved that DataSet in the view State to make it accessible on PostBack in Page Load

Code for Password Hashing in ASP.NET

Below is the simplest method to Hash Passwords in ASP.NET

using System.Web.Configuration;
using System.Web.Security;



protected void btnRegister_Click(object sender, EventArgs e)
    {
        string strUserInputtedHashedPassword =
             FormsAuthentication.HashPasswordForStoringInConfigFile(
                                      txtPwd.Text, "sha1");
        //Write code to insert above password in DB
    }





 protected void btnLogin_Click(object sender, EventArgs e)
    {
        string strUserInputtedHashedPassword =
             FormsAuthentication.HashPasswordForStoringInConfigFile(
                                      txtPwd.Text, "sha1");
        //Write code to Match above password  From DB
    }

Set Auto generated columns as read only in Editable Grid View

Below code can be used to make read only columns in grid view


  protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        rowIndex = e.NewEditIndex;


        BindData();
        FillGrid();
    }



//Make 1,2,3,4,7,8,9 as readonly

  protected void GridView1_PreRender(object sender, EventArgs e)
    {
        if (rowIndex > -1)
        {
            if (GridView1.Rows.Count > rowIndex)
            {
                if (GridView1.Rows[rowIndex].Cells.Count > 1)
                {
                    if (GridView1.Rows[rowIndex].Cells[1].Controls.Count > 0)
                    {
                        for (int i = 1; i < 4; i++)
                        {
                            if (GridView1.Rows[rowIndex].Cells[i].Controls[0].GetType() == typeof(TextBox))
                            {
                                ((TextBox)GridView1.Rows[rowIndex].Cells[i].Controls[0]).Visible = false;
                                GridView1.Rows[rowIndex].Cells[i].Text
                                    = ((TextBox)GridView1.Rows[rowIndex].Cells[i].Controls[0]).Text;
                            }
                        }
                        for (int j = 7; j < 10; j++)
                        {
                            if (GridView1.Rows[rowIndex].Cells[j].Controls[0].GetType() == typeof(TextBox))
                            {
                                ((TextBox)GridView1.Rows[rowIndex].Cells[j].Controls[0]).Visible = false;
                                GridView1.Rows[rowIndex].Cells[j].Text
                                    = ((TextBox)GridView1.Rows[rowIndex].Cells[j].Controls[0]).Text;
                            }
                        }






                    }
                }
            }
        }
    }

Ref Keyword(argument passed by reference) Example in C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace Ref_Key_Word
{
        class TestRef
        {
            //Pass by Value
            public static void TestWithoutRef(int i)
            {
            i++;
            }


            //Pass by reference
            public static void TestWithRef(ref int i) // note ref
            {
                i++;
            }


            public static void Main()
            {
                //a value will not change after calling a method as is is pass by value
                int a = 2;
                TestWithoutRef(a);
                Console.WriteLine("The value of a is " + a);


                //b value will be chnaged( as it is pass by ref 
                int b = 2;
                TestWithRef(ref b);
                Console.WriteLine("The value of b is " + b);
            }
        }




           
}

Below will be the o/p

Open New browser window on Link Button Click

When you want to open a new browser window on link button click.(not the same window)
add below code in your LinkButton

OnClientClick="aspnetForm.target ='_blank';"




see the below code sample


<asp:LinkButton ID="UploadCheckLink" runat="server" OnClientClick="aspnetForm.target ='_blank';" 
                onclick="UploadCheckLink_Click" >Upload Check Images</asp:LinkButton>



 protected void UploadCheckLink_Click(object sender, EventArgs e)
    {        
      Response .Redirect  ("NewWindow.aspx");


    }

Pop Up Window Code in ASP.NET Using JAVA Script

Below is the code to open pop up window is ASP.NET

in .aspx page

 <script type="text/javascript" >
function OpenWindow(url)
{
    newwindow = window.open(url, 'mynewwindow', 'width=500,height=400');  


}
</script>


in .aspx.cs page ( Server Side Code)


protected void OpenNewWindow(object sennder, EventArgs e)
    {
        StringBuilder popupScript = new StringBuilder();
        popupScript.Append("<script language='JavaScript'> window.open('UploadChecks.aspx?OrderSeqNo=" + lblOrderSeqno.Text + "', '', 'width=950, height=580,scrollbars,status,menubar=no,resizable,toolbar=no,titlebar=no,location=no');</script>");
       
    }

Free Web Services to use

Find All free web services

SDN Check to Comply with OFAC Dept
http://freewebservicesx.com/SDNWebService.aspx

Gold Spot Price
http://freewebservicesx.com/GoldSpotPrice.aspx

Silver Spot Price
http://freewebservicesx.com/SilverPrice.aspx

Platinum Spot Price
http://freewebservicesx.com/PlatinumPrice.aspx

Palladium Spot Price
http://freewebservicesx.com/PalladiumPrice.aspx

Rhodium Spot Price
http://freewebservicesx.com/Rhodium.aspx

Add Third Party dll in GAC(Global Assembly Cache)

You can only add Strong named dll in GAC folder
Below are the steps to add dll in GAC
1.) Open Command Prompt of window (Go to start-->Run and Type 'cmd')
2.)you need Gacutil.exe which is mostly in the below path on the Drive.
     C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin
3.) In the command prompt go till here by command cd(Change Directory) ie

   
4.)Now use this command to register your dll in GAC
   gacutil /i mydll.dll

Java Script Code to compare two Date values in String

  function ValidateDate()
  {
 var fd=document.getElementById ('FromDate_txtDate');
 var td=document.getElementById ('ToDate_txtDate');

 if (Date.parse(fd.value) > Date.parse(td.value)) {

     alert("Invalid Date Range!\nFrom Date cannot be after To Date!")
    return false;
   }
  
}

Nested Class and Static Nested Class

//Static Nested Class
using System;
public class A
{
    int y;


    public static class B
    {
         static  int x;
       public static void F()
        {
            x = 10;
            Console.WriteLine("IN B  , X is"+x);
        }
    }
}
class Program
{
    public static void Main()
    {
        A.B.F();


    }
}

//Non Static Nested Class

using System;
public class A
{
    int y;


    public  class B
    {
        int x;
       public  void F()
        {
            x = 10;
            Console.WriteLine("IN B  , X is"+x);
        }
    }
}


class Program
{
    public static void Main()
    {
        A.B obj = new A.B();
        obj.F();
    }
}

ASP.NET Table Server Control

<asp:TableID="Table1"runat="server">
<asp:TableRowRunat="server"Font-Bold="True"
ForeColor="Black"BackColor="Silver">
<asp:TableHeaderCell>FirstName</asp:TableHeaderCell>
<asp:TableHeaderCell>LastName</asp:TableHeaderCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Bill</asp:TableCell>
<asp:TableCell>Evjen</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Devin</asp:TableCell>
<asp:TableCell>Rader</asp:TableCell>
</asp:TableRow>
</asp:Table>



Below code snippet is used to add a row dynamically in the Table Server Control

C#
protected void Page_Load(object sender,EventArgs e)
{
TableRowtr=new TableRow();
TableCell name=new TableCell();
name.Text="Test3";
tr.Cells.Add(name);
TableCell lname=new TableCell();
lname.Text="Test4";
tr.Cells.Add(lname);
Table1.Rows.Add(tr);
}

Multiple Button controls to work from a single Function

default.aspx
<asp:ButtonID="Button1"runat="server"Text="Button1"
OnCommand="Button_Command"CommandName="btn1"/>
<asp:ButtonID="Button2"runat="server"Text="Button2"
OnCommand="Button_Command"CommandName="btn2"/>

default.aspx.cs

protected voidButton_Command(Object sender,
System.Web.UI.WebControls.CommandEventArgs e)
{
switch(e.CommandName)
{
case("btn1"):
Response.Write("Button1 was clicked");
break;
case("btn2"):
Response.Write("Button2 was clicked");
break;
}
}

CodeExample for out modifier in C#

out modifier requires that a variable is assigned a value before 
returning from a method

using System;
class Test {
  static void Split(string name, out string firstNames, 
                    out string lastName) {
     int i = name.LastIndexOf(' ');
     firstNames = name.Substring(0, i);
     lastName = name.Substring(i+1);
  }
  static void Main( ) {
    string a, b;
    Split("Sachin Tendulkar", out a, out b);
    Console.WriteLine("FirstName:{0}, LastName:{1}", a, b);
  }
}

Code Snippet to Bind YearList

  public void GetYearList(DropDownList ddlYearList)
    {
        int i = DateTime.Now.Year;
        for (i = i - 1; i <= DateTime.Now.Year + 3; i++)
            ddlYearList.Items.Add(Convert.ToString(i));


      
    }

Code Snippet to Bind MonthList

 public void GetMonthList(DropDownList ddlMonthList)
    {
        DateTime month = Convert.ToDateTime("1/1/2000");
        for (int i = 0; i < 12; i++)
        {


            DateTime NextMonth = month.AddMonths(i);
            ListItem list = new ListItem();
            list.Text = NextMonth .ToString("MMMM");
            list.Value = NextMonth .Month.ToString();
            ddlMonthList.Items.Add(list);
        }
       




    }

Sql Convert Month Name to Number

Below is sql code to convert Month Name to Number ex.
January to 1

DATEPART(mm,CAST('MonthName'+ ' 1900' AS DATETIME))

OCR in C# using MODI -Microsoft Office Document Imaging(Fetch Text From image in C#)

Check my previous post to see what OCR is
http://www.dotnetissues.com/2011/09/ocr-in-c-using-googles-tessnet2-fetch.html

In this Post I am going to use  MODI  -Microsoft Office Document Imaging and it gives 100% correct results and works perfectly fine with digits too.

1.) I Installed MSOffice 2007
2.) Go to Add Reference -->Com and Select Microsoft Office Document Imaging library .

  3.)Below is the sample code for Extract Text from Image using MODI

using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using System.Threading;
using MODI;


namespace TesseractConsole
{
    class Program
    {
        static void Main(string[] args)
        {  
            DocumentClass doc = new DocumentClass();
            doc.Create(@"C:\Documents and Settings\lak\Desktop/quotes_7a.jpg");
            doc.OCR(MiLANGUAGES.miLANG_ENGLISH, true, true);


            foreach (MODI.Image image in doc.Images)
            {
                Console.WriteLine(image.Layout.Text);
            }
        }
    }
}


I got 100% correct result and found it better then Google's tessnet2

OCR in C# using Google's Tessnet2 (Fetch Text From image in C#)

I am experimenting on OCR (Optical Character Recognition) .
which is Read Data from an image , I searched a lot over the web 
Found two solutions 


1.) Using Google's Tessnet2 
2.) Using MODI (Microsoft_Office_Document_Imaging Library)


MODI So far i culdn' try as this library comes with MSOfiice 2007 or XP Which I can not get hold of so far.


I tried Google's Tessnet2 and it gave me 98% correct result but only read Alphabets couldn't read digits though.


Below are the Steps which I have used to use this .


1.) Download Tessnet2 binary  from the below link
http://www.pixel-technology.com/freeware/tessnet2/


2.) Add reference of Tessnet2 _32.dll (for 32 bit OS) Tessnet2 _64.dll(for 64 bit os)
in Visual Studio Project Solution

3)Download language data definition file(tesseract-2.00.eng.tar.gz) (I did it for English ) from the below link
http://code.google.com/p/tesseract-ocr/downloads/list


4) UnZip the Above folder and Keep all files in Directory 'tessdata'
    Place this directory in your App/bin/debug  folder
    ex. my case I put it here "D:\TanviDoc\OCRApp\OCRApp\bin\Debug\tessdata"


5.) Below is the sample code to do OCR

using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using System.Threading;




namespace TesseractConsole
{
    class Program
    {
        static void Main(string[] args)
        {
        
           
            Bitmap bmp = new Bitmap(@"C:\Documents and Settings\lak\Desktop/quotes_7a.jpg");
            tessnet2.Tesseract ocr = new tessnet2.Tesseract();
            // ocr.SetVariable("tessedit_cha/r_whitelist", "0123456789");
            ocr.Init(null, "eng", false);
            // List<tessnet2.Word> r1 = ocr.DoOCR(bmp, new Rectangle(792, 247, 130, 54));
            List<tessnet2.Word> r1 = ocr.DoOCR(bmp, Rectangle.Empty);
            int lc = tessnet2.Tesseract.LineCount(r1);
            for (int i = 0; i < lc; i++)
            {
                List<tessnet2.Word> lineWords = tessnet2.Tesseract.GetLineWords(r1, i);
                Console.WriteLine("Line {0} = {1}", i, tessnet2.Tesseract.GetLineText(r1, i));
            }
            foreach (tessnet2.Word word in r1)
                Console.WriteLine("{0}:{1}", word.Confidence, word.Text);
        
        }
    }


  


}




6.) Execute this ,you 'll find Image converted into text.

I Got all the data correct just got 'In' in place of 'On'


Above technique did not convert Digits from Image to text , which still I have to figure out.


Microsoft_Office_Document_Imaging Library)And Still have to experiment with MODI( and have to figure out which one is the better one

Gold Price Web Service

This Web Service returns current gold price , last updated on time  and change in price.
Operation GetCurrentGoldPrice returns ArrayOfStrings with Price,Date,ChangeInPrice


WebService URL
http://184.168.209.81/GoldWebService/GetGoldPrice.asmx

wsdl Document
http://184.168.209.81/GoldWebService/GetGoldPrice.asmx?WSDL

code for screen scraping with Html Agility Pack

Html Agility Pack  is a .NET code library that allows you to parse "out of the web" HTML files.

If you want to scrap some data from HTMl file over the we this is the easiest solution .

The only problem is you are dependent on third party , IF they change the structure , then you have to again work around with the changes.

Below is the sample code I am writing to for this

1.) To use Html Agility Pack download it from the below path

http://htmlagilitypack.codeplex.com/releases/view/44954

2.) Add the reference of HTMLAgilityPack.dll from the above downloaded folder in your bin folder of the ASP.NET solution.

3.) See the below code to read the data using this in you Class

using System;
using System.Data;
using System.Configuration;
using System.Collections;
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.Net;
using HtmlAgilityPack;
public partial class GetGoldPrice : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
           FindDIVFromHTMLOnWeb();


    }
    private void FindDIVFromHTMLOnWeb()
    {
        HtmlWeb web = new HtmlWeb();
        HtmlDocument doc = web.Load("http://www.testdomain.com");
        HtmlNode dataNode = doc.DocumentNode.SelectSingleNode("//div[@id='[id of the div to be fetched]']");
        string data = dataNode.InnerText;
        Response.Write(data);




    }
}

Code for Data Caching In ASP.NET with SqlCacheDependency

Below is the code sample for Data Caching


ExDatacaching.aspx.cs

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.Caching;


public partial class ExDatacaching : System.Web.UI.Page
{


    SqlConnection connObj = new SqlConnection();
    DataSet ds = new DataSet();
    SqlDataAdapter objAdapter;
    CacheItemRemovedCallback onRemove;
    protected void Page_Load(object sender, EventArgs e)
    {
        onRemove = new CacheItemRemovedCallback(this.RemovedCallback);
        if (Cache["Student"] == null)
        {
            connObj.ConnectionString = ConfigurationManager.ConnectionStrings["dotNetTrainingConnectionString"].ToString();
            connObj.Open();


            SqlCommand objCommand = new SqlCommand("Select * from student", connObj);
         
           objAdapter = new SqlDataAdapter(objCommand);  
            objAdapter.Fill(ds);
            SqlCacheDependency dep = new SqlCacheDependency(objCommand);  
            connObj.Close();


            Cache.Insert("Student", ds, dep, Cache.NoAbsoluteExpiration, System.TimeSpan.FromMinutes(1),
               CacheItemPriority.Default, onRemove);




            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
            lblStatus.Text = "Fresh From DB";
        }
        else
        {
            GridView1.DataSource = Cache["Student"];
            GridView1.DataBind();


        }


    }


    void RemovedCallback(string key, Object value,
       CacheItemRemovedReason reason)
    {
        Cache["Status"] = "Cache item: " + key + " value: " +
           value.ToString() + " was " + reason.ToString();
    }


    protected void btnRemove_Click1(object sender, EventArgs e)
    {
        Cache.Remove("Student");
    }


    public void ExDatacaching_PreRender(object sender, EventArgs e)
    {
        // Display status.
        if (Cache["Status"] != null)
            lblStatus.Text = Cache["Status"].ToString();


    }
}

ExDatacaching.aspx

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


<!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>
</head>
<body>
    <form id="form1" runat="server" onprerender ="ExDatacaching_PreRender">
    <div>
         <asp:Button ID="btnRemove" runat="server" Text ="Remove" OnClick="btnRemove_Click1" />
       <asp:Label ID="lblStatus" runat="server" Text ="" />
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    
    </div>
    </form>
</body>
</html>

Above SqlCacheDependency works only when SQLSERVER is allowed to Notify about the changes made and Application is ready to receive the notifications

For this

First write below code in global.asax file

 void Application_Start(object sender, EventArgs e) 
    {
        // Code that runs on application startup


        System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["dotNetTrainingConnectionString"].ToString());


    }
    
    void Application_End(object sender, EventArgs e) 
    {
        //  Code that runs on application shutdown


        System.Data.SqlClient.SqlDependency.Stop (ConfigurationManager.ConnectionStrings["dotNetTrainingConnectionString"].ToString());


    }


Plus make sure Database is also configured to notify for the changes 
to do that 
execute below command on sqlserver



SELECT NAME, IS_BROKER_ENABLED
FROM   SYS.DATABASES


If it is not enabled (IS_BROKER_ENABLED=0)execute below command to enable notification 


ALTER DATABASE <database>  SET ENABLE_BROKER