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