C# bulk copy

Question

Post reply

C# bulk copy

jjzzmp

Old Hand

Points: 352
More actions
August 11, 2011 at 8:51 am

#145958

Hello,
I am using series of bulk copy programs to load data into a SQL Server database. An example of a bulk copy program that I am using now (from a DOS prompt) is
bcp dbentree.dbo.t_001_data in J:\m_001_in_bcp\prod7381.txt -c -T -S fbc\inst3
I want to use C# to read a list of names of files, create a bulk copy command and execute this bulk copy command in the C# program.
list of files:
prod7381.txt
prod7382.txt
prod7383.txt
create and execute commands:
bcp dbentree.dbo.t_001_data in J:\m_001_in_bcp\prod7381.txt -c -T -S fbc\inst3
bcp dbentree.dbo.t_001_data in J:\m_001_in_bcp\prod7382.txt -c -T -S fbc\inst3
bcp dbentree.dbo.t_001_data in J:\m_001_in_bcp\prod7383.txt -c -T -S fbc\inst3
But how to program this in C#?
Help is very much appreciated.
Thanks,
John

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

.NET has a bulkCopy object you can use.

you need to read the data into a datatable first, and like bcp, the table has to exist as well.

here's a translated from vb.Net to C#.Net example:

let me know if you need an example of reading a file into a datatable;

//Requires Imports System.Data.SqlClient

//no errors

//now use SQLBulk Copy to get the data into the server, instead of RBAR:

//note my connection string is based on String.Replace of this:

//Private Const SqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Application Name=MyApplicationName.exe;"

//Private Const SqlTrustedConnectionFormat As String = "data source={0};initial catalog={1};Trusted_Connection=True;Application Name=MyApplicationName.exe;"

{

try {

SqlConnection myConn = new SqlConnection(this.ConnectionString);

myConn.Open();

using (SqlBulkCopy myBulkCopy = new SqlBulkCopy(myConn)) {

myBulkCopy.DestinationTableName = "[" + DestinationTable + "]";

myBulkCopy.WriteToServer(dt);

}

} catch (Exception ex) {

Debug.Print(ex.Message);

}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jjzzmp Old Hand Points: 352 More actions · Answer 2

Dear Lowell,

Thanks for your post!

I am new to C# so can you please help me to convert this manual bcp to a C# bcp

bcp SUNN.dbo.t_211_invoer_test_01 in Z:\m_51_sunn_in\6030_2010_01.dat -c -T -S SVS01\inst01

And can you tell me what is the function of the SqlConnectionFormat and the SqlTrustedConnectionFormat and what values I should assign to them.

Regards,

John

nadabadan SSCommitted Points: 1503 More actions · Answer 3

Lowell (8/11/2011)
.NET has a bulkCopy object you can use.
you need to read the data into a datatable first, and like bcp, the table has to exist as well.
here's a translated from vb.Net to C#.Net example:
let me know if you need an example of reading a file into a datatable;
//Requires Imports System.Data.SqlClient
//no errors
//now use SQLBulk Copy to get the data into the server, instead of RBAR:
//note my connection string is based on String.Replace of this:
//Private Const SqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Application Name=MyApplicationName.exe;"
//Private Const SqlTrustedConnectionFormat As String = "data source={0};initial catalog={1};Trusted_Connection=True;Application Name=MyApplicationName.exe;"
{
try {
SqlConnection myConn = new SqlConnection(this.ConnectionString);
myConn.Open();
using (SqlBulkCopy myBulkCopy = new SqlBulkCopy(myConn)) {
myBulkCopy.DestinationTableName = "[" + DestinationTable + "]";
myBulkCopy.WriteToServer(dt);
}
} catch (Exception ex) {
Debug.Print(ex.Message);
}
}

Why use try/catch when you can nest using statements? Also, where are you closing the connection and disposing of the SQLConnection object? Might want to add a finally clause in there or just use "using" throughout.