How To Insert DataTable Into MS Access (Record at a time too slow)

[oopman2002]

I have created a DataSet DataTable which is comprised of data from a
number of sources. The resultant datatable is about 500,000 records and
writing them out one at a time takes forever. Does anyone have some
example code or tell me how to dump a datatable to Access.

One problem is that the datatable structure changes and I would like to
create a generic method, which scans the datatable column types and
builds the Access table on the fly. My current method works all the way
upto writing the data out to an Access table which is terribly slow.
Writing the same data in VBA takes about 1 minute to process 400,000
records but under C# it would take hours if I had the patience to let
it run.

The problem that I see is that the OleDbCommand must be opened and
closed during the record write and that must be where it is falling on
its face. If I open the command once, I could not get the data to
change that was being written to the file. It just wrote the first
record over and over. It ignored the changing parameter values. I even
reset the parameters and recreated them to no avail.

Any help especially some sample code would be greatly appreciated!

[Robbe Morris [C# MVP]]

..NET + OleDb + Microsoft Access = slow

You may need to go with COM and DAO.

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Microsoft .NET Search Engine Scoring Analysis
How does your site rate?
http://www.topichound.com


"oopman2002" <oo********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>I have created a DataSet DataTable which is comprised of data from a
number of sources. The resultant datatable is about 500,000 records and
writing them out one at a time takes forever. Does anyone have some
example code or tell me how to dump a datatable to Access.

One problem is that the datatable structure changes and I would like to
create a generic method, which scans the datatable column types and
builds the Access table on the fly. My current method works all the way
upto writing the data out to an Access table which is terribly slow.
Writing the same data in VBA takes about 1 minute to process 400,000
records but under C# it would take hours if I had the patience to let
it run.

The problem that I see is that the OleDbCommand must be opened and
closed during the record write and that must be where it is falling on
its face. If I open the command once, I could not get the data to
change that was being written to the file. It just wrote the first
record over and over. It ignored the changing parameter values. I even
reset the parameters and recreated them to no avail.

Any help especially some sample code would be greatly appreciated!

[oopman2002]

That is what I figured except I can't find much in the way of help for
this. I'm familiar with doing it in VB6 but am not sure about the
calling process with c#. Do you have any links that may help?

Thanks
Lyle

Robbe Morris [C# MVP] wrote:

.NET + OleDb + Microsoft Access = slow

You may need to go with COM and DAO.

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Microsoft .NET Search Engine Scoring Analysis
How does your site rate?
http://www.topichound.com


"oopman2002" <oo********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

I have created a DataSet DataTable which is comprised of data from a
number of sources. The resultant datatable is about 500,000 records and
writing them out one at a time takes forever. Does anyone have some
example code or tell me how to dump a datatable to Access.

One problem is that the datatable structure changes and I would like to
create a generic method, which scans the datatable column types and
builds the Access table on the fly. My current method works all the way
upto writing the data out to an Access table which is terribly slow.
Writing the same data in VBA takes about 1 minute to process 400,000
records but under C# it would take hours if I had the patience to let
it run.

The problem that I see is that the OleDbCommand must be opened and
closed during the record write and that must be where it is falling on
its face. If I open the command once, I could not get the data to
change that was being written to the file. It just wrote the first
record over and over. It ignored the changing parameter values. I even
reset the parameters and recreated them to no avail.

Any help especially some sample code would be greatly appreciated!

[Robbe Morris [C# MVP]]

It is just COM. You include the COM reference
in your .NET project and go.

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Microsoft .NET Search Engine Scoring Analysis
How does your site rate?
http://www.topichound.com


"oopman2002" <oo********@yahoo.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

That is what I figured except I can't find much in the way of help for
this. I'm familiar with doing it in VB6 but am not sure about the
calling process with c#. Do you have any links that may help?

Thanks
Lyle

Robbe Morris [C# MVP] wrote:

>.NET + OleDb + Microsoft Access = slow

You may need to go with COM and DAO.

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Microsoft .NET Search Engine Scoring Analysis
How does your site rate?
http://www.topichound.com


"oopman2002" <oo********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googleg roups.com...

>I have created a DataSet DataTable which is comprised of data from a
number of sources. The resultant datatable is about 500,000 records and
writing them out one at a time takes forever. Does anyone have some
example code or tell me how to dump a datatable to Access.

One problem is that the datatable structure changes and I would like to
create a generic method, which scans the datatable column types and
builds the Access table on the fly. My current method works all the way
upto writing the data out to an Access table which is terribly slow.
Writing the same data in VBA takes about 1 minute to process 400,000
records but under C# it would take hours if I had the patience to let
it run.

The problem that I see is that the OleDbCommand must be opened and
closed during the record write and that must be where it is falling on
its face. If I open the command once, I could not get the data to
change that was being written to the file. It just wrote the first
record over and over. It ignored the changing parameter values. I even
reset the parameters and recreated them to no avail.

Any help especially some sample code would be greatly appreciated!

[oopman2002]

Robbe,

First thanks for responding and I'm sorry but I'm not as familiar with
this as you are and it doesn't seem as easy to me as including a COM
reference.

1 ) I'm assuming that when you say a COM reference you are meaning
ADODB
2 ) If you look at the available options, I'm not sure which one to
use.
For example: ADODB._Connection, .Connection, .Connection15,
..ConnectionClass
Similarily for recordset _Recordset, .Recordset, .Recordset15, 20, 21,
..RecordsetClass
3 ) By trial and error, I've managed to get a program running but I
don't know if I've created problems waiting to surface or not.
4 ) What would be nice would be a simple program that wrote some stuff
out to an Access database.

Thanks again and sorry to bitch but this has been a really frustrating
experience.

Lyle

Robbe Morris [C# MVP] wrote:

It is just COM. You include the COM reference
in your .NET project and go.

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Microsoft .NET Search Engine Scoring Analysis
How does your site rate?
http://www.topichound.com


"oopman2002" <oo********@yahoo.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

That is what I figured except I can't find much in the way of help for
this. I'm familiar with doing it in VB6 but am not sure about the
calling process with c#. Do you have any links that may help?

Thanks
Lyle

Robbe Morris [C# MVP] wrote:

.NET + OleDb + Microsoft Access = slow

You may need to go with COM and DAO.

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Microsoft .NET Search Engine Scoring Analysis
How does your site rate?
http://www.topichound.com


"oopman2002" <oo********@yahoo.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
I have created a DataSet DataTable which is comprised of data from a
number of sources. The resultant datatable is about 500,000 records and
writing them out one at a time takes forever. Does anyone have some
example code or tell me how to dump a datatable to Access.

One problem is that the datatable structure changes and I would like to
create a generic method, which scans the datatable column types and
builds the Access table on the fly. My current method works all the way
upto writing the data out to an Access table which is terribly slow.
Writing the same data in VBA takes about 1 minute to process 400,000
records but under C# it would take hours if I had the patience to let
it run.

The problem that I see is that the OleDbCommand must be opened and
closed during the record write and that must be where it is falling on
its face. If I open the command once, I could not get the data to
change that was being written to the file. It just wrote the first
record over and over. It ignored the changing parameter values. I even
reset the parameters and recreated them to no avail.

Any help especially some sample code would be greatly appreciated!