using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;

namespace SqlNotificationRequestEx
{
  class Class1
  {
    static string connstring = null;
    static SqlConnection conn = null;
    SqlDataReader rdr = null;

    static void Main(string[] args)
  {
    connstring = GetConnectStringFromConfigFile();
    conn = new SqlConnection(connstring);
    Class1 c = new Class1();
    c.DoWork();  
  }

    void DoWork()
    {
      conn.Open();
      rdr = GetJobs(2);
      if (rdr != null)
      {
        rdr.Close();
        WaitForChanges();
      }
      conn.Dispose();
    }

    public SqlDataReader GetJobs(int JobId)
    {
      using (SqlCommand cmd = new SqlCommand(
          "SELECT job_id, job_desc FROM dbo.jobs WHERE job_id = @id",
          conn))
      {

        try
        {
          cmd.Parameters.AddWithValue("@id", JobId);
          SqlNotificationRequest not = new SqlNotificationRequest();
          not.UserData = (new Guid()).ToString();

          // this must be a service named myservice in the pubs database
          // associated with a queue called notificationqueue (see below)
          // service must go by QueryNotifications contract
          not.Options = "service=myservice;local database=pubs";
          not.Timeout = 0;
          // hook up the notification request
          cmd.Notification = not;

          rdr = cmd.ExecuteReader();
          while (rdr.Read())
            Console.WriteLine(rdr[0]);
          rdr.Close();
        }
        catch (Exception ex)
        { Console.WriteLine(ex.Message); }
        return rdr;
      }
    }

    public void WaitForChanges()
    {
      // wait for notification to appear on the queue
      // then read it yourself
      using (SqlCommand cmd = new SqlCommand(
       "WAITFOR (RECEIVE CONVERT(xml,message_body) FROM myqueue)",
        conn))
      {
        object o = cmd.ExecuteScalar();
        // process the notification message however you like
        Console.WriteLine(o);
      }
    }

    static string GetConnectStringFromConfigFile()
    {
      return "server=.;integrated security=true;database=pubs";
    }
  }
}
