Csharp/C Sharp/Database ADO.net/SqlConnection

Connection String

 
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();
        }
        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
                "Pooling = False";              //specify nonpooled connection
            con.Open();
        }
    }
}

ConnectionTimeout

 
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 = @"Data Source=.\sqlexpress;Database=Northwind;Integrated Security=SSPI";    
            con.Open();
            if (con.State == ConnectionState.Open) {
                Console.WriteLine("  Timeout = " + con.ConnectionTimeout);
            } else {
                Console.WriteLine("SqlConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}

Connect to Access database

// connect to Access database
/*
 * C# Programmers Pocket Consultant
 * Author: Gregory S. MacBeth
 * Email: gmacbeth@comporium.net
 * Create Date: June 27, 2003
 * Last Modified Date:
 * Version: 1
 */
using System;
using System.Data.OleDb;
namespace Client.Chapter_13___ADO.NET
{
    public class ConnectingToAccess
    {
        static void Main(string[] args)
        {
            OleDbConnection MyConnection = new OleDbConnection (
            @"Provider=Microsft.Jet.OLEDB.4.0; Data Source = c:\MyAccessDB.mdb");
            MyConnection.Open();            
        }
    }
}

Create SqlCommand object

using System;
using System.Data;
using System.Data.SqlClient;
   class CommandExampleSql
   {
      static void Main() 
      {
         SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
         SqlCommand thisCommand = new SqlCommand();
         Console.WriteLine("Command created.");
         try {
            thisConnection.Open();
            thisCommand.Connection = thisConnection;
 
            thisCommand.rumandText = "SELECT COUNT(*) FROM Employee";
            Console.WriteLine("Ready to execute SQL command: {0}", thisCommand.rumandText);
         } catch (SqlException ex) {
            Console.WriteLine(ex.ToString());
         } finally {
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }

Creating SQL Connections

/*
 * C# Programmers Pocket Consultant
 * Author: Gregory S. MacBeth
 * Email: gmacbeth@comporium.net
 * Create Date: June 27, 2003
 * Last Modified Date:
 * Version: 1
 */
using System;
using System.Data.SqlClient;
namespace Client.Chapter_13___ADO.NET
{
    public class CreatingSQLConnections
    {
        static void Main(string[] args)
        {
            SqlConnection MyConnection = new SqlConnection("Data Source=(local); Initial  Catalog = MyDatabase; User ID=sa;Password=");
            MyConnection.Open();
        }
    }
}

DataSource

 
using System;
using System.Data.SqlClient;
class MainClass {
    public static void Main(string[] args) {
        string conString = @"Data Source=.\sqlexpress;" +
            "Database=Northwind;Integrated Security=SSPI;" +
            "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" +
            "Connection Lifetime=600;";
        SqlConnectionStringBuilder sb1 = new SqlConnectionStringBuilder(conString);
        Console.WriteLine("  Database Source = " + sb1.DataSource);
        SqlConnectionStringBuilder sb2 = new SqlConnectionStringBuilder(conString);
        sb2.DataSource = @".\sqlexpress";
        Console.WriteLine("  " + sb2.ConnectionString);
    }
}

PacketSize

 
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 = @"Data Source=.\sqlexpress;Database=Northwind;Integrated Security=SSPI";    
            con.Open();
            if (con.State == ConnectionState.Open) {
                Console.WriteLine("  Packet Size = " + con.PacketSize);
            } else {
                Console.WriteLine("SqlConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}

SqlConnection: info message event handler

using System;
using System.Data;
using System.Data.SqlClient;
   class SqlDemo {
      static void Main(){
         string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
         SqlConnection cn = new SqlConnection(connString);
         cn.InfoMessage += new SqlInfoMessageEventHandler(CnInfoMessage);
         cn.StateChange += new StateChangeEventHandler(CnStateChange); 
         SqlCommand cmd = new SqlCommand();
         cmd.rumandText = "SELECT TOP 2 Id FROM Employee";
         cmd.Connection = cn;
         try
         {
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader();      
            while(dr.Read())
            {
               Console.WriteLine(dr.GetInt32(0));    
            }
            dr.Close();
            cmd.rumandText = "PRINT "Get Id for all Employees"";
            cmd.ExecuteNonQuery();
         }
         catch(SqlException ex)
         {
            Console.WriteLine(ex.Message);
         }      
         finally
         {
            cn.Close();
         }      
      }

      static void CnInfoMessage(object sender, SqlInfoMessageEventArgs ev)
      {
         foreach (SqlError err in ev.Errors)
         {
            Console.WriteLine("Entering InfoMessage Event Handler");
            Console.WriteLine("Source- " + err.Source);
            Console.WriteLine("State- " + err.State);
            Console.WriteLine("Number- " + err.Number);
            Console.WriteLine("Procedure- " + err.Procedure);
            Console.WriteLine("Server- " + err.Server);
            Console.WriteLine("Message- " + err.Message);
            Console.WriteLine("Exiting InfoMessage Event Handler");
         }
      }
      static void CnStateChange(object sender, StateChangeEventArgs ev)
      {
         Console.WriteLine("Entering StateChange EventHandler");
         Console.WriteLine("Sender = "+ sender.ToString());
         Console.WriteLine("Original State = "+ ev.OriginalState.ToString());
         Console.WriteLine("Current State = "+ ev.CurrentState.ToString()); 
         Console.WriteLine("Exiting StateChange EventHandler");
      }
   }

SqlConnection properties

using System;
using System.Data;
using System.Data.SqlClient;
class MySqlConnection {
  public static void Main() {
    string connectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
    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();
  }
}

SqlConnection state change event

using System;
using System.Data;
using System.Data.SqlClient;
   class SqlDemo {
      static void Main(){
         string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
         SqlConnection cn = new SqlConnection(connString);
         SqlCommand cmd = new SqlCommand();
         cmd.rumandText = "SELECT TOP 1 Id, FirstName FROM Employee";
         cmd.Connection = cn;
         cn.StateChange += new StateChangeEventHandler(CnStateChange);
         try
         {
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while(dr.Read()) {
               Console.WriteLine(dr.GetString(0) + "-" + dr.GetString(1));
            }
         }
         catch(SqlException e1)
         {
            Console.WriteLine(e1.Message);
         }
         finally
         {
            cn.Close();
         }
      }

      static void CnStateChange(object sender, StateChangeEventArgs ev)
      {
         Console.WriteLine("------------------------------");
         Console.WriteLine("Entering StateChange EventHandler");
         Console.WriteLine("Sender = "+ sender.ToString());
         Console.WriteLine("Original State = "+ ev.OriginalState.ToString());
         Console.WriteLine("Current State = "+ ev.CurrentState.ToString()); 
         Console.WriteLine("Exiting StateChange EventHandler");
         Console.WriteLine("------------------------------");
      }
   }

WorkstationId

 
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 = @"Data Source=.\sqlexpress;Database=Northwind;Integrated Security=SSPI";    
            con.Open();
            if (con.State == ConnectionState.Open) {
                Console.WriteLine("  Workstation Id = " + con.WorkstationId);
            } else {
                Console.WriteLine("SqlConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}