ASP.NET

ASP.NET中连接池和sql server中的最大连接数

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