In this GridView is getting populated from database using SqlDataSource, I've put checkbox in first column of gridview using ItemTemplate and textbox in ItemTemplate to display records
Html markup of the aspx page is
BorderWidth="0px" >
BorderStyle="none" BorderWidth="0px">
DeleteCommand="DELETE FROM Details WHERE (ID = @ID)"
UpdateCommand="UPDATE [Details] SET [Name] = @Name,
[Location] = @Location WHERE [ID] = @ID">
For Delete Confirmation write this JavaScript in head section of page . This script is called by delete button by specifying OnClientClick attribute in html code of button
In the code behnd i've created a StringBuilder to store update commands separated by ; for records to be edited. than looping through gridview rows to find checked rows, then find the value in textbox using findControl.
C# code behind for btnUpdate_Click
public partial class _Default : System.Web.UI.Page
{
//Define global Connection String
string strConnection=ConfigurationManager.ConnectionStrings
["ConnectionString"].ConnectionString;
protected void btnUpdate_Click(object sender, EventArgs e)
{
//Create stringbuilder to store multiple DML statements
StringBuilder strSql = new StringBuilder(string.Empty);
//Create sql connection and command
SqlConnection con = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand();
//Loop through gridview rows to find checkbox
//and check whether it is checked or not
for (int i = 0; i < GridView1.Rows.Count; i++) { CheckBox chkUpdate = (CheckBox) GridView1.Rows[i].Cells[0].FindControl("chkSelect"); if (chkUpdate != null) { if (chkUpdate.Checked) { // Get the values of textboxes using findControl string strID = GridView1.Rows[i].Cells[1].Text; string strName = ((TextBox) GridView1.Rows[i].FindControl("txtName")).Text; string strLocation = ((TextBox) GridView1.Rows[i].FindControl("txtLocation")).Text; string strUpdate = "Update Details set Name = '" + strName + "'," + + " Location = '" + strLocation + "'" + + " WHERE ID ='" + strID +"'" +";" ; //append update statement in stringBuilder strSql.Append(strUpdate); } } } try { cmd.CommandType = CommandType.Text; cmd.CommandText = strSql.ToString(); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { string errorMsg = "Error in Updation"; errorMsg += ex.Message; throw new Exception(errorMsg); } finally { con.Close(); } UncheckAll(); } This will update all records by connection to database only one time But this method is not considered good as it is vulnerable to sql injection so we can use Sql parameters instead try { string strUpdate = "Update Details set Name = @Name,"+ +" Location = @Location WHERE ID = @ID"; cmd.CommandType = CommandType.Text; cmd.CommandText = strUpdate.ToString(); cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Name", strName); cmd.Parameters.AddWithValue("@Location", strLocation); cmd.Parameters.AddWithValue("@ID", strID); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { string errorMsg = "Error in Updation"; errorMsg += ex.Message; throw new Exception(errorMsg); } finally { con.Close(); } For deleting selected records at once write this code in click event of Delete button protected void btnDelete_Click(object sender, EventArgs e) { //Create String Collection to store IDs of //records to be deleted StringCollection idCollection = new StringCollection(); string strID = string.Empty; //Loop through GridView rows to find checked rows for (int i = 0; i < GridView1.Rows.Count; i++) { CheckBox chkDelete = (CheckBox)GridView1.Rows[i]. Cells[0].FindControl("chkSelect"); if (chkDelete != null) { if (chkDelete.Checked) { strID = GridView1.Rows[i].Cells[1].Text; idCollection.Add(strID); } } } if (idCollection.Count > 0)
{
//Call the method to Delete records
DeleteMultipleRecords(idCollection);
// rebind the GridView
GridView1.DataBind();
}
else
{
lblMessage.Text = "Please select any row to delete";
}
}
private void DeleteMultipleRecords(StringCollection idCollection)
{
//Create sql Connection and Sql Command
SqlConnection con = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand();
string IDs = "";
foreach (string id in idCollection)
{
IDs += id.ToString() + ",";
}
try
{
string test = IDs.Substring
(0, IDs.LastIndexOf(","));
string sql = "Delete from Details"+
+" WHERE ID in (" + test + ")";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
string errorMsg = "Error in Deletion";
errorMsg += ex.Message;
throw new Exception(errorMsg);
}
finally
{
con.Close();
}
}
Write this code in the CheckedChanged Event of CheckBox
protected void chkSelect_CheckedChanged
(object sender, EventArgs e)
{
CheckBox chkTest = (CheckBox)sender;
GridViewRow grdRow = (GridViewRow)chkTest.NamingContainer;
TextBox txtname = (TextBox)grdRow.FindControl
("txtName");
TextBox txtlocation = (TextBox)grdRow.FindControl
("txtLocation");
if (chkTest.Checked)
{
txtname.ReadOnly = false;
txtlocation.ReadOnly = false;
txtname.ForeColor = System.Drawing.Color.Black;
txtlocation.ForeColor = System.Drawing.Color.Black;
}
else
{
txtname.ReadOnly = true;
txtlocation.ReadOnly = true;
txtname.ForeColor = System.Drawing.Color.Blue;
txtlocation.ForeColor = System.Drawing.Color.Blue;
}
}
private void UncheckAll()
{
foreach (GridViewRow row in GridView1.Rows)
{
CheckBox chkUncheck = (CheckBox)
row.FindControl("chkSelect");
TextBox txtname = (TextBox)
row.FindControl("txtName");
TextBox txtlocation = (TextBox)
row.FindControl("txtLocation");
chkUncheck.Checked = false;
txtname.ReadOnly = true;
txtlocation.ReadOnly = true;
txtname.ForeColor = System.Drawing.Color.Blue;
txtlocation.ForeColor = System.Drawing.Color.Blue;
}
}
Second Method:
This methis is better described here
public partial class _Default : System.Web.UI.Page
{
//Define global Connection String
string strConnection = ConfigurationManager.ConnectionStrings
["ConnectionString"].ConnectionString;
private bool tableCopied = false;
private DataTable originalTable;
protected void GridView1_RowDataBound
(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (!tableCopied)
{
originalTable = ((System.Data.DataRowView)
e.Row.DataItem).Row.Table.Copy();
ViewState["originalValues"] = originalTable;
tableCopied = true;
}
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
originalTable = (DataTable)ViewState["originalValues"];
foreach (GridViewRow row in GridView1.Rows)
if(IsRowModified(row))
{
GridView1.UpdateRow(row.RowIndex,false);
}
tableCopied = false;
GridView1.DataBind();
}
protected bool IsRowModified(GridViewRow row)
{
int currentID;
string currentName;
string currentLocation;
currentID = Convert.ToInt32(GridView1.DataKeys
[row.RowIndex].Value);
currentName = ((TextBox)row.FindControl
("txtName")).Text;
currentLocation = ((TextBox)row.FindControl
("txtLocation")).Text;
System.Data.DataRow newRow = originalTable.Select
(String.Format("ID = {0}", currentID))[0];
if (!currentName.Equals(newRow["Name"].ToString()))
{ return true; }
if (!currentLocation.Equals(newRow["Location"].ToString()))
{ return true; }
return false;
}
For records deletion
protected void btnDelete_Click(object sender, EventArgs e)
{
originalTable = (DataTable)ViewState["originalValues"];
foreach (GridViewRow row in GridView1.Rows)
{
CheckBox chkDelete = (CheckBox)row.FindControl
("chkSelect");
if(chkDelete.Checked)
{
GridView1.DeleteRow(row.RowIndex);
}
}
tableCopied = false;
GridView1.DataBind();
}
Download the sample code attached
http://www.box.net/shared/4v7kthgvyo
No comments :
Post a Comment