Insert Command Best Approach

B

Bill

I'm creating a DataSet from an ODBC source that I'd like
to insert all rows into a local database table using the
Insert Command, but none of rows have RowState equal to
DataRowState.Added. To use the Insert Command, I loop
through my DataSet and insert rows into a new DataSet I
created. I then call the Insert Command and it works.

This is much faster than calling an Add method for each
row.

But my question: Is there a better way to do this?

Thanks,
Bill
 
V

Val Mazur

Hi Bill,

It depends on a database. I have made some performance tests some time ago
and in a case if you are using SQL Server 2000, you could pass your dataset
as an XML into the stored procedure and work with this XML inside of SP to
insert records in one batch. In some cases you could reduce time by several
times.
 
W

William Ryan eMVP

Hi Bill:

First off, cool name ;-)

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Bill said:
I'm creating a DataSet from an ODBC source that I'd like
to insert all rows into a local database table using the
Insert Command, but none of rows have RowState equal to
DataRowState.Added.

Ok, if you dont' have Rowstate of Added then the DataAdapter isn't going to
do any inserts when you call update.
http://www.knowdotnet.com/articles/efficient_pt4.html However, when you say
that none of the rows have Added as their rowstate but you have a populated
dataset, you may be able to get there rather easily. If you are using
another Database table to populate this dataset, you can set the
..AcceptChangesDuringFill property to false before you call DataAdapter.Fill
and the rowstate of each row will now be added.
http://www.knowdotnet.com/articles/efficient_pt4.html

By simply calling .Update on the same dataset/datatable with a different
adapter pointing to another db table (or using the same adapter but changing
the command text [I'd use two adapters though], you can automatically have
all the data inserted into the new table, provided of course no Integrity
Constraints (ie PK) are violated and all of the fields in the datatable
correspond to fields in the destination table.

To use the Insert Command, I loop
through my DataSet and insert rows into a new DataSet I
created. I then call the Insert Command and it works.

This is much faster than calling an Add method for each
row.

Well, the adapter simply loops through your dataset and then checks the
rowstate of each row. If the rowstate is added, it will use the Insert
command and depending on how you set up your Insert command, it will use the
column mappings and set each parameter in the command to the respective
field in the row.

So, the adapter is doing essentially the same thing you are when you loop
through it. The only difference is that by creating a new dataset so that
the Rowstate is added, you are adding in another layer of complexity. If
you can use the method I mention above, I would. Otherwise, it would be
more efficient to simply loop through it on your own adn build the update
statement. If you are using a DB that supports batch updates, you could add
them all together as one command (batch updates will be supported in ADO.NET
2.0 which is cool although doesn't do much good now) and minimize trips to
the db. I don't know how practical this would be if your table is very
large but it may be viable. Also, if you are going through each row on your
own, you probably may want to consider leaving the connection open at the
beginning of the loop and closing it at the end. This doesn't mean that
it's ok or recommended to leave connections open in general but the rule is
leave them open as long as necessary but no longer. Firing x queries in a
loop back to back is essentially the same processs so you may be able to
boost the speed by not opening and closing it over and over. This may or may
not be applicable here, again it depends on the size of the update. Anyway,
I hope this answers your question but if not, please let me know.

Cheers,

Bill
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top