using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Threading;

namespace ConsoleApplication1
{
    class Program
    {
        const string ConnectionString = "Data Source=SERVER1\\SQLEXPRESS; Initial Catalog=demoAsyncTrigger; Integrated Security=True";

        static ManualResetEvent closeEvent = new ManualResetEvent(false);

        static void Main(string[] args)
        {
            Console.CancelKeyPress += new ConsoleCancelEventHandler(Console_CancelKeyPress);
            try
            {
                FetchData();
                StartListener();
                closeEvent.WaitOne();
            }
            catch (Exception ex)
            {
                Console.WriteLine("");
                Console.WriteLine(ex.ToString());
            }
        }

        private static void FetchData()
        {
            Console.WriteLine("Opening Db Connection...");
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                Console.WriteLine("Retrieving data...");

                var command = new SqlCommand("SELECT a FROM foo", connection);
                using (var reader = command.ExecuteReader())
                    while (reader.Read())
                        Console.WriteLine("Key = {0}", reader.GetInt32(0));
            }
        }

        private static void StartListener()
        {
            Thread thread = new Thread(ListenMethod);
            thread.IsBackground = true;
            thread.Name = "DML_Notification Listener";
            thread.Start();
        }

        private static void ListenMethod()
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                while (true)
                {
                    Console.WriteLine("Registering for notification");
                    var command = new SqlCommand("WAITFOR ( receive cast(message_body as xml), * from [DML_Notification])", connection);
                    var notificationRequest = new SqlNotificationRequest();
                    notificationRequest.UserData = new Guid().ToString();
                    notificationRequest.Options = "service=[DML_Notification]; Local Database=demoAsyncTrigger";
                    notificationRequest.Timeout = 600;
                    command.Notification = notificationRequest;
                    command.CommandTimeout = 615;
                    SqlDataReader reader = null;
                    try
                    {
                        reader = command.ExecuteReader();
                        while (reader != null && reader.Read())
                            Console.WriteLine(reader.GetSqlXml(0).Value);
                    }
                    catch (SqlException ex)
                    {
                        // TODO: Check if exception is timeout, bail on any other exception
                        //  I don't know how check for that specific error.
                        Console.WriteLine(ex.ToString());
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Dispose();
                    }
                }
            }
        }

        static int cancelPressCount = 0;
        static void Console_CancelKeyPress(object sender, ConsoleCancelEventArgs e)
        {
            if (cancelPressCount == 0)
                Console.WriteLine("Shutting down...");
            else
                Console.WriteLine("Shutdown request was acknowledged, press Ctrl+C again to terminate.");
            if (++cancelPressCount < 3)
            {
                e.Cancel = true;
                closeEvent.Set();
            }
        }
    }
}