Csharp/CSharp Tutorial/ADO.Net/SqlConnection

Connection Display

using System;
using System.Data;
using System.Data.SqlClient;
    class ConnectionDisplay
    {
        static void Main()
        {
            SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;user id=administrator;integrated security = true;");
            try
            {
                conn.Open();
                Console.WriteLine("\tConnection String: {0}",conn.ConnectionString);
                Console.WriteLine("\tDatabase: {0}",conn.Database);
                Console.WriteLine("\tDataSource: {0}",conn.DataSource);
                Console.WriteLine("\tServerVersion: {0}",conn.ServerVersion);
                Console.WriteLine("\tState: {0}",conn.State);
                Console.WriteLine("\tWorkstationId: {0}",conn.WorkstationId);
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e);
            }
            finally
            {
                conn.Close();
                Console.WriteLine("Connection closed.");
            }
        }
    }

Connection event: state changed and information

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
public class MainClass{
   public static void Main(){
    SqlConnection cn = new SqlConnection();
    try
    {
      cn.StateChange += new StateChangeEventHandler(StateChange);
      cn.InfoMessage += new SqlInfoMessageEventHandler(InfoMessage);
      cn.ConnectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
      cn.Open();
      cn.ChangeDatabase("Biblio");
    }
    catch( Exception ex )
    {
      Console.WriteLine(ex.Message);
    }
    finally
    {
      // Unsubscribe from the Events we subscribed to.
      cn.StateChange -= new StateChangeEventHandler(StateChange);
      cn.InfoMessage -= new SqlInfoMessageEventHandler(InfoMessage);
    }
   }
  private static void StateChange(object sender, System.Data.StateChangeEventArgs e)
  {
    Console.WriteLine("State was:" + e.OriginalState.ToString() + ". New state = " + e.CurrentState.ToString() );
  }
  
  private static void InfoMessage( object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
  {
    int i;
    for (i=0; i < e.Errors.Count; i++ )
    {
      Console.WriteLine(e.Errors[i].Message);
    }
  }
}

State was:Closed. New state = Open
Could not locate entry in sysdatabases for database "Biblio". No entry found with that name. Make su
re that the name is entered correctly.

Connect to SQLserver

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
   static void Main(string[] args)
   {
      string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
      SqlConnection conn = new SqlConnection(connString); 
      try {
         conn.Open();
         Console.WriteLine("Connection opened.");
      }
      catch (SqlException e) {
         Console.WriteLine("Error: " + e);
      }
      finally {
         conn.Close();
         Console.WriteLine("Connection closed.");
      }
   }
}

Connection opened.
Connection closed.

Connect to SqlServer and set the IP Address

using System;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string connectString =
                "Network Library=dbmssocn;Network Address=127.0.0.1;" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks";
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                connection.Open(  );
                Console.WriteLine(connection.State);
            }
        }
    }

Connect to SqlServer Named Instance

using System;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = @"Data Source=(local)\SQLExpress;Integrated security=SSPI;Initial Catalog=master;";
            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                connection.Open(  );
                Console.WriteLine(connection.State);
                Console.WriteLine(connection.DataSource);
                Console.WriteLine(connection.ServerVersion);
            }
        }
    }

Connect to SqlServer with Integrated security

using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString1 = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            using (SqlConnection connection = new SqlConnection(sqlConnectString1))
            {
                connection.Open(  );
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString1);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
        }
    }

Connect to SqlServer with password and id

using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString2 = "Data Source=(local);User Id=sa;Password=password;Initial Catalog=AdventureWorks;";
            using (SqlConnection connection = new SqlConnection(sqlConnectString2))
            {
                connection.Open(  );
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString2);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
        }
    }

Deal with connection exception

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
   static void Main(string[] args)
   {
      string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
      SqlConnection conn = new SqlConnection(connString); 
      try {
         conn.Open();
         Console.WriteLine("Connection opened.");
      }
      catch (SqlException e) {
         Console.WriteLine("Error: " + e);
      }
      finally {
         conn.Close();
         Console.WriteLine("Connection closed.");
      }
   }
}

Get Sql Statistics

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Collections;
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;");
            conn.StatisticsEnabled = true;
            conn.Open();
   
            Hashtable ht = (Hashtable)conn.RetrieveStatistics();
            foreach (string key in ht.Keys)
            {
                Console.WriteLine("{0} : {1}", key, ht[key]);
            }            
        }
    }

Obtain a nonpooled connection

using System;
using System.Data.SqlClient;
class MainClass
{
    public static void Main()
    {
            
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object"s connection string.
                con.ConnectionString =
                    @"Data Source = .\sqlexpress;" +//local SQL Server instance
                    "Database = Northwind;" +       //the sample Northwind DB
                    "Integrated Security = SSPI;" + //integrated Windows security
                    "Pooling = False";              //specify nonpooled connection
    
                con.Open();
            }
    }
}

Obtain a pooled connection

using System;
using System.Data.SqlClient;
class MainClass
{
    public static void Main()
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString =
                @"Data Source = .\sqlexpress;" +// local SQL Server instance
                "Database = Northwind;" +       // the sample Northwind DB
                "Integrated Security = SSPI;" + // integrated Windows security
                "Min Pool Size = 5;" +          // configure minimum pool size
                "Max Pool Size = 15;" +         // configure maximum pool size
                "Connection Reset = True;" +    // reset connections each use
                "Connection Lifetime = 600";    // set max connection lifetime
            con.Open();
        }

    }
}

Opens and verifies a connection

using System;
using System.Data.SqlClient;
public class ConnectionTest
{
    public static void Main() 
    {
        SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI");
        con.Open();
        Console.WriteLine("Connection is " + con.State.ToString());
    
        con.Close();
        Console.WriteLine("Connection is " + con.State.ToString());
    }
}

Properties of SqlConnection

using System;
using System.Data;
using System.Data.SqlClient;
class MySqlConnection {
    public static void Main() {
        string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa";
        SqlConnection mySqlConnection = new SqlConnection(connectionString);
        mySqlConnection.Open();
        Console.WriteLine("mySqlConnection.ConnectionString = " + mySqlConnection.ConnectionString);
        Console.WriteLine("mySqlConnection.ConnectionTimeout = " + mySqlConnection.ConnectionTimeout);
        Console.WriteLine("mySqlConnection.Database = " + mySqlConnection.Database);
        Console.WriteLine("mySqlConnection.DataSource = " + mySqlConnection.DataSource);
        Console.WriteLine("mySqlConnection.PacketSize = " + mySqlConnection.PacketSize);
        Console.WriteLine("mySqlConnection.ServerVersion = " + mySqlConnection.ServerVersion);
        Console.WriteLine("mySqlConnection.State = " + mySqlConnection.State);
        Console.WriteLine("mySqlConnection.WorkstationId = " + mySqlConnection.WorkstationId);
        mySqlConnection.Close();
    }
}

Set AttachDbFilename in connection string

using System;
using System.Data;
using System.Data.SqlClient;
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection thisConnection = new SqlConnection(
                @"Data Source=.\SQLEXPRESS;" +
                @"AttachDbFilename="NORTHWND.MDF";" +
                @"Integrated Security=True;Connect Timeout=30;User Instance=true");
      thisConnection.Open();
      SqlCommand thisCommand = thisConnection.CreateCommand();
      thisCommand.rumandText = "SELECT CustomerID, CompanyName from Customers";
      SqlDataReader thisReader = thisCommand.ExecuteReader();
      while (thisReader.Read())
      {
        Console.WriteLine("\t{0}\t{1}",
        thisReader["CustomerID"], thisReader["CompanyName"]);
      }
      thisReader.Close();
      thisConnection.Close();
    }
  }

Setting Connection Pooling Options

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            SqlConnection connection = new SqlConnection(  );
            connection.StateChange += new StateChangeEventHandler(connection_StateChange);
            connection.ConnectionString = sqlConnectString +
                "Connection Timeout=15;Connection Lifetime=0;" +
                "Min Pool Size=0;Max Pool Size=100;Pooling=true;";
            connection.Open();
            connection.Close();
            connection.ConnectionString = sqlConnectString +
                "Connection Timeout=30;Connection Lifetime=0;" +
                "Min Pool Size=0;Max Pool Size=200;Pooling=true;";
            connection.Open();
            connection.Close();
        }
        static void connection_StateChange(object sender, StateChangeEventArgs e)
        {
            Console.WriteLine("\tOriginalState = {0}", e.OriginalState.ToString());
            Console.WriteLine("\tCurrentState = {0}", e.CurrentState.ToString());
        }
    }

Show the connection properties for SqlConnection

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

public class MainClass{
    static void Main() {
       // Create a Connection Object
       string ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
       SqlConnection conn = new SqlConnection(ConnectionString);
       // Open the connection
       if( conn.State != ConnectionState.Open)
           conn.Open();

       // 
       Console.WriteLine( "Connection String :"+conn.ConnectionString 
           + ", Workstation Id:"+ conn.WorkstationId.ToString()
           +", Packet Size :"+ conn.PacketSize.ToString()
           +", Server Version "+ conn.ServerVersion.ToString()
           +", DataSource :"+ conn.DataSource.ToString()
           +", Server Version:"+ conn.ServerVersion.ToString()
           +", Connection Time Out:"+ conn.ConnectionTimeout.ToString() );
       // Close the connection
       if( conn.State == ConnectionState.Open)
           conn.Close();       
    }
}

Connection String :server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;, Workstat
ion Id:nfex, Packet Size :8000, Server Version 09.00.1399, DataSource :(local)\SQLEXPRESS, Server
Version:09.00.1399, Connection Time Out:15

SqlConnection connection state

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
class MainClass
{
    public static void Main()
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString ="server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";    
            con.Open();
            if (con.State == ConnectionState.Open)
            {
                Console.WriteLine("SqlConnection Information:");
                Console.WriteLine("  Connection State = " + con.State);
                Console.WriteLine("  Connection String = " + con.ConnectionString);
                Console.WriteLine("  Database Source = " + con.DataSource);
                Console.WriteLine("  Database = " + con.Database);
                Console.WriteLine("  Server Version = " + con.ServerVersion);
                Console.WriteLine("  Workstation Id = " + con.WorkstationId);
                Console.WriteLine("  Timeout = " + con.ConnectionTimeout);
                Console.WriteLine("  Packet Size = " + con.PacketSize);
            }
            else
            {
                Console.WriteLine("SqlConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}

SqlConnection Information:
  Connection State = Open
  Connection String = server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;
  Database Source = (local)\SQLEXPRESS
  Database = MyDatabase
  Server Version = 09.00.1399
  Workstation Id = nfex
  Timeout = 15
  Packet Size = 8000