Simple Multiple Row Insert Question

[Simon Harvey]

Hi everyone,

I have a pretty simple situation and I'm hoping that someone could give
me some advice on the best way to handle it.

I need to insert a whole load of rows into SQL Server 2005 using ado.net.

I use custom business objects to represent the rows. So for example, in
this specific case, I have a big collection of "Booking" objects.

I also have a stored procedure called CreateBooking.

My question is - assuming that I need to import say, 7000 rows in one
go, is simply looping through the Bookings collection and calling the
Stored Procedure for each booking the best way to do things?

Is there some other way to get that much data into the database without
constantly calling the stored procedure 7000 times.

I use the same connection each time, but still, it seems a bit clumsy.

I was wondering if there is a better way.

Many thanks to anyone who can advise...

Simon

PS: I really need to use ADO.net to do this - importing by some other
mechanism isn't going to be possible in this instance

 

[Jim Rand]

Upload the 7000 rows as a file and then do a bulk insert. You will be
amazed at the perfomance improvement.