SQL data change Notification in .Net Core: SqlDependencyEx
To understand how sql table data changes is notified in C# application, we write a console application using .Net core 3.1 framework with SqlDependencyEx class.
Note: the SqlDependencyEx class still not available in Nuget Library, nor a part of .net core 3.1 framework, we have to use the code base of github SqlDependencyEx class, which are being contributed and designed by developers.
SQL Table Change Notification in C#
First, create a console application in the example class write the following sql connection string. Keep the connection strings same as described below.
string _connectionString= "Server='MyMachine\\SQLEXPRESS';Database=MLSDatabase;Trusted_Connection=True;MultipleActiveResultSets=true; Integrated Security=false;User ID=myusername;Password=mypassword";
Create a new instance of SqlDependencyEx class with all required parameters, and register the TableChanged event inside the constructor of your class.
SqlDependencyEx _sqlDep = null;
public SqlDependencyExExample()
{
_sqlDep = new SqlDependencyEx(_connectionString, "MLSDatabase",
"tbStock", "dbo");
_sqlDep.TableChanged += _sqlDep_TableChanged;
}
Now write a method to pull data from database, and start the SqlDependency listener object.
public async Task<List<Stock>> GetStockDetails1()
{
_sqlDep.Start();
var stockDetails = new List<Stock>();
stockDetails= await StockDTO.GetStockDetails();
return stockDetails;
}
This Sql TableChanged event will get executed when any data changes made in sql table we have specified in instance of SqlDependencyEx object.
private void _sqlDep_TableChanged(object sender, SqlDependencyEx.TableChangedEventArgs e)
{
string UpdatedRow = e.Data.ToString();
Console.WriteLine(UpdatedRow);
}
Open your SQL Query Analyzer and update price column in tbStock table, like example below
update tbStock set price=10.06 where StockId=4
Notice, there are two records (inserted and deleted) for same StockId. this gives an opportunity to log details or send email to application owner.
The result in form of XML with all the fields in modified sql table. so you need to write some additional code to retrive value from the output XML, the xml string starts with "root" element
<root>
<inserted>
<row>
<StockId>4</StockId>
<quantity>120</quantity>
<price>10.06</price>
<productName>LPB</productName>
<updatedOn>2020-09-26T15:57:16.980</updatedOn>
</row>
</inserted>
<deleted>
<row>
<StockId>4</StockId>
<quantity>120</quantity>
<price>7450.06</price>
<productName>LPB</productName>
<updatedOn>2020-09-26T15:57:16.980</updatedOn>
</row>
</deleted>
</root>
You may be interested in following examples
