GridView Update All Rows At Once
Few weeks back I wrote an article in which I explained how to change the state of the GridView in edit mode where all rows are replaced by the textboxes. You can check out the article at the link below:
http://gridviewguy.com/ArticleDetails.aspx?articleID=219
Serveral readers asked me how to send the data to the database at one go. Well, you can contruct an XML string or appending text and send the text to the database to be executed as a query. You need to be very careful when doing this sort of thing since, you might be inviting SQL Injections to the party. You can use Server.HtmlEncode to encode the strings and then send to them to be executed as a query.
Here is the Update method which iterates through all the rows in the Gridview and creates a long update statement.
private void Update()
{
StringBuilder sb = new StringBuilder();
// build the query
foreach (GridViewRow row in GridView1.Rows)
{
sb.Append("UPDATE Users SET FirstName = '");
sb.Append((row.FindControl("txtFirstName") as TextBox).Text);
sb.Append("',");
sb.Append("LastName = '");
sb.Append((row.FindControl("txtLastName") as TextBox).Text);
sb.Append("', ");
sb.Append("ClassCode = '");
sb.Append((row.FindControl("txtClassCode") as TextBox).Text);
sb.Append("'");
sb.Append(" WHERE UserID = ");
sb.Append(Convert.ToInt32((row.FindControl("lblUserID") as Label).Text));
sb.Append(" ");
}
string connectionString = "Server=HCUBE008;Database=School;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand(sb.ToString(), myConnection);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}