Update Bulk Data Using GridView

In this post I am updating bulk data at a time using gridview with maintaing transaction.

below controls are under gridview template column.

<asp:Label ID=”lblID” runat=”server” Text=” Width=”30px”>

<asp:Label ID=”txtFaults” runat=”server” Text=” Width=”200px”>

<asp:DropDownList CssClass=”dropdown” ID=”ddlRepair” runat=”server” Width=”100px” SelectedValue=”>

<asp:TextBox CssClass=”textbox” ID=”txtRepairedRemarks” runat=”server” Text=” Width=”180px”>

———————————————————–
DataTable objDt = new DataTable();
SqlDataAdapter objAdapter;
SqlConnection cnn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con"].ToString());
SqlCommand cmd = new SqlCommand();

//Get values in GridView

cmd = new SqlCommand(“SP_Name”, cnn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(“@Parm”, SqlDbType.Int).Value = 12;

objAdapter = new SqlDataAdapter(cmd,cnn);

objAdapter.Fill(objDt);

gv.DataSource = objDt;
gv.DataBind();

//code to update data in database

private void BeginToUpdate()
{
cnn.Open();
SqlTransaction tran = cnn.BeginTransaction();
cmd.Connection = cnn;
cmd.Transaction = tran;
cmd.CommandText = ” UPDATE FAULTS SET REPAIRED=@parm WHERE FAULT_ID=@id”;
SqlParameter p1 = new SqlParameter(“@parm”, SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter(“@id”, SqlDbType.Int);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);

}

private void CompleteToUpdate()
{
try
{
cmd.Transaction.Commit();
}
catch (Exception ex)
{
//show error
}
finally
{
cnn.Close();
}
}

private void UpdateData(string repaired, Int32 id)
{
try
{
cmd.Parameters[0].Value = Convert.ToChar(repaired);
cmd.Parameters[1].Value = Convert.ToChar(id);
cmd.ExecuteNonQuery();
}
catch
{
cmd.Transaction.Rollback();
}
}

//write this under save/update button onClick event

BeginToUpdate();
foreach (GridViewRow row in gv.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
Int32 id = Convert.ToInt32(((Label)row.FindControl(“lblid”)).Text);
string repaired = ((DropDownList)row.FindControl(“ddlRepair”)).SelectedValue.ToString();

if (id > 0 && repaired==”R”)
{
UpdateData(repaired,id);
}
}
}

CompleteToUpdate();