news:1160990980.173723.31380@f16g2000cwb.googlegroups.com...
> Has connection pooling implementation changed in .NET 2.0?!
>
> I was happily running a code in 1.1 and now it seems that I have a
> problem and receive this error:
>
> System.Data.SqlClient.SqlException : A transport-level error has
> occurred when sending the request to the server. (provider: TCP
> Provider, error: 0 - An existing connection was forcibly closed by the
> remote host.)
>
> The code I am running is a database unit test whereby for every test I
> attach my database, run the test and then detach it. Each test, running
> on its own works fine. When I run the tests in succession (eg two tests
> in the same test fixture by clicking on the test fixture on NUnit GUI)
> it fails on the second test with the error above.
>
> The tests open and EXPLICITLY close their connections via a helper
> class and never keep any connection open.
>
> Now, when I set pooling=false in my connection string, it runs fine and
> now problem at all. To me it seems that .NET 2.0 not only pools the
> coonections, but also caches them and since database is restarted,
> connection is not valid anymore. If that is the case, this is a major
> change from 1.1 and to me it is a bug, even if it is by design. Because
> it introduces the potential problem for applications  if server is
> restarted so they must implement handle an exception that used to be
> gracefully handled by pooling itself.
>
> Guys from MS! Could you please shed some light on this?
>
> Here is the trace. I can see a change of implementation from 1.1:
>
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
>   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
>   at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
>   at
> System.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning()
>   at System.Data.SqlClient.TdsParserStateObject.WriteSni()
>   at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte
> flushMode)
>   at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
>   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray,
> Int32 timeout, Boolean inSchema, SqlNotificationRequest
> notificationRequest, TdsParserStateObject stateObj)
>   at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
> async)
>   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method, DbAsyncResult result)
>   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
> method)
>   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
>   at System.Data.SqlClient.SqlCommand.ExecuteReader()
>   at System.Data.SqlClient.SqlCommand.DeriveParameters()
>   at
> System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(SqlCommand
> command)
>   at
> DRS.Common.Data.SqlHelperParameterCache.DiscoverSpParameterSet(String
> connectionString, String spName, Boolean includeReturnValueParameter,
> Object[] parameterValues)
>   at DRS.Common.Data.SqlHelperParameterCache.GetSpParameterSet(String
> connectionString, String spName, Boolean includeReturnValueParameter)
>   at DRS.Common.Data.SqlHelperParameterCache.GetSpParameterSet(String
> connectionString, String spName)
>   at DRS.Common.Data.SqlHelper.ExecuteReader(String connectionString,
> String spName, Object[] parameterValues)
>   at DRS.Common.Data.SqlHelper.ExecuteEntity(String connectionString,
> String spName, Type entityType, Object[] parameterValues)
>   at DRS.ScriptMarking.WaveData.GetCurrentWave(Int32 ctrId, Int32
> comId) in C:\Projects\DRS Products\Script
> Marking\Source2\DataLayer\DRS.ScriptMarking.DataLayer.Unit\WaveData.cs:line
> 20
>   at DRS.ScriptMarking.WaveDataTest.TestSetWaveAsExported() in
> C:\Projects\DRS Products\Script
> Marking\Source2\DataLayer\DRS.ScriptMarking.DataLayer.UnitTest\WaveDataTest.cs:line
> 20
>

"aliostad" <alios***@gmail.com> wrote in message
news:1161174957.999706.62380@k70g2000cwa.googlegroups.com...
> Bill, thanks a lot for taking notice of this post.
>
> I do not see any problem with flushing all pooled connections if server
> goes down. As you said, they are all bad and invalid and it is best to
> reset the pool. However, I cannot see how this could explain why I
> should get an exception while not holding on to any connection. Surely
> all invalid connections have been flushed after server restart and with
> asking for a
>
> As I said, I use a helper class to open the connection, do the job and
> then close it. This helper class was designed based on one the earlier
> versions of Microsoft Data Access Block and we changed it slightly to
> retunr us our own Entities.
>
> In order to reproduce, it is enough to run this code in a console
> application:
>
>
> using System;
> using System.Collections.Generic;
> using System.Text;
> using System.Data;
> using System.Data.SqlClient;
> using System.Threading;
>
> namespace ConnectionTest
> {
>  class Program
>  {
>    static void Main(string[] args)
>    {
>      OpenConnectionDoSomethingAndClose();
>      Thread.Sleep(20000); // sleep 20 secs. Go and restart database
>      OpenConnectionDoSomethingAndClose(); // this will raise Transport
> level error!!
>
>    }
>
>    private static void OpenConnectionDoSomethingAndClose()
>    {
>      const string CONNECTION_STRING =
> "server=.;database=SMW;integrated security=true";
>      const string DO_SOMETHING = "SELECT GETDATE()";
>
>      SqlConnection cn = new SqlConnection(CONNECTION_STRING);
>      cn.Open();
>      try
>      {
>        SqlCommand cm = new SqlCommand(DO_SOMETHING, cn);
>        DateTime now = (DateTime) cm.ExecuteScalar();
>        Console.WriteLine(now);
>      }
>      finally
>      {
>        cn.Close();
>      }
>    }
>
>  }
> }
>
> I am sorry Bill, but it does not look right to me. I can understand
> that if i had kept a connection open, that connection would be useless
> after server restart but here, I am opening a new connection. I mustn't
> be given an error because server restarted or there was a network
> glitch or ... as I have asked for a connection after all of this.
>
> I might be mising something but pooling that used to hide all of this
> now is returning me some errors that it used to deal with. My allegory
> is buying something from a shop but shopkeepr asks for extra because
> items in warehouse became wet due to a leakage of his warehouse roof! I
> should not care about the warehouse and its items.
>
> I appreciate your feedback and on how I need to proceed on this issue.
>
> Many thanks
> Ali Kheyrollahi
> http://www.drs.co.uk
>

"aliostad" <alios***@gmail.com> wrote in message
news:1161350612.289988.173380@k70g2000cwa.googlegroups.com...
> Bill,
>
> Let's forget my original problem and focus on the sample code I sent as
> it reproduces my original problem and you can see all the code and no
> magic is behind it.
>
> This does not leak connections but causes exception while it should
> have been handled at the pooling level. To be honst to me it looks like
> pooling not only does not flush all similar connections, but also it
> even does not remove invalid one.
>
> Is this not a bug?
>
> Thanks
> Ali
>