title: Any way to speed up this excel import?
body: <p>I have an Excel document that has about 250000 rows which takes forever to import. I have done many variations of this import, however there are a few requirements:
   - Need to validate the data in each cell
   - Must check if a duplicate exists in the database
   - If a duplicate exists, update the entry
   - If no entry exists, insert a new one</p>
  
  <p>I have used parallelization as much as possible however I am sure that there must be some way to get this import to run much faster. Any assistance or ideas would be greatly appreciated.</p>
  
  <p><em>Note that the database is on a LAN, and yes I know I haven't used parameterized sql commands (yet).</em></p>
  
  <pre><code>        public string BulkUserInsertAndUpdate()
          {
              DateTime startTime = DateTime.Now;
              try
              {
                  ProcessInParallel();
                  Debug.WriteLine("Time taken: " + (DateTime.Now - startTime));
              }
              catch (Exception ex)
              {
                  return ex.Message;
              }
  
              return "";
          }
  
  
         private IEnumerable&lt;Row&gt; ReadDocument()
          {
              using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(_fileName, false))
              {
                  WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
  
                  Sheet ss = workbookPart.Workbook.Descendants&lt;Sheet&gt;().SingleOrDefault(s =&gt; s.Name == "User");
  
                  if (ss == null)
                      throw new Exception("There was a problem trying to import the file. Please insure that the Sheet's name is: User");
  
                  WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
  
                  OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
                  StringTablePart = workbookPart.SharedStringTablePart;
  
                  while (reader.Read())
                  {
                      if (reader.ElementType == typeof(Row))
                      {
                          do
                          {
                              if (reader.HasAttributes)
                              {
                                  var rowNum = int.Parse(reader.Attributes.First(a =&gt; a.LocalName == "r").Value);
  
                                  if (rowNum == 1)
                                      continue;
  
                                  var row = (Row)reader.LoadCurrentElement();
                                  yield return row;
                              }
  
                          } while (reader.ReadNextSibling()); // Skip to the next row
                          break; // We just looped through all the rows so no need to continue reading the worksheet
                      }
                  }
              }
          }
  
   private void ProcessInParallel()
          {
              // Use ConcurrentQueue to enable safe enqueueing from multiple threads. 
              var exceptions = new ConcurrentQueue&lt;Exception&gt;();
  
  
              Parallel.ForEach(ReadDocument(), (row, loopState) =&gt;
                  {
  
                      List&lt;Cell&gt; cells = row.Descendants&lt;Cell&gt;().ToList();
  
                      if (string.IsNullOrEmpty(GetCellValue(cells[0], StringTablePart)))
                          return;
  
                      // validation code goes here....
  
  
                      try
                      {
                          using (SqlConnection connection = new SqlConnection("user id=sa;password=D3vAdm!n@;server=196.30.181.143;database=TheUnlimitedUSSD;MultipleActiveResultSets=True"))
                          {
                              connection.Open();
                              SqlCommand command = new SqlCommand("SELECT count(*) FROM dbo.[User] WHERE MobileNumber = '" + mobileNumber + "'", connection);
                              var userCount = (int) command.ExecuteScalar();
                              if (userCount &gt; 0)
                              {
                                  // update
                                  command = new SqlCommand("UPDATE [user] SET NewMenu = " + (newMenuIndicator ? "1" : "0") + ", PolicyNumber = '" + policyNumber + "', Status = '" + status + "' WHERE MobileNumber = '" + mobileNumber + "'", connection);
                                  command.ExecuteScalar();
                                  Debug.WriteLine("Update cmd");
                              }
                              else
                              {
                                  // insert
                                  command = new SqlCommand("INSERT INTO dbo.[User] ( MobileNumber , Status , PolicyNumber ,  NewMenu ) VALUES  ( '" + mobileNumber + "' , '" + status + "' ,  '" + policyNumber + "' ,  " + (newMenuIndicator ? "1" : "0") + " )", connection);
                                  command.ExecuteScalar();
                                  Debug.WriteLine("Insert cmd");
                              }
                          }
                      }
                      catch (Exception ex)
                      {
                          exceptions.Enqueue(ex);
                          Debug.WriteLine(ex.Message);
                          loopState.Break();
                      }
                  });
  
              // Throw the exceptions here after the loop completes. 
              if (exceptions.Count &gt; 0)
                  throw new AggregateException(exceptions);
  
          }
  </code></pre>