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