C#

void OnDataChange(object sender, SqlNotificationEventArgs e)
        {
            BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);

<pre>
        if (e.Source == SqlNotificationSource.Timeout)
        {
            BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
            Environment.Exit(1);
        }
        else if (e.Source != SqlNotificationSource.Data)
        {
            BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
            Environment.Exit(1);
        }
        else if (e.Type == SqlNotificationType.Change)
        {
            BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
        }
        else
        {
            BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
        }

        CallWebService();
        //((SqlDependency)sender).OnChange -= OnDataChange;
        //RegisterNotification();
    }</pre>

void OnDataChange(object sender, SqlNotificationEventArgs e)
{
    BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);

    if (e.Source == SqlNotificationSource.Timeout)
    {
    BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
    Environment.Exit(1);
    }
    else if (e.Source != SqlNotificationSource.Data)
    {
    BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
    Environment.Exit(1);
    }
    else if (e.Type == SqlNotificationType.Change)
    {
    BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
    }
    else
    {
    BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
    }

    CallWebService();
    //((SqlDependency)sender).OnChange -= OnDataChange;
    //RegisterNotification();
}

private void RegisterNotification()
    {
        string tmpdata = "";
        BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);

        System.Data.SqlClient.SqlDependency.Stop(connectionString);
        System.Data.SqlClient.SqlDependency.Start(connectionString);

        try
        {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT ActivityDate FROM [dbo].tablename";
            dep = new SqlDependency(cmd);
            dep.OnChange += new OnChangeEventHandler(OnDataChange);
            SqlDataReader dr = cmd.ExecuteReader();
            {
            if (dr.HasRows)
            {
                dr.Read();
                tmpdata = dr[0].ToString();
            }
            }
            dr.Dispose();
            cmd.Dispose();
        }
        }
        catch (Exception ex)
        {
        BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
        }
        finally
        {
        BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);

        }

}

public partial class PartIndexer : ServiceBase
{
    static string connectionString = "server=xxx;uid=xxx;password=xxx;database=xxx;Pooling=true;Connect Timeout=20;";
    SqlDependency dep;

<pre>
public PartIndexer()
{
    InitializeComponent();
}

private string GetLoggedInUser()
{
    string userName = "";
    if (System.Security.Principal.WindowsIdentity.GetCurrent() != null)
    {
        userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
    }
    return userName;
}

#region OnStart
protected override void OnStart(string[] args)
{
    BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
    RegisterNotification();
    MailSend(); // notification mail send
    BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
}
#endregion

#region RegisterNotification
/// <summary>
/// RegisterNotification
/// this is main routine which will monitor data change in ContentChangeLog table
/// </summary>
private void RegisterNotification()
{
    string tmpdata = "";
    BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);

    System.Data.SqlClient.SqlDependency.Stop(connectionString);
    System.Data.SqlClient.SqlDependency.Start(connectionString);

    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT TestTable FROM [dbo].ContentChangeLog";
            dep = new SqlDependency(cmd);
            dep.OnChange += new OnChangeEventHandler(OnDataChange);
            SqlDataReader dr = cmd.ExecuteReader();
            {
                if (dr.HasRows)
                {
                    dr.Read();
                    tmpdata = dr[0].ToString();
                }
            }
            dr.Dispose();
            cmd.Dispose();
        }
    }
    catch (Exception ex)
    {
        BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
    }
    finally
    {
        BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);

    }

}
#endregion

#region OnDataChange
/// <summary>
/// OnDataChange
/// OnDataChange will fire when after data change found in ContentChangeLog table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
    ((SqlDependency)sender).OnChange -= OnDataChange;

    BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);

    if (e.Source == SqlNotificationSource.Timeout)
    {
MailSend(); // notification mail send
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);

        Environment.Exit(1);
    }
    else if (e.Source != SqlNotificationSource.Data)
    {
    MailSend(); // notification mail send
        BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);

        Environment.Exit(1);
    }
    else if (e.Type == SqlNotificationType.Change)
    {
        StartIndex();
        BBALogger.Write("PartIndexer Service Data changed", BBALogger.MsgType.Info);
    }
    else
    {
        BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
    }

    RegisterNotification();
}
 #endregion

#region StartIndex
/// <summary>
/// StartIndex
/// this routine will call web service in bba reman website which will invoke routine to re-index data
/// </summary>
void StartIndex()
{
    // calling web service if change is notified

}
#endregion

#region MailSend
/// <summary>
/// MailNotify
/// fire mail when apps start & exit
/// </summary>

if (this.DataCenterLaborFileUpload.HasFile)
             {
                 var extension = Path.GetExtension(DataCenterLaborFileUpload.FileName);
                 string currentName = "";
                decimal currentHours = 0.00M;
                 decimal currentFTE = 0.00M;
                 string ResourceName = "";

                if (extension == ".csv")
                 {
                     StreamReader csvreader = new StreamReader(DataCenterLaborFileUpload.FileContent);
                     DataTable dt = new DataTable();
                     dt.Columns.Add("txtName");
                     dt.Columns.Add("txtHours");
                     dt.Columns.Add("txtFTE");

                    while (!csvreader.EndOfStream)
                     {
                         DataRow dr = dt.NewRow();

                        var line = csvreader.ReadLine();
                         var values = line.Split(',');

                        if (values[0].Trim() != "Pers.No.")
                         {

                            using (DbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString))
                             {
                                 conn.Open();
                                 using (DbCommand cmd = conn.CreateCommand())
                                 {
                                     cmd.Connection = conn;
                                     cmd.CommandText = "SELECT ResourceName FROM StaffTracking  order by PersonnelResourceType, ResourceName";
                                  //   DbParameter p1 = cmd.CreateParameter();
                                  //   p1.ParameterName = "restype";
                                  //   p1.Value = "Supplier";
                                  //   cmd.Parameters.Add(p1);
                                     using (DbDataReader row = cmd.ExecuteReader())
                                     {
                                         while (row.Read())
                                         {


                                             ResourceName = row.GetString(0);
                                             //            }
                                             //        }
                                             //    }
                                             //}
                                             if (ResourceName != (values[1].Trim()))
                                             {


                                                 if (values[1].Trim() == currentName)
                                                 {

                                                    currentHours = currentHours + Convert.ToDecimal(values[9].Trim());

                                                }
                                                 else
                                                 {
                                                     if (currentName == "")
                                                     {
                                                         dr["txtName"] = currentName;
                                                         dr["txtHours"] = currentHours;
                                                         dr["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);


                                                         dt.Rows.Add(dr);
                                                         dt.AcceptChanges();


                                                     }
                                                     
                                                         currentHours = Convert.ToDecimal(values[9].Trim());
                                                         currentName = values[1].Trim();
                                                     }
                                                 }
                                             }
                                         }
                                     }
                                 }
                             }
                         }

                    DataRow drfinal2 = dt.NewRow();
                     drfinal2["txtName"] = currentName;
                     drfinal2["txtHours"] = currentHours;
                     drfinal2["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);
                     dt.Rows.Add(drfinal2);
                     dt.AcceptChanges();


                     gvDataCenterLabor.DataSource = dt;

                }


                 gvDataCenterLabor.DataBind();

                //     Page.DataBind();
             }

        }