触发Windows服务当新记录插入到数据库 [英] Trigger Windows Service when the new record insert in to DB

问题描述

可能重复：结果
的与SQL Server 2008

我只是不知道是有反正我可以写在C＃中的窗口服务，将触发当新记录被插入到数据库中。

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.

和我想DB连接直通WCF还。请给任何意见或建议。

And i would like to connect DB thru wcf also. Please give any ideas or suggestion.

在此先感谢。

根据demo.b指令，这里是代码。

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

SQL数据库详细信息

SQL Database Details

我的数据库名称：MyWeb即可，表名：StoryItems，
柱：位置，标题，名称，类型

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");
      }
    }
}
}

一些怎么不喜欢列名，我不知道什么是缺少在这里。Trigger_Web是我的CLR SP名称。

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.

文件 - >新建 - >项目 - >数据库 - >选择Visual C＃CLR数据库项目。

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

它会提示你连接到一个数据库。一旦完成，确保您的应用程序触发收听记录插入你喜欢的任何表（你可以阅读更多关于视觉工作室CLR应用程序的这里）。

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)
            {

            }

        }

请注意：GetTransaction是你要创建的触发器的名称，在这种情况下Evnlog是表

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

这篇关于触发Windows服务当新记录插入到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！