Hi RW,

> I write record by record from a datatable into an SQL Server database
> table.
> Is there a quicker way to do that than record by record?

Yes, .NET supports doing such things automatically, for example using a
so-called Data Adapter. For SQL Server, there's a class called
SqlDataAdapter which has a method called Update, which in turn takes a
DataTable and then calls the proper INSERT, UPDATE or DELETE statement for
each modified row in the DataTable.

See the method's documentation on MSDN:

http://msdn.microsoft.com/library/de...dateTopic3.asp

A Visual Basic example is also included.

Good luck!

--
Regards,

Mr. Jani Järvinen
C# MVP
Helsinki, Finland
(E-Mail Removed)
http://www.saunalahti.fi/janij/

Thanks Jani Järvinen,

Actualy I am using a data adapter, but that is too slow, because it opens
the connection, writes a record, closes the connection again and that for
every record in the datatable.

What I had in mind is a methode that opens the connection once, dumps the
records in a table very very fast and then clauses the connection again .
Speed is the main issue here. The records actual contains only 1 (sometimes
3) Guid item(s) but there are a lot of them (up to 125.000 records in one
run). So every millisecond counts.

--
RW


"Jani Järvinen [MVP]" wrote:

> Hi RW,
>
> > I write record by record from a datatable into an SQL Server database
> > table.
> > Is there a quicker way to do that than record by record?
>
> Yes, .NET supports doing such things automatically, for example using a
> so-called Data Adapter. For SQL Server, there's a class called
> SqlDataAdapter which has a method called Update, which in turn takes a
> DataTable and then calls the proper INSERT, UPDATE or DELETE statement for
> each modified row in the DataTable.
>
> See the method's documentation on MSDN:
>
> http://msdn.microsoft.com/library/de...dateTopic3.asp
>
> A Visual Basic example is also included.
>
> Good luck!
>
> --
> Regards,
>
> Mr. Jani Järvinen
> C# MVP
> Helsinki, Finland
> (E-Mail Removed)
> http://www.saunalahti.fi/janij/
>
>
>

Can you post your code? A data adapter does not open the connection,
write one record, and close the connection again, over and over again.

Robin S.
--------------------------------------
"RW" <(E-Mail Removed)> wrote in message
news:5EA4E5D1-6E67-483D-9C1A-(E-Mail Removed)...
> Thanks Jani Järvinen,
>
> Actualy I am using a data adapter, but that is too slow, because it
> opens
> the connection, writes a record, closes the connection again and that
> for
> every record in the datatable.
>
> What I had in mind is a methode that opens the connection once, dumps
> the
> records in a table very very fast and then clauses the connection
> again .
> Speed is the main issue here. The records actual contains only 1
> (sometimes
> 3) Guid item(s) but there are a lot of them (up to 125.000 records in
> one
> run). So every millisecond counts.
>
> --
> RW
>
>
> "Jani Järvinen [MVP]" wrote:
>
>> Hi RW,
>>
>> > I write record by record from a datatable into an SQL Server
>> > database
>> > table.
>> > Is there a quicker way to do that than record by record?
>>
>> Yes, .NET supports doing such things automatically, for example using
>> a
>> so-called Data Adapter. For SQL Server, there's a class called
>> SqlDataAdapter which has a method called Update, which in turn takes
>> a
>> DataTable and then calls the proper INSERT, UPDATE or DELETE
>> statement for
>> each modified row in the DataTable.
>>
>> See the method's documentation on MSDN:
>>
>> http://msdn.microsoft.com/library/de...dateTopic3.asp
>>
>> A Visual Basic example is also included.
>>
>> Good luck!
>>
>> --
>> Regards,
>>
>> Mr. Jani Järvinen
>> C# MVP
>> Helsinki, Finland
>> (E-Mail Removed)
>> http://www.saunalahti.fi/janij/
>>
>>
>>

Hi RW,

I second what Robin S. said; there's something wrong with your code.

You are supposed to create a SqlConnection object, then open the connection
to the database by calling Open(), and then using the SqlDataAdapter's
Update method to insert the records you have to the database. This should be
reasonably fast, even with 100.000+ rows.

Can you post some your code so that we could see what is the problem? What
kind of table definition do you use for your table(s) in SQL Server?

Thanks!

--
Regards,

Mr. Jani Järvinen
C# MVP
Helsinki, Finland
(E-Mail Removed)
http://www.saunalahti.fi/janij/

Hi RW,

I second what Robin S. said; there's something wrong with your code.

You are supposed to create a SqlConnection object, then open the connection
to the database by calling Open(), and then using the SqlDataAdapter's
Update method to insert the records you have to the database. This should be
reasonably fast, even with 100.000+ rows.

Can you post some your code so that we could see what is the problem? What
kind of table definition do you use for your table(s) in SQL Server?

Thanks!

--
Regards,

Mr. Jani Järvinen
C# MVP
Helsinki, Finland
(E-Mail Removed)
http://www.saunalahti.fi/janij/