ADO.Net SqlDependency class, SqlDependency C# example

SqlDependency example in Ado.Net C#

SqlDependency is Ado.net Notification service just like SqlTableDependency.

In this tutorial, you will learn how to use SqlDependency class to notify c# application when some changes done in database tables. This is basically a notification mechanism, when some data changes happen in database we can add some more action on onChange event, actions like logging, sending email, broadcasting updated rate etc.

sql dependency example in c#

SqlDependency class depend on SqlConnection object, in order to receive notification from SQL Database, the dependency infrastructure depends on SQLClientPermission and Code Access Security and Ado.Net classes like SqlCommand and Connection ect.

Create SQL table in database

create TABLE [dbo].[tbStock](
	[StockId] [bigint]  primary key identity(1,1)  NOT NULL ,
	[quantity] [int] NOT NULL,
	[price] [decimal](18, 2) NOT NULL,
	[productName] [varchar](50) NOT NULL,
	[updatedOn] [datetime] NOT NULL
) ON [PRIMARY]
GO

Insert some data for testing

    
INSERT INTO [tbStock]
           ([quantity],[price],[productName],[updatedOn])
     VALUES
          (100,325.05,'TCS',getdate()),
		   (200,895.05,'ABS',getdate()),
		   (50,708.05,'PPT',getdate()),
		   (120,504.05,'LPB',getdate()) ,
		   (130,890.05,'GBH',getdate()),
		   (140,250.05,'AMH',getdate()),
		   (150,700.05,'VGT',getdate()),
		   (160,403.05,'KOP',getdate())
 GO

Enabling Query Notifications

We need to create a queue like in below example we have created a queue with name "StockChangeMessages"

Then create a notification service on that queue.

CREATE QUEUE StockChangeMessages;  
  
CREATE SERVICE StockChangeNotifications  
  ON QUEUE StockChangeMessages  
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);  

Enable service broker and grant "SUBSCRIBE QUERY NOTIFICATIONS" permission to current login user.

ALTER DATABASE [MLData] SET ENABLE_BROKER;


GRANT SUBSCRIBE QUERY NOTIFICATIONS TO msa

SqlDependency with SqlCommand Object

Now we need to understand how SqlDependency class detect changes made in sql database with the help of Ado.net SqlCommand object.

Create a c# console application and write the following code.

Here are few things you must know before starting with SqlDependency implementation in C# code.

  • When writing any sql statement in sql command object, we should not use any * mark in SQL Statement.

    for example Select * from [dbo].[tbStock]; not right

    select statement should have all column name specified, like Select stockid, quantity, price, productname from [dbo].[tbStock]

  • We must query on base table only, we cannot query on view, cannot use any type of join in the query

  • There should not be calculated column in the query, we also cannot use function like avg, max, min etc.

SqlDependency implementation in C#

Here we write an task to get all stock details from database, then we see when any stock price changes in sql database how SqlDependency onChange events gets fired.

public async Task<List<Stock>> GetStockDetails()
        {
            var stockDetails = new List<Stock>();
            Stock _stock = null;
    try
    {
        using (SqlConnection con = new SqlConnection(Util.DBConnectionString))
        {
            con.Open();

            SqlDependency.Start(con.ConnectionString);

            SqlCommand cmd = new SqlCommand("Select stockid, quantity, price, productname from [dbo].[tbStock]", con);

            SqlDependency dependency = new SqlDependency(cmd);
            dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);

                                        
            SqlDataReader reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                _stock = new Stock();
                _stock.Price = Convert.ToDecimal(reader["price"]);
                _stock.Qty = Convert.ToInt32(reader["quantity"]);
                _stock.Product = Convert.ToString(reader["productName"]);
                stockDetails.Add(_stock);
            }
            con.Close();

            SqlDependency.Stop(con.ConnectionString);
        }
    }
    catch (Exception ex)
    {
        throw (ex);
    }
    return stockDetails;
    }

Connection string, make sure you have added integrated security=SSPI in your connection string like example below, this is required for SqlDependency object to access database.

public class Util
{
    public static string DBConnectionString
    {
        get
        {
            return "Server ='com111\\SQLEXPRESS'; Database='MLData'; User ID='sa1'; Password ='pass100';Trusted_Connection=False;MultipleActiveResultSets=true;integrated security=SSPI";
        }
    }
}

SqlDependency OnChange Event

When any changes happen in database then this event will get executed.

private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Change)
    {

    }
    
}

You may be interested in following examples

 
learn ado.net
SqlDependency Example
Ado.net Interview Questions Answers

Ado.Net C# Examples | Join Asp.Net MVC Course