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;
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) 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
DECLARE @TempTableVariable TABLE(
element_id INT,
sequenceNo INT,
parent_ID INT,
[Object_ID] INT,
[NAME] NVARCHAR(2000),
StringValue NVARCHAR(MAX),
ValueType NVARCHAR(10)
)
INSERT INTO @TempTableVariable
SELECT * FROM parseJSON(@inputJSON)
SELECT @Root_ID = MAX([Object_ID]) FROM @TempTableVariable
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)
END
ELSE
BEGIN
DECLARE curMarks CURSOR LOCAL SCROLL STATIC
FOR
SELECT [Object_ID] FROM @TempTableVariable
WHERE [parent_ID] = @Root_ID
OPEN curMarks
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
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)
FETCH NEXT FROM curMarks INTO @parent_ID
END
CLOSE curMarks DEALLOCATE curMarks 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
{
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
});
}
}
if (students.Count > 0)
{
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
