How to implement bulk insert into SQL Server with C#

[moonriver]

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?

[Arne Janning]

Re: How to implement bulk insert into SQL Server with C#

moonriver wrote:
[color=blue]
> 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?[/color]

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-us/adminsql/ad_impt_bcp_67oh.asp

http://longhorn.msdn.microsoft.com/lhsdk/ref/system.data.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/?url=/library/en-us/tsqlref/ts_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.ToStri ng()
+ ", FIELDTERMINATOR =';')";
//Console.WriteLi ne(cmd.CommandT ext);
cmd.ExecuteNonQ uery();

[bcp]
bcp can be used with System.Diagnost ics.Process.Sta rt
Reference:

http://msdn.microsoft.com/library/?url=/library/en-us/coprompt/cp_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

[Sarfraz Hooda]

Re: How to implement bulk insert into SQL Server with C#

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

Regards,

Sarfraz
"moonriver" <xiaodan98@yaho o.com> wrote in message
news:813B4BEC-D515-40D8-AECA-DCF75274E309@mi crosoft.com...[color=blue]
> Right now I develop an application to retrieve over 30,000 records from a[/color]
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.[color=blue]
>
> So could I make use of the bulk-insert mechanism of SQL Server to reduce[/color]
the loading time in C# codes? or other performance improvement solutions?

[B 0nj]

RE: How to implement bulk insert into SQL Server with C#

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