Comments (7)
yes
from monitor-table-change-with-sqltabledependency.
What is new approach?
from monitor-table-change-with-sqltabledependency.
I think this approach is a bit too complicated imo. I would just solve it with triggers alone, insert record to some table like "change_notifications" and have a background service that checks this table for new records and sends out notifications/ using a queue and ordering items in a queue by "notification_time". At least for my use case that would work. I guess the approach in this repo is mostly based on something like https://stackoverflow.com/questions/305637/send-message-from-sql-server-trigger (didn't look deep into the code)
@nikriaz I think he meant this repo still uses System.Data.SqlClient instead of recommended Microsoft.Data.SqlClient for example.
from monitor-table-change-with-sqltabledependency.
I looked closer to this problem and realized that it seems there is no reason to use any external libraries like this for this purpose. The only reason why people come here is multiple copy-pasted blog articles that referenced it.
I did my code based on the Microsoft.Data.SqlClient
and specifically on SqlDataReader
. The code is fairly simple, it's just a couple of dozens lines of code. Few months in production, works well. No triggers required. Not sure if I understand what is "a queue by "notification_time". None of them required. The basic idea how it works is registering a "snapshot" of some SELECT which is stored as notification stored procedure on the SQL server. As soon as content of the same SELECT changes, SQL emits notification that fires up the regular C# event.
I don't remember how I came up to the solution. ChatGPT, I think.
from monitor-table-change-with-sqltabledependency.
I looked closer to this problem and realized that it seems there is no reason to use any external libraries like this for this purpose. The only reason why people come here is multiple copy-pasted blog articles that referenced it.
I did my code based on the
Microsoft.Data.SqlClient
and specifically onSqlDataReader
. The code is fairly simple, it's just a couple of dozens lines of code. Few months in production, works well. No triggers required. Not sure if I understand what is "a queue by "notification_time". None of them required. The basic idea how it works is registering a "snapshot" of some SELECT which is stored as notification stored procedure on the SQL server. As soon as content of the same SELECT changes, SQL emits notification that fires up the regular C# event.I don't remember how I came up to the solution. ChatGPT, I think.
So you're basically just comparing two SELECT results periodically(polling) and detecting what got deleted, added or changed. I would still choose the triggers over this or SQL server CDC (change data capture) feature. You already have service that pushes changes to kafka and uses CDC
https://debezium.io/
https://debezium.io/documentation/reference/stable/connectors/sqlserver.html
from monitor-table-change-with-sqltabledependency.
No, of course no, No polling. I don't do it manually, server does it. Internal SQL mechanism seems to be the same to triggers but triggers have no built-in notification mechanism, they are supposed to be for data manipulation while you need notifications. So, it's like conditional triggers that emit notifications through service broker. Try to re-read the text for this library. It's not as complex as it looks like. General principles are explained correctly but you don't need this library itself: it's already built-in.
from monitor-table-change-with-sqltabledependency.
@nikriaz Are you saying about change data capture feature?
from monitor-table-change-with-sqltabledependency.
Related Issues (20)
- sqltabledependency trigger on my table “locks” all upcoming DML operations HOT 4
- Use DataTable or DataRow
- Permission error
- SqlTableDependency event trigger is not working if running as a BackgroundService HOT 1
- I cannot find a database table named "Table name"
- Nested object
- Returning dynamic Object / ExpandoObject
- Monitor multiple tables HOT 2
- Incorrect syntax near 'POISON_MESSAGE_HANDLING'
- The event object always returns null
- Design question - feasible to use in Azure DevOps and PowerShell script?
- OnChanged doesn't work after some time
- Text column type is not an supperted by SqlTableDependency
- Detecting only Insert and update but not the Delete
- Is there a limit to the number of fields that can be declared in the C# class for trigger detecting?
- Keyword 'trust server certificate' not supported HOT 1
- Could not load file or assembly 'TableDependency.SqlClient, Version=8.5.8.0 HOT 1
- Delete command not working
- .net 8.0.100-rc.2.23502.2 error for SqlTableDependency HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from monitor-table-change-with-sqltabledependency.