"David Kirkman" <foxwaycomput***@gmail.com> wrote in message
news:1140712150.883755.140470@o13g2000cwo.googlegroups.com...
>I have a C# Windows Forms app. (Framework 1.1 VS 2003) using a SQL
> Server 2K database. It appears that despite closing and disposing all
> connections immediately after use and always using the same connection
> string the connections in the connection pool are not being reused.
> When the code attempts to open the 100th connection, the following
> exception is thrown:
>
> System.InvalidOperationException: Timeout expired.  The timeout period
> elapsed prior to obtaining a connection from the pool.  This may have
> occurred because all pooled connections were in use and max pool size
> was reached.
>
> Using SQL Enterprise manager it can be seen that the connections are
> indeed being closed i.e there is only ever one connection open - so
> what is going on? According to everything I have read about connection
> pooling, the connections should be stored in the pool when they are
> closed and if an attempt is made to open a new one with the same
> connection string, a connection in the pool is used.
>
> Here is some code which uses Northwind so anyone can run it. Just
> create a form and put a button on it, You will need to make sure that
> the Output window is visible in VS:
>
> private DataSet GetDataSetSql(string sqlString)
>        {
>            string cnString = "server=" + "Put your server name here" +
>                ";Trusted_Connection=yes; database=Northwind";
>            SqlConnection cn;
>
>            try
>            {
>         cn = new SqlConnection (cnString);
>                cn.Open();
>                SqlDataAdapter da = new SqlDataAdapter(sqlString, cn);
>                DataSet ds = new DataSet();
>                da.Fill(ds);
>                cn.Close();
>                cn.Dispose();
>                return ds;
>
>            }
>            catch(Exception ex)
>            {
>                MessageBox.Show(ex.ToString());
>                Console.Write(ex.ToString());
>                return null;
>            }
>
>        }
>
>        private void button1_Click(object sender, System.EventArgs e)
>        {
>            string sql;
>            DataSet ds;
>
>            for (int x = 10247; x < 10447; x++)
>            {
>                sql = "select orderid from orders where orderid = " +
> x.ToString();
>                ds = GetDataSetSql(sql);
>                Console.Write(x.ToString() + "\n");
>            }
>        }
>
> On my machine the OrderIDs are shown in the output window up to 10346
> (the 99th order in the table) it then fails. I would be interested to
> know if this behaviour occurs for others. I have tried increasing the
> Pool size in the connection string and this allows more connections to
> be made but there should be no need to do this as 100 should be more
> than enough.
>

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:eWIWR6LOGHA.428@tk2msftngp13.phx.gbl...
> Hi Bill,
>
> "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message
> news:uJh$b7JOGHA.3856@TK2MSFTNGP12.phx.gbl...
>>I looked at the code and didn't see anything egregious. I do know that
>>you're doing more work than you need to. Remove the Open, Close and
>>Dispose. All of these are handled by the Fill.
>> I would also look elsewhere for the problem. If this is all of your code
>> then I don't understand why it's not working. Your assessment of how the
>> connection pool works is correct. The symptom you're seeing is when the
>> connection is not closed or still in use (results pending) when the Open
>> occurs.
>
> Right.
>
>> Note that the connection pool has a very minor role to play in a Windows
>> Forms (smart client) application. I generally open the connection and
>> leave it open for the life of the application. Sure, I might timeout the
>> connection after so many minutes of idle time, but not always. By using a
>> single connection, you'll uncover pending results issues that might be
>> part of your problem.
>
> I wonder why are you taking this approach, I mean I can think of at least
> two of the downsides:
> - troubles for multithreading (of course, it doesn't matter if you don't
> do multithread database access)
> - if connection errors then you explicitly need to re-open it
> Generally I prefer open JIT and close ASAP.
>
> --
> Miha Markic [MVP C#]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>

"David Kirkman" <foxwaycomput***@gmail.com> wrote in message
news:1140736197.692632.286050@e56g2000cwe.googlegroups.com...
> Keeping the connection open is an interesting idea, as a relative
> newcomer to .NET, I had got the impression from a number of sources
> that the connection should be opened for as short a time as possible so
> it is interesting to hear a recognised expert in the field suggest
> this. Could there be a problem with SQL licences? I will certainly
> consider this approach, but I would like to understand why the
> connection pooling does not appear to work. I have now tried the code I
> supplied in the first post on three different machines (one using a
> local copy of the database) with the same result.
>
> Is it a bug, perhaps introduced via a service pack?
>
> I guess many people on this forum have upgraded to .NET 2.0 but if
> anyone is still on 1.1 can you please try my Northwind example. If you
> don't have the same problem then there must be something else that I am
> doing wrong but I can't imagine what it could be with such a simple
> example.
>
> I don't understand how there can be results still pending on the
> connection as it is only returning one record to the DataSet which it
> appears to do successfully. Examining the locals window when stepping
> through the code I see that the _internalConnection._pool.connections
> ArrayList gradually increases to the max pool size, however when I use
> the Performance monitor the SqlClient Current # pooled connections
> counter never gets above 1. This counter drops to zero each time the
> debugger steps past the cn.Close() line.
>

"David Kirkman" <foxwaycomput***@gmail.com> wrote in message
news:1140809448.055109.243690@i40g2000cwc.googlegroups.com...
> Thanks for your help Bill
> I have finally cracked it - a few months ago I attempted (unsuccesfully
> - but that is another story) to debug a stored procedure in VS .NET and
> set the 'Enable SQL Debugging' Configuration property for the project
> in question to true. If I set this property to false the connection
> pool problem disappears.
>