Google

Wednesday, November 14, 2007

Adding Rows dynamically to a gridview [Part 2]

In our last post, we saw how to add a row dynamically to a gridview. But we still have to see how to persist values added on the client side to the server side. We shall not be using the recent AJAX based methodologies, instead we'll be using the good old fashioned way of persisting client side data to the server-side -- hidden variables. Before we move further, we need to make two changes to the gridview we created in our last post: 1) Add a hidden variable with runat="server", and id="hdnRowCount". This will contain the number of rows in the gridview when the gridview was created, so that we can identify which rows have been dynamically added by the user. 2) Add a hidden variable with runat="server" and id ="hdnPostAddedRows". This variable is responsible for persisting the changes we made on the client side to the server side. More on how to do this later in the article. Here is how the updated page looks like :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="WorkOrderID"
                DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="WorkOrderID" HeaderText="WorkOrderID" InsertVisible="False"
                        ReadOnly="True" SortExpression="WorkOrderID" />
                    <asp:BoundField DataField="ProductID" HeaderText="ProductID" SortExpression="ProductID" />
                    <asp:BoundField DataField="OrderQty" HeaderText="OrderQty" SortExpression="OrderQty" />
                    <asp:BoundField DataField="StockedQty" HeaderText="StockedQty" ReadOnly="True" SortExpression="StockedQty" />
                    <asp:BoundField DataField="ScrappedQty" HeaderText="ScrappedQty" SortExpression="ScrappedQty" />
                    <asp:BoundField DataField="StartDate" HeaderText="StartDate" SortExpression="StartDate" />
                    <asp:BoundField DataField="EndDate" HeaderText="EndDate" SortExpression="EndDate" />
                    <asp:BoundField DataField="DueDate" HeaderText="DueDate" SortExpression="DueDate" />
                    <asp:BoundField DataField="ScrapReasonID" HeaderText="ScrapReasonID" SortExpression="ScrapReasonID" />
                    <asp:BoundField DataField="ModifiedDate" HeaderText="ModifiedDate" SortExpression="ModifiedDate" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %> "
                SelectCommand="select top 10 * from Production.WorkOrder"></asp:SqlDataSource>
        </div>
        <input type="hidden" runat="server" id="hdnRowCount" />
        <input type="hidden" runat="server" id="hdnPostAddedRows" />
    </form>
</body>
</html>
Next, let us revise the problem we're trying to solve here : We are adding new rows to a gridview using client side script. These rows are not accessible on the server side, and there are no events being raised for these new rows on the server side. Our objective is to persist the rows that have been added, to the database. So, the question here is, "How do we access controls added to the page on the client side using Javascript, in our server side (or C# / VB.Net) code ?" To solve this, we are going to create a pipe-delimited string for all the values inserted on client side, and put that string in the hidden variables created above. For e.g., if the following three rows were added to our gridview Saurabh 45 Testing Data Description Swati 89 Some more description TestName 2322 End of new rows We shall convert this into the following string : Saurabh45Testing Data Description~~Swati89Some more description~~TestName2322End of new rows In the above case, the character "" is the column seperator, while the character combination "~~" is the row seperator. Once we put this string in one of the hidden variables created above, and post back the page. Upon postback, on the server side, we can still access the hidden variable, since we declared it with runat="server". We shall read values from the hidden variable, and run an insert query for each row inserted into the gridview. First, in your Page_Load method, populate the number of rows in the gridview in the hidden variable we created above (hdnRowCount)
protected void Page_Load(object sender, EventArgs e)
{
    hdnRowCount.Value = GridView1.Rows.Count;
}
Next, add a button "Save" to the page that triggers the save process :
<asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="Save" />
Also create a server side event handler btnSave_Click. Next, create a javascript function that will construct the pipe-delimited string we discussed above. Here's a short pseudocode :
{
    1. Get the number of rows originally present in the gridview using the hidden variable hdnRowCount
    2. Create a variable strExpression, and set it to blank ""
    2. Loop through all the newly added rows
    {
        Note : Ignore the first cell, since this is blank.
        3. Read the second cell, append a "" character at the end
        4. Read the third cell, append a "" character at the end
        .
        .
        .
        .
        8. Read the eigth cell, append a "" character at the end
        9. Append a row seperator character, "~~" at the end of the row
        10. Append the string created above to strExpression variable.
    }
    10. strExpression is the string we want. Assign this to the hidden variable "hdnPostAddedRows" so we can access this on the server side.
}
Before we dive into the code, let us ponder over one question. How do we access the values the user has inserted in the table cells. For this, we need to access the individual textboxes in the table cells. We can do this using : table.rows([rowcount]).cells([cell number]).childNodes(0).value This would first access the cell using table.rows([rowcount]).cells([cell number]), and extract the value of the first child of the cell (childNodes(0).value). Again, if one of the columns is a checkbox, you cannot do a .childNodes(0).value to get its value. The reason being, a checkbox does not have a "value" property. Instead, it has the "checked" property. So, to access a checkbox in a table, we need to use : table.rows([rowcount]).cells([cell number]).childNodes(0).checked Let us look at the code now :
            function fn_BuildPostback()
            {
                var rowCount = 0;
                var strExpression = '';
            var table = document.getElementById("GridView1");

    //        1. Get the number of rows originally present in the gridview using the     //        hidden variable hdnRowCount
                var originalRowCount = parseInt(document.getElementById("hdnRowCount").value);
           
            if ( originalRowCount == table.rows.length )
            {
                // No new rows have been added
                return "";
            }
 
    //    2. Loop through all the newly added rows            
                for ( rowCount = originalRowCount - 1; rowCount < table.rows.length ; rowCount++ )
                {
                    var str = '';
                    var operator = '';
                 
                    var tr = table.rows(rowCount);
        // Ignore the first column, move to the second one.
                    str = str + tr.cells(1).childNodes(0).value + '';                
                    str = str + tr.cells(2).childNodes(0).value + '';                
                    str = str + tr.cells(3).childNodes(0).value + '';                
                    str = str + tr.cells(4).childNodes(0).value + '';                
                    str = str + tr.cells(5).childNodes(0).value + '';                
                    str = str + tr.cells(6).childNodes(0).value + '';                
                    str = str + tr.cells(7).childNodes(0).value + '';                
                    str = str + tr.cells(8).childNodes(0).value + '';                

               strExpression = strExpression + str + '~~';
                }

                10. strExpression is the string we want. Assign this to the hidden variable "hdnPostAddedRows" so we can access this on the server side.
                document.getElementById('hdnPostAddedRows').value = strExpression;
            }

The above function just reads the dynamically added rows of the table one by one, and consolidates the contents of the rows into a single, pipe-delimited string. We assign this string to the hidden variable 'hdnPostAddedRows', which is also accessible on the server side in your c# code. The next thing that needs to be done is to call this function when the user clicks the button "btnSave". That is easy, just add the following line at the end of your page_load method : btnSave.Attributes.Add("onclick", "fn_BuildPostback();") If you run the code now, here's the sequence of events : 1. You dynamically add rows to the gridview. Enter some test values, and hit "Save" button. 2. That would create the pipe-delimited string, assign it to the hidden variable, and finally post back the page. 3. Upon postback, the btnSave_Click event handler is called. Inside the event handler, we can write code to split the string into individual rows, and fire insert statements (or stored procedures, as in this case) for each row. Here's a sample code that you might want to build upon :
    protected void btnSave_Click(object sender, EventArgs e)
    {
        string hdnExpr = hdnPostAddedRows.Value;
        int count = 0;
        int originalRowCount = Convert.ToInt32(hdnRowCount.Value.ToString().Trim());
     
        string [] exprArray = hdnExpr.Split("~~".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
        string [] rowArgs;
        SqlConnection conn = new SqlConnection(@"Data Source=wwwddsd331337\SQLExpress;Initial Catalog=TestDB;User Id=sd;Password=xyz;");
        conn.Open();

        foreach (string expression in exprArray)
        {
            // If this is an extra row added on client side
            if (count + 1 > originalRowCount)
            {
                SqlCommand command = new SqlCommand("re_InsertRow", conn);
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter param;
                rowArgs = expression.Split("".ToCharArray());

                param = new SqlParameter("@workorderid", rowArgs[0]);
                param.Direction = ParameterDirection.Input;
                param.DbType = (DbType)SqlDbType.Int;
                command.Parameters.Add(param);

                param = new SqlParameter("@productid", rowArgs[1]);
                param.Direction = ParameterDirection.Input;
                param.DbType = (DbType)SqlDbType.Int;
                command.Parameters.Add(param);

                param = new SqlParameter("@orderqty", rowArgs[2]);
                param.Direction = ParameterDirection.Input;
                param.DbType = (DbType)SqlDbType.VarChar;
                param.Size = 30;
                command.Parameters.Add(param);

                param = new SqlParameter("@stockedqty", rowArgs[3]);
                param.Direction = ParameterDirection.Input;
                param.DbType = (DbType)SqlDbType.VarChar;
                param.Size = 7;
                command.Parameters.Add(param);
            .
            .
            .
            .
            .
            // Add parameters for all columns
                }

                command.ExecuteNonQuery();
            }
            count++;
        }
    // Call a method to bind the gridview again.
        LoadGridData();
    }
That's it !! You've created a gridview with client-side insert functionality. Give yourself a pat on the back, and be prepared for all the good comments ur gonna receive from your website users for making their life easier.

8 comments:

Anonymous said...

Feel like providing the source code? Not sure how to interpret some of your pseudocode with out seeing the code itself.

Anonymous said...

Feel like posting a working example?

Simon said...

Thanks man, this helped me a lot.

Simon

Jagan said...

hi Swathi,

can you post some about edit the columns data , after populating data from database

raghvendra said...

I found This Post Most valuable and it works great

Anonymous said...

wonderful

Amit said...

Really nice article swathi. I am just beginning with .net but ur article [rather all ur blogs ] has explained all the things excellently.

www.oracledba.in said...

i like this