using System;
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();
      }

      private void button1_Click(object sender, EventArgs e)
      {
         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
      }

      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");

         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
         }
      }

      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");

         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);
      }

      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");

         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);
      }

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");

   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);
   }
}

   }
}