Csharp/C Sharp/Database ADO.net/SqlConnection

Connection String

<source lang="csharp"> 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();
       }
   }

}

</source>

ConnectionTimeout

<source lang="csharp"> 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);
           }
       }
   }

}

</source>

Connect to Access database

<source lang="csharp">

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

}

      </source>

Create SqlCommand object

<source lang="csharp"> 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.");
        }
     }
  }

      </source>

Creating SQL Connections

<source lang="csharp"> /*

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

}

      </source>

DataSource

<source lang="csharp"> 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);
   }

}

</source>

PacketSize

<source lang="csharp"> 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);
           }
       }
   }

}

</source>

SqlConnection: info message event handler

<source lang="csharp"> 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");
     }
  }
          
      </source>

SqlConnection properties

<source lang="csharp"> 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();
 }

}

      </source>

SqlConnection state change event

<source lang="csharp">

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("------------------------------");
     }
  }
          
      </source>

WorkstationId

<source lang="csharp"> 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);
           }
       }
   }

}

</source>