CODEWITHSUNDEEP
c#oledb
David Božjak
David Božjak

Reputation: 17607

OleDbDataAdapter Update question

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!

Upvotes: 0

Views: 1867

Answers (2)

Cerebrus
Cerebrus

Reputation: 25775

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.

Upvotes: 1

Rune Grimstad
Rune Grimstad

Reputation: 36300

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

Upvotes: 1

Related Questions