use Sqldependency with [ORM]

Asked

Viewed 62 times

3

I am developing a service that must perform a procedure whenever a record as a certain condition is entered in the database.

However, despite using the SqlDependency, I’m not using the SqlCommand, it is running and discarded next.

static class Program
{
    static void Main()
    {
        ServiceBase[] ServicesToRun;
        ServicesToRun = new ServiceBase[]
        {
            new MyService()
        };
        ServiceBase.Run(ServicesToRun);
    }
}


public partial class MyService : ServiceBase
{
    private bool IsRunning;

    private static string ConnectionString
    {
        get
        {
            return ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
        }
    }

    public MyService()
    {   
        InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {            
        this.IsRunning = true;
        this.Agendar();
    }

    protected override void OnStop()
    {
        this.IsRunning = false;
    }

    private void Agendar()
    {
        if (!this.IsRunning)
            return;
        var processo = Task.Run(Processar);
        processo.ContinueWith(task =>
        {
            Console.WriteLine(task.Exception);
        }, TaskContinuationOptions.OnlyOnFaulted);
    }

    private async Task Processar()
    {
        using (var comando = new SqlCommand(Properties.Resources.MyQueryName))
        {
            var depedency = default(SqlDependency);
            var handler = default(OnChangeEventHandler);
            handler = new OnChangeEventHandler(async (sender, e) => {
                depedency = sender as SqlDependency;
                if (depedency != null) {
                    depedency.OnChange -= handler;
                    comando.Notification = null;
                }

                depedency = new SqlDependency(comando);
                depedency.OnChange += handler;

                using (var conexao = new SqlConnection(Program.ConnectionString))
                {
                    await conexao.OpenAsync();
                    comando.Connection = conexao;
                    (await comando.ExecuteReader()).Close();
                }

                if (e == null || e.Info == SqlNotificationInfo.Insert)
                    this.DoSomeWork();
            });
            handler.Invoke(depedency, null);
        }
    }

    private async Task DoSomeWork()
    {
        using (var contexto = new MyDbContext())
        {
            // regras de negocio.
        }
    }
}

So, is there any more elegant solution than the one shown below?

(await comando.ExecuteReaderAsync()).Close();
  • I couldn’t use a Rigger @Tobias ?

  • @Marconi I need to access a WCF Service, I know I could write a CLR Integration for this purpose, but as the implementation would be tortuous (read bureaucracy), I prefer to develop a Service.

  • When using the using the command will already be closed, no need to make an additional cahamda to the method Close

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.