I'm using the Adapter template and try to improve performance in our application. I'm trying to keep our sql connection open during one call to our server application. I have noticed that after I call SaveEntity:

this.Adapter.SaveEntity(user, false, user.GetConcurrencyPredicate(ConcurrencyPredicateType.Save), false); 

in the SQL profiler window I see

exec sp_reset_connection

which I beleive means that a connection is being reused from the pool.

Using the debugger I can see that the states on the ADO.NET connection is closed after the SaveEntity call.

I have set KeepConnectionOpen = true and we are using a connection string (not config file).

Is this a bug? or am i missing something?

Thanks in advance

solidstore wrote:

I'm using the Adapter template and try to improve performance in our application. I'm trying to keep our sql connection open during one call to our server application. I have noticed that after I call SaveEntity:

this.Adapter.SaveEntity(user, false, user.GetConcurrencyPredicate(ConcurrencyPredicateType.Save), false); 

in the SQL profiler window I see

exec sp_reset_connection

which I beleive means that a connection is being reused from the pool.

Using the debugger I can see that the states on the ADO.NET connection is closed after the SaveEntity call.

I have set KeepConnectionOpen = true and we are using a connection string (not config file).

Is this a bug? or am i missing something? Thanks in advance

sp_reset_connection is called by SqlClient when a new connection is retrieved from the pool. When you specify KeepConnectionOpen = true, it's not yet opened. The first time the connection is opened, you'll see the sp_reset_connection call.

Best way to do it:

try
{
adapter.OpenConnection();
// do your actions
}
finally
{
adapter.CloseConnection();
adapter.Dispose();
}

agreed, but I'm seeing an additional reset after calling SaveEntity before closing the connection in my code. I'm saving several entities within one api and after the 3 call to SaveEntity the underlying connection is closed, and therefore reset on the next call. Why might a connection be closed by SaveEntity?

solidstore wrote:

agreed, but I'm seeing an additional reset after calling SaveEntity before closing the connection in my code. I'm saving several entities within one api and after the 3 call to SaveEntity the underlying connection is closed, and therefore reset on the next call. Why might a connection be closed by SaveEntity?

Strange indeed. You save multiple entities in one routine? Do you start a transaction? Starting a transaction before the first save and committing it after the last save, keeps open the connection (of course) and makes sure the save is atomic.

Non-recursive saves as you perform, don't start their own transaction, so they then check if the connection is already open, if not, they open it. And at the end of the routine, it checks if the keepconnectionopen flag is set. If it's not set, connection is closed, otherwise it's kept open. (if a transaction is in progress it also is kept open).

You specify you don't want to refetch. SaveEntity() temporary manipulates keepConnectionOpen when refetch is set to true, so a refetch call doesn't close the connection. You specify false for that, so that's not an issue.

It's every 4th entity which produces the reset?

Yes, I am having the same problem. Using adapter model, when I call SaveEntity my connection is closed although I have set KeepConnectionOpen