Andrzej Siminski

Andrzej Siminski

  • NA
  • 1
  • 22.9k

Adding new rows to .dbf database. DAtaAdapter.Update is really slow.

Oct 11 2010 2:12 AM
Hi!
I have problem with inserting new rows to dbf database.
I have dataTable with specified rows, and (empty) dbf db, and i want to insert them into db as quick as possible.

public void load(DataTable data)
{
DataSet ds = new DataSet();

string strAccessConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FolderPath + ";Extended Properties=dBASE IV;";
string strAccessSelect = "SELECT * FROM " + TableName;

OleDbConnection myAccessConn = null;
try
{
myAccessConn = new OleDbConnection(strAccessConn);
}
catch (Exception e){throw;}

OleDbDataAdapter myDataAdapter = null;
OleDbTransaction myTransaction = null;
try
{
OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
myDataAdapter = new OleDbDataAdapter(myAccessCommand);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter);
myDataAdapter.InsertCommand=custCB.GetInsertCommand();
myDataAdapter.ContinueUpdateOnError=true;
myAccessConn.Open();

myDataAdapter.Fill(ds, TableName);
ds.Merge(data);
myTransaction = myAccessConn.BeginTransaction();
myDataAdapter.InsertCommand.Transaction = myTransaction;
myDataAdapter.Update(ds, TableName);
myTransaction.Commit();
}
catch (Exception ex){throw;}
finally
{
myAccessConn.Close();
}
}


This code adds rows to database, but it is extremely slow (adding 10 rows is about 600 ms).
Application will have to add about 8000 rows, so that code is too slow.

I'd be grateful if anyone helps me with this problem.

PS: Since database is empty in the begining, saving dataTable to dbf would work as well.