OleDbDataAdapter Update question

Question

Hello!

I have a DataTable with a lot of rows (Over a hundred million) and am writing an application that needs to insert into that table.

I will be using OleDbDataAdapter for the job and I am puzzled whats the best way to do this. I only need to insert into this enormous table, however I don't want to hard code the insert statement into application.

I figured I could use

OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("select * from table_name");
OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
...
adapter.Fill(data_set_name, "Table_name");

But this would be really bad since I don't need/want the data and the memory usage would be awful. So I was interesting if I could alter SelectCommand with TOP? It would look like so:

adapter.SelectCommand = new OleDbCommand("select TOP 1 * from table_name");

Now the Fill command would be really fast and I would have the data I needed for all the future insert statements. I could add rows to datatable and then just call

adapter.Update(data_set_name, "Table_name");

Would this work? And is this a valid / recommended way of doing this? It is really important that the application is fast and uses only the necessary resources. Is there a better way of doing this?

Thank you for your input!

Answer 1

If you don't need the data you can change the select command to

SELECT * FROM Table_Name WHERE 1=2

Then you won't get any rows back

Answer 2

IMO, the best way would be to:

1. Use the OleDbDataAdapter.FillSchema(data_set_name, SchemaType.Source) method to create the DataTable with a structure mapped from the datasource. You are basically trying to do the same thing by pulling a single row in your Select statement. Your Select statement in this case could remain "select * from table_name". I believe that you do not need to call the OleDbDataAdapter.Fill method now.

2. Instead of using a CommandBuilder, create your InsertCommand statement yourself.