using System;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.OracleClient;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.OracleClient;
namespace ConnectionPooling
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try { ConnectionLeak(); }
catch (SqlException)
{
//do something cool to recover
}
MessageBox.Show("Done");
}
{
try { ConnectionLeak(); }
catch (SqlException)
{
//do something cool to recover
}
MessageBox.Show("Done");
}
public void ConnectionLeak()
{
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
@"Data Source=.;"
+ "Database=PUBS;"
+ "Integrated Security=True;"
+ "Min Pool Size=3;Max Pool Size=5");
SqlConnection cn = new SqlConnection(cnSettings.ConnectionString);
cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "raiserror ('Simulate an error in sql', 17,1)";
cmd.ExecuteNonQuery(); //throws the SqlException
cn.Close(); //Call connection close, but this code is not reached
}
{
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
@"Data Source=.;"
+ "Database=PUBS;"
+ "Integrated Security=True;"
+ "Min Pool Size=3;Max Pool Size=5");
SqlConnection cn = new SqlConnection(cnSettings.ConnectionString);
cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "raiserror ('Simulate an error in sql', 17,1)";
cmd.ExecuteNonQuery(); //throws the SqlException
cn.Close(); //Call connection close, but this code is not reached
}
private void button2_Click(object sender, EventArgs e)
{
try { NoConnectionLeak(); }
catch (SqlException)
{
//do something cool to recover
}
MessageBox.Show("Done");
}
public void NoConnectionLeak()
{
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
@"Data Source=.;"
+ "Database=PUBS;"
+ "Integrated Security=True;"
+ "Max Pool Size=5");
{
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
@"Data Source=.;"
+ "Database=PUBS;"
+ "Integrated Security=True;"
+ "Max Pool Size=5");
using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "raiserror ('Simulate an error in sql', 17,1)";
cmd.ExecuteNonQuery(); //throws the SqlException
}
cn.Close(); //Call connection close, but this code is not reached
}
}
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "raiserror ('Simulate an error in sql', 17,1)";
cmd.ExecuteNonQuery(); //throws the SqlException
}
cn.Close(); //Call connection close, but this code is not reached
}
}
private void button3_Click(object sender, EventArgs e)
{
string ver = null;
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
@"Data Source=.;"
+ "Database=PUBS;"
+ "Integrated Security=True;"
+ "Max Pool Size=5");
{
string ver = null;
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
@"Data Source=.;"
+ "Database=PUBS;"
+ "Integrated Security=True;"
+ "Max Pool Size=5");
using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "Select @@Version";
ver = (string)cmd.ExecuteScalar();
}
}
MessageBox.Show(ver);
}
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "Select @@Version";
ver = (string)cmd.ExecuteScalar();
}
}
MessageBox.Show(ver);
}
private void button4_Click(object sender, EventArgs e)
{
try
{
DisplayVersion();
}
catch (SqlException xcp)
{
if (xcp.Number != 1236) throw xcp; //first chance?
System.Diagnostics.Debug.WriteLine("Clearing Pools");
SqlConnection.ClearAllPools();//recover
DisplayVersion();//retry
}
}
public void DisplayVersion()
{
string ver = null;
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
@"Data Source=.;"
+ "Database=PUBS;"
+ "Integrated Security=True;"
+ "Max Pool Size=5");
{
string ver = null;
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
@"Data Source=.;"
+ "Database=PUBS;"
+ "Integrated Security=True;"
+ "Max Pool Size=5");
using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "Select @@Version";
ver = (string)cmd.ExecuteScalar();
}
}
MessageBox.Show(ver);
}
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "Select @@Version";
ver = (string)cmd.ExecuteScalar();
}
}
MessageBox.Show(ver);
}
private void button5_Click(object sender, EventArgs e)
{
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
"Data Source=.;"
+ "Database=FailTest;"
+ "Integrated Security=True;"
+ "Max Pool Size=5;"
+ @"FailOver Partner=.\Partner");
{
SqlConnectionStringBuilder cnSettings;
cnSettings = new SqlConnectionStringBuilder(
"Data Source=.;"
+ "Database=FailTest;"
+ "Integrated Security=True;"
+ "Max Pool Size=5;"
+ @"FailOver Partner=.\Partner");
using (SqlConnection cn = new SqlConnection(cnSettings.ConnectionString))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = string.Format(
"Insert into TestTable(Id, Name) Values('{0}','{1}')",
Guid.NewGuid(),DateTime.Now.ToLongTimeString());
cmd.ExecuteNonQuery();
}
MessageBox.Show("Data entered into server: " + cn.DataSource);
}
}
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = string.Format(
"Insert into TestTable(Id, Name) Values('{0}','{1}')",
Guid.NewGuid(),DateTime.Now.ToLongTimeString());
cmd.ExecuteNonQuery();
}
MessageBox.Show("Data entered into server: " + cn.DataSource);
}
}
}
}
}
转载于:https://blog.51cto.com/C/10486