Page 1 of 2
Reset Connection
Posted: Wed 04 Apr 2012 12:26
by abadia
When we use the native connection on .NET framework (System.Data.SqlClient), by taking a connection from the pool, the connection is reseted, restoring the connection settings and deleting any temporary tables. Actually the native connection runs the stored procedure "sp_reset_connection" to do this. How do I get the same effect using dotConnect for SQL Server?
Posted: Fri 06 Apr 2012 12:06
by Pinturiccio
We will investigate the possibility to add this feature and notify you as soon as possible.
Re: Reset Connection
Posted: Wed 11 Jul 2012 12:10
by Pinturiccio
Try using the Connection Reset parameter in your connection string. For more information, please refer to
http://www.devart.com/dotconnect/sqlser ... tring.html
Re: Reset Connection
Posted: Mon 23 Jul 2012 05:14
by abadia
Sorry for the lack of clarity. I have used that parameter and it does not work. Is there anything else I should do for work?
Re: Reset Connection
Posted: Fri 27 Jul 2012 13:57
by Shalex
Thank you for your report. We have reproduced the problem. We will post here when it is fixed.
Re: Reset Connection
Posted: Wed 01 Aug 2012 13:21
by Shalex
sp_reset_connection is documented as an internal-use-only API system stored procedure:
http://msdn.microsoft.com/en-us/library/ms187961
http://social.msdn.microsoft.com/Forums ... 49dcfbd754
Unfortunately, there is no way to call sp_reset_connection with our provider (no support). We will correct our documentation.
Re: Reset Connection
Posted: Thu 18 Oct 2012 14:08
by abadia
I understand that and this is one of the stored procedures for internal use. Actually there is no other purpose for it in an application. It serves only to an implementation of a connection pool, which applies in this case.
There is no way to solve the problem without calling this procedure.
Currently, it is present in all versions of SQL Server at least from the 2005 version. You can create a conditional running the version of SQL Server.
The insistence on this is that its implementation is adherent to the internal pool functionality of the .net framework.
We have a development environment with a lot of legacy code, not always where temporary tables are removed at the end of use, or environment variables are returned to the previous value.
http://www.sqlmag.com/article/sql-serve ... -procedure
Please reconsider this issue. I think it's so important for us and for many other users who use the pool of connections.
Re: Reset Connection
Posted: Fri 19 Oct 2012 11:50
by Pinturiccio
Unfortunately we can't implement the call of this function, as it's closed. If Microsoft gives access to call this function, we will implement this functionality.
Re: Reset Connection
Posted: Fri 19 Oct 2012 13:03
by abadia
Microsoft does not impose any limitation on the use of this procedure.
Actually, I managed to simulate the operation of the native connection pooling.
Of course a solution in the component itself would be much more elegant and would not provide any compatibility issue because who does not want to use just does not pass the parameter in the connection string.
Here's what I did:
Code: Select all
private SqlConnection cn = new SqlConnection();
private SqlCommand resetConnectionCmd;
public void Connect()
{
if (cn.ConnectionString == String.Empty)
{
cn.ConnectionString = ConnectionString;
cn.StateChange += (sender, e) =>
{
if (e.CurrentState == ConnectionState.Open)
{
if (resetConnectionCmd == null)
{
resetConnectionCmd = ((SqlConnection)sender).CreateCommand();
resetConnectionCmd.CommandText = "sp_reset_connection";
resetConnectionCmd.CommandType = CommandType.StoredProcedure;
}
resetConnectionCmd.ExecuteNonQuery();
}
};
}
cn.Open();
}
Re: Reset Connection
Posted: Wed 24 Oct 2012 13:43
by Pinturiccio
We will investigate the possibility to implement this functionality and notify you as soon as possible.
Re: Reset Connection
Posted: Wed 14 Nov 2012 11:32
by Pinturiccio
We have fixed the bug with the 'Connection Reset' connection string parameter. We will post here when the corresponding build of dotConnect for SQL Server is available for download.
Re: Reset Connection
Posted: Thu 15 Nov 2012 15:08
by Pinturiccio
The new build of dotConnect for SQL Server 2.60.615 is available for download now!
It can be downloaded from
http://www.devart.com/dotconnect/sqlser ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to
http://forums.devart.com/viewtopic.php?t=25283
Re: Reset Connection
Posted: Thu 22 Nov 2012 12:12
by abadia
I'm very happy that you made this feature. I've tested and observed the execution in my log with SQLMonitor and I have a suggestion: IMHO, since sp_resset_connection is called many times, it could be a attribute of the connection object instatiated when 'Connection Reset' parameter is "true". Thus you avoid recriation of the SQLCommand and improve speed and memory usage.
Re: Reset Connection
Posted: Fri 23 Nov 2012 11:58
by Pinturiccio
If we understand you right, you want us to implement such property for the SqlConnection object that would allow you to specify explicitly whether sp_reset_connection should be performed for a specific connection instance or not.
I.e. the possibility to choose whether to run sp_reset_connection for every specific connection instance, not for all connections using this internal connection from the pool.
Re: Reset Connection
Posted: Fri 23 Nov 2012 16:09
by abadia
No. This feature is functionally perfect as is. My suggestion is just about an improvement over implementation. Since the object SQLCommand is created on each execution of the procedure, it can be cached on a private attribute (not property).