How to Make GridView Auto Partial Update like Gmail Inbox

• Download source - 51.51 KB

Introduction

The way presented in this article is a way of how to add new rows from database to gridview without refresh of the page. This is useful when a client wants to see new rows (e.g., new letters) without having to refresh the page.

If you want to see a live demo, please login to your Gmail account (load standard Gmail), then send a letter from a different browser to your Gmail address and see that your Gmail Inbox will auto add the new letter's row to your letters' gridView.

There is a quick way to show new rows: put GridView inside an updatePanel and refresh the updatePanel every 3 (or any) seconds, but this way increases page source's size and page's load time.......

But in this article, we will learn a way to automatically get new rows every 3 (or any) seconds [by JavaScript timeout] from server [by jQuery get method] and then those new rows to gridView [by jQuery HTML manipulation].

Background

You should know:

• jQuery HTML manipulation - you can learn from w3schools.com
• jQuery and Ajax - you can learn from w3schools.com

Using the Code

Suppose we want to have a gridView with the ability of auto adding new rows from database to it, we follow the steps given below to have a gridView like Gmail Inbox perl gridView!

Step 1

Add the following JavaScript code to the page that contains your gridView:

<script type="text/javascript" src="jquery-1.4.js"></script>

<script type="text/javascript" src="jquery.cookie.js"></script>

<script type="text/javascript">
    var jq = jQuery.noConflict();
    f1();
    function f1() {

        jq(document).ready(function() {

            var lastid = jq.cookie("maxletterid");
            if (lastid != "0") // if gridView already have any rows 
                               // so in the cookie is a row-id  that is not equal 
                               // to zero (the cookie initially set by Page_Init 
                               // method of the current page having the gridView) 
                jq.get("test2.aspx", function(dataReturned) {
                    if (dataReturned != "")    //if there are new rows in database 
                                                         //for adding to the gridView 
                    {
                        //jq('#<%=gvLetters.ClientID %>').prepend(dataReturned); //for
					// <asp:gridview showheader="False" ... />
                          jq('#<%=gvLetters.ClientID %> 
				tr:first').after(dataReturned); //for <asp:gridview 
                                                           //showheader="True" ... />

                    }
                    window.setTimeout("f1()", 1000); // gridView will be 
                                                  // updated every 1000 ms(1 second) 
                 });
            else
                jq.get("test2.aspx", function(dataReturned) {
                    if (dataReturned != "")     //if there are new rows in database 
                                                          //for adding to the gridView
                    {
                        jq('#<%=gvLetters.ClientID %>').html(dataReturned);
                    }
                    window.setTimeout("f1()", 1000);  // for calling function f1() 
                                                // every 1000 milliseconds(1 second)
                });
        });
    }    

Since the above code uses jQuery technology, note not to forget to copy the jquery-1.4.js and jquery.cookie.js files (related to jQuery itself and its cookie plugin) to your site's directory.

In the above code:

• replace gvLetters with the id of your gridView control
• replace test2.aspx with the name of your aspx or ashx page that gets the new rows from databse and send them back to the client (the current page that has the gridView shown to the client)
  If you want to send a request to the server (for getting new rows to add to gridView) every [x : x != 1000] milliseconds, change the 1000 in the window.setTimeout function

Step 2

Add the following C# code to codeBehind (.aspx.cs) of the page containing your gridView:

protected void Page_Init(object sender, EventArgs e)
    {       
        HttpCookie c1 = new HttpCookie("maxletterid", getMaxLetterId().ToString());
        Response.Cookies.Add(c1);
    }
    
    public static int getMaxLetterId()
    {
        int maxLetterId = 0;
        SqlConnection sqlc1 = new SqlConnection
	("Data Source=kazem-pc;Initial Catalog=test1;Integrated Security=True");
        string strcommand1 = "SELECT max(id) from tblTest";
        SqlCommand sqlcommand1 = new SqlCommand(strcommand1, sqlc1);

        SqlDataReader sqldr1 = null;

        try
        {
            sqlc1.Open();
            sqldr1 = sqlcommand1.ExecuteReader();

            while (sqldr1.Read())
            {
                maxLetterId = Convert.ToInt32(sqldr1[0]);

            }
            sqlc1.Close();
        }
        catch (Exception e1)
        {
            if (sqlc1.State == ConnectionState.Open)
                sqlc1.Close();
        }

        return maxLetterId;
    }

In the above code:

• replace test1 in Initial Catalog=test1; with the name of your SQL Server database
• replace id in SELECT max(id) from tblTest with the name of [id] column of your table in database (the table your gridView is binded to it) - the first column of the table commonly the primary-key that has identity specifications identified when table was created
• replace tblTest in SELECT max(id) from tblTest with the name of your table in database (the table your gridView is binded to it)

Step 3

Add the following C# code to codeBehind (.aspx.cs) of the second page (the page that is responsible for getting the client's request, then getting new rows from database and send them back to the client).

protected void Page_Load(object sender, EventArgs e)
    {
        string strReturn = string.Empty;
        int intfirstRead = 0;
        int lastoldid = Convert.ToInt32(Request.Cookies["maxletterid"].Value);

        // you can change the following two commands to adjust with your dataBase, table
        SqlConnection sqlc1 = new SqlConnection("Data Source=kazem-pc;
        Initial Catalog=test1;Integrated Security=True");
        string strcommand1 = "SELECT * FROM [tblTest] where id >  
        " + lastoldid.ToString() +" order by id desc";

        SqlCommand sqlcommand1 = new SqlCommand(strcommand1, sqlc1);

        SqlDataReader sqldr1 = null;

        try
        {
            sqlc1.Open();
            sqldr1 = sqlcommand1.ExecuteReader();

            if (sqldr1.HasRows)
            {
                intfirstRead = 0;
                while (sqldr1.Read())
                {
                    //------------------------------------------------------------------- 
                    //the following 'if' block is when  <asp:GridView ShowHeader="
                    //true" .../> in page "test1.aspx", so please comment 
                    //this 'if' when <asp:GridView ShowHeader="false" .../>
                    if ((lastoldid == 0) && (intfirstRead == 0))
                    {
                        strReturn = "<tr style=\"color:White;
                        background-color:#5D7B9D;font-weight:bold;\">";
                        for (int k = 0; k < sqldr1.FieldCount; k++)
                            strReturn += "<th scope=\"col\">" + 
                            sqldr1.GetName(k) + "</th>";

                        strReturn += "</tr>";

                        intfirstRead = 1;
                    }
                    //-------------------------------------------------------------------

                    strReturn += "<tr style=\"background-color:#D6E0ED; 
                    	font-style:italic;font-weight:bold;\">";
                    for (int j = 0; j < sqldr1.FieldCount; j++)
                        strReturn += "<td>" + sqldr1[j].ToString() + "</td>";

                    //strReturn += "<td>"+sqldr1["letterid"].ToString()+
                    "</td><td>"+sqldr1["subject"].ToString()+"</td>";
                    strReturn += "</tr>";
                }

                HttpCookie c1 = new HttpCookie
				("maxletterid", getMaxLetterId().ToString());
                Response.Cookies.Add(c1);
            }
            sqlc1.Close();
        }
        catch (Exception e1)
        {
            if (sqlc1.State == ConnectionState.Open)
                sqlc1.Close();

            strReturn = e1.Message;
        }

        Response.Expires = -1;
        Response.ContentType = "text/plain";
        Response.Write(strReturn);
        Response.End();
    } 

    public static int getMaxLetterId()
    {
        int maxLetterId = 0;
        SqlConnection sqlc1 = new SqlConnection("Data Source=kazem-pc;
        	Initial Catalog=test1;Integrated Security=True");
        string strcommand1 = "SELECT max(id) from tblTest";
        SqlCommand sqlcommand1 = new SqlCommand(strcommand1, sqlc1);

        SqlDataReader sqldr1 = null;

        try
        {
            sqlc1.Open();
            sqldr1 = sqlcommand1.ExecuteReader();

            while (sqldr1.Read())
            {
                maxLetterId = Convert.ToInt32(sqldr1[0]);
            }
            sqlc1.Close();
        }
        catch (Exception e1)
        {
            if (sqlc1.State == ConnectionState.Open)
                sqlc1.Close();
        }

        return maxLetterId;
    }

In the above code:

• replace test1, id, tblTest as explained above for the previous code
• in the test1.aspx, correct <asp:SqlDataSource ID="SqlDataSource1" .... > ConnectionString and SelectCommand values with your database and table names

Now you are ready to see the auto adding gridView, just create an insert page (like insert.aspx) to insert to your table, then browse test1.aspx and your insert pages at the same time, and then insert to your database and at the same time see the new rows will added to your gridView......... I'm waiting for your ideas to be sent to my mail.......

Running the Demo that you Download from Above Links in this Article

Please read ReadMe.txt first. This file is included in the zip file you've downloaded from the link at the top of this article.