SQL events in c#

Sourav.Kayal
Posted by in C# category on for Beginner level | Points: 250 | Views : 5664 red flag

How to capture various 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]);
        }
    }
}

 

 


 

Page copy protected against web site content infringement by Copyscape

About the Author

Sourav.Kayal
Full Name: Sourav Kayal
Member Level: Silver
Member Status: Member,MVP
Member Since: 6/20/2013 2:09:01 AM
Country: India
Read my blog here http://ctrlcvprogrammer.blogspot.in/
http://www.dotnetfunda.com
I am .NET developer working for HelixDNA Technologies,Bangalore in healthcare domain. Like to learn new technology and programming language. Currently working in ASP.NET ,C# and other microsoft technologies.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)