Geek Answers Handbook

Excel with .NET ACE timeout using data adapter update

I am having a timeout problem while writing data from a dataset to 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 "Connection timeout" property is not supported by the provider. Also there is no connection timeout, timeout, ConnectionTimeout, ConnectTimeout, etc. My code created a data adapter and InsertCommand, as well as updates from a dataset. This works fine if the Update command itself takes more than 15 seconds, which is the default OleDb timeout. That is why I am trying to install it in the Connection line. I have increased the default CommandTimeout to 30, but it really doesn't matter. 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 skipped the loops and thus created the content and parameters of the insert command. Believe me, everything works fine. 2200+ entries work great.

It was after I added the timer that I discovered that the problem was due to a timeout. When processing 2221 records, it takes 14.95 seconds and is displayed in the table only in order. When processing 2260 records, it takes 15.21 seconds and nothing is displayed. There are no errors. I checked the status of the row after updating for all rows, and they all do not detect a failure.

The bottom line, since the ConnectionTimeout property is ReadOnly, and the provider does not seem to support Timeout in the connection string, what to do ....

Thanks.

+6
source share
2 answers

Here are my ideas:

  • Are you really using this as appSetting and not connectionStrings? If you use it as connectionStrings, check this: Escape quote in the connection string web.config
  • The data source does not matter, just pointing to it, because you left this part of the code
  • The source of your problem may be the session pool timeout settings: INFO: OLE DB pool timeout configuration
  • You must use the operators associated with your connection, adapter and command lines, but this cannot create a steam lock form and force a timeout.

  • Are you sure this is a timeout and not a "Full Text" error? Here is my code that ends after ~ 1: 50, if you hit it up to 4,000,000 rows, you will get "The table 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(); }
+1
source

More articles:


All Articles