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.
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
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?
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.
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?
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;
}
}
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.
Member
89 Points
243 Posts
OledbConnection.Close() taking too long to execute
Jul 13, 2015 09:16 AM|pramuk97|LINK
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.
All-Star
113020 Points
18361 Posts
MVP
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 10:11 AM|Rion Williams|LINK
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.
Member
89 Points
243 Posts
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 11:11 AM|pramuk97|LINK
I already tried tried this same thing happening when function returning value So by using this method time consumption is same
All-Star
113020 Points
18361 Posts
MVP
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 11:14 AM|Rion Williams|LINK
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?
Member
89 Points
243 Posts
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 11:18 AM|pramuk97|LINK
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.
All-Star
113020 Points
18361 Posts
MVP
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 12:24 PM|Rion Williams|LINK
That's quite strange.
Could you post your code or an example of what your code looks like with the connection?
Member
89 Points
243 Posts
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 12:35 PM|pramuk97|LINK
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();All-Star
113020 Points
18361 Posts
MVP
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 12:49 PM|Rion Williams|LINK
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?
Member
89 Points
243 Posts
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 01:16 PM|pramuk97|LINK
All-Star
113020 Points
18361 Posts
MVP
Re: OledbConnection.Close() taking too long to execute
Jul 13, 2015 02:10 PM|Rion Williams|LINK
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; } }Member
89 Points
243 Posts
Re: OledbConnection.Close() taking too long to execute
Jul 14, 2015 01:39 AM|pramuk97|LINK
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.
Star
9860 Points
974 Posts
Microsoft
Re: OledbConnection.Close() taking too long to execute
Jul 14, 2015 03:32 AM|Li Wang|LINK
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
Member
89 Points
243 Posts
Re: OledbConnection.Close() taking too long to execute
Jul 14, 2015 05:44 AM|pramuk97|LINK
Ok I tried with different Excels but issue is still persistent.
Star
9860 Points
974 Posts
Microsoft
Re: OledbConnection.Close() taking too long to execute
Jul 27, 2015 05:31 AM|Li Wang|LINK
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
Member
89 Points
243 Posts
Re: OledbConnection.Close() taking too long to execute
Sep 10, 2015 03:15 AM|pramuk97|LINK
I am using Excel 2013 file & I also tested on other machines but the connection.close taking too much time