asked on

Bulk Update of rows in data table in datagrid

I have a datagrid which is connected to a datatable.
This datatable has got around 7000 rows. I want to update 3 columns in all the 7000 rows.
Currently I am using following code :

this.dataGrid1.DataSource = null;

foreach(DataRow dr in DealsTable.Rows)
{
dr["MarketRate"] = "dummy";
dr["PandL"] = "dummy1";
dr["PandLUSD"] = "dummy2";
}
this.dataGrid1.DataSource = DealsView;

The foreach loop takes up lot of time. Is there a way where I can do the bulk update of these rows ?

Egore

To my knowledge, there is no way to do a bulk update. However, you may be able to gain some speed by switching to a typed DataSet. That way everything will be accessible as a property with all type information known.

Of course, your best bet would be to use a profiler to determine what part of this process is killing performance. It could easily be that iterating over each row in the DataTable is taking a long time, but updating each value is relatively quick. Or it could be the assignment of DealsView to dataGrid1.DataSource is taking up all of the time. At http://www.compuware.com/ they have a pretty good profiler that they give away for free (you will get called by their sales department, but they won't harass you if you aren't interested in their other products).

You could also try commenting out all portions of the code and timing it if you don't want to deal with a profiler.

graye

Well, you could do an update query against the underlying database, (using ExecuteScalar) and then completely reload the DataTable (using Fill)

The SQL statement would look lik this:

Update TableName Set MarketRate='dummy', PandL='dummy1', PandLUSD='dummy2'

I'd bet it'd be a tremendous speed improvement

JeraldX

ASKER

We have in memory representation of data table. It is not attached to any data base.
Well, one the possible solution can be to have two tables in dataset. These two tables are connected through referential integrity. We want to update the tables and then query the data present in these two tables and generate a result set. Actually we want to join these tables to get the final result. Is there any way to join these tables, just the way we do it in databases.

ASKER CERTIFIED SOLUTION

graye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial