Below is my code

using System;
using System.Data.SqlClient;

namespace asldf
{
    class Program
    {
        private static string connectionString = "Server=.\\SQLEXPRESS;Database=Sample;User id=ad;Password=Pass1234;";
        private static SqlDependency sqlDependency;

        static void Main()
        {
            try
            {
                Console.WriteLine("Starting SqlDependency...");
                SqlDependency.Start(connectionString, "SQLDependencyQueue"); // Start dependency first

                if (string.IsNullOrEmpty(connectionString))
                {
                    throw new ArgumentNullException("Connection string cannot be null or empty.");
                }
               
                Console.WriteLine("SqlDependency started.");

                MonitorVolumeStore();

                Console.WriteLine("Monitoring changes. Press Enter to quit.");
                Console.ReadLine();

                Console.WriteLine("Stopping SqlDependency...");
                SqlDependency.Stop(connectionString);
                Console.WriteLine("SqlDependency stopped.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred in Main: {ex.Message}");
            }
        }

        private static void MonitorVolumeStore()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = "SELECT Date, InstrumentTocken, Volume, FullVolume, LTP, TotalBuyers, TotalSellers FROM VolumeStore WHERE InstrumentTocken = 11087106";

                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        Console.WriteLine("Creating SqlDependency...");
                      
                        Console.WriteLine("SqlDependency started.");

                        sqlDependency = new SqlDependency(command); // Initialize here
                        sqlDependency.OnChange += OnVolumeStoreChange;
                        Console.WriteLine("SqlDependency created.");

                        connection.Open();
                        Console.WriteLine("Connection opened.");
                        command.ExecuteReader();
                        Console.WriteLine("Query executed.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred in MonitorVolumeStore: {ex.Message}");
            }
        }

        private static void OnVolumeStoreChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("New row added to VolumeStore with InstrumentTocken = 11087106");

            sqlDependency.OnChange -= OnVolumeStoreChange;
            MonitorVolumeStore();

            FetchLatestVolumeStoreRow();
        }

        private static void FetchLatestVolumeStoreRow()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                    SELECT TOP 1 Date, InstrumentTocken, Volume, FullVolume, LTP, TotalBuyers, TotalSellers
                    FROM VolumeStore
                    WHERE InstrumentTocken = 11087106
                    ORDER BY Date DESC";

                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        connection.Open();
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                DateTime date = reader.GetDateTime(0);
                                int instrumentTocken = reader.GetInt32(1);
                                int volume = reader.GetInt32(2);
                                int fullVolume = reader.GetInt32(3);
                                decimal ltp = reader.GetDecimal(4);
                                int totalBuyers = reader.GetInt32(5);
                                int totalSellers = reader.GetInt32(6);

                                Console.WriteLine($"Date: {date}, InstrumentTocken: {instrumentTocken}, Volume: {volume}, FullVolume: {fullVolume}, LTP: {ltp}, TotalBuyers: {totalBuyers}, TotalSellers: {totalSellers}");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred in FetchLatestVolumeStoreRow: {ex.Message}");
            }
        }
    }
}

However, SqlDependency.Start(connectionString, "SQLDependencyQueue"); is throwing a NullReferenceException.

I have also done:

ALTER SERVER ROLE sysadmin ADD MEMBER ad;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO ad;
ALTER DATABASE Sample SET ENABLE_BROKER;

Please help!

Below is my code

using System;
using System.Data.SqlClient;

namespace asldf
{
    class Program
    {
        private static string connectionString = "Server=.\\SQLEXPRESS;Database=Sample;User id=ad;Password=Pass1234;";
        private static SqlDependency sqlDependency;

        static void Main()
        {
            try
            {
                Console.WriteLine("Starting SqlDependency...");
                SqlDependency.Start(connectionString, "SQLDependencyQueue"); // Start dependency first

                if (string.IsNullOrEmpty(connectionString))
                {
                    throw new ArgumentNullException("Connection string cannot be null or empty.");
                }
               
                Console.WriteLine("SqlDependency started.");

                MonitorVolumeStore();

                Console.WriteLine("Monitoring changes. Press Enter to quit.");
                Console.ReadLine();

                Console.WriteLine("Stopping SqlDependency...");
                SqlDependency.Stop(connectionString);
                Console.WriteLine("SqlDependency stopped.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred in Main: {ex.Message}");
            }
        }

        private static void MonitorVolumeStore()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = "SELECT Date, InstrumentTocken, Volume, FullVolume, LTP, TotalBuyers, TotalSellers FROM VolumeStore WHERE InstrumentTocken = 11087106";

                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        Console.WriteLine("Creating SqlDependency...");
                      
                        Console.WriteLine("SqlDependency started.");

                        sqlDependency = new SqlDependency(command); // Initialize here
                        sqlDependency.OnChange += OnVolumeStoreChange;
                        Console.WriteLine("SqlDependency created.");

                        connection.Open();
                        Console.WriteLine("Connection opened.");
                        command.ExecuteReader();
                        Console.WriteLine("Query executed.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred in MonitorVolumeStore: {ex.Message}");
            }
        }

        private static void OnVolumeStoreChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine("New row added to VolumeStore with InstrumentTocken = 11087106");

            sqlDependency.OnChange -= OnVolumeStoreChange;
            MonitorVolumeStore();

            FetchLatestVolumeStoreRow();
        }

        private static void FetchLatestVolumeStoreRow()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = @"
                    SELECT TOP 1 Date, InstrumentTocken, Volume, FullVolume, LTP, TotalBuyers, TotalSellers
                    FROM VolumeStore
                    WHERE InstrumentTocken = 11087106
                    ORDER BY Date DESC";

                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        connection.Open();
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                DateTime date = reader.GetDateTime(0);
                                int instrumentTocken = reader.GetInt32(1);
                                int volume = reader.GetInt32(2);
                                int fullVolume = reader.GetInt32(3);
                                decimal ltp = reader.GetDecimal(4);
                                int totalBuyers = reader.GetInt32(5);
                                int totalSellers = reader.GetInt32(6);

                                Console.WriteLine($"Date: {date}, InstrumentTocken: {instrumentTocken}, Volume: {volume}, FullVolume: {fullVolume}, LTP: {ltp}, TotalBuyers: {totalBuyers}, TotalSellers: {totalSellers}");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred in FetchLatestVolumeStoreRow: {ex.Message}");
            }
        }
    }
}

However, SqlDependency.Start(connectionString, "SQLDependencyQueue"); is throwing a NullReferenceException.

I have also done:

ALTER SERVER ROLE sysadmin ADD MEMBER ad;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO ad;
ALTER DATABASE Sample SET ENABLE_BROKER;

Please help!

• c#
• sql-server
• service-broker
• sqldependency

• SqlDependency.Start(connectionString, "SQLDependencyQueue"); // Start dependency first if (string.IsNullOrEmpty(connectionString)) { throw new ArgumentNullException("Connection string cannot be null or empty."); } I am going to do out on a limb and suggest you need that logic before the if to after the if. – mjwills Commented Nov 19, 2024 at 9:23
• @mjwills Sorry. I already tried that but issue is same. – Aditya Bokade Commented Nov 19, 2024 at 9:43
• 4 Can we have a full stack trace. Also is there any reason you are not using the newer Microsoft.Data.SqlClient – Charlieface Commented Nov 19, 2024 at 10:41
• 1 Does the SQLDependencyQueue service broker queue exist? If not , try omitting the specification: SqlDependency.Start(connectionString); – Dan Guzman Commented Nov 19, 2024 at 14:18

1 Answer 1

Changing System.Data.SqlClient to newer Microsoft.Data.SqlClient resolved the issue.