Csharp/C Sharp/Database ADO.net/SQL Events

How to use the InfoMessage event

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class InfoMessage {

 public static void InfoMessageHandler(object mySender, SqlInfoMessageEventArgs myEvent)
 {
   Console.WriteLine("The following message was produced:\n" + myEvent.Errors[0]);
 }
 public static void Main(){
   SqlConnection mySqlConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   mySqlConnection.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);
   mySqlConnection.Open();
   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
   mySqlCommand.rumandText = "PRINT "This is the message from the PRINT statement"";
 
   mySqlCommand.ExecuteNonQuery();
   mySqlCommand.rumandText = "RAISERROR("This is the message from the RAISERROR statement", 10, 1)";
 
   mySqlCommand.ExecuteNonQuery();
   mySqlConnection.Close();
 }

}

      </source>

How to use the StateChange event

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class StateChange {

 public static void StateChangeHandler(object mySender, StateChangeEventArgs myEvent)
 {
   Console.WriteLine("mySqlConnection State has changed from "+myEvent.OriginalState + "to "+myEvent.CurrentState);
 }
 public static void Main(){
   SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   mySqlConnection.StateChange +=new StateChangeEventHandler(StateChangeHandler);
   Console.WriteLine("Calling mySqlConnection.Open()");
   mySqlConnection.Open();
   Console.WriteLine("Calling mySqlConnection.Close()");
   mySqlConnection.Close();
 }

}

      </source>

On row updating and updated 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);
        try
        {
           cn.Open();
           SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employee", cn);
    
           SqlCommandBuilder cb = new SqlCommandBuilder(da);
           DataSet ds = new DataSet();
           da.Fill(ds, 0, 1, "Employee");
           da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
           da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
           DataTable dt = ds.Tables["Employee"];
           dt.Rows[0][1] = "T";
           da.Update(ds, "Employee");
           da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
           da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);

        } catch (SqlException ex) {
           Console.WriteLine(ex.Message);
        }
        finally
        {
           cn.Close();
        }
     }

     static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e) 
     {
        Console.WriteLine("OnRowUpdating event");
        if (e.Status != UpdateStatus.Continue) 
           Console.WriteLine("RowStatus = " + e.Status.ToString());
     } 
     static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e) 
     {
        Console.WriteLine("OnRowUpdating event");
        if (e.Status != UpdateStatus.Continue) 
           Console.WriteLine("RowStatus = " + e.Status.ToString());
     }
 }
          
      </source>

Register two SqlConnection change events

<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.StateChange += new StateChangeEventHandler(CnStateChange);
        cn.StateChange += new StateChangeEventHandler(CnStateChange2);
     
        SqlCommand cmd = new SqlCommand();
        cmd.rumandText = "SELECT LastName, FirstName FROM Employee";
        cmd.Connection = cn;
        try
        {
           cn.Open();
           SqlDataReader dr = cmd.ExecuteReader();
           while(dr.Read())
           {
              Console.WriteLine(dr.GetString(0) + "-" + dr.GetString(1));    
           }
        }
        catch(SqlException ex)
        {
           Console.WriteLine (ex.Message);
        }
        finally
        {
           cn.Close();
        }   
     }
     static void CnStateChange2(object sender, StateChangeEventArgs ev)
     {
        Console.WriteLine("------------------------------");
        Console.WriteLine("Entering Second Statechange EventHandler");
        Console.WriteLine("Sender = " + sender.ToString());  
        Console.WriteLine("Original State = " + ev.OriginalState.ToString());
        Console.WriteLine("Current State = " + ev.CurrentState.ToString());    
        Console.WriteLine("Exiting Second StateChange EventHandler");
        Console.WriteLine("------------------------------");
     }
     
     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>