CODEWITHSUNDEEP
c#sqlvisual-studio-2010sql-server-2008
Kameron
Kameron

Reputation: 733

Invoke a C# method from inside a SQL trigger

I am into a Winforms application using C# and Visual Studio 2010 along with SQL Server Management Studio 2008 R2.

My application checks the previous values in the database and if the current value being inserted falls under a certain range, I need to execute a method written in C# i.e. the host language. The method actually sends E-mails to the e-mail addresses stored in the database.

Now the checking part can be easily done using a trigger but the latter is troublesome for me.

Can anyone please help me out?

Upvotes: 2

Views: 2193

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You might want to look into Database Mail. This integrates email sending functionality within SQL Server.

Otherwise, I'd strongly suggest not trying to do this directly within the trigger - sending an email could be a time consuming process, and you wouldn't want to delay the completion of the transaction if the email functionality isn't currently working.

Upvotes: 2

mellamokb
mellamokb

Reputation: 56769

There are a couple of ways to handle this.

  1. You could log an entry to a database that a C# service watches, and when an entry is added, it sends e-mails. This could be some sort of a Windows Service that runs on a Timer every 1 minute checking for updates.
  2. You can use the SQL-CLR to integrate C# code with SQL code. This requires opening a few security points and embedding the binary files from your project into SQL Server. This is a less preferred method for security reasons, but it can do the job.

Upvotes: 3

Related Questions