Btukfyl

I am using sql dependency in windows service, which will moitor for changes in student table. Any changes in student table should be notified to windows service which will do it's work after that.

The same code is working fine when tried in web application even for multiple changes done in table. But when hosted as windows service, only the first change is notified and the subsequent changes are not hitting windows service when i tried to attach the process to debug.

What could be the issue ?

   protected override void OnStart(string args)

{

    SqlDependency.Stop(connString);

    //Start SqlDependency with application initialization

    SqlDependency.Start(connString);



    StudentService studentService = new StudentService();

    studentService.GetStudentDetails();

}



protected override void OnStop()

{

    //Stop SqlDependency

    SqlDependency.Stop(connString);

}







 public static List<Student> GetStudentRecords()

    {

        var lstStudentRecords = new List<Student>();

        string dbConnectionSettings = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;



    using (var dbConnection = new SqlConnection(dbConnectionSettings))

    {

        dbConnection.Open();



        var sqlCommandText = @"SELECT [StudentID],[StudentName],[DOB],[Weight] FROM [dbo].[Student]";



        using (var sqlCommand = new SqlCommand(sqlCommandText, dbConnection))

        {

            AddSQLDependency(sqlCommand);



            if (dbConnection.State == ConnectionState.Closed)

                dbConnection.Open();



            var reader = sqlCommand.ExecuteReader();

            lstStudentRecords = GetStudentRecords(reader);

        }

    }

    return lstStudentRecords;

} 



private static void AddSQLDependency(SqlCommand sqlCommand)

{

    sqlCommand.Notification = null;



    var dependency = new SqlDependency(sqlCommand);



    dependency.OnChange += (sender, sqlNotificationEvents) =>

    {

        if (sqlNotificationEvents.Type == SqlNotificationType.Change)

        { 

            HitHere(sqlNotificationEvents.Info); 

        }

    };

}