How To Import Large Data Into SQL In Fastest Way With .NET?

"The definition of genius is taking the complex and making it simple." - Albert Einstein

In many projects, software engineers face the challenge of loading large data files into the database in the fastest way.

When the project is based on .NET, several options exist to interact with the database. ADO.NET and Entity Framework are native parts of the .NET to help us in this challenge.

However, loading those into database tables may take a while if you have really large files with millions of rows of information.

So, how to load large data files into the database?

Fortunately, .NET provides an approach to solve the case fast and easily. The solution is a part of ADO.NET, which is a native library of the .NET.

To solve the case most efficiently, we will use the class SqlBulkCopy. Here is the code snippet with an example.

static void Main(string[] args)
 {
     string cs = @"Your Database Connection String";
     string filePath = @"C:\temp\file.csv";
     DataTable csvData = new DataTable();
     using (var fileReader=new StreamReader (filePath))
     {
       using (TextFieldParser csvReader = new TextFieldParser(fileReader))
                {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    bool tableCreated = false;
                    string[] colFields;
                    while (tableCreated == false)
                    {
                        colFields = csvReader.ReadFields();
                        foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                            datecolumn.AllowDBNull = true;
                            csvData.Columns.Add(datecolumn);
                    }
                        tableCreated = true;
                    }
                    while (!csvReader.EndOfData)
                    {
                        csvData.Rows.Add(csvReader.ReadFields());
                    }
                }
            }
        DateTime fileLoaded = DateTime.Now;
        using (var conn=new SqlConnection (cs))
        {
          conn.Open ();
          using (var bulkCopy = new SqlBulkCopy(conn))
          {
            bulkCopy.DestinationTableName = "dbo.TempTable";
            bulkCopy.ColumnMappings.Add("Id", "Id");
            bulkCopy.ColumnMappings.Add("Total_Funding", "Funding");
            bulkCopy.ColumnMappings.Add("Job Title","Title");
            bulkCopy.ColumnMappings.Add("Apply URL", "ApplyUrl");
            bulkCopy.ColumnMappings.Add("Location", "Location");
            bulkCopy.ColumnMappings.Add("Category", "Category");
            bulkCopy.ColumnMappings.Add("Company", "Company");
            bulkCopy.ColumnMappings.Add("Job Description", "Description");
            bulkCopy.WriteToServer(csvData);
          } 
        }
      }

You can find the full snippet code and the CSV file for testing in the attached files.

BulkCopyDemo

.zip

Download ZIP • 9.14MB