Geek Answers Handbook

Windows Service trigger when a new record is inserted into DB

Possible duplicate:
Change notification with Sql Server 2008

Just wondering if in any case I can write a Windows service in C # that will be launched when a new record is inserted into the database.

And I would also like to connect DB thru wcf. Please give any ideas or suggestions.

Thanks at Advance.

Based on the demo.b instruction, here is the code.

SQL Database Details

My Database Name: MyWeb, Table Name: StoryItems, Columns: location, title, name, genre.

public partial class Triggers { // Enter existing table or view for the target and uncomment the attribute line [Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger_Web", Target = "StoryItems", Event = "FOR INSERT")] public static void Trigger_Web() { SqlCommand command; SqlTriggerContext triggerContext = SqlContext.TriggerContext; SqlPipe pipe = SqlContext.Pipe; SqlDataReader reader; if (triggerContext.TriggerAction == TriggerAction.Insert) { using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT * FROM StoryItems", connection); reader = command.ExecuteReader(); reader.Read(); // get inserted value // ***********Here am trying to retrieve the location and name column value Location= (string)reader[9]; Name= (String) reader[9]; reader.Close(); try { // try to pass parameter to windows service WindowsService param = new WindowService(InsertedValue1, InsertedValue2); } catch (Exception ex) { } // Replace with your own code SqlContext.Pipe.Send("Trigger FIRED"); } } } }

Some, like this column name, I’m not sure what is missing here. "Trigger_Web" is my name CLR SP.

+7
source share
2 answers

First you need to create a trigger application in visual studios.

File -> new -> project -> Database -> select Visual C # CLR database project.

He will offer you to connect to the database. After that, make sure that the trigger application listens to the record in any table (you can learn more about the CLR application in visual studios here ).

from the above steps, add a trigger. your method should look like this:

 [Microsoft.SqlServer.Server.SqlTrigger(Name = "GetTransaction", Target = "EvnLog", Event = "FOR INSERT")] public static void GetTransaction() { SqlCommand command; SqlTriggerContext triggerContext = SqlContext.TriggerContext; SqlPipe pipe = SqlContext.Pipe; SqlDataReader reader; if (triggerContext.TriggerAction == TriggerAction.Insert) { using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT * FROM INSERTED", connection); reader = command.ExecuteReader(); reader.Read(); // get inserted value InsertedValue1 = (DateTime)reader[0]; InsertedValue2 = (string)reader[9]; reader.Close(); try { // try to pass parameter to windows service WindowsService param = new WindowService(InsertedValue1,InsertedValue2) } catch (Exception ex) { } }

Note. GetTransaction is the name of the trigger you want to create, in this case Evnlog is the name of the table

+3
source

Use something like an extended stored procedure on an SQL server that calls your C # class / executable, which can then execute the service.

You can also call command line functions from triggers in the on_insert event in the table, which can start / stop the service or start the exe or batch file.

Some ideas: http://www.sqlservercentral.com/Forums/Topic960855-392-1.aspx

And http://msdn.microsoft.com/en-us/library/ms189799.aspx

0
source

More articles:


All Articles