c# - Load large text files to database

Two ways to load a large text files to a table in the database are using

• bcp command with xp_cmdShell enabled on the database server
• bulk insert

The files I load are in the range of 10-15 GB tab delimited text files.These files are created by daily processes and are stored on file servers running Windows Server 2008.After the files are created they are loaded to SQL Server Database.

bcp command with xp_cmdShell

using System;
using System.Text;
using System.Configuration;
using System.IO;
using System.Data.SqlClient;

public class LoadLargeFileToDB
{ 
          static void Main()
          {
                 string sConnectionString = "server=MyServer;database=MyDatabase;uid='username';pwd='password'";
                 string sServer = "MyServer";
                 string sTableName = "MyTable";
                 string sImportFilePath = @"\\Servername\DriveName\SharedFolder\FileName.txt";

                 try

                 {

                      if (!File.Exists(sImportFilePath))

                              throw new Exception("Error importing BCP file = " + sImportFilePath + ". File does not exists.");

                                     

                      using (SqlConnection oSqlConnection = new SqlConnection(sConnectionString))

                      {

                             oSqlConnection.Open();

                             SqlCommand oSqlCommand = new SqlCommand("exec xp_cmdShell 'bcp " + sTableName + " in " + sImportFilePath + " -c -T -S " + sServer + "'", oSqlConnection);

                             oSqlCommand.CommandTimeout = 120000;

                             oSqlCommand.ExecuteNonQuery();

                      }

                }

                catch (Exception ex)

                {

                      Console.Error(ex.Message);

                      Console.Error(ex.InnerException);

                      Console.Error(ex.StackTrace);

                }

         }

}

Bulk insert

using System;
using System.Text;
using System.Configuration;
using System.IO;
using System.Data.SqlClient;

public class LoadLargeFileToDB
{ 
          static void Main()
          {
                 string sConnectionString = "server=MyServer;database=MyDatabase;uid='username';pwd='password'";
                 string sServer = "MyServer";
                 string sTableName = "MyTable";
                 string sImportFilePath = @"\\Servername\DriveName\SharedFolder\FileName.txt";

                 try

                 {

                      if (!File.Exists(sImportFilePath))

                              throw new Exception("Error importing BCP file = " + sImportFilePath + ". File does not exists.");

                                     

                      using (SqlConnection oSqlConnection = new SqlConnection(sConnectionString))

                      {

                             oSqlConnection.Open();

                             SqlCommand oSqlCommand = new SqlCommand("bulk insert "+ sTableName +" from '"+ sImportFilePath +"'";, oSqlConnection);

                             oSqlCommand.CommandTimeout = 120000;

                             oSqlCommand.ExecuteNonQuery();

                      }

                }

                catch (Exception ex)

                {

                      Console.Error(ex.Message);

                      Console.Error(ex.InnerException);

                      Console.Error(ex.StackTrace);

                }

         }

}