Using SqlBulkCopy for Bulk Insert of records in C#

Using SqlBulkCopy for Bulk Insert of records in C#want

You want to insert a large number of rows to a database table, and you want it to be fast (and simple). Use SqlBulkCopy.


Simply create a DataTable with columns that match your table. Then populate the DataTable object with your records, and call a SqlBulkCopy WriteToServer.

DataTable table = new DataTable("MyTableName");
table.Columns.Add(new DataColumn("Id", typeof(int)));
table.Columns.Add(new DataColumn("Name", typeof(string)));
table.Columns.Add(new DataColumn("City", typeof(string)));

foreach(var person in persons)
	// NOTE: If Id is identity, then the value set here is ignored
	table.Rows.Add(person.Id, person.Name, person.City);

SqlConnection sqlConnection = new SqlConnection(...);

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
	bulkCopy.BatchSize = 100;
	bulkCopy.BulkCopyTimeout = 600; // seconds
	bulkCopy.DestinationTableName = "[DBO].[MyTableName]";


Any errors that I’ve observed result in an exception being thrown, so be sure to add a try catch block around wherever you want to handle exceptions.


Make local MySQL or ElasticSearch instance publicly available for a MVC.Net website with NgRok


I have a GoDaddy hosting account for, and I want to use and provide a large data set (like a personal copy of Wikipedia). GoDaddy’s MySQL database limits are 1GB, which is good – but not awesome. I could host MySQL at home on my personal computer, but how could my GoDaddy MVC.Net project access the data without a public address on my local instance?

(Note: I use ngrok, but they are certainly not paying me to write about it. Please read the security disclaimer at the bottom of this article. Exposing local resources to the internet is awesome and will have extra security concerns.)

Introducting: NgRok

If you are thinking something like “I want to expose my local service to a public address” then ngrok is a great (free) tool that I like using. My current usage is exposing my local MySQL database service to the internet, so my public services can use its data.

I kick off ngrok on my local machine like this:

ngrok.exe tcp 3306

That starts up a local process that gives me a public address and port that I can connect to using my MVC.Net projects:

After running ngrok.exe, you should see something like the above. This shows TCP traffic at the public address on port 123456 (actual value obfuscated) is routed to my localhost:3306, which is exactly where my local MySQL is listening.

Head over to and create a free account, and download their ngrok.exe. Your free account has a secret key associated with it. The first time you run ngrok.exe you’ll need to set that key like this:

ngrok.exe authtoken <YOUR_AUTHTOKEN>

After that, you’re all set to spin up ngrok for exposing local services to the internet.

MVC.Net Connection String:

In order for your connection string to hit the ngrok pubilc address, you’ll need to change the data source part of the connection string.

An example server and port for connecting to an exposed MySQL instance:;Port=123456
An example data source for connecting to an exposed SQL Server:

Notice the comma before the port number in the SQL Server connection string format.

In my experience, I only had to change data source or server/port. All of the other fields (user, password, timeouts, etc…) did not need any changes when switching to a ngrok publicly exposed database.

Other Uses:

I also use to expose my local ElasticSearch and some local IIS hosted websites for various (short term) demos and other purposes.

When you publish a website to local IIS, that will be serving over some port that you can configure. We’ll use 5555 for this example. You can expose that local IIS hosted website to the internet like this:

ngrok.exe http 5555

Or, a local ElasticSearch like this (with usually runs on localhost:9200):

ngrok.exe http 9200

Warning: ElasticSearch doesn’t have built in authentication, so wrapping that with an nginx locally is recommended. I do that with in that I’m hosting my website via GoDaddy and use an ElasticSearch in Azure with ngrok and nginx authentication.

Security Disclaimer:

Be careful with ngrok. Exposing your local ElasticSearch directly is not recommended, because ElasticSearch doesn’t have authentication built in. You can use things like nginx to create a basic authentication wrapper around ElasticSearch, and then expose the nginx service to the internet via ngrok. The same is with any other local service. Ngrok is awesome, but be careful.