Loads data from a CSV file using the Microsoft.VisualBasic.FileIO.TextFieldParser, then uses the SqlBulkCopy to put the data into a temp table in SQL Server, then copies the data to the live table.

LoadCsvDataIntoSqlServer.cs

public class LoadCsvDataIntoSqlServer
{
    // Queries for truncating the tables and copying data
    protected string _truncateTempTableCommandText = @"TempTable";
    protected string _truncateLiveTableCommandText = @"LiveTable";

    protected string  _copyDataCommandText = @"
    INSERT INTO LiveTable (FirstName, LastName)
    SELECT FirstName, LastName
    FROM TempTable";

    public void LoadCsvDataIntoSqlServer()
    {
        // This should be the full path
        var fileName = @"C:\Path\To\File.csv";

        var createdCount = 0;

        using (var textFieldParser = new TextFieldParser(fileName))
        {
            textFieldParser.TextFieldType = FieldType.Delimited;
            textFieldParser.Delimiters = new[] { "," };
            textFieldParser.HasFieldsEnclosedInQuotes = true;

            var connectionString = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;

            var dataTable = new DataTable("TempTable");

            // Add the columns in the temp table
            dataTable.Columns.Add("FirstName");
            dataTable.Columns.Add("LastName");

            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();

                // Truncate the temp table
                using (var sqlCommand = new SqlCommand(_truncateTempTableCommandText, sqlConnection))
                {
                    sqlCommand.ExecuteNonQuery();
                }

                // Truncate the live table
                using (var sqlCommand = new SqlCommand(_truncateLiveTableCommandText, sqlConnection))
                {
                    sqlCommand.ExecuteNonQuery();
                }

                // Loop through the CSV and load each set of 10,000 records into a DataTable
                // Then send it to the TempTable
                // Then copy it to the LiveTable
                // Then TRUNCATE the TempTable
                while (!textFieldParser.EndOfData)
                {
                    dataTable.Rows.Add(textFieldParser.ReadFields());

                    createdCount++;

                    if (createdCount % 10000 == 0)
                    {
                        SendDataTableTo(sqlConnection, dataTable);

                        break;
                    }
                }

                // Don't forget to send the last batch under 10,000
                SendDataTableTo(sqlConnection, dataTable);

                sqlConnection.Close();
            }
        }
    }

    protected void SendDataTableTo(SqlConnection sqlConnection, DataTable dataTable)
    {
        // Send the data to the server
        var copy = new SqlBulkCopy(sqlConnection);
        copy.DestinationTableName = "TempTable";
        copy.WriteToServer(dataTable);

        // Copy the data from the temp table to live table
        using (var sqlCommand = new SqlCommand(_copyDataCommandText, sqlConnection))
        {
            sqlCommand.ExecuteNonQuery();
        }

        // Truncate the temp table
        using (var sqlCommand = new SqlCommand(_truncateTempTableCommandText, sqlConnection))
        {
            sqlCommand.ExecuteNonQuery();
        }

        // Clear out our DataTable so we can load more data into it again
        dataTable.Rows.Clear();
    }
}