dataset error

[AVL]

Hi,
I've a requirement where in I need to read the data from a csv file and
load the data into the sql database. The file has around 1 lakh records. I'm
reading 300 records at a time from the file, loading into a dataset.
From the dataset, I'm reading the rows sequentially,and updating one by one
in to the database. This happens with a sql trnasaction.

The problem is here with the performance.. The file to be loaded exists on
one server and the database exists on another server. The loading process is
too slow
and it breaks with the below error
'The SqlTransaction is closed;it is no longer usable'.
I've searched on the google..but couldn't get appropriate resolution. Can
someone help me out?

 

[William Vaughn]

Ah no. ADO or ADO.NET were never intended to be bulk operation
interfaces--not until ADO.NET 2.0 which introduced access to the SQL Server
bulk copy interfaces. Check out the SqlBulkCopy class. It can import
flat-file data far, far faster than any query-based program. See
http://www.developer.com/db/article.php/3702826 or my book for more details.



--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Cowboy \(Gregory A. Beamer\)]

If you want speed, use a Process object to encapsulate BCP, the Bulk Copy
bits from SQL Server. As long as the file is set up correctly, or you have a
mapping file, you will find that it uploads very quickly. There is also
BULK_INSERT, if you need the additional features.

We successfully used this method to load files with millions of records (up
to 25 GB in size) into SQL Server, so I know it works.

One caveat if you are using IDENTITY on the table. If the system is actively
being added to, you should count the records first and then use the DBCC
command to increment the IDENTITY. You will then have to add identity to
each record (ripping through file with a FileReader/FileWriter?) prior to
uploading.

Another option is load the bits you need into the DataSet, as you are, and
then send the XML to a sproc that uses the XML features to insert records.
You basically end up using the DataSet XML as a "table" in the sproc. This
is slower than BCP, but works well when you cannot map.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************