does Ado.Net 2.0 have bulk insert functionality?

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

Hello,

I have to read bulk data from a non RDBMS to Sql Server
2k. I tried using Ado.Net with dataAdapter.Fill, where
the app reads in 500,000 records to a dataTable in memory
and then applies dAdapt.Fill to fill a table in sql Server
2k. This hung the server up to the point where I had to
ctrl-alt-delete/close the program.

So, for my next trick, I was reading the data into a bunch
of delimited text files and looping through this set of
textfiles using a DTS package converted from vb6 to
vb.net. This is working except that after I finish
looping through all the text files (where I invoke the DTS
package for each text file) I get a nasty error message
that some memory couldn't be read. I have been
circumventing this error message with a SendKeys{Enter} to
get rid of the error message at the end of the procedure.
This is micky mouse and doesn't work 100% of the time.
So, does anyone know if Ado.Net 2.0 have bulk insert that
can perform data transfer like DTS or BCP?

If yes, is ADO.Net 2.0 contained in VS.Net2005? If not,
where can I get it?

Thanks,
Rich
 
Rich,

The ADO.NET team at Microsoft has really done wonders in 2.0. Not only are
the datatable/dataset improved a lot, so you wouldn't get or atleast get
lesser of the problems you describe below - a lot of operations in general
are a lot more faster. Not to mention - the usability features they have
added. 1/2 a million rows in a datatable is probably not the right use for
that object anyway. You might want to consider an alternate mechanism to
store that much data in memory. You can pin a table in memory - inside sql
server - which albeit isn't in a datatable, but might get you where you are
trying to get.

ADO.NET 2.0 amongst it's many many new fantastic features introduces a new
class called SqlBulkCopy. Here is a comparison of DTS/SqlBulkCopy and old
fashioned 1.1 style updates -
http://www.thedatafarm.com/blog/PermaLink.aspx?guid=15cf7a6c-d3d9-4633-8f69-6bbf62a4d33e

As you can see, ADO.NET 2.0 / SqlBulkCopy will let you do what you are
trying to do.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Maybe I wasn't exactly loading 500,000 recs in the data
table, I think it was like 20,000 rec and then using
dAdap.Fill and looping through the source data. But Fill
was too slow. So I did the same technique using the
delimited text files and DTS. I am thinking that the
datatable object would be the structure of choice to read
20,000 recs into so that I could use the DTS/SqlBulkCopy
functionality of Ado.Net2 and loop through that. Can a
datatable structure handle 20,000 recs (160 fields per
rec). If not maybe I could just load 10,000 recs at a
time. Or should I stick with the text files? Can
DTS/SqlBulkCopy read delimited text files like regular DTS?

I already have a beta version of VS.Net2005, but I have
not tried it yet (or loaded it yet). My company will pay
for the Enterprise version when it comes out of beta. I
can't wait!

Thanks for your reply
Rich
 
Thanks for the link. I think xml might be a little too
bulky for the volume of data I need to read and write. I
just hope that ADO.Net2 comes through with the
DTS/SqlBulkCopy feature. Does ADO.Net2 come with
VS.Net2005?

Thanks,
Rich
 
Bulk ops (one way or another are the way to go in your case). Yes VS 2005
(drop the .NET) comes with ADO.NET 2.0.
You can use BCP/DTS today from code. Try SQL DMO to activate it or use it
from a batch.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Thanks for the link. I think xml might be a little too
bulky for the volume of data I need to read and write. I
just hope that ADO.Net2 comes through with the
DTS/SqlBulkCopy feature. Does ADO.Net2 come with
VS.Net2005?

Thanks,
Rich
 
Yes, I converted a vb6 dts package to vb.net using the
convert wizard. My problem is that I have 4 packages and
I loop through them using an interface constuct. At the
end of all the looping I am getting a nasty error
message "unable to read memory at location ...". This is
why I am hoping that ADO.Net 2.0 has its own sqlbulkcopy
class. I think the problem I am having is that I
converted unmanaged code to vb.net, and there is one (or
maybe more) obscure thing that hasn't been accounted for
(I had to fix quite a few things with CType(...) after the
conversion and had to tweak a few lines to make the
packages work in a loop - like closing the package, re-
opening the same package, stuff that wasn't included in
the original DTS module.
 
Back
Top