学步园

连接池是可以自动回收无效链接的；
我的机器上的sql server的max_connection 是32767；
所以我写了个测试程序来测试连接池的最大连接数就是max_connection，但是由于连接池自己的回收机制，总是不能如愿，但是发现连接池的Max Size不管设置多大，不同的机器到最后总是一定的，比方说说是250或336等；每建立一个和连接池的链接，会自动生成一个sqlserver.exe和之对应通信；
只要是连接池的连接字串一样，不管你在哪里链接，都会使用同一个连接池来响应的；
所以说一个连接池的最大连接数目是很小的；sql server的理论最大连接数是32767，但是能不能达到我不知道；

参考：

http://www.sqljunkies.com/WebLog/sqldude/archive/2004/06/14/3146.aspx

http://msdn.microsoft.com/zh-cn/library/8xx3tyca(en-us,vs.71).aspx

http://msdn.microsoft.com/zh-cn/library/ms254503.aspx

源码：

using System;<br /> using System.Data.SqlClient;<br /> using System.Diagnostics;<br /> using System.Runtime.InteropServices;<br /> namespace ConsoleApplication1<br /> {<br /> class Program<br /> {<br /> PerformanceCounter[] PerfCounters = new PerformanceCounter[10];<br /> SqlConnection connection = new SqlConnection();<br /> static void Main()<br /> {<br /> Program prog = new Program();<br /> // Open a connection and create the performance counters.<br /> prog.connection.ConnectionString =<br /> GetIntegratedSecurityConnectionString();<br /> prog.SetUpPerformanceCounters();<br /> Console.WriteLine("Available Performance Counters:");<br /> // Create the connections and display the results.<br /> prog.CreateConnections();<br /> Console.WriteLine("Press Enter to finish.");<br /> Console.ReadLine();<br /> }<br /> private void CreateConnections()<br /> {<br /> // List the Performance counters.<br /> WritePerformanceCounters();<br /> CreateManyConnetcion(3276800);<br /> // Create 4 connections and display counter information.<br /> SqlConnection connection1 = new SqlConnection(<br /> GetIntegratedSecurityConnectionString());<br /> connection1.Open();<br /> Console.WriteLine("Opened the 1st Connection:");<br /> WritePerformanceCounters();<br /> SqlConnection connection2 = new SqlConnection(<br /> GetIntegratedSecurityConnectionString());<br /> connection2.Open();<br /> Console.WriteLine("Opened the 2nd Connection:");<br /> WritePerformanceCounters();<br /> SqlConnection connection3 = new SqlConnection(<br /> GetIntegratedSecurityConnectionString());<br /> connection3.Open();<br /> Console.WriteLine("Opened the 3rd Connection:");<br /> WritePerformanceCounters();<br /> SqlConnection connection4 = new SqlConnection(<br /> GetIntegratedSecurityConnectionString());<br /> connection4.Open();<br /> Console.WriteLine("Opened the 4th Connection:");<br /> WritePerformanceCounters();<br /> SqlConnection connection5 = new SqlConnection(<br /> GetIntegratedSecurityConnectionString());<br /> try<br /> {<br /> connection5.Open();<br /> }<br /> catch (System.Exception ex)<br /> {<br /> Console.WriteLine(ex.ToString());<br /> }</p> <p> Console.WriteLine("Opened the 5th Connection:");<br /> WritePerformanceCounters();<br /> connection1.Close();<br /> Console.WriteLine("Closed the 1st Connection:");<br /> WritePerformanceCounters();<br /> connection2.Close();<br /> Console.WriteLine("Closed the 2nd Connection:");<br /> WritePerformanceCounters();<br /> connection3.Close();<br /> Console.WriteLine("Closed the 3rd Connection:");<br /> WritePerformanceCounters();<br /> connection4.Close();<br /> Console.WriteLine("Closed the 4th Connection:");<br /> WritePerformanceCounters();<br /> }<br /> private void CreateManyConnetcion(int number)<br /> {<br /> for (int i = 0; i <= number; i ++)<br /> {<br /> SqlConnection connection1 = new SqlConnection(<br /> GetIntegratedSecurityConnectionString());<br /> try<br /> {<br /> connection1.Open();<br /> }<br /> catch (System.Exception ex)<br /> {<br /> Console.WriteLine(ex.ToString());<br /> }<br /> Console.WriteLine("Opened the " + i +"st Connection:");<br /> WritePerformanceCounters();<br /> if(i > 32765)<br /> {<br /> Console.Read();<br /> }<br /> }<br /> }<br /> private enum ADO_Net_Performance_Counters<br /> {<br /> NumberOfActiveConnectionPools,<br /> NumberOfReclaimedConnections,<br /> HardConnectsPerSecond,<br /> HardDisconnectsPerSecond,<br /> NumberOfActiveConnectionPoolGroups,<br /> NumberOfInactiveConnectionPoolGroups,<br /> NumberOfInactiveConnectionPools,<br /> NumberOfNonPooledConnections,<br /> NumberOfPooledConnections,<br /> NumberOfStasisConnections<br /> // The following performance counters are more expensive to track.<br /> // Enable ConnectionPoolPerformanceCounterDetail in your config file.<br /> // SoftConnectsPerSecond<br /> // SoftDisconnectsPerSecond<br /> // NumberOfActiveConnections<br /> // NumberOfFreeConnections<br /> }<br /> private void SetUpPerformanceCounters()<br /> {<br /> connection.Close();<br /> this.PerfCounters = new PerformanceCounter[10];<br /> string instanceName = GetInstanceName();<br /> Type apc = typeof(ADO_Net_Performance_Counters);<br /> int i = 0;<br /> foreach (string s in Enum.GetNames(apc))<br /> {<br /> this.PerfCounters[i] = new PerformanceCounter();<br /> this.PerfCounters[i].CategoryName = ".NET Data Provider for SqlServer";<br /> this.PerfCounters[i].CounterName = s;<br /> this.PerfCounters[i].InstanceName = instanceName;<br /> i++;<br /> }<br /> }<br /> [DllImport("kernel32.dll", SetLastError = true)]<br /> static extern int GetCurrentProcessId();<br /> private string GetInstanceName()<br /> {<br /> //This works for Winforms apps.<br /> string instanceName =<br /> System.Reflection.Assembly.GetEntryAssembly().GetName().Name;<br /> // Must replace special characters like (, ), #, /, //<br /> string instanceName2 =<br /> AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(', '[')<br /> .Replace(')', ']').Replace('#', '_').Replace('/', '_').Replace('//', '_');<br /> // For ASP.NET applications your instanceName will be your CurrentDomain's<br /> // FriendlyName. Replace the line above that sets the instanceName with this:<br /> // instanceName = AppDomain.CurrentDomain.FriendlyName.ToString().Replace('(','[')<br /> // .Replace(')',']').Replace('#','_').Replace('/','_').Replace('//','_');<br /> string pid = GetCurrentProcessId().ToString();<br /> instanceName = instanceName + "[" + pid + "]";<br /> Console.WriteLine("Instance Name: {0}", instanceName);<br /> Console.WriteLine("---------------------------");<br /> return instanceName;<br /> }<br /> private void WritePerformanceCounters()<br /> {<br /> Console.WriteLine("---------------------------");<br /> foreach (PerformanceCounter p in this.PerfCounters)<br /> {<br /> Console.WriteLine("{0} = {1}", p.CounterName, p.NextValue());<br /> }<br /> Console.WriteLine("---------------------------");<br /> }<br /> private static string GetIntegratedSecurityConnectionString()<br /> {<br /> // To avoid storing the connection string in your code,<br /> // you can retrive it from a configuration file.<br /> return @"Data Source=192.168.10.3/SQLEXPRESS;Connection Lifetime =1;Initial Catalog=test;User ID=sa;Password=1;Max Pool Size=32769;Min Pool Size=0;";<br /> }<br /> private static string GetSqlConnectionString()<br /> {<br /> // To avoid storing the connection string in your code,<br /> // you can retrive it from a configuration file.<br /> return @"Data Source=192.168.10.2/SQLEXPRESS;Initial Catalog=test;User ID=sa;Password=1;";<br /> // "Initial Catalog=AdventureWorks";<br /> }<br /> private static string GetSqlConnectionStringDifferent()<br /> {<br /> // To avoid storing the connection string in your code,<br /> // you can retrive it from a configuration file.<br /> return @"Data Source=192.168.10.1/SQLEXPRESS;Initial Catalog=test;User ID=sa;Password=1;Max Pool Size=100;Min Pool Size=0;";<br /> }<br /> }<br /> }<br />