SqlTableDependency example in Ado.Net C#

SqlTableDependency is a very low-level notification service that sends notification from SQL server to C# component, this class allow to create dependency for any particular table and it’s field, so whenever any changes happen in that field, C# code gets notification.

Note, SqlTableDependency is not a wrapper class of SqlDependency.

In this tutorial, you will learn how to use SqlTableDependency class to notify c# application when some changes done in database field of any specified table. we can create SqlTableDependency object for any table and field, where we want to track database changes and immediately notify the application (and users or system owners). This is basically a notification mechanism, when some data changes happen in database we can execute some more action on onChange event, like logging, sending email, broadcasting updated rate etc.

First, Create SQL table in SQL 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

Enable service broker.

ALTER DATABASE [MLData] SET ENABLE_BROKER;
 go;

Create SqlTableDependency Test Application

Create a console application, here in this example we have used visual studio 2019 with .net Core 3.1 framework

Now we write a method which will fetch a list of stock from database and display on console screen, to understand how SqlTableDependency class works, we make some changes in database price field and see how notfication comes to our C# code.

public async Task<List<Stock>> GetStockDetails()
        {
            var stockDetails = new List<Stock>();
            Stock _stock = null;
    try
    {
        using (SqlConnection con = new SqlConnection(Util.DBConnectionString))
        {
            con.Open();
        
            SqlCommand cmd = new SqlCommand("Select stockid, quantity, price, productname from [dbo].[tbStock]", con);       
                                        
            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();
             
        }
    }
    catch (Exception ex)
    {
        throw (ex);
    }
    return stockDetails;
    }

Connection string, make sure you have added integrated security=false;Trusted_Connection=true; in your connection string like example below, this is required for SqlTableDependency object to access database.

public class Util
{
    public static string DBConnectionString
    {
        get
        {
                return "Server ='machine1\\SQLEXPRESS'; initial catalog=MLData; User ID='msa1'; Password ='pass123'; integrated security=false;Trusted_Connection=true;";
        }
    }
}

ModelToTableMapper and SqlTableDependency object

Install SqlTableDependency package from nuget package manager. PM> Install-Package SqlTableDependency

Create a table ModelToTableMapper and SqlTableDependency object.

In ModelToTableMapper objec you can specify the object and the property you want to map with sql table field name. the object property name and the database field name can be different, though here in example i have kept the same name "price", example stockMapper.AddMapping(s => s.Price, "price"); but you can specify any field name for each property.

var stockMapper = new ModelToTableMapper<Stock>();
stockMapper.AddMapping(s => s.Price, "price");

Now we create a SqlTableDependency object.

We need to specify connection, sql table name, schema name and the mapper (as we have created above).

 
using (var dep = new SqlTableDependency<Stock>(Util.DBConnectionString1,
                tableName: "tbStock",
                schemaName: "dbo",
                mapper: stockMapper))
{
    dep.OnChanged += Stock_OnChanged;
    dep.OnError += Stock_OnError;
    dep.Start();
    // Here are some action to be performed.
    // dep.Stop();
}

Whenever any stock item gets added or changed in database table, we can send email, capture log details or call any service.

e.Entity contain all the details of the changed entity.

private void Stock_OnChanged(object sender, RecordChangedEventArgs<Stock> e)
{
 var chandedEntity = e.Entity;
        switch (e.ChangeType)
        {
            case ChangeType.Delete:
                // log . email . service call;
                break;
            case ChangeType.Insert:
                //log . email . service call;
                break;
            case ChangeType.Update:
                //log . email . service call;
                break;
        }
    }

If anything goes wrong in SqlTableDependency changed event, then this OnError event gets executed.

private void Stock_OnError(object sender, ErrorEventArgs e)
{
    throw e.Error;
}

You may be interested in following examples

Ado.net Tutorial | Ado.net Interview Questions

SqlTableDependency Example

Learn MS-SQL Development

Learn .Net LINQ with C#

Learn Entityframework using C#

Connect SQL database using Ado.Net component from Asp.net application, learn how to use Ado.net as data access layer.

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