1 // Licensed to the .NET Foundation under one or more agreements. 2 // The .NET Foundation licenses this file to you under the MIT license. 3 // See the LICENSE file in the project root for more information. 4 5 using System.Runtime.CompilerServices; 6 using Xunit; 7 8 namespace System.Data.SqlClient.ManualTesting.Tests 9 { 10 public static class MARSSessionPoolingTest 11 { 12 private const string COMMAND_STATUS = "select count(*) as ConnectionCount from sys.dm_exec_connections where session_id=@@spid and net_transport='Session'; select count(*) as ActiveRequestCount from sys.dm_exec_requests where session_id=@@spid and status='running' or session_id=@@spid and status='suspended'"; 13 private const string COMMAND_SPID = "select @@spid"; 14 private const int CONCURRENT_COMMANDS = 5; 15 16 private const string _COMMAND_RPC = "sp_who"; 17 private const string _COMMAND_SQL = 18 "select * from sys.databases; select * from sys.databases; select * from sys.databases; select * from sys.databases; select * from sys.databases; " + 19 "select * from sys.databases; select * from sys.databases; select * from sys.databases; select * from sys.databases; select * from sys.databases; " + 20 "select * from sys.databases; select * from sys.databases; select * from sys.databases; select * from sys.databases; select * from sys.databases; " + 21 "select * from sys.databases; select * from sys.databases; select * from sys.databases; select * from sys.databases; select * from sys.databases; " + 22 "select * from sys.databases; print 'THIS IS THE END!'"; 23 24 private static readonly string _testConnString = 25 (new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr) 26 { 27 PacketSize = 512, 28 MaxPoolSize = 1, 29 MultipleActiveResultSets = true 30 }).ConnectionString; 31 32 [CheckConnStrSetupFact] MarsExecuteScalar_AllFlavors()33 public static void MarsExecuteScalar_AllFlavors() 34 { 35 TestMARSSessionPooling("Case: Text, ExecuteScalar", _testConnString, CommandType.Text, ExecuteType.ExecuteScalar, ReaderTestType.ReaderClose, GCType.Wait); 36 TestMARSSessionPooling("Case: RPC, ExecuteScalar", _testConnString, CommandType.StoredProcedure, ExecuteType.ExecuteScalar, ReaderTestType.ReaderClose, GCType.Wait); 37 } 38 39 [CheckConnStrSetupFact] MarsExecuteNonQuery_AllFlavors()40 public static void MarsExecuteNonQuery_AllFlavors() 41 { 42 TestMARSSessionPooling("Case: Text, ExecuteNonQuery", _testConnString, CommandType.Text, ExecuteType.ExecuteNonQuery, ReaderTestType.ReaderClose, GCType.Wait); 43 TestMARSSessionPooling("Case: RPC, ExecuteNonQuery", _testConnString, CommandType.StoredProcedure, ExecuteType.ExecuteNonQuery, ReaderTestType.ReaderClose, GCType.Wait); 44 } 45 46 [CheckConnStrSetupFact] MarsExecuteReader_Text_NoGC()47 public static void MarsExecuteReader_Text_NoGC() 48 { 49 TestMARSSessionPooling("Case: Text, ExecuteReader, ReaderClose", _testConnString, CommandType.Text, ExecuteType.ExecuteReader, ReaderTestType.ReaderClose, GCType.Wait); 50 TestMARSSessionPooling("Case: Text, ExecuteReader, ReaderDispose", _testConnString, CommandType.Text, ExecuteType.ExecuteReader, ReaderTestType.ReaderDispose, GCType.Wait); 51 TestMARSSessionPooling("Case: Text, ExecuteReader, ConnectionClose", _testConnString, CommandType.Text, ExecuteType.ExecuteReader, ReaderTestType.ConnectionClose, GCType.Wait); 52 } 53 54 [CheckConnStrSetupFact] MarsExecuteReader_RPC_NoGC()55 public static void MarsExecuteReader_RPC_NoGC() 56 { 57 TestMARSSessionPooling("Case: RPC, ExecuteReader, ReaderClose", _testConnString, CommandType.StoredProcedure, ExecuteType.ExecuteReader, ReaderTestType.ReaderClose, GCType.Wait); 58 TestMARSSessionPooling("Case: RPC, ExecuteReader, ReaderDispose", _testConnString, CommandType.StoredProcedure, ExecuteType.ExecuteReader, ReaderTestType.ReaderDispose, GCType.Wait); 59 TestMARSSessionPooling("Case: RPC, ExecuteReader, ConnectionClose", _testConnString, CommandType.StoredProcedure, ExecuteType.ExecuteReader, ReaderTestType.ConnectionClose, GCType.Wait); 60 } 61 62 [CheckConnStrSetupFact] MarsExecuteReader_Text_WithGC()63 public static void MarsExecuteReader_Text_WithGC() 64 { 65 TestMARSSessionPooling("Case: Text, ExecuteReader, GC-Wait", _testConnString, CommandType.Text, ExecuteType.ExecuteReader, ReaderTestType.ReaderGC, GCType.Wait); 66 TestMARSSessionPooling("Case: Text, ExecuteReader, GC-NoWait", _testConnString, CommandType.Text, ExecuteType.ExecuteReader, ReaderTestType.ReaderGC, GCType.NoWait); 67 } 68 69 [CheckConnStrSetupFact] MarsExecuteReader_StoredProcedure_WithGC()70 public static void MarsExecuteReader_StoredProcedure_WithGC() 71 { 72 TestMARSSessionPooling("Case: RPC, ExecuteReader, GC-Wait", _testConnString, CommandType.StoredProcedure, ExecuteType.ExecuteReader, ReaderTestType.ReaderGC, GCType.Wait); 73 TestMARSSessionPooling("Case: RPC, ExecuteReader, GC-NoWait", _testConnString, CommandType.StoredProcedure, ExecuteType.ExecuteReader, ReaderTestType.ReaderGC, GCType.NoWait); 74 75 TestMARSSessionPooling("Case: Text, ExecuteReader, NoCloses", _testConnString + " ", CommandType.Text, ExecuteType.ExecuteReader, ReaderTestType.NoCloses, GCType.Wait); 76 TestMARSSessionPooling("Case: RPC, ExecuteReader, NoCloses", _testConnString + " ", CommandType.StoredProcedure, ExecuteType.ExecuteReader, ReaderTestType.NoCloses, GCType.Wait); 77 } 78 79 private enum ExecuteType 80 { 81 ExecuteScalar, 82 ExecuteNonQuery, 83 ExecuteReader, 84 } 85 86 private enum ReaderTestType 87 { 88 ReaderClose, 89 ReaderDispose, 90 ReaderGC, 91 ConnectionClose, 92 NoCloses, 93 } 94 95 private enum GCType 96 { 97 Wait, 98 NoWait, 99 } 100 101 [MethodImpl(MethodImplOptions.NoInlining)] TestMARSSessionPooling(string caseName, string connectionString, CommandType commandType, ExecuteType executeType, ReaderTestType readerTestType, GCType gcType)102 private static void TestMARSSessionPooling(string caseName, string connectionString, CommandType commandType, 103 ExecuteType executeType, ReaderTestType readerTestType, GCType gcType) 104 { 105 SqlCommand[] cmd = new SqlCommand[CONCURRENT_COMMANDS]; 106 SqlDataReader[] gch = new SqlDataReader[CONCURRENT_COMMANDS]; 107 108 using (SqlConnection con = new SqlConnection(connectionString)) 109 { 110 con.Open(); 111 112 for (int i = 0; i < CONCURRENT_COMMANDS; i++) 113 { 114 // Prepare all commands 115 cmd[i] = con.CreateCommand(); 116 switch (commandType) 117 { 118 case CommandType.Text: 119 cmd[i].CommandText = _COMMAND_SQL; 120 cmd[i].CommandTimeout = 120; 121 break; 122 case CommandType.StoredProcedure: 123 cmd[i].CommandText = _COMMAND_RPC; 124 cmd[i].CommandTimeout = 120; 125 cmd[i].CommandType = CommandType.StoredProcedure; 126 break; 127 } 128 } 129 130 for (int i = 0; i < CONCURRENT_COMMANDS; i++) 131 { 132 switch (executeType) 133 { 134 case ExecuteType.ExecuteScalar: 135 cmd[i].ExecuteScalar(); 136 break; 137 case ExecuteType.ExecuteNonQuery: 138 cmd[i].ExecuteNonQuery(); 139 break; 140 case ExecuteType.ExecuteReader: 141 if (readerTestType != ReaderTestType.ReaderGC) 142 gch[i] = cmd[i].ExecuteReader(); 143 144 switch (readerTestType) 145 { 146 case ReaderTestType.ReaderClose: 147 { 148 gch[i].Dispose(); 149 break; 150 } 151 case ReaderTestType.ReaderDispose: 152 gch[i].Dispose(); 153 break; 154 case ReaderTestType.ReaderGC: 155 gch[i] = null; 156 WeakReference weak = OpenReaderThenNullify(cmd[i]); 157 GC.Collect(); 158 159 if (gcType == GCType.Wait) 160 { 161 GC.WaitForPendingFinalizers(); 162 Assert.False(weak.IsAlive, "Error - target still alive!"); 163 } 164 break; 165 case ReaderTestType.ConnectionClose: 166 GC.SuppressFinalize(gch[i]); 167 con.Close(); 168 con.Open(); 169 break; 170 case ReaderTestType.NoCloses: 171 GC.SuppressFinalize(gch[i]); 172 break; 173 } 174 break; 175 } 176 177 if (readerTestType != ReaderTestType.NoCloses) 178 { 179 con.Close(); 180 con.Open(); // Close and open, to re-assure collection! 181 } 182 183 SqlCommand verificationCmd = con.CreateCommand(); 184 185 verificationCmd.CommandText = COMMAND_STATUS; 186 using (SqlDataReader rdr = verificationCmd.ExecuteReader()) 187 { 188 rdr.Read(); 189 int connections = (int)rdr.GetValue(0); 190 rdr.NextResult(); 191 rdr.Read(); 192 int requests = (int)rdr.GetValue(0); 193 194 switch (executeType) 195 { 196 case ExecuteType.ExecuteScalar: 197 case ExecuteType.ExecuteNonQuery: 198 // 1 for connection, 1 for command 199 Assert.True(connections == 2, "Failure - incorrect number of connections for ExecuteScalar! #connections: " + connections); 200 201 // only 1 executing 202 Assert.True(requests == 1, "Failure - incorrect number of requests for ExecuteScalar! #requests: " + requests); 203 break; 204 case ExecuteType.ExecuteReader: 205 switch (readerTestType) 206 { 207 case ReaderTestType.ReaderClose: 208 case ReaderTestType.ReaderDispose: 209 case ReaderTestType.ConnectionClose: 210 // 1 for connection, 1 for command 211 Assert.True(connections == 2, "Failure - Incorrect number of connections for ReaderClose / ReaderDispose / ConnectionClose! #connections: " + connections); 212 213 // only 1 executing 214 Assert.True(requests == 1, "Failure - incorrect number of requests for ReaderClose/ReaderDispose/ConnectionClose! #requests: " + requests); 215 break; 216 case ReaderTestType.ReaderGC: 217 switch (gcType) 218 { 219 case GCType.Wait: 220 // 1 for connection, 1 for open reader 221 Assert.True(connections == 2, "Failure - incorrect number of connections for ReaderGCWait! #connections: " + connections); 222 // only 1 executing 223 Assert.True(requests == 1, "Failure - incorrect number of requests for ReaderGCWait! #requests: " + requests); 224 break; 225 case GCType.NoWait: 226 // 1 for connection, 1 for open reader 227 Assert.True(connections == 2, "Failure - incorrect number of connections for ReaderGCNoWait! #connections: " + connections); 228 229 // only 1 executing 230 Assert.True(requests == 1, "Failure - incorrect number of requests for ReaderGCNoWait! #requests: " + requests); 231 break; 232 } 233 break; 234 case ReaderTestType.NoCloses: 235 // 1 for connection, 1 for current command, 1 for 0 based array offset, plus i for open readers 236 Assert.True(connections == (3 + i), "Failure - incorrect number of connections for NoCloses: " + connections); 237 238 // 1 for current command, 1 for 0 based array offset, plus i open readers 239 Assert.True(requests == (2 + i), "Failure - incorrect number of requests for NoCloses: " + requests); 240 break; 241 } 242 break; 243 } 244 } 245 } 246 } 247 } 248 OpenReaderThenNullify(SqlCommand command)249 private static WeakReference OpenReaderThenNullify(SqlCommand command) 250 { 251 SqlDataReader reader = command.ExecuteReader(); 252 WeakReference weak = new WeakReference(reader); 253 reader = null; 254 return weak; 255 } 256 } 257 } 258