C# LINQ detect insert

Question

I have a Web API where i want to trigger a function when a new row has been inserted to a DB table. I don't want to have to get all the data and look for new data manually.

Is it possible to have a SQL Server trigger that somehow calls a function in my C# Web API?

How can this be done? Is it a good idea?

You can use a Service Broker to receive messages from the database engine. Have a look at this [answer](http://stackoverflow.com/questions/15225147/watch-for-a-table-new-records-in-sql-database). — Andre Hofmeister, Oct 31 '16 at 09:08
The broader question here, "how to structure my application so that I can react to it putting things in the database" is probably too broad for a Q&A site such as Stack Overflow. The actual question here, "Can I get a SQL Server trigger to talk to my web application, in that direction", then probably not. You could maybe do it by using SQLCLR but no, it is not a good idea. There are plenty of alternatives, such as SqlCacheDependency or just structuring your code to do the inserts using a framework that can tell you this but then we're off into the too-broad country. — Lasse V. Karlsen, Oct 31 '16 at 09:31

Sid · Answer 1 · 2016-10-31T09:38:11.330

4

You can override SubmitChanges in your DataContext, something like:

public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
{

    var set = this.GetChangeSet();//list of pending changes

    //Call your methods here
    foreach (var insert in set.Inserts)
    {
        //Insert Logic 
    }
    foreach (var update in set.Updates)
    {
        //Update Logic 
    }
    foreach (var item in set.Deletes)
    {
        //Delete Logic
    }


    base.SubmitChanges(failureMode);//allow the DataContext to perform it's default work (including your new log changes)

}

edited Oct 31 '16 at 09:38

answered Oct 31 '16 at 09:16

Sid

14,176
7
40
48

1

Does `GetChangesSet` tell you if it is an INSERT, UPDATE or DELETE ? – Fourat Oct 31 '16 at 09:34
@Fourat yes you can detect it, I will update my answer :) – Sid Oct 31 '16 at 09:37

score 0 · Answer 2 · answered Oct 31 '16 at 09:19

Are you sure that you need a Trigger? Why not choose a pub/sub system (such as rabbitmq, zeromq, redis...) where you can create a channel and every client pubblish and subscribe to it?
But if you prefer use a trigger here you can find two document on how to consume a sql server (link1 and link2)

C# LINQ detect insert

2 Answers2