My application hangs when performing large number of
update
operations.
I have one
DataGridView
.
Now I want to update any cell value from any row as per requirement.
So I am editing it, and on clicking on one single "
Save" button, all the row changes will be updated to the database. Right?
For that, I am using
foreach
loop and updating rows one by one from
datagridview
.
I think this is the reason why my application hangs.
So how can I update all datagridview
rows to the database using a single Save button?
One way can be by using:
StringBuilder updateQuery = new StringBuilder("");
This means append all the
update
query in "
updateQuery
" string builder and then apply update after exit of for loop but how can I pass SQL parameter along with it because I am using:
List<SqlParameter> param = new List<SqlParameter>();
What I have tried:
foreach (DataGridViewRow drupdateTimeSheet in grdTimeAndExp.Rows)
{
try
{
string Query = "UPDATE TS_Time SET JobListID =@JobListID,
Name =@Name, EmployeeDetailsId=@Empid,
TrackSubid =@TrackSubid, Time =@Time,
Description =@Description, status =@status,
BillState=@BillState,Date=@Date,
Comment=@Comment,AdminStatus=@AdminStatus,
TrackSubName=@TrackSubName where TimeSheetID=@TimeSheetID";
List<SqlParameter> param = new List<SqlParameter>();
string queryString1 = " SELECT Id
FROM EmployeeDetails where UserName = '" +
drupdateTimeSheet.Cells["TimeSheetUser"].Value.ToString() + "' ";
Int64 FetchEmpId = StMethod.GetSingleInt64(queryString1);
Int64 UpdateEmpId = Convert.ToInt64(FetchEmpId);
param.Add(new SqlParameter("@JobListID",
Convert.ToInt64(drupdateTimeSheet.Cells["JobListID"].Value.ToString())));
param.Add(new SqlParameter("@Name", drupdateTimeSheet.Cells["TimeSheetUser"].Value.ToString()));
param.Add(new SqlParameter("@EmpId", UpdateEmpId));
param.Add(new SqlParameter("@TrackSubid", drupdateTimeSheet.Cells["TrackSubid"].Value.ToString()));
param.Add(new SqlParameter("@TrackSubName", drupdateTimeSheet.Cells["TrackSubName"].Value.ToString()));
param.Add(new SqlParameter("@Time",
drupdateTimeSheet.Cells["Time"].Value.ToString()));
param.Add(new SqlParameter("@Description", drupdateTimeSheet.Cells["Description"].Value.ToString()));
param.Add(new SqlParameter("@status", drupdateTimeSheet.Cells["TimeItemNameSTATUS"].Value.ToString()));
param.Add(new SqlParameter("@BillState", drupdateTimeSheet.Cells["TimeItemNameBILLSTATE"].Value.ToString()));
string DueDate = string.Empty;
DueDate = drupdateTimeSheet.Cells["Date"].Value.ToString();
DateTime datevalue = (Convert.ToDateTime(DueDate.ToString()));
string s1 =
DateTime.Parse(datevalue.ToString()).ToString("MM/dd/yyyy hh:mm:ss tt");
param.Add(new SqlParameter("@Date", s1.ToString()));
param.Add(new SqlParameter("@Comment", drupdateTimeSheet.Cells["Comment"].Value.ToString()));
if (Properties.Settings.Default.timeSheetLoginUserType == "Admin")
{
param.Add(new SqlParameter("@AdminStatus",
drupdateTimeSheet.Cells["AdminStatus"].Value.ToString()));
}
else
{
param.Add(new SqlParameter("@AdminStatus",
drupdateTimeSheet.Cells["AdminStatus"].Value.ToString()));
}
param.Add(new SqlParameter("@TimeSheetID", drupdateTimeSheet.Cells["TimeSheetID"].Value.ToString()));
if (StMethod.UpdateRecord(Query, param) > 0)
{
}
}
}