How to delete thousands of rows? It's VERY slow?

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,

I'm conneting my dataset to an Access MDB file using the Jet 4 OLE
connector.

I read in my data using:

oleDbDataAdapter.Fill(myDataSet.E);

I display this dataset using a datagrid.

I need to allow the user to delete all the entries in the database. I
tried calling Clear() on the DataTable and myDataSet.E.Rows.Clear().
This removes the rows from the dataset, but when I call
oleDbDataAdapter.Update(energyDataSet.E) they aren't removed from the
database.

So I figured that I need to call .Delete() to have the RowState set
properly for each row. So I'm doing this:

for (int i=0; i < myDataSet.E.Rows.Count; i++)
{
myDataSet.E.Rows.Delete();
}

This takes a really long time. It takes 10 seconds to delete 50
records. Is this reasonable?

What should I be doing differently?

THANKS!
John
 
Hi John,

You might issues a sql statement directly (using OleDbCommand): DELETE FROM
YourTable.
It will do it in a breeze.
 
THANK YOU!

I did not realize I could issue SQL commands to Access.

John

Miha Markic said:
Hi John,

You might issues a sql statement directly (using OleDbCommand): DELETE FROM
YourTable.
It will do it in a breeze.
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

John said:
Hi,

I'm conneting my dataset to an Access MDB file using the Jet 4 OLE
connector.

I read in my data using:

oleDbDataAdapter.Fill(myDataSet.E);

I display this dataset using a datagrid.

I need to allow the user to delete all the entries in the database. I
tried calling Clear() on the DataTable and myDataSet.E.Rows.Clear().
This removes the rows from the dataset, but when I call
oleDbDataAdapter.Update(energyDataSet.E) they aren't removed from the
database.

So I figured that I need to call .Delete() to have the RowState set
properly for each row. So I'm doing this:

for (int i=0; i < myDataSet.E.Rows.Count; i++)
{
myDataSet.E.Rows.Delete();
}

This takes a really long time. It takes 10 seconds to delete 50
records. Is this reasonable?

What should I be doing differently?

THANKS!
John
Click to expand...
Click to expand...
 
Back
Top