Invoke a C# method from inside a SQL trigger

Question

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?

mellamokb · Accepted Answer · 2011-08-16 14:04:17Z

3

There are a couple of ways to handle this.

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.
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.

answered Aug 16, 2011 at 14:04

mellamokb

56.9k12 gold badges111 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kameron Over a year ago

Will method 1. work if my Win-Forms application is shut-down? Say n records are added to the database but when they were trying to be e-mailed, the user shuts down the application. If it does work, how do we do it?

Sascha Over a year ago

I think what mellamokb proposes with (1) is a second program ( a windows service that starts with the windows operating system on the database server ) that monitors the table for new entries and then does the work. This is a usual pattern and I know several places where it is used. There is no need to have the main application running.

Damien_The_Unbeliever · Accepted Answer · 2011-08-16 14:20:03Z

2

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.

answered Aug 16, 2011 at 14:20

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Collectives™ on Stack Overflow

Invoke a C# method from inside a SQL trigger

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related