public static void KillConnectionTest(string connectionString)
{
InternalConnectionWrapper wrapper = null;
using (SqlConnection connection = new(connectionString))
{
connection.Open();
wrapper = new InternalConnectionWrapper(connection);
using SqlCommand command = new("SELECT 5;", connection);
DataTestUtility.AssertEqualsWithDescription(5, command.ExecuteScalar(), "Incorrect scalar result.");
wrapper.KillConnection();
}
using (SqlConnection connection2 = new(connectionString))
{
connection2.Open();
Assert.False(wrapper.IsInternalConnectionOf(connection2), "New connection has internal connection that was just killed");
using SqlCommand command = new("SELECT 5;", connection2);
DataTestUtility.AssertEqualsWithDescription(5, command.ExecuteScalar(), "Incorrect scalar result.");
}
}
/// <summary>
/// Checks if an 'emancipated' internal connection is reclaimed when a new connection is opened AND we hit max pool size
/// NOTE: 'emancipated' means that the internal connection's SqlConnection has fallen out of scope and has no references, but was not explicitly disposed\closed
/// </summary>
/// <param name="connectionString"></param>
private static void ReclaimEmancipatedOnOpenTest(string connectionString)
{
string newConnectionString = (new SqlConnectionStringBuilder(connectionString)
{
MaxPoolSize = 1
}).ConnectionString;
SqlConnection.ClearAllPools();
InternalConnectionWrapper internalConnection = CreateEmancipatedConnection(newConnectionString);
ConnectionPoolWrapper connectionPool = internalConnection.ConnectionPool;
GC.Collect();
GC.WaitForPendingFinalizers();
DataTestUtility.AssertEqualsWithDescription(1, connectionPool.ConnectionCount, "Wrong number of connections in the pool.");
DataTestUtility.AssertEqualsWithDescription(0, connectionPool.FreeConnectionCount, "Wrong number of free connections in the pool.");
using (SqlConnection connection = new SqlConnection(newConnectionString))
{
connection.Open();
Assert.True(internalConnection.IsInternalConnectionOf(connection), "Connection has wrong internal connection");
Assert.True(connectionPool.ContainsConnection(connection), "Connection is in wrong connection pool");
}
}
/// <summary>
/// Tests that using the same connection string results in the same pool\internal connection and a different string results in a different pool\internal connection
/// </summary>
/// <param name="connectionString"></param>
private static void BasicConnectionPoolingTest(string connectionString)
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
InternalConnectionWrapper internalConnection = new InternalConnectionWrapper(connection);
ConnectionPoolWrapper connectionPool = new ConnectionPoolWrapper(connection);
connection.Close();
SqlConnection connection2 = new SqlConnection(connectionString);
connection2.Open();
Assert.True(internalConnection.IsInternalConnectionOf(connection2), "New connection does not use same internal connection");
Assert.True(connectionPool.ContainsConnection(connection2), "New connection is in a different pool");
connection2.Close();
SqlConnection connection3 = new SqlConnection(connectionString + ";App=SqlConnectionPoolUnitTest;");
connection3.Open();
Assert.False(internalConnection.IsInternalConnectionOf(connection3), "Connection with different connection string uses same internal connection");
Assert.False(connectionPool.ContainsConnection(connection3), "Connection with different connection string uses same connection pool");
connection3.Close();
connectionPool.Cleanup();
SqlConnection connection4 = new SqlConnection(connectionString);
connection4.Open();
Assert.True(internalConnection.IsInternalConnectionOf(connection4), "New connection does not use same internal connection");
Assert.True(connectionPool.ContainsConnection(connection4), "New connection is in a different pool");
connection4.Close();
}
public static void MaxPoolWaitForConnectionTest(string connectionString)
{
string newConnectionString = (new SqlConnectionStringBuilder(connectionString)
{
MaxPoolSize = 1
}).ConnectionString;
SqlConnection.ClearAllPools();
using SqlConnection connection1 = new SqlConnection(newConnectionString);
connection1.Open();
InternalConnectionWrapper internalConnection = new InternalConnectionWrapper(connection1);
ConnectionPoolWrapper connectionPool = new ConnectionPoolWrapper(connection1);
ManualResetEventSlim taskAllowedToSpeak = new ManualResetEventSlim(false);
Task waitTask = Task.Factory.StartNew(() => MaxPoolWaitForConnectionTask(newConnectionString, internalConnection, connectionPool, taskAllowedToSpeak));
int count = 5;
while (waitTask.Status == TaskStatus.WaitingToRun && count-- > 0)
{
Thread.Sleep(200);
}
Assert.Equal(TaskStatus.Running, waitTask.Status);
connection1.Close();
taskAllowedToSpeak.Set();
waitTask.Wait();
Assert.Equal(TaskStatus.RanToCompletion, waitTask.Status);
}
private static void MaxPoolWaitForConnectionTask(string connectionString, InternalConnectionWrapper internalConnection, ConnectionPoolWrapper connectionPool, ManualResetEventSlim waitToSpeak)
{
using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
waitToSpeak.Wait();
Assert.True(internalConnection.IsInternalConnectionOf(connection), "Connection has wrong internal connection");
Assert.True(connectionPool.ContainsConnection(connection), "Connection is in wrong connection pool");
}
public static void ConnectionResiliencyTest()
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(DataTestUtility.TCPConnectionString);
builder.ConnectRetryCount = 0;
builder.ConnectRetryInterval = 5;
// No connection resiliency
using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
{
conn.Open();
InternalConnectionWrapper wrapper = new InternalConnectionWrapper(conn, true, builder.ConnectionString);
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT TOP 1 * FROM dbo.Employees";
wrapper.KillConnectionByTSql();
bool cmdSuccess = false;
try
{
cmd.ExecuteScalar();
cmdSuccess = true;
}
// Windows always throws SqlException. Unix sometimes throws AggregateException against Azure SQL DB.
catch (Exception ex) when(ex is SqlException || ex is AggregateException)
{
}
Assert.False(cmdSuccess);
}
}
builder.ConnectRetryCount = 2;
using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
{
conn.Open();
InternalConnectionWrapper wrapper = new InternalConnectionWrapper(conn, true, builder.ConnectionString);
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT TOP 1 * FROM dbo.Employees";
using (SqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
}
wrapper.KillConnectionByTSql();
// Connection resiliency should reconnect transparently
using (SqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
}
}
}
}
// override object.Equals
public override bool Equals(object obj)
{
bool areEquals = false;
InternalConnectionWrapper objAsWrapper = obj as InternalConnectionWrapper;
if ((objAsWrapper != null) && (objAsWrapper._internalConnection == _internalConnection))
{
areEquals = true;
}
return(areEquals);
}
public static void AccessTokenConnectionPoolingTest()
{
// Remove cred info and add invalid token
string[] credKeys = { "User ID", "Password", "UID", "PWD", "Authentication" };
string connectionString = DataTestUtility.RemoveKeysInConnStr(DataTestUtility.AADPasswordConnectionString, credKeys);
SqlConnection connection = new SqlConnection(connectionString);
connection.AccessToken = DataTestUtility.GetAccessToken();
connection.Open();
InternalConnectionWrapper internalConnection = new InternalConnectionWrapper(connection);
ConnectionPoolWrapper connectionPool = new ConnectionPoolWrapper(connection);
connection.Close();
SqlConnection connection2 = new SqlConnection(connectionString);
connection2.AccessToken = DataTestUtility.GetAccessToken();
connection2.Open();
Assert.True(internalConnection.IsInternalConnectionOf(connection2), "New connection does not use same internal connection");
Assert.True(connectionPool.ContainsConnection(connection2), "New connection is in a different pool");
connection2.Close();
SqlConnection connection3 = new SqlConnection(connectionString + ";App=SqlConnectionPoolUnitTest;");
connection3.AccessToken = DataTestUtility.GetAccessToken();
connection3.Open();
Assert.False(internalConnection.IsInternalConnectionOf(connection3), "Connection with different connection string uses same internal connection");
Assert.False(connectionPool.ContainsConnection(connection3), "Connection with different connection string uses same connection pool");
connection3.Close();
connectionPool.Cleanup();
SqlConnection connection4 = new SqlConnection(connectionString);
connection4.AccessToken = DataTestUtility.GetAccessToken();
connection4.Open();
Assert.True(internalConnection.IsInternalConnectionOf(connection4), "New connection does not use same internal connection");
Assert.True(connectionPool.ContainsConnection(connection4), "New connection is in a different pool");
connection4.Close();
}
private static InternalConnectionWrapper ReplacementConnectionUsesSemaphoreTask(string connectionString, Barrier syncBarrier)
{
InternalConnectionWrapper internalConnection = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
internalConnection = new InternalConnectionWrapper(connection);
}
catch
{
syncBarrier.SignalAndWait();
throw;
}
syncBarrier.SignalAndWait();
}
return(internalConnection);
}
public void EventCounter_ReclaimedConnectionsCounter_Functional()
{
SqlConnection.ClearAllPools();
var stringBuilder = new SqlConnectionStringBuilder(DataTestUtility.TCPConnectionString)
{
Pooling = true, MaxPoolSize = 1
};
long rc = SqlClientEventSourceProps.ReclaimedConnections;
InternalConnectionWrapper internalConnection = CreateEmancipatedConnection(stringBuilder.ToString());
GC.Collect();
GC.WaitForPendingFinalizers();
using (SqlConnection conn = new SqlConnection(stringBuilder.ToString()))
{
conn.Open();
// when calling open, the connection is reclaimed.
Assert.Equal(rc + 1, SqlClientEventSourceProps.ReclaimedConnections);
}
}
public static void ConnectionResiliencySPIDTest()
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(DataTestUtility.TCPConnectionString);
builder.ConnectRetryCount = 0;
builder.ConnectRetryInterval = 5;
// No connection resiliency
using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
{
conn.Open();
InternalConnectionWrapper wrapper = new InternalConnectionWrapper(conn, true, builder.ConnectionString);
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT @@SPID";
wrapper.KillConnectionByTSql();
bool cmdSuccess = false;
try
{
cmd.ExecuteScalar();
cmdSuccess = true;
}
// Windows always throws SqlException. Unix sometimes throws AggregateException against Azure SQL DB.
catch (Exception ex) when(ex is SqlException || ex is AggregateException)
{
}
Assert.False(cmdSuccess);
}
}
builder.ConnectRetryCount = 2;
// Also check SPID changes with connection resiliency
using (SqlConnection conn = new SqlConnection(builder.ConnectionString))
{
conn.Open();
int clientSPID = conn.ServerProcessId;
int serverSPID = 0;
InternalConnectionWrapper wrapper = new InternalConnectionWrapper(conn, true, builder.ConnectionString);
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT @@SPID";
using (SqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
serverSPID = reader.GetInt16(0);
}
Assert.Equal(serverSPID, clientSPID);
// Also check SPID after query execution
Assert.Equal(serverSPID, conn.ServerProcessId);
wrapper.KillConnectionByTSql();
// Connection resiliency should reconnect transparently
using (SqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
serverSPID = reader.GetInt16(0);
}
// SPID should match server's SPID
Assert.Equal(serverSPID, conn.ServerProcessId);
}
}
}
private static void ReplacementConnectionUsesSemaphoreTest(string connectionString)
{
string newConnectionString = (new SqlConnectionStringBuilder(connectionString)
{
MaxPoolSize = 2, ConnectTimeout = 5
}).ConnectionString;
SqlConnection.ClearAllPools();
SqlConnection liveConnection = new SqlConnection(newConnectionString);
SqlConnection deadConnection = new SqlConnection(newConnectionString);
liveConnection.Open();
deadConnection.Open();
InternalConnectionWrapper deadConnectionInternal = new InternalConnectionWrapper(deadConnection);
InternalConnectionWrapper liveConnectionInternal = new InternalConnectionWrapper(liveConnection);
deadConnectionInternal.KillConnection();
deadConnection.Close();
liveConnection.Close();
Task <InternalConnectionWrapper>[] tasks = new Task <InternalConnectionWrapper> [3];
Barrier syncBarrier = new Barrier(tasks.Length);
Func <InternalConnectionWrapper> taskFunction = (() => ReplacementConnectionUsesSemaphoreTask(newConnectionString, syncBarrier));
for (int i = 0; i < tasks.Length; i++)
{
tasks[i] = Task.Factory.StartNew <InternalConnectionWrapper>(taskFunction);
}
bool taskWithLiveConnection = false;
bool taskWithNewConnection = false;
bool taskWithCorrectException = false;
Task waitAllTask = Task.Factory.ContinueWhenAll(tasks, (completedTasks) =>
{
foreach (var item in completedTasks)
{
if (item.Status == TaskStatus.Faulted)
{
// One task should have a timeout exception
if ((!taskWithCorrectException) && (item.Exception.InnerException is InvalidOperationException) && (item.Exception.InnerException.Message.StartsWith(SystemDataResourceManager.Instance.ADP_PooledOpenTimeout)))
{
taskWithCorrectException = true;
}
else if (!taskWithCorrectException)
{
// Rethrow the unknown exception
ExceptionDispatchInfo exceptionInfo = ExceptionDispatchInfo.Capture(item.Exception);
exceptionInfo.Throw();
}
}
else if (item.Status == TaskStatus.RanToCompletion)
{
// One task should get the live connection
if (item.Result.Equals(liveConnectionInternal))
{
if (!taskWithLiveConnection)
{
taskWithLiveConnection = true;
}
}
else if (!item.Result.Equals(deadConnectionInternal) && !taskWithNewConnection)
{
taskWithNewConnection = true;
}
}
else
{
Console.WriteLine("ERROR: Task in unknown state: {0}", item.Status);
}
}
});
waitAllTask.Wait();
Assert.True(taskWithLiveConnection && taskWithNewConnection && taskWithCorrectException, string.Format("Tasks didn't finish as expected.\nTask with live connection: {0}\nTask with new connection: {1}\nTask with correct exception: {2}\n", taskWithLiveConnection, taskWithNewConnection, taskWithCorrectException));
}
