Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Update Multiple Rows of GridView using JSON in ASP.NET

0.00/5 (No votes)
20 Jun 2014 1  
This article will talk about how to update multiple records in a GridView on a single button click using JSON in ASP.NET

Introduction

If you are a web developer using ASP.NET, then I assume that you must have played with GridView control several times. There is no doubt that GridView control is one of the most powerful controls that ASP.NET library has provided so far.

We must have used GridView control to allow user perform CRUD (Create Read Update Delete) operations in many occasions. In a typical scenario, user clicks on Edit button of a particular row, modifies data and clicks Update to make the changes in database.

Let’s consider that user wants to modify multiple records at a time. Once modifications are done, user will hit an Update button and all the modified data will be updated in the database.

User clicks the respective CheckBox to edit the row and enters data:

Once data entry is done, user clicks Update button to send the modified data to database:

Background

This can be achieved simply by looping through all the rows in a GridView. If the row is checked for edit, then execute an UPDATE statement or call a Stored Procedure to update the data in the database.

But in this approach, if we are updating 20 records, then we are going to hit database server 20 times. This approach may not be a very efficient way of updating bulk records.

What if we could pass the entire data (to be modified) to database in one shot and all the rows would get updated! Sounds good! But the BIG question is HOW?

We know about JSON which is a very lightweight technology to exchange data over the network. How about sending the data in JSON format to SQL Server! We can create a JSON string with all the required data and pass it to a Stored Procedure. Then the Stored Procedure will parse the data and create dynamic UPDATE statements and execute.

Challenge

The main challenge that we're going to face is to parse JSON string in our STORED PROC as there is no system function available in SQL Server till date. Then I found a saviour - an article on "Consuming JSON Strings in SQL Server" written by Phill Factor. He has written a wonderful JSON parser function in T-SQL named parseJSON() which accepts a JSON string and returns data into a temp table format. I'm going to utilize parseJSON() function to complete my demo.

Please make sure that you download and execute the parseJSON.sql script to create the require parser function. You may also visit the following link:

Database Changes

Let's quickly setup our database environment by creating a table - tblStudent and inserting some dummy data:

CREATE TABLE [dbo].[tblStudent](
    [StudentID] [int] NOT NULL,
    [Name] [varchar](50) NULL,
    [C] [int] NULL,
    [CPP] [int] NULL,
    [CS] [int] NULL
) ON [PRIMARY]

INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(1,'Amitabh Bachchan')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(2,'Saif Ali Khan')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(3,'Ranbir Kapoor')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(4,'Shahid Kapoor ')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(5,'Ajay Devgan')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(6,'Hritik Roshan')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(7,'Akshay Kumar')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(8,'Shahrukh Khan')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(9,'Amir Khan')
INSERT INTO [tblStudent] ([StudentID],[Name]) VALUES(10,'Salman Khan')

Using the Code

Let's first create a ASP.NET Empty Web Application and then add a new Web Form. Then add the following GridView control to display the details from [tblStudent] table.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="480px">
    <Columns>
        <asp:TemplateField ItemStyle-Width="20px">
           <ItemTemplate>
              <asp:CheckBox runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />
           </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="StudentID" HeaderText="ID" SortExpression="StudentID"
            ItemStyle-Width="25px" />
        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        <asp:TemplateField HeaderText="C" ItemStyle-HorizontalAlign="Center">
            <ItemTemplate>
                <asp:Label runat="server" Text='<%# Eval("C") %>'></asp:Label>
                <asp:TextBox ID="txtC" runat="server" Text='<%# Eval("C") %>'
                    Width="30px" Visible="false" MaxLength="3"></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="C++" ItemStyle-HorizontalAlign="Center">
            <ItemTemplate>
                <asp:Label runat="server" Text='<%# Eval("CPP") %>'></asp:Label>
                <asp:TextBox ID="txtCPP" runat="server" Text='<%# Eval("CPP") %>'
                    Width="30px" Visible="false" MaxLength="3"></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="C#" ItemStyle-HorizontalAlign="Center">
            <ItemTemplate>
                <asp:Label runat="server" Text='<%# Eval("CS") %>'></asp:Label>
                <asp:TextBox ID="txtCS" runat="server" Text='<%# Eval("CS") %>'
                    Width="30px" Visible="false" MaxLength="3"></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Code Explanation

<asp:CheckBox runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />

Here we have used a CheckBox control which has AutoPostback="true" so that it postbacks everytime user checks/unchecks any checkbox. We are going to handle the CheckedChanged event in code behind.

<asp:BoundField DataField="StudentID" HeaderText="ID" SortExpression="StudentID"
            ItemStyle-Width="25px" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />

The above two BoundField have been added to display ID and Name columns.

<asp:TemplateField HeaderText="C" ItemStyle-HorizontalAlign="Center" ItemStyle-Width="50px">
    <ItemTemplate>
        <asp:Label runat="server" Text='<%# Eval("C") %>'></asp:Label>
        <asp:TextBox ID="txtC" runat="server" Text='<%# Eval("C") %>' 
            Width="30px" Visible="false" MaxLength="3"></asp:TextBox>
    </ItemTemplate>
</asp:TemplateField>

The above TemplateField will be used to display marks column for the subject - C. In this column, we have two controls - a Label and a TextBox. Initially Label control will be visible and TextBox will remain hidden. Once user checks the respective CheckBox control, the Label control will become hidden and TextBox will be visible so that user can edit the marks. MaxLength="3" is also set so that user cannot enter more than 3 digits.

Similarly, we have two other columns for the subjects: C++ and C#

Let's quickly write down LoadData() to populate the GridView with the data from [tblStudent] table:

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        LoadData();
    }
}

private void LoadData()
{
    string consString = ConfigurationManager.ConnectionStrings["TEST"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(consString))
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM [tblStudent]", conn);
        cmd.CommandType = CommandType.Text;
        conn.Open();
        SqlDataReader drStudents = cmd.ExecuteReader();
        GridView1.DataSource = drStudents;
        GridView1.DataBind();
    }
}

By this point, our example should fetch the data from database and display on the GridView.

Now, let's add two more controls on our web page - a Button and a Label controls.

  • btnUpdate - User will click this button to update the changes in database.
  • lblMessage - To display nice message to the user.
<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click"
    Visible="false" />
<asp:Label ID="lblMessage" runat="server" ForeColor="Green"></asp:Label>

Note: Initially, the Button control is set Visible="false". Once user checks any CheckBox for edit, the Update button will become visible.

Let's move on to add the OnCheckedChanged event which will be fired everytime user checks/unchecks any row.

protected void OnCheckedChanged(object sender, EventArgs e)
{
    bool isUpdateVisible = false;
    Label1.Text = string.Empty;

    //Loop through all rows in GridView
    foreach (GridViewRow row in GridView1.Rows)
    {
        if (row.RowType == DataControlRowType.DataRow)
        {
            bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
            if (isChecked)
                row.RowState = DataControlRowState.Edit;

            for (int i = 3; i < row.Cells.Count; i++)
            {
                row.Cells[i].Controls.OfType<Label>().FirstOrDefault().Visible = !isChecked;
                if (row.Cells[i].Controls.OfType<TextBox>().ToList().Count > 0)
                {
                    row.Cells[i].Controls.OfType<TextBox>().FirstOrDefault().Visible = isChecked;
                }
                        
                if (isChecked && !isUpdateVisible)
                {
                    isUpdateVisible = true;
                }
            }
        }
    }
    btnUpdate.Visible = isUpdateVisible;
}

Code Explanation

bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
if (isChecked)
    row.RowState = DataControlRowState.Edit;

This line of code will get whether the CheckBox control on the first column is checked or not. If it's checked, then let's set the RowState as Edit. This will ensure that the row is displayed in Edit mode.

for (int i = 3; i < row.Cells.Count; i++)

Here we're looping through the other columns starting from 3 as we have to skip the two columns - ID and Name.

if (row.Cells[i].Controls.OfType<TextBox>().ToList().Count > 0)
{
    row.Cells[i].Controls.OfType<TextBox>().FirstOrDefault().Visible = isChecked;
}

In the above code, we're checking if there is a TextBox control found. If yes, then set the visibility as per the CheckBox checked value. If the CheckBox is checked, make the TextBox visible otherwise hide the TextBox control.

btnUpdate.Visible = isUpdateVisible;

Finally, we make sure that btnUpdate control is only visible when at least one checkBox is checked.

Final Step

I think the time has come when we should focus on how are going to send the data to SQL Server and update the data in database. As per our requirement, we'll be sending the data in a JSON string to a Stored Procedure which will parse the input JSON string and create UPDATE statements dynamically.

Here is a sample JSON string with details about two records:

[
 {"ID":"1","C":"60","CPP":"60","CS":"60"},
 {"ID":"2","C":"55","CPP":"60","CS":"60"}
]

So, we have to generate this string from our code behind and pass it to the Stored Procedure.

Before we write the code to generate the string, I would like to complete the Stored Procedure first.

CREATE PROC [dbo].[spUpdateMarks]
    @inputJSON VARCHAR(MAX)  -- '[{"ID":"1","C":"60","CPP":"60","CS":"60"}]'
AS
BEGIN
    DECLARE @UpdateSQL AS VARCHAR(MAX)
    DECLARE @Root_ID AS INT
    DECLARE @parent_ID AS INT
    DECLARE @StudentID AS INT
    DECLARE @C AS INT
    DECLARE @CPP AS INT
    DECLARE @CS AS INT

    -- Temp table to hold the parsed data
    DECLARE @TempTableVariable TABLE(
        element_id INT,
        sequenceNo INT,
        parent_ID INT,
        [Object_ID] INT,
        [NAME] NVARCHAR(2000),
        StringValue NVARCHAR(MAX),
        ValueType NVARCHAR(10)
    )
    -- Parse JSON string into a temp table
    INSERT INTO @TempTableVariable
    SELECT * FROM parseJSON(@inputJSON)
    
    -- Get the Root node ID
    SELECT @Root_ID = MAX([Object_ID]) FROM @TempTableVariable
    
    -- If there is only one record to update
    IF @Root_ID = 1
    BEGIN
        SELECT @StudentID = StringValue FROM @TempTableVariable
        WHERE NAME = 'ID' AND parent_ID = @Root_ID
        
        SELECT @C = StringValue FROM @TempTableVariable
        WHERE NAME = 'C' AND parent_ID = @Root_ID

        SELECT @CPP = StringValue FROM @TempTableVariable
        WHERE NAME = 'CPP' AND parent_ID = @Root_ID

        SELECT @CS = StringValue FROM @TempTableVariable
        WHERE NAME = 'CS' AND parent_ID = @Root_ID
        
        SET @UpdateSQL = ' UPDATE [tblStudent] ' +
                         ' SET C = ' + CAST(@C AS VARCHAR) + ',' +
                         ' CPP = ' + CAST(@CPP AS VARCHAR) + ',' +
                         ' CS = ' + CAST(@CS AS VARCHAR) +
                         ' WHERE StudentID = ' + CAST(@StudentID AS VARCHAR)
        EXECUTE(@UpdateSQL)
        --PRINT @UpdateSQL
    END
    ELSE
    BEGIN
        DECLARE curMarks CURSOR -- Declare cursor
        LOCAL SCROLL STATIC
        FOR
        SELECT [Object_ID] FROM @TempTableVariable
        WHERE [parent_ID] = @Root_ID
        
        OPEN curMarks -- open the cursor
        
        FETCH NEXT FROM curMarks
        INTO @parent_ID
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @StudentID = StringValue FROM @TempTableVariable
            WHERE NAME = 'ID' AND parent_ID = @parent_ID
            
            SELECT @C = StringValue FROM @TempTableVariable
            WHERE NAME = 'C' AND parent_ID = @parent_ID

            SELECT @CPP = StringValue FROM @TempTableVariable
            WHERE NAME = 'CPP' AND parent_ID = @parent_ID

            SELECT @CS = StringValue FROM @TempTableVariable
            WHERE NAME = 'CS' AND parent_ID = @parent_ID
            
            -- Create the UPDATE query dynamically and then execute
            SET @UpdateSQL = ' UPDATE [tblStudent] ' +
                             ' SET C = ' + CAST(@C AS VARCHAR) + ',' +
                             ' CPP = ' + CAST(@CPP AS VARCHAR) + ',' +
                             ' CS = ' + CAST(@CS AS VARCHAR) +
                             ' WHERE StudentID = ' + CAST(@StudentID AS VARCHAR)
            EXECUTE(@UpdateSQL)
            --PRINT @UpdateSQL
            
            FETCH NEXT FROM curMarks INTO @parent_ID
        END
    
        CLOSE curMarks -- close the cursor
        DEALLOCATE curMarks -- Deallocate the cursor
    END
END

Please consider the inline comments to understand the logic. Let me know if you need more clarifications, I'll update the article.

Let's complete our Update button click event to create a JSON string as shown above.

protected void btnUpdate_Click(object sender, EventArgs e)
{
    try
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("[");
        foreach (GridViewRow row in GridView1.Rows)
        {
            bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
            if (isChecked)
            {
                TextBox txtC = (TextBox)row.FindControl("txtC");
                TextBox txtCPP = (TextBox)row.FindControl("txtCPP");
                TextBox txtCS = (TextBox)row.FindControl("txtCS");

                sb.Append("{");
                sb.AppendFormat("\"ID\":\"{0}\",\"C\":\"{1}\",\"CPP\":\"{2}\",\"CS\":\"{3}\"", 
                                row.Cells[1].Text, txtC.Text, txtCPP.Text, txtCS.Text);
                sb.Append("},");
            }
        }

        if (sb.ToString().Length > 1)
        {
            sb.Append("]");

            string inputData = sb.ToString();

            string consString = ConfigurationManager.ConnectionStrings["TEST"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(consString))
            {
                SqlCommand cmd = new SqlCommand("spUpdateMarks", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@inputJSON", inputData);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            btnUpdate.Visible = false;
            lblMessage.Text = "Data updated successfully!";
            LoadData();
        }
        else
        {
            lblMessage.Text = "No value selected for update!";
        }
    }
    catch (SqlException ex)
    {
        lblMessage.Text = "error" + ex.ToString();
    }
}

Code Explanation

StringBuilder sb = new StringBuilder();
sb.Append("[");
foreach (GridViewRow row in GridView1.Rows)
{
    bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
    if (isChecked)
    {
        TextBox txtC = (TextBox)row.FindControl("txtC");
        TextBox txtCPP = (TextBox)row.FindControl("txtCPP");
        TextBox txtCS = (TextBox)row.FindControl("txtCS");

        sb.Append("{");
        sb.AppendFormat("\"ID\":\"{0}\",\"C\":\"{1}\",\"CPP\":\"{2}\",\"CS\":\"{3}\"", 
                        row.Cells[1].Text, txtC.Text, txtCPP.Text, txtCS.Text);
        sb.Append("},");
    }
}

The above lines of code are creating a StringBuilder object to create a JSON string by looping through all rows in the GridView and concatenating the required values.

Rest of the code is pretty straight forward which is doing basic ADO.NET operation to connect to the database and execute the Stored Procedure.

Code Optimization

Did you guys like the way I created the JSON string? Don't you think that it's bit clumsy?

Let's optimize the JSON string generation part of the article and try to make it more structured. There is a JavaScriptSerializer class provided in System.Web.Script.Serialization namespace which we'll be using to serialize our modified data into a JSON string.

Let's add a new class - Student.cs as follows:

public class Student
{
    public string ID { get; set; }
    public string C { get; set; }
    public string CPP { get; set; }
    public string CS { get; set; }
}

Now we'll modify the Update button click event to use Serialize() method of JavaScriptSerializer class to generate JSON string (see added codes in bold):

protected void btnUpdate_Click(object sender, EventArgs e)
{
    try
    {
        //StringBuilder sb = new StringBuilder();
        //sb.Append("[");
        List<Student> students = new List<Student>();

        foreach (GridViewRow row in GridView1.Rows)
        {
            bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
            if (isChecked)
            {
                TextBox txtC = (TextBox)row.FindControl("txtC");
                TextBox txtCPP = (TextBox)row.FindControl("txtCPP");
                TextBox txtCS = (TextBox)row.FindControl("txtCS");

                students.Add(new Student()
                {
                    ID = row.Cells[1].Text,
                    C = txtC.Text,
                    CPP = txtCPP.Text,
                    CS = txtCS.Text
                });

                //sb.Append("{");
                //sb.AppendFormat("\"ID\":\"{0}\",\"C\":\"{1}\",\"CPP\":\"{2}\",\"CS\":\"{3}\"",
                //row.Cells[1].Text, txtC.Text, txtCPP.Text, txtCS.Text);
                //sb.Append("},");
            }
        }

        //if (sb.ToString().Length > 1)
        if (students.Count > 0)
        {
            //sb.Append("]");
            //string inputData = sb.ToString();
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            string inputData = serializer.Serialize(students);

            string consString = ConfigurationManager.ConnectionStrings["TEST"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(consString))
            {
                SqlCommand cmd = new SqlCommand("spUpdateMarks", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@inputJSON", inputData);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            btnUpdate.Visible = false;
            lblMessage.Text = "Data updated successfully!";
            LoadData();
        }
        else
        {
            lblMessage.Text = "No value selected for update!";
        }
    }
    catch (SqlException ex)
    {
        lblMessage.Text = "error" + ex.ToString();
    }
}

Code Explanation

List<Student> students = new List<Student>();

Declared a List<> object of Student class to hold all the modified records details.

students.Add(new Student()
{
    ID = row.Cells[1].Text,
    C = txtC.Text,
    CPP = txtCPP.Text,
    CS = txtCS.Text
});

Adding new Student object to the List<Student> collection object.

JavaScriptSerializer serializer = new JavaScriptSerializer();
string inputData = serializer.Serialize(students);

Pretty simple code which is creating an object of JavaScriptSerializer class and calling Serialize() method by passing students List object. The Serialize() function will return a JSON string same as we created earlier manually. :)

Now the code looks better! Isn't it?

All required changes are done. You can execute the example and see how a single click is actually passing the entire data in a JSON format to the Stored Procedure. Then Stored Procedure is taking care of parsing the JSON by calling parseJSON() and executing the UPDATE statements after creating it dynamically.

Please share your comments or feedback. If you like this article, then please give your rating. :)

Happy coding :)

History

  • 20th June, 2014: Initial version
  • 26th June, 2014: Added Code Optimization section

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here