SQL EVENTS IN C#

                                        SQL EVENTS IN C#

SQL events in c#

In this article we will learn how to trigger SQL event in c#. As we know ADO.NET gives lot of classes to connect with various data source like SQL server, Oracle and many more.

We can handle trigger using delegate when some event happen in ADO.NET object. Here we will see little demonstration of them.

StateChange event

State change event is useful when we want to detect state of Connection object. When connection object will change its state (open to close or vice versa) this trigger will get fired. In below example we have implemented one sample example.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program    {
        static void Main(string[] args)
        {
 
            string connString = "Data
Source=SOURAV-PC\\SQL_INSTANCE2;Initial Catalog=test;Integrated
Security=True";
            SqlConnection cn = new SqlConnection(connString);
            cn.StateChange += new   StateChangeEventHandler(CnStateChange);
            
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from test";
            cmd.Connection = cn;
 
            try            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
 
                while (dr.Read())
                {
               
   Console.WriteLine(dr.GetValue(0) + "-" + dr.GetValue(1));               
}            }            catch (SqlException
ex)            {                Console.WriteLine(ex.Message);            }
            finally            {
                cn.Close();
            }
            Console.ReadLine();
        }
        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("------------------------------");
        }
 
    }
}
 

InfoMessage event

InfoMessage event will get fired when some message or information get fire in SQL Server operation. In below example we are attaching connection object and then firing one simple message as a command and at that time MessageShow() event handler is getting fire.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program    {
        static void Main(string[] args)
        {
 
            SqlConnection
mySqlConnection = new SqlConnection("Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
Catalog=test;Integrated Security=True");
            mySqlConnection.InfoMessage += new SqlInfoMessageEventHandler(MessageShow);
            mySqlConnection.Open();
            SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "PRINT 'Sourav Kayal'";
            mySqlCommand.ExecuteNonQuery();
            Console.ReadLine();
        }
        public static void MessageShow(object mySender, SqlInfoMessageEventArgs myEvent)
        {
            Console.WriteLine("Message is printed \n" +
myEvent.Errors[0]);
        }
    }
}