Excel with ACE Timeout in .NET using Data Adapter Update

Question

I am experiencing a timeout issue writing data from a dataset into an excel spreadsheet. Here is my connection string currently:

<add key="ExcelConnectionStringHeader" value="Provider=Microsoft.ACE.OLEDB.12.0;Connect Timeout=30;Extended Properties=&quot;Excel 12.0;HDR=YES&quot;;Data Source="/>

The Connect Timeout property doesn't seem to be supported by the provider. Neither is Connection Timeout, Timeout, ConnectionTimeout, ConnectTimeout, etc. My code created a Data Adapter and and InsertCommand, and updates from a dataset. This works great unless the Update command itself takes more than 15 seconds, which is the default OleDb timeout. Thats why I am trying to set it in the Connection string. I have increased the CommandTimeout from the default of 30, but that is really irrelevant. Below is my Code:

        OleDbDataAdapter da = new OleDbDataAdapter(szHeaderSelect, oCnn); // Ratings_Test -- Loan_Test$A1:F1]",
        DataSet dsTest = new DataSet();

        da.InsertCommand = new OleDbCommand(szNewSQL + "(" + szColumns + ") " + "VALUES ( " + szParams + ")", oCnn);
        da.InsertCommand.CommandTimeout = 300;


        foreach (DataRow oDr in dtTable.Rows)
        {

            drNew = dsTest.Tables[0].NewRow();

            dsTest.Tables[0].Rows.Add(drNew);
        }

        var timer = new System.Diagnostics.Stopwatch();
        timer.Start();
        var recs = da.Update(dsTest, szExcelTab); // Ratings_Test           }
        timer.Stop();

I omitted the loops and such that actually build the content and parameters of the insert command. Trust me, it all works fine. 2200+ records work fine.

It was after I added the timer that I discovered the problem was timeout. When processing 2221 records, it takes 14.95 seconds and shows up just fine in the spreadsheet. When processing 2260 records, it takes 15.21 seconds and nothing shows up. There are no errors of any kind. I've checked the row state after the update for all rows and they all show no failure.

Bottom line, since the ConnectionTimeout property is ReadOnly, and the provider doesn't seem to support a Timeout in the connection string, what to do....

Thanks.

Monthly

Just as an extra thing, checkout openxml, it is working pretty nice with Excel sheets also with very large excel sheets – Scarlatti

Answer 1

Here are my ideas:

• Are you actually using this as an appSetting and not a connectionStrings? If you are using it as a connectionStrings check out this: Escape quote in web.config connection string
• Data Source is missing its Value, just pointing it out because you left out that part of the code
• The source of your problem might be your Session Pooling timeout settings: INFO: OLE DB Session Pooling Timeout Configuration
• You should put using statements around your connection, adapter and command creation lines, not doing this can create a form of vapor lock and force a timeout.

• Are you sure it is a timeout and not a "Spreadsheet Full" error? Here is my code that completes after ~1:50, if you bump it up to 4,000,000 rows you get the "Spreadsheet is full." error after ~4:50:

  static void Main(string[] args)
  {
      var timer = new System.Diagnostics.Stopwatch();
  
      try
      {
          string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;HDR=YES;\";Data Source=Book1.xlsx";
  
          using (OleDbConnection oleDbConnection = new OleDbConnection(connectionString))
          {
              oleDbConnection.Open();
  
              string szHeaderSelect = "SELECT [A1] FROM  from [Sheet1$]";
              using (OleDbDataAdapter da = new OleDbDataAdapter(szHeaderSelect, oleDbConnection))
              {
                  using (da.InsertCommand = new OleDbCommand("INSERT INTO [Sheet1$] ( [A1] ) VALUES (?)",
                      oleDbConnection))
                  {
                      da.InsertCommand.Parameters.Add("A1", OleDbType.Integer, 20, "[A1]");
  
                      List<int> testData = new List<int>();
                      for (int i = 1; i < 400000; i++)
                      {
                          testData.Add(i);
                      }
  
                      DataSet dsTest = new DataSet();
                      dsTest.Tables.Add("[Sheet1$]");
                      dsTest.Tables[0].Columns.Add("[A1]");
  
                      foreach (int number in testData)
                      {
                          DataRow drNew = dsTest.Tables[0].NewRow();
                          drNew["[A1]"] = number;
  
                          dsTest.Tables[0].Rows.Add(drNew);
                      }
  
                      timer.Start();
                      var recs = da.Update(dsTest, "[Sheet1$]");
                  }
              }
          }
      }
      catch (Exception ex)
      {
          Console.Out.WriteLine(ex.Message);
      }
      finally
      {
          timer.Stop();
  
          Console.WriteLine(timer.Elapsed);
      }
  
      // Don't close before I get to read the results
      Console.WriteLine();
      Console.WriteLine("Press Enter to quit.");
      Console.ReadLine();
  }
  

Larainelarboard

Answer 2

Set Application Pool account to "LocalSystem" or a machine administrator.

Foxing