SQL trigger -> C# program

  • Thread starter Thread starter Gregi
  • Start date Start date
G

Gregi

Hello,

I'm using VS2005 and SQL 2005.

Is it possible for a SQL trigger to immediately raise an event in my C#
program?
Actually it's C++/CLI, but it's easy to translate C# -> C++/CLI, so any help
will be appreciate.
My program reads rows from database when some column ("Row sent") in these
rows has value of false.
I have to do this as fast as possible, but I don't know any _quick_ way to
find out if any row has value of false
in the specific column.
As far as I know there are a few ways to do this :
1) constantly asking database with select statement
2) SQL Notification Services
3) SQL trigger (INSERT/UPDATE) :
a) connects to my program (listening on TCP/IP port) and gives
information about column changed
b) writes Event to EventViewer (EventLog class), while my program has
OnEntryWritten function (EntryWrittenEventArgs Class)
c) ?

These methods are slow :/ Do you know anything better ?

Thx.
Gregi
 
Gregi said:
Hello,

I'm using VS2005 and SQL 2005.

Is it possible for a SQL trigger to immediately raise an event in my C#
program?
Actually it's C++/CLI, but it's easy to translate C# -> C++/CLI, so any
help
will be appreciate.
My program reads rows from database when some column ("Row sent") in these
rows has value of false.
I have to do this as fast as possible, but I don't know any _quick_ way to
find out if any row has value of false
in the specific column.
As far as I know there are a few ways to do this :
1) constantly asking database with select statement
2) SQL Notification Services
3) SQL trigger (INSERT/UPDATE) :
a) connects to my program (listening on TCP/IP port) and gives
information about column changed
b) writes Event to EventViewer (EventLog class), while my program has
OnEntryWritten function (EntryWrittenEventArgs Class)
c) ?

These methods are slow :/ Do you know anything better ?
Click to expand...

Yes. SQL 2005 and .NET 2.0 support query notifications and in-database
queuing with Service Broker.

Using SqlDependency to Subscribe to Query Notifications
http://msdn2.microsoft.com/en-us/library/ms179575.aspx

Using SqlNotificationRequest to Subscribe to Query Notifications
http://msdn2.microsoft.com/en-us/library/ms190270.aspx

This is all built on SQL Server Service Broker, which you can use directly
to to queueing and notification in the database. SO instead of a query
notification you could use a trigger to drop a message in a queue, and have
your external C++/CLI or C# program immediately pick up the queued message.

Service Broker Programming
http://msdn2.microsoft.com/en-us/library/ms171562.aspx

David
 
Back
Top