ADO.net, asp.net & Bulk Copy

[Francis]

Hi everyone,
Just wondering if someone could help me. I have a CSV file of around
100,000 entries. The application I am building will accept a CSV upload
from the user and insert it into the database.
I noticed, using the normal insert into a Data Table, then out into the
database was very slow. I looked round and found that bulk copying would
be the best way. However. I have 2 questions:
1. Is there a way of uploading a file to the sql server from another PC?
2. Is it possible to manipulate the data before bulk inserting?
3. Is it possible to just bulk insert the data from a Data Table/ Data Set?

Thank you
Francis

 

[Elton W]

1. Is there a way of uploading a file to the sql

server from another PC?

Yes. You can use bulk insert text file to Db:

command.CommandText = "BULK INSERT db_table_name
FROM 'filepath\filename' WITH
(DATAFILETYPE = 'char', FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')"
command.ExecuteNonQuery()

2. Is it possible to manipulate the data before

bulk inserting?
Yes. But you need read data to datatable first.

3. Is it possible to just bulk insert the data

from a Data Table/ Data Set?
In ADO.NET 2.0, SqlBulkCopy can write dataset to db
directly. In ADO.NET 1.X, you need implement your code.
Look at following URL
http://support.microsoft.com/kb/315968/EN-US/

HTH

Elton Wang
(e-mail address removed)

 

[Francis]

Hi,
Thanks for the reply. Just trying that out now, but getting the error:
"You do not have permission to use the BULK INSERT statement" although I
am setup as db_owner.
Thanks for your help.

 

[Guest]

Members of the bulkadmin role are allowed to execute the BULK INSERT
statement. So you need to add the user to the Fixed server role.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_6ndx.asp

HTH

Elton Wang