Application hangs while updating large database records from C#

Question

0.00/5 (No votes)

See more:

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:

C#

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:

C#

List<SqlParameter> param = new List<SqlParameter>();

What I have tried:

C#

 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)
{
                                    
}
}
}

Posted 23-Mar-22 21:24pm

Devendra Sarang

Updated 24-Mar-22 0:40am

Deeksha Shenoy

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2022-03-23T22:42:00

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.

CHill60 · Answer 2 · 2022-03-23T22:48:00

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

Application hangs while updating large database records from C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0