Re: large dataSet (continued)

You might want to check out my csv parser/writer utility for this.
http://www.csvreader.com
It should write out the data directly from the DataTable to your csv
format in basically the fastest way possible. I'm also close to
releasing a new version that will work with 2.0's SqlBulkCopy class to
insert the rows directly into the database from the csv file. If you're
adventurous, and are working on 2.0, you can try creating an instance
of DataTableReader and pass it into the SqlBulkCopy class to insert the
rows directly, although I haven't yet benchmarked that method.

Bruce Dunwiddie

Francois Malgreve wrote:
> Linas:
> Ok but don't you think that doing 50.000 inserts into a remote DB could be
> an issue?
> Anyway I will give it a try and let you know. My application and some of my
> DB servers are literally at the opposite side of the planet. A ping takes
> already 250 ms.
>
> Miha:
> Actually if I could find a way to dump a DataSet efficiently (fast) in a
> simple format (such as CSV) and import it in SQL server that would be good
> enough :) As then I can call a DTS to read that file and import the data
> into SQL Server. DTS tasks are very fast to insert data in bulk and I would
> really like to be able to leverage that great feature of SQL Server. But
> well my main obstable so far has been to dump my DataSet into a flat file. I
> tried to save it as XML as it is a built in feature of DataSet and then run
> a XSLT on it to transform the data from XML format to CSV format but it is
> by FAR too slow and hangs the computer on which it runs - CPU 100% usage.
>
> Best regards,
>
> Francois.
>
>
>
> "Francois Malgreve" <francois.malgreveADgmail.com> wrote in message
> news:OzRn3c24FHA.3976@xxxxxxxxxxxxxxxxxxxxxxx
> > Hi,
> >
> > In a previous post I was asking how could I save (import) a large DataSet
> > (potentially 60.000 rows) into a SQL Server 2000 DB. It is a simple Data
> > transfer operation.
> > It seemed that the best way was to do it through a DTS.
> > I then started with a small DataSet that I saved into an Excel format.
> Then
> > I call the DTS that will import the excel file into the DataBase. This
> works
> > well.
> >
> > But my problem is that the performance is VERY VERY low. It takes 30
> seconds
> > to save a 250 rows Dataset into an excel ***. Then my question is, does
> > anyone know how to save a DataSet in Excel in a better way? I will give a
> > code snippet .
> > Or should I save my DataSet into an other format and change my DTS? What
> > other format could I use?
> >
> > Any help and clue would be very appreciated as I am kind of stuck here. I
> > did some research on the net, saw plenty of stuff to read from Excel but
> > much less to save on excel. Except something about Excel XML but it is not
> > an Excel file, just some XML format that Excel can understand.
> >
> > Code snippet:
> > Basically to insert all fields from the dataSet into the Excel ***, I
> just
> > loop through all rows, and for each rows loop through each columns. I then
> > insert manually the value from the DataSet into the Excel ***. Well I
> can
> > guess that looping like that is unefficient but how else can I do?
> >
> > ''dsExcelExport is the dataSet
> > ''Excel is the instance of excel.
> >
> > For intRow = 0 To dsExcelExport.Tables(TableNo).Rows.Count - 1
> > For intColumnValue = 0 To
> dsExcelExport.Tables(TableNo).Columns.Count -
> > 1
> > Excel.Cells(intRow + 2, intColumnValue + 1).Value =
> >
> dsExcelExport.Tables(TableNo).Rows(intRow).ItemArray(intColumnValue).ToStrin
> > g
> > Next
> > Next
> >
> > Best regards,
> >
> > Francois
> >
> >

.