Setting up SQL Dependency in a C# Application

Introduction:

In modern applications, keeping the UI up-to-date with database changes in real-time is critical for user experience. Traditionally, developers would use polling techniques, periodically asking the database for updates. This approach is inefficient, leading to unnecessary network and database overhead, and can also introduce noticeable delays for the user.

SQL Server offers a feature called SqlDependency, which allows applications to be notified when data has changed. Instead of constantly asking the database for changes, our application can simply “listen” for changes and respond when they occur. This not only reduces the overhead but ensures near real-time updates. This tutorial aims to guide developers on how to harness this feature in C# applications.

1. Prerequisites:

• SQL Server (express or full version).
• A database on SQL Server.
• Visual Studio.

2. Enable Service Broker on the Database:

Before using SqlDependency, ensure your database has Service Broker enabled.

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

3. Create a Table for Testing:

CREATE TABLE TestTable
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50)
);

4. Create a New C# Project in Visual Studio:

• Go to File -> New -> Project.
• Select Console App (.NET Core or .NET Framework).

5. Add Required Namespaces:

using System;
using Microsoft.Data.SqlClient;

6. Create the SqlDependency:

class Program
{
    private static string connectionString = "YourConnectionString";

    static void Main(string[] args)
    {
        StartDependency();
        Console.ReadKey();
    }

    static void StartDependency()
    {
        SqlDependency.Start(connectionString);

        RegisterDependency();
    }

    static void RegisterDependency()
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();

        SqlCommand command = new SqlCommand("SELECT Id, Name FROM [dbo].[TestTable]", connection);

        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += OnDataChange;

        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            // Process data if needed.
        }
        reader.Close();
    }

    private static void OnDataChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Info == SqlNotificationInfo.Insert || e.Info == SqlNotificationInfo.Update || e.Info == SqlNotificationInfo.Delete)
        {
            Console.WriteLine("Data has changed!");

            SqlDependency dependency = (SqlDependency)sender;
            dependency.OnChange -= OnDataChange;

            // Re-register the SqlDependency to continue receiving notifications.
            RegisterDependency();
        }
    }
}

Common Issues and Solutions:

1. Issue: SqlDependency stops working after the first notification.

   • Solution: SqlDependency is designed for a one-time notification. To continue receiving notifications, you need to re-register it, as shown in the OnDataChange method above.

2. Issue: Notifications are not being received.

   • Solution: Ensure that Service Broker is enabled on your database. Ensure that the SQL query you’re using with SqlDependency is supported. Certain types of queries, like ones with JOIN, may not be supported.

3. Issue: SqlDependency cannot be started because the Service Broker contract has not been enabled for the current database.

   • Solution: Use the SQL command given above to enable the Service Broker.

4. Issue: Permission errors.

   • Solution: The SQL user must have SUBSCRIBE QUERY NOTIFICATIONS permission. Ensure that your SQL user has this permission.

5. Issue: High memory usage.

   • Solution: Remember to stop any old SqlDependency before starting a new one, as demonstrated in the StartDependency method.

6. Issue: SqlDependency does not support express editions of SQL Server.

   • Solution: This is a limitation of SQL Server Express. Consider using a full version of SQL Server if you need to use SqlDependency.

Conclusion:

SqlDependency provides an efficient way to receive data change notifications from SQL Server. This tutorial has shown you how to set up SqlDependency in a C# application, and provided solutions for common issues you might encounter. Always ensure your queries are supported and that your SQL user has the necessary permissions.

Feedback and Questions

Your insights drive us! For any questions, feedback, or thoughts, feel free to connect:

1. Email: shady@shadynagy.com
2. Twitter: @ShadyNagy_
3. LinkedIn: Shady Nagy
4. GitHub: ShadyNagy

If you found this guide beneficial, don’t hesitate to share it with your network and help others discover these useful features in C# and MSSQL.

Until the next guide, happy coding!