By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,649 Members | 2,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,649 IT Pros & Developers. It's quick & easy.

How to implement bulk insert into SQL Server with C#

P: n/a
Right now I develop an application to retrieve over 30,000 records from a binary file and then load them into a SQL Server DB. So far I load those records one by one, but the performance is very poor: it takes more than 6 hours to finish the loading.

So could I make use of the bulk-insert mechanism of SQL Server to reduce the loading time in C# codes? or other performance improvement solutions?
Nov 16 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
moonriver wrote:
Right now I develop an application to retrieve over 30,000 records from a binary file and then load them into a SQL Server DB. So far I load those records one by one, but the performance is very poor: it takes more than 6 hours to finish the loading.

So could I make use of the bulk-insert mechanism of SQL Server to reduce the loading time in C# codes? or other performance improvement solutions?


Hi moonriver,

as long as the ADO.NET doesn't provide an equivalent to IRowsetFastLoad
(OLEDB) you'll have to use BULK INSERT, BCP or DTS.

This article might help you in your decision which tool to use.
http://msdn.microsoft.com/library/en...t_bcp_67oh.asp

http://longhorn.msdn.microsoft.com/l...sqlclient.aspx
shows that bulk-insert-functionality might be build-in in .NET 2.0.

[BULK INSERT]
Reference of the BULK INSERT-Command:
http://msdn.microsoft.com/library/?u...ba-bz_4fec.asp

This should be easy to use in C#, something like:
SqlCommand cm = new SqlCommand();
cmd.CommandText = "BULK INSERT myData\nFROM '"
+ fileName + "'\nWITH (BATCHSIZE="
+ sqlCount.ToString()
+ ", FIELDTERMINATOR=';')";
//Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();

[bcp]
bcp can be used with System.Diagnostics.Process.Start
Reference:
http://msdn.microsoft.com/library/?u...p_bcp_61et.asp

[DTS]
AFAIK there is no DTS object library (class) for .NET so one solution is
to use the DTS objects through the COM/interop.

An Example is here:
http://sqldev.net/dts/ExecutePackage.htm

Another option will be to create a job with the DTS package as Step 1.
Then, you could use sp_start_job through a stored procedure that the
application executed through ADO.NET's command object.

HTH!

Cheers

Arne Janning

Nov 16 '05 #2

P: n/a
You can also make a stored procedure which has the bulk insert statement and
execute it through c#

Regards,

Sarfraz
"moonriver" <xi*******@yahoo.com> wrote in message
news:81**********************************@microsof t.com...
Right now I develop an application to retrieve over 30,000 records from a binary file and then load them into a SQL Server DB. So far I load those
records one by one, but the performance is very poor: it takes more than 6
hours to finish the loading.
So could I make use of the bulk-insert mechanism of SQL Server to reduce

the loading time in C# codes? or other performance improvement solutions?
Nov 16 '05 #3

P: n/a
Bulk insert's poor, it requires admin privileges and is slower than bcp
bcp is your best bet, to run it in fast mode make sure there are no indexes on the tabl
(if your table needs indexes, drop them, bcp in, then recreate the indexes... yes this I
the fastest way of doing it

Nov 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.