1

I'm working with C# and Microsoft SQL Server Express 2008. The table will be populated by a remote hardware so i don't have control over SQL command for the insert. Can I set SQL server to generate an event on insert catchable in C# (or other .NET) application?

Ufo.rob
  • 141
  • 1
  • 2
  • 11
  • You can use the SqlDependency class. For more info, try this link: http://stackoverflow.com/questions/5288434/how-to-monitor-sql-server-table-changes-by-using-c – Riv Jul 17 '13 at 10:42

4 Answers4

1

There are many ways to do that if i would you then i will do something like....

Write insert Trigger on that table and in that trigger call exe

For example :

declare @sqlcmd varchar(200)

SET @SQLCmd = 'c:\dba\sampl_2.exe'

EXEC master..xp_cmdshell @SQLCmd , no_output

and in that exe you can handle whatever you want...

Access Denied
  • 886
  • 1
  • 13
  • 24
0

Probably you are looking for: SqlDependency class.

But as far as I remember SQL Server Express does not support SqlDependency features. If so, I would create one more column, something like "IsProcessed" and once the app processes the row set it to true or something. By using this approach you can query the table using where clause: where IsProcessed is null.

icodebuster
  • 8,890
  • 7
  • 62
  • 65
Vladimir Gondarev
  • 1,243
  • 7
  • 14
0

There are several options to do something like this. It's easy to set up an insert trigger in SQL that effectively fires an event during which you can carry out any number of tasks: send a message, write to another table, write to a log. etc.

For me, the question is how best to you get your C# program to "listen" for this trigger event.

One option might be to set up a little WCF program as as listener that responds to messages sent by SQL server when the insert trigger fires. Here's a link to a CodeProject piece about doing something like that.

http://www.codeproject.com/Articles/21149/Invoking-a-WCF-Service-from-a-CLR-Trigger

Brian
  • 3,653
  • 1
  • 22
  • 33
0

It's interesting how this kind of question emerges on SO from time to time. And there is still no explicit unique answer that can be given in all situations.

Actually, the question asks on how should a multiple-insert-event be captured in SQL server for a particular table. I hope it is a particular table, because in SQL Server itself there isn't a method to listen DML changes to all tables (something like * from tables) at once. If you want, you can create N triggers (it could be done e.g. using dynamic SQL) on N tables and listen to these, but what if a new table gets added?

Let's think we have a specific table MyTable where we would like to listen for INSERT, UPDATE and DELETE events. One way to do it is implementing a trigger (speciffically AFTER trigger, since INSTEAD OF triggers are not made for this case). Another option is a query notification. But which one do you really need?

Query notification implies that you have to code .NET application. Trigger implies all that is trigger-related: you can have TSQL code, you can even call a web service from your database using CLR trigger. But - what do you need?

So, if you really need to react in the way to just write a log record in a table, use plain old trigger. Otherwise you should think: why do you need really need C# application here?

For example, say you need to catch a delete statement and serialize all deleted rows in XML format to the file system in a file. I would use CLR trigger in EXTERNAL_ACCESS mode to be able to create file and save information to disk.

OzrenTkalcecKrznaric
  • 5,535
  • 4
  • 34
  • 57