none
Disabling connection pooling RRS feed

  • Question

  • Hello,

     

    Does anyone know if it's possible to disable connection pooling when opening two connections to the same Excel file?  The two connection strings are identical except for the IMEX option:

    Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file1.xls; Extended Properties="Excel 8.0;HDR=No;IMEX=0"

    Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file1.xls; Extended Properties="Excel 8.0;HDR=No;IMEX=1"

     

    I'm creating two OleDbConnection objects which will both be opened, selected from using two OleDbCommand objects, and read using two OleDbDataReader objects.  The problem is that the first opened connection (IMEX=0) is used for both, so effectively the second connection string (IMEX=1) is never used.

    (I know this because the data I'm getting from the two connections is identical where for some cells it should be different. If I do this sequentially instead (ie. open using IMEX=0, read, close, open using IMEX=1, read, close) then the data is indeed different!)

     

    Thanks in advance!

    Friday, July 2, 2010 9:05 AM

Answers

  • Hi,

     

    Thank you very much for your following up!   Yes, I can repro this problem when using OleDbDataReader.  It’s really strange since OleDbDataAdapter is working fine in the same scenario.  

     

    Could you please give me some detailed information about how this issue can impact your app?  I will open a bug ticket in Microsoft Connect and add the impact of this issue to raise the priority.  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 8, 2010 8:01 AM
    Moderator
  • Thanks for all the help!

    We're getting around this by making a temporary copy of the Excel file, so effectively we'll have two different connection strings:

    const string cs0 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file1.xls; " + 
     "Extended Properties=\"Excel 8.0;HDR=No;IMEX=0\"";
    
    const string cs1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file2.xls; " +
     "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";

    The reason we need two simultaneous connections is because Excel determines the column types by scanning the first eight rows (by default) of the table. This can cause problems if you've then got a cell later in that column that contains a value of a different type.

    E.g. suppose it's deemed a double-type column but a later cell contains an alphanumeric string. A null value will be returned using the IMEX=0 connection. But we need those values regardless, so in such cases we just grab the corresponding value from the IMEX=1 connection (no data type interpretation is performed, everything's just a text value). (BTW, we can't use just text values, we do need to know the column types too. It's not perfect, but it's good enough!)

    I'll take a look at your ExcelReader Val, but I think we're ok with this workaround.

    Many thanks

    Thursday, July 8, 2010 1:30 PM

All replies

  • I forgot to add that inserting

    OLE DB Services=-4

    into the connection string has no effect.

    Friday, July 2, 2010 9:22 AM
  • If I am not mistaken, you cannot manipulate with connection pull while using OLEB provider in .NET. Connection pool is maintained automatically in this case by each specific OLEDB provider, if supported. I do not think your issue is related to connection pool, since connection strings are different. I would also expect that connection pool would not be supported in a case when provider works with Excel files.

    Can you show us what exactly you do in your code and how you open connections and readers?

     


    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 2, 2010 10:34 AM
    Moderator
  • Hi VMazur,

    Basically I'm doing this:

     

    const string cs0 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file1.xls; " + 
     "Extended Properties=\"Excel 8.0;HDR=No;IMEX=0\"";
    
    const string cs1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file1.xls; " +
     "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
    
    using (OleDbConnection con0 = new OleDbConnection( cs0 ), con1 = new OleDbConnection( cs1 ))
    {
     con0.Open();
     con1.Open();
    
     using (OleDbCommand cmd0 = con0.CreateCommand(), cmd1 = con1.CreateCommand())
     {
      cmd0.CommandType = cmd1.CommandType = System.Data.CommandType.Text;
      cmd0.CommandText = cmd1.CommandText = "SELECT * FROM [table1$]";
      cmd0.CommandTimeout = cmd1.CommandTimeout = 0;
    
      using (OleDbDataReader reader0 = cmd0.ExecuteReader(), reader1 = cmd1.ExecuteReader())
      {
       //(read data)
      }
     }
    }

    It could be a connection pooling issue if the extended properties are (erroneously?) being ignored, in which case the connection strings are assumed identical?

     

    Many thanks

    • Edited by G Bull Friday, July 2, 2010 10:52 AM minor typo in code
    Friday, July 2, 2010 10:50 AM
  • Hello,

     

    Welcome to ADO.NET Managed Providers forum!

     

    I used similar code logic as yours, but my testing result is that DataTable dt0 and dt1 have different data value as the table columns.  

    ==================================================================================

                const string cs0 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file.xls; " +

                 "Extended Properties=\"Excel 8.0;HDR=No;IMEX=0\"";

     

                const string cs1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file.xls; " +

                 "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";

     

                using (OleDbConnection con0 = new OleDbConnection(cs0), con1 = new OleDbConnection(cs1))

                {

                    con0.Open();

                    con1.Open();

     

                    using (OleDbCommand cmd0 = con0.CreateCommand(), cmd1 = con1.CreateCommand())

                    {

                        cmd0.CommandType = cmd1.CommandType = System.Data.CommandType.Text;

                        cmd0.CommandText = cmd1.CommandText = "SELECT * FROM [Sheet1$]";

                        cmd0.CommandTimeout = cmd1.CommandTimeout = 0;

     

                        using (OleDbDataAdapter da0 = new OleDbDataAdapter(cmd0), da1 = new OleDbDataAdapter(cmd1))

                        {

                            DataTable dt0 = new DataTable(), dt1 = new DataTable();

     

                            da0.Fill(dt0);

                            da1.Fill(dt1);

                        }

                    }

                }

    ==================================================================================

    Do you mean if you only use the connection string cs1, the result is different from if we only use the connection string cs0?   I second VMazur and I don’t think the issue here is related to the connection pooling.

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, July 5, 2010 7:12 AM
    Moderator
  • Hi Lingzhi,

    Unfortunately filling a DataTable potentially has high resource usage, so it's not an option for us.

    When we use two OleDbDataReader objects (using the two different connection strings) we get the same values read in from both readers. We expect to get different values read in, but don't.

    The reason I assumed it's a connection pooling issue was because I thought the IMEX option was being ignored - a bug, so that the connection strings are incorrectly assumed identical?

    Thanks!

    Wednesday, July 7, 2010 4:09 PM
  • Hi,

     

    Thank you very much for your following up!   Yes, I can repro this problem when using OleDbDataReader.  It’s really strange since OleDbDataAdapter is working fine in the same scenario.  

     

    Could you please give me some detailed information about how this issue can impact your app?  I will open a bug ticket in Microsoft Connect and add the impact of this issue to raise the priority.  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 8, 2010 8:01 AM
    Moderator
  • If you need this functionality immediately, you could try ExcelReader from my web site to see if that works for you


    Val Mazur (MVP) http://www.xporttools.net
    Thursday, July 8, 2010 10:39 AM
    Moderator
  • Thanks for all the help!

    We're getting around this by making a temporary copy of the Excel file, so effectively we'll have two different connection strings:

    const string cs0 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file1.xls; " + 
     "Extended Properties=\"Excel 8.0;HDR=No;IMEX=0\"";
    
    const string cs1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=file2.xls; " +
     "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";

    The reason we need two simultaneous connections is because Excel determines the column types by scanning the first eight rows (by default) of the table. This can cause problems if you've then got a cell later in that column that contains a value of a different type.

    E.g. suppose it's deemed a double-type column but a later cell contains an alphanumeric string. A null value will be returned using the IMEX=0 connection. But we need those values regardless, so in such cases we just grab the corresponding value from the IMEX=1 connection (no data type interpretation is performed, everything's just a text value). (BTW, we can't use just text values, we do need to know the column types too. It's not perfect, but it's good enough!)

    I'll take a look at your ExcelReader Val, but I think we're ok with this workaround.

    Many thanks

    Thursday, July 8, 2010 1:30 PM
  • Hi,

     

    Does Val's ExcelReader works on your scenario?   BTW, if you can use .xlsx files, Open XML SDK is another good choice.  :-)

    http://www.microsoft.com/downloads/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, July 13, 2010 8:59 AM
    Moderator