Trigger Windows Service when the new record insert in to DB
Possible Duplicate:
Change Notification with Sql Server 2008
Am just wondering is there's anyway i can write a windows service in C# that will be trigger when the new record get inserted into Database.
And i would like to connect DB thru wcf also. Please give any ideas or suggestion.
Thanks in Advance.
Based on demo.b Instruction,here is the code.
SQL Database Details
My Database Name : MyWeb,Table Name : StoryItems, Columns: Location,Headline,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 how it doesn't like column name, am not sure what am missing here."Trigger_Web" is my CLR SP name.
First you need to create a trigger application in visual studios.
File --> new --> project --> Database --> select Visual C# CLR database project.
It will prompt you to connect to a database. Once done, ensure your trigger application listen to record insertion on any table you like (you can read more about CLR app in visual studios here).
from steps in link above, 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
Use something like an Extended Stored Procedure in SQL server, that calls your C# class/executable, which then can execute the service.
You can also call command line functions from the triggers on the on_insert event on the table, which can start/stop a service, or run an 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
链接地址: http://www.djcxy.com/p/10344.html上一篇: c＃编译器如何连接字符串