title: How to improve performance of CSV upload via datatable
body: <p>I have a working solution for uploading a CSV file. Currently, I use the <code>IFormCollection</code> for a user to upload multiple CSV files from a view. </p>
  
  <p>The CSV files are saved as a temp file as follows:</p>
  
  <pre><code>List&lt;string&gt; fileLocations = new List&lt;string&gt;();
  foreach (var formFile in files)
  {
     filePath = Path.GetTempFileName();    
     if (formFile.Length &gt; 0)
     {
         using (var stream = new FileStream(filePath, FileMode.Create))
         {
             await formFile.CopyToAsync(stream);
         }
     }
  
     fileLocations.Add(filePath);
  }
  </code></pre>
  
  <p>I send the list of file locations to another method (just below). I loop through the file locations and stream the data from the temp files, I then use a data table and <code>SqlBulkCopy</code>to insert the data. I currently upload between 50 and 200 files at a time and each file is around 330KB. To insert a hundred, it takes around 6 minutes, which is around 30-35MB.</p>
  
  <pre><code>public void SplitCsvData(string fileLocation, Guid uid)
          {
              MetaDataModel MetaDatas;
              List&lt;RawDataModel&gt; RawDatas;
  
              var reader = new StreamReader(File.OpenRead(fileLocation));
              List&lt;string&gt; listRows = new List&lt;string&gt;();
              while (!reader.EndOfStream)
              {
                  listRows.Add(reader.ReadLine());
              }
  
              var metaData = new List&lt;string&gt;();
              var rawData = new List&lt;string&gt;();
  
              foreach (var row in listRows)
              {
                  var rowName = row.Split(',')[0];
                  bool parsed = int.TryParse(rowName, out int result);
  
                  if (parsed == false)
                  {
                      metaData.Add(row);
                  }
                  else
                  {
                      rawData.Add(row);
                  }
              }
  
           //Assigns the vertical header name and value to the object by splitting string 
           RawDatas = GetRawData.SplitRawData(rawData);
           SaveRawData(RawDatas);
  
           MetaDatas = GetMetaData.SplitRawData(rawData);
           SaveRawData(RawDatas);
  
          }
  </code></pre>
  
  <p>This code then passes the object to the to create the datatable and insert the data.</p>
  
  <pre><code>private DataTable CreateRawDataTable
  {
     get
     {
         var dt = new DataTable();
         dt.Columns.Add("Id", typeof(int));
         dt.Columns.Add("SerialNumber", typeof(string));
         dt.Columns.Add("ReadingNumber", typeof(int));
         dt.Columns.Add("ReadingDate", typeof(string));
         dt.Columns.Add("ReadingTime", typeof(string));
         dt.Columns.Add("RunTime", typeof(string));
         dt.Columns.Add("Temperature", typeof(double));
         dt.Columns.Add("ProjectGuid", typeof(Guid));
         dt.Columns.Add("CombineDateTime", typeof(string));
  
          return dt;
    }
  }
  
  public void SaveRawData(List&lt;RawDataModel&gt; data)
  {
         DataTable dt = CreateRawDataTable;
         var count = data.Count;          
  
         for (var i = 1; i &lt; count; i++)
         {
             DataRow row = dt.NewRow();
             row["Id"] = data[i].Id;
             row["ProjectGuid"] = data[i].ProjectGuid;
             row["SerialNumber"] = data[i].SerialNumber;
             row["ReadingNumber"] = data[i].ReadingNumber;
             row["ReadingDate"] = data[i].ReadingDate;
             row["ReadingTime"] = data[i].ReadingTime;
             row["CombineDateTime"] = data[i].CombineDateTime;
             row["RunTime"] = data[i].RunTime;
             row["Temperature"] = data[i].Temperature;
             dt.Rows.Add(row);
          }
  
          using (var conn = new SqlConnection(connectionString))
          {
             conn.Open();
             using (SqlTransaction tr = conn.BeginTransaction())
             {
                 using (var sqlBulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tr))
                 {
                     sqlBulk.BatchSize = 1000;
                     sqlBulk.DestinationTableName = "RawData";
                     sqlBulk.WriteToServer(dt);
                 }
                 tr.Commit();
             }
         }
     }
  </code></pre>
  
  <p>Is there another way to do this or a better way to improve performance so that the time to upload is reduced as it can take a long time and I am seeing an ever increasing use of memory to around 500MB.</p>
  
  <p>TIA</p>