C # / SQL Listener to tell me if a row has been inserted into a table

Question

Can someone send a sample code or tell me how to set up a listener to notify me (trigger an event) if a new row is inserted into the SQL Server database?

I do not want to use any messaging services or SQL Server brokers. I currently have a file listener that will notify me when new lines are added to a flat file. I would like to do the same for a database table.

Answer 1

Perhaps you are looking for the SqlDependency class, which allows you to register your code case when changes occur?

SqlDependency ideal for caching scenarios where your ASP.NET application or mid-level service needs to store certain information in a cache in memory. SqlDependency allows SqlDependency to receive notifications when the source data in the database changes so that the cache can be updated.

Or does it fall into the sphere of things that you forbid, is this not entirely clear?

Answer 2

You will need to do some kind of database trigger to insert or poll the database in order to search for new records. I do not recommend the last sentence, as it can be very intense.

In addition, you do not give us much time to continue. What version of SQL Server are you using? What have you tried? What problems have you encountered?

---

Here are a few links that may point you in the right direction:

Exploring SQL Server Triggers

How to create and run a SQL Server CLR trigger

A similar question that suggests an alternative (better?) Way to do this: Can SQL CLR run this? Or is there a better way?

Answer 3

You can use sql notifications for this, but you said you did not want to use a broker. Otherwise, you can poll, but as mentioned, this can lead to performance problems.

Another way to do this is to use triggers in a database table to touch a file in the file system.

 exec master..xp_cmdshell 'echo changed > c:\temp\filewatcher.txt' 

Then use FileSystemWatcher , as already mentioned, you already do in your application to be notified when a file changes.

There are security permissions that you need to grant your Sql Server user to make this possible, but if acceptable, it will work without using a broker.

Answer 4

I also recommend a trigger that registers insertions somewhere in db (and you should still query the database, which is not so expensive if the trigger modifies a special table with one row). But if you have IDENTITY on your main key, you can control the value of the current table identifier:

 SELECT IDENT_CURRENT('TableName') 

This is a hack, however, but quick, and you do not need to modify your database (of course, it only works for insert operations). However, you can skip pasting operations if between the values ​​of the survey identifier was explicitly changed and the value that it was at the time of the previous poll (which is not very likely) was set exactly.

Answer 5

I would consider a CLR trigger, read the following articles ...

Build your first CLR trigger in SQL Server

CLR triggers

Answer 6

According to "Damien" you need to use sqldependence to detect changes.

Sample for sqldependency in msdn: - https://msdn.microsoft.com/en-us/library/62xk7953(v=vs.110).aspx

Answer 7

Best to use trigger sounds:

 create TRIGGER [tI_Notifier] ON [dbo].[your_table_name] AFTER INSERT AS BEGIN SET NOCOUNT ON; declare @id1 int --or same type as your key --declare other variables you want to read from the inserted row --read columns values from inserted row select @id1 = <some_key_column>, @id2=<second_column> from inserted --do something with row new values SET NOCOUNT OFF; END