Old stuff. Anyone maintaining this or new approach?
belchski opened this issue · 7 comments
What is new approach?
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.
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.
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.SqlClientand 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
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.
yes