Reset Connection

[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?

[Pinturiccio]

We will investigate the possibility to add this feature and notify you as soon as possible.

[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

[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?

[Shalex]

Thank you for your report. We have reproduced the problem. We will post here when it is fixed.

[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.

[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.

[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.

[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();
}

[Pinturiccio]

We will investigate the possibility to implement this functionality and notify you as soon as possible.

[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.

[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

[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.

[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.

[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).