Click here to Skip to main content
15,614,421 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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)

	string Query = "UPDATE TS_Time SET  JobListID =@JobListID, 
                    Name =@Name, EmployeeDetailsId=@Empid, 
                    TrackSubid =@TrackSubid, Time =@Time, 
                    Description =@Description, status =@status, 
                    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", 

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", 
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", 
    param.Add(new SqlParameter("@AdminStatus", 
param.Add(new SqlParameter("@TimeSheetID", drupdateTimeSheet.Cells["TimeSheetID"].Value.ToString()));

if (StMethod.UpdateRecord(Query, param) > 0)
Updated 24-Mar-22 0:40am

When you run any time-intensive operation on the default thread, the application can't do anything else (like update the display or respond to user inputs) until the long running method exits.

So what you need to do is move it to a different thread.
There are a lot of different ways to do that, but the simplest is to use a BackgroundWorker[^] - it allows you to show progress easily as well as it knows when it is finished (this link includes code examples) - which is handy for the user!

You would need to change how you do it, as you can only access controls (such as the DataGridView) from the original UI thread - so I'd suggest you use the underlying DataSource of the DGV instead of the rows themselves. If you don't use a DataSource, then construct a DataTable or a List<MyClass> to hold the data and pass that to the BackgroundWorker to process.
Share this answer
Databases are set based - so don't do this line by line. There are several similar questions here on CodeProject that offer more appropriate solutions e.g. C# how to save changes in datagridview to sql?[^] - and if you look to the right side of that page there will be links to others.

Not an answer to your question but a problem you should address. Some of your code is vulnerable to SQL Injection attack because you are creating the sql statement by concatenating user input. See SQL Injection | OWASP Foundation[^] for why this is a bad idea and
SQL Injection Prevention - OWASP Cheat Sheet Series[^].

You are already using a parameterised query so do the same with queryString1
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900