How to manage SQLDependency for transient errors when connecting to Azure SQL Managed Instance?

[ryms-nnit]

I have a Azure App Service web app running in C# code that uses ASP.NET Framework 4.8 and Microsoft.Data.SqlClient 2.1.7.

I would like to understand how would the SQLDependency handle the transient connectivity issue?
Do I need to specifically update my code to handle the transient errors?
It would be helpful if you could share any example using SQLDependency to handle transient errors related Azure SQL Managed Instance.

[ryms-nnit]

Would like to make a follow-up on this if possible please

[JRahnama]

@ryms-nnit

below could be a sample of retry on SqlDependency.

using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Connection string
        string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True";

        // Start SqlDependency
        // this will return a bool value which could also be useful in some cases
        _ = SqlDependency.Start(connectionString);

        // Create a SQLDependency
        using var connection = new SqlConnection(connectionString);
        using var command = new SqlCommand("SELECT * FROM Customers", connection);
        // Create the dependency
        var dependency = new SqlDependency(command);
        dependency.OnChange += Dependency_OnChange;

        // Open the connection
        connection.Open();

        // Execute the query
        try
        {
            command.ExecuteReader();
        }
        catch (SqlException ex)
        {
            // Handle the initial query execution error
            Console.WriteLine($"Error executing query: {ex.Message}");
        }

        // Wait for changes (or handle the initial result)
    }

    private static void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            // Handle the database change (e.g., update UI, process data, etc.)
            Console.WriteLine("Database change detected!");
        }
        else if (e.Type == SqlNotificationType.Subscribe || e.Type == SqlNotificationType.Unknown)
        {
            // Retry the subscription (e.g., transient connectivity issue)
            RetrySubscription();
        }
    }

    private static void RetrySubscription()
    {
        int maxRetries = 3;
        int retryCount = 0;

        while (retryCount < maxRetries)
        {
            try
            {
                // Re-subscribe to the query
                Console.WriteLine($"Retrying subscription (attempt {retryCount + 1})...");
                // Create a new SqlCommand and SqlDependency here

                // Exit the loop if successful
                break;
            }
            catch (SqlException ex)
            {
                // Handle the subscription error (e.g., log, wait, etc.)
                Console.WriteLine($"Subscription error: {ex.Message}");
                retryCount++;
                Thread.Sleep(1000); // Wait before retrying
            }
        }

        if (retryCount == maxRetries)
        {
            // Handle the maximum retry count (e.g., notify user, log, etc.)
            Console.WriteLine("Subscription failed after multiple retries.");
        }
    }
}

I hope this answers your question.

[JRahnama]

here is a modified version of the above code

using Microsoft.Data.SqlClient;

class Program
{
    static string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True";

    static void Main()
    {
        // Start SqlDependency
        _ = SqlDependency.Start(connectionString);

        // Subscribe to database changes
        SubscribeToChanges("SELECT * FROM Customers");
    }

    static void SubscribeToChanges(string query)
    {
        using var connection = CreateConnection();
        using var command = new SqlCommand(query, connection);
        var dependency = new SqlDependency(command);
        dependency.OnChange += Dependency_OnChange;

        try
        {
            connection.Open();
            command.ExecuteReader();
        }
        catch (SqlException ex)
        {
            HandleSubscriptionError(ex);
        }
    }

    static SqlConnection CreateConnection()
    {
        return new SqlConnection(connectionString);
    }

    static void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            // Handle the database change
            Console.WriteLine("Database change detected!");
        }
        else
        {
            // Retry the subscription
            RetrySubscription("SELECT * FROM Customers");
        }
    }

    static void RetrySubscription(string query)
    {
        const int maxRetries = 3;
        int retryCount = 0;
        TimeSpan delay = TimeSpan.FromSeconds(1);

        while (retryCount < maxRetries)
        {
            try
            {
                Console.WriteLine($"Retrying subscription (attempt {retryCount + 1})...");

                SubscribeToChanges(query);

                return;
            }
            catch (SqlException ex)
            {
                HandleSubscriptionError(ex);
            }

            retryCount++;
            Thread.Sleep(delay);
            delay *= 2; // Exponential backoff
        }

        Console.WriteLine("Subscription failed after multiple retries.");
    }

    static void HandleSubscriptionError(SqlException ex)
    {
        Console.WriteLine($"Subscription error: {ex.Message}");

        // Additional error handling logic can be added here, e.g., logging, notifying, etc.
    }
}