locked
OledbConnection.Close() taking too long to execute RRS feed

  • Question

  • User2029810015 posted

    I am using oledb to read data from excel file.

    Everything is fast but when I am closing the connection its taking too much time.

    Monday, July 13, 2015 9:16 AM

Answers

  • User281315223 posted

    Have you considered using Using statements for your connections as opposed to manually disposing / closing them on your own? It's generally a much easier way to handle things like this :

    using(var connection = new OleDbConnection("Your Connection String"))
    {
         // Open your connection
         connection.Open();
    
         // Do something here
    }

    Using statements don't require you to explicitly close or dispose of the connections and should avoid any issues with resource / memory-leaks. If the issue still persists however, it could be an issue of what you are actually doing within the connection itself.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 13, 2015 10:11 AM

All replies

  • User281315223 posted

    Have you considered using Using statements for your connections as opposed to manually disposing / closing them on your own? It's generally a much easier way to handle things like this :

    using(var connection = new OleDbConnection("Your Connection String"))
    {
         // Open your connection
         connection.Open();
    
         // Do something here
    }

    Using statements don't require you to explicitly close or dispose of the connections and should avoid any issues with resource / memory-leaks. If the issue still persists however, it could be an issue of what you are actually doing within the connection itself.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 13, 2015 10:11 AM
  • User2029810015 posted

    Have you considered using Using statements for your connections as opposed to manually disposing / closing them on your own? It's generally a much easier way to handle things like this :

    using(var connection = new OleDbConnection("Your Connection String"))
    {
         // Open your connection
         connection.Open();
    
         // Do something here
    }

    Using statements don't require you to explicitly close or dispose of the connections and should avoid any issues with resource / memory-leaks. If the issue still persists however, it could be an issue of what you are actually doing within the connection itself.

    I already tried tried this same thing happening when function returning value So by using this method time consumption is same

    Monday, July 13, 2015 11:11 AM
  • User281315223 posted

    What does the query that you are actually executing look like?

    It might not be an issue of the actual closing process taking a long time, as it could be the query itself that is long running. Is that a possibility?

    Monday, July 13, 2015 11:14 AM
  • User2029810015 posted

    What does the query that you are actually executing look like?

    It might not be an issue of the actual closing process taking a long time, as it could be the query itself that is long running. Is that a possibility?

    I am querying a Excel that have 10-20 row and filling the DataTable from that.

    upto filling of datatable is very fast withing second and able to see the data in datable but after that when I am closing the connection it takes around 10s or more that that.

    Monday, July 13, 2015 11:18 AM
  • User281315223 posted

    That's quite strange.

    Could you post your code or an example of what your code looks like with the connection?

    Monday, July 13, 2015 12:24 PM
  • User2029810015 posted

    That's quite strange.

    Could you post your code or an example of what your code looks like with the connection?

    OleDbConnection conn = null;
    OleDbDataAdapter adapter;
    DataTable dt;
    filePath=@"C:\Users\ABC.xlsx"
      conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" +
                        "Data Source=" + filePath + ";" +
                        "Extended Properties=Excel 12.0 Xml;");
      conn.Open();
    
      dt = conn.GetSchema("Tables");
      adapter = new OleDbDataAdapter("SELECT * FROM [" + dt.Rows[0][2] + "]", conn);
    
                    new OleDbCommandBuilder(adapter);
    
                    dt = new DataTable();
                    adapter.Fill(dt);
    
      conn.Close();

    Monday, July 13, 2015 12:35 PM
  • User281315223 posted

    You still should be using the Using statements as opposed to this code :

    // Build your connection string path
    var path = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;",filePath);
    using(var connection = new OleDbConnection(path))
    {
         // Open your connection
         connection.Open();
     
         // Populate a database with the schema
         var schema = connection.GetSchema("Tables");
         using(var adapter = new OleDbDataAdapter("SELECT * FROM [@Row]", connection)) 
         {
               // Add your parameter
               adapter.SelectCommand.Parameters.AddWithValue("@Row",schema.Rows[0][2]);
               // Build your data table
               var results = new DataTable();
               adapter.Fill(results);
         }
    }

    You don't need to actually close the connection at all, as the using statement will do that for you. Are you planning on returning anything from this query?

    Monday, July 13, 2015 12:49 PM
  • User2029810015 posted
    Yes I want to return datatable from this
    Monday, July 13, 2015 1:16 PM
  • User281315223 posted

    Yes I want to return datatable from this

    Then you could return it within the using statement itself, this should push out the datatable and handle closing the connection (behind the scenes) and shouldn't require you to wait for it :

    // Build your connection string path
    var path = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;",filePath);
    using(var connection = new OleDbConnection(path))
    {
         // Open your connection
         connection.Open();
     
         // Populate a database with the schema
         var schema = connection.GetSchema("Tables");
         using(var adapter = new OleDbDataAdapter("SELECT * FROM [@Row]", connection)) 
         {
               // Add your parameter
               adapter.SelectCommand.Parameters.AddWithValue("@Row",schema.Rows[0][2]);
               // Build your data table
               var results = new DataTable();
               adapter.Fill(results);
               // Return it
               return results;
         }
    }

    Monday, July 13, 2015 2:10 PM
  • User2029810015 posted

    pramuk97

    Yes I want to return datatable from this

    Then you could return it within the using statement itself, this should push out the datatable and handle closing the connection (behind the scenes) and shouldn't require you to wait for it :

    // Build your connection string path
    var path = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;",filePath);
    using(var connection = new OleDbConnection(path))
    {
         // Open your connection
         connection.Open();
     
         // Populate a database with the schema
         var schema = connection.GetSchema("Tables");
         using(var adapter = new OleDbDataAdapter("SELECT * FROM [@Row]", connection)) 
         {
               // Add your parameter
               adapter.SelectCommand.Parameters.AddWithValue("@Row",schema.Rows[0][2]);
               // Build your data table
               var results = new DataTable();
               adapter.Fill(results);
               // Return it
               return results;
         }
    }

    I am facing same issue in this code also at this time on the last line when we are returning the DataTable at that line its taking more that 10-20 seconds.

    Tuesday, July 14, 2015 1:39 AM
  • User-84896714 posted

    Hi pramuk97,

    I tested your code, and it works fine on my side, it only spend less than 1s, you could change another excel file and try again.

    Best Regards,
    Wang Li

    Tuesday, July 14, 2015 3:32 AM
  • User2029810015 posted

    Hi pramuk97,

    I tested your code, and it works fine on my side, it only spend less than 1s, you could change another excel file and try again.

    Best Regards,
    Wang Li

    Ok I tried with different Excels but issue is still persistent.

    Tuesday, July 14, 2015 5:44 AM
  • User-84896714 posted

    Hi pramuk97,

    What's the version of your excel file? Please check whether your computer has been attack or infected with some viruses.

    Best Regards,
    Wang Li

    Monday, July 27, 2015 5:31 AM
  • User2029810015 posted

    Hi pramuk97,

    What's the version of your excel file? Please check whether your computer has been attack or infected with some viruses.

    Best Regards,
    Wang Li

    I am using Excel 2013 file & I also tested on other machines but the connection.close taking too much time

    Thursday, September 10, 2015 3:15 AM