locked
Use sql notification for console application C# RRS feed

  • Question

  • I have developed ucma C# console application which sends IM  to users. 

    Now I want to send IM if any new entry is added in the table 

    So i want to use query notification .

    I have enabled service broker on my database .

    And I have added code for sqldepedency  in my code file.

    as follows.

     public static void Main(string[] args)
        {
          
          UCMASampleInstantMessagingCall ucmaSampleInstantMessagingCall =
                                        new UCMASampleInstantMessagingCall();
          ucmaSampleInstantMessagingCall.connectionfunction();
    }

    public void connectionfunction()
    
    string connectionString = @"Data Source=PIXEL-IHANNAH2\PPINSTANCE;User ID=sqlPPUser;Password=ppuser1;Initial Catalog=Ian;";
    
    
    
          SqlDependency.Stop(connectionString);
    
    
    
    
    
    
    
          SqlConnection sqlConnection = new SqlConnection(connectionString);
    
    
    
    
    
    
    
          string statement = "select * from dbo.tblTest";
    
    
    
          SqlCommand sqlCommand = new SqlCommand(statement, sqlConnection);
    
    
    
    
    
    
    
          // Create and bind the SqlDependency object to the command object.
    
    
    
          SqlDependency dependency = new SqlDependency(sqlCommand, null, 0);
    
    
    
          SqlDependency.Start(connectionString);
    
    
    
    
    
    
    
          dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
    
    
    
        }
    
    
    
        
    
    
    
        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    
    
    
        {
    
    
    ucmaSampleInstantMessagingCall.run()
    }
    private void Run() { // Initialize and startup the platform. Exception ex = null; try { // Create the UserEndpoint _helper = new UCMASampleHelper(); _userendpoint = _helper.CreateEstablishedUserEndpoint();

    Now I want to run this application in the background , so f any new entry is added it will notify my C# application and call run function.
    i tried above code but it was not working. 
    Sample code is avaliableon net but it is for form application and we can see output if we run the form application .
    so if i want to run my C# application what should I do?

    Saturday, August 27, 2011 6:43 PM

Answers

  •  But in my application my application will run (send IM) when any new entry is added in the database . 
    As I need running application to achieve notification , what kind of changes are required?

    If you don't want the application ro run as an interactive console application, another option is to run the app as a Windows service.  This will not allow the user to interact directly with the application, though.  I'm not familar with inc but it seems the purpose of the code is to just respond to IM messages without user interaction so maybe the service approach will work for you.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Monday, September 5, 2011 8:15 AM
    Tuesday, August 30, 2011 12:23 AM

All replies

  • One of the requirements for QueryNotifications is that the SELECT command must not specify an asterick; an explict columns list must be specified instead.  See http://msdn.microsoft.com/en-us/library/ms181122(v=SQL.105).aspx for other considerations.

    Below is a console example.  Note that this blocks the main thread until enter is pressed.  The SqlDependency thread handles the query notifications.

     

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Configuration;
    
     class SqlNotificationConsole
     {
    
      private static SqlDependency dependency;
      private static string connectionString = @"Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI";
    
      static void Main(string[] args)
      {
       var connection = new SqlConnection(connectionString);
       SqlDependency.Start(connectionString);
       RefreshDataWithSqlDependency();
    
       //block main thread - SqlDependency thread will monitor changes
       Console.ReadLine();
       SqlDependency.Stop(connectionString);
      }
    
      static void RefreshDataWithSqlDependency()
      {
    
       try
       {
        //Remove existing dependency, if necessary
        if (dependency != null)
        {
         dependency.OnChange -= onDependencyChange;
         dependency = null;
        }
    
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
    
        SqlCommand command = new SqlCommand(
         "select col1 from dbo.tblTest",
         connection);
    
        // Create a dependency (class member) and associate it with the command.
        dependency = new SqlDependency(command, null, 5);
    
        // Subscribe to the SqlDependency event.
        dependency.OnChange += new OnChangeEventHandler(onDependencyChange);
    
        // start dependency listener
        SqlDependency.Start(connectionString);
    
        // execute command and refresh data
        refreshData(command);
    
        connection.Close();
    
       }
       catch (SqlException ex)
       {
        throw ex;
       }
      }
    
      private static void onDependencyChange(Object o, SqlNotificationEventArgs args)
      {
       if ((args.Source.ToString() == "Data") || (args.Source.ToString() == "Timeout"))
       {
        Console.WriteLine("Refreshing data due to {0}", args.Source);
        RefreshDataWithSqlDependency();
       }
       else
       {
        Console.ForegroundColor = ConsoleColor.Red;
        Console.WriteLine("Data not refreshed due to unexpected SqlNotificationEventArgs: Source={0}, Info={1}, Type={2}", args.Source, args.Info, args.Type.ToString());
        Console.ForegroundColor = ConsoleColor.Gray;
       }
      }
    
      private static void refreshData(SqlCommand command)
      {
       var reader = command.ExecuteReader();
       while (reader.Read())
       {
        //process data here
       }
       reader.Close();
      }
     }
    
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Saturday, August 27, 2011 7:52 PM change connection string
    Saturday, August 27, 2011 7:52 PM
  • thanks for the reply ..

    So i can use my console application with above modifications.. no need to write any service for this. as in my code after adding new record  , i need to call Run function in my file .. Is it right ? 

    My doubt is how my application will be callled when any value is inserted , as i am not running this console application in the background?

    So as per above code I can call run function instead of refresh data ..

    and in sql side i have enabled service broker. and i am using windows authentication ,so do i need to enable /add any service /queue for above code? I have read that if u are using sqldepedency u need to enable service broker only.

    Sunday, August 28, 2011 2:39 PM
  • I can't see your code clearly (use the Insert code block button so it is formatted better) and it's incomplete so it's difficult for me to be specific.  I can say that the applciation must be running in order to receive notifications and invoke the Run method.  You can do whatever you want in the Run method but be aware that the dependency stops automatically after the OnChange event fires.  You will need to start the dependency and execute the command again if you want to receive further change notifications.

    On the SQL Server side, all you need to do is enable service broker and, if your account is not privileged, assign the needed database permissions.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, August 28, 2011 3:44 PM
  • using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Configuration;
    using System.Security.Principal;
    using System.Security.Cryptography.X509Certificates;
    using System.Net;
    using System.Threading;
    using System.Diagnostics;
    using Microsoft.Rtc.Collaboration;
    using Microsoft.Rtc.Signaling;
    using System.Runtime.InteropServices;
    //using Microsoft.Rtc.Collaboration.Sample.Common;
    namespace UserPresence
    {
      class UCMASampleInstantMessagingCall
      {
        #region Locals
        // The information for the conversation and the far end participant.    
    
        // Subject of the conversation; will appear in the center of the title bar of the 
        // conversation window if Microsoft Lync is the far end client.
        private static String _conversationSubject = "The Microsoft Lync Server!";
    
        // Priority of the conversation will appear in the left corner of the title bar of the
        // conversation window if Microsoft Lync is the far end client.
        private static String _conversationPriority = ConversationPriority.Urgent;
    
        // The Instant Message that will be sent to the far end.
        private static String _messageToSend = "Hello World! I am a bot, and will echo whatever you type. " +
          "Please send 'bye' to end this application.";
    
        private InstantMessagingCall _instantMessaging;
    
        private InstantMessagingFlow _instantMessagingFlow;
    
        //private ApplicationEndpoint _applicationEndpoint;
        private UserEndpoint _userendpoint;
        private UCMASampleHelper _helper;
    private static SqlDependency dependency;
     private static string connectionString = @"Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI";
    
        // Event to notify application main thread on completion of the sample.
        private AutoResetEvent _sampleCompletedEvent = new AutoResetEvent(false);
        #endregion
        #region Methods
        /// <summary>
        /// Instantiate and run the InstantMessagingCall quickstart.
        /// </summary>
        /// <param name="args">unused</param>
        //private string _helper;
        //UCMASampleHelper _helper = new UCMASampleHelper();
        public static void Main(string[] args)
        {
          
          UCMASampleInstantMessagingCall ucmaSampleInstantMessagingCall =
                                        new UCMASampleInstantMessagingCall();
    var connection = new SqlConnection(connectionString);
      SqlDependency.Start(connectionString);
    SqlConnection connection = new SqlConnection(connectionString);
      connection.Open();
    
      SqlCommand command = new SqlCommand(
       "select col1 from dbo.tblTest",
       connection);
    
      // Create a dependency (class member) and associate it with the command.
      dependency = new SqlDependency(command, null, 5);
    
      // Subscribe to the SqlDependency event.
      dependency.OnChange += new OnChangeEventHandler(onDependencyChange);
    
      // start dependency listener
      SqlDependency.Start(connectionString);
          //ucmaSampleInstantMessagingCall.Run();
        }
    private static void onDependencyChange(Object o, SqlNotificationEventArgs args)
     {
     
      run();
      }
      
     }
        private void Run()
        {
    
          // Initialize and startup the platform.
          Exception ex = null;
    
          try
          {
    
            // Create the UserEndpoint
            _helper = new UCMASampleHelper();
    
            _userendpoint = _helper.CreateEstablishedUserEndpoint();
    
            Console.Write("The User Endpoint owned by URI: ");
            Console.Write(_userendpoint.OwnerUri);
            Console.WriteLine(" is now established and registered.");
            List<string> _Users = new List<string>();
            _Users.Add("sip:demouser@lync.abc.com");
            _Users.Add("sip:demouser@lync.abc.com");
            foreach (string useruri in _Users)
            {
            // Setup the conversation and place the call.
            ConversationSettings convSettings = new ConversationSettings();
            convSettings.Priority = _conversationPriority;
            convSettings.Subject = _conversationSubject;
    
            // Conversation represents a collection of modes of communication
            // (media types)in the context of a dialog with one or multiple 
            // callees.
            //Convers conver =new ConversationParticipant
            Conversation conversation = new Conversation(_userendpoint, convSettings);
            InstantMessagingCall _instantMessaging = new InstantMessagingCall(conversation);
    
            // Call: StateChanged: Only hooked up for logging. Generally, 
            // this can be used to surface changes in Call state to the UI
            _instantMessaging.StateChanged += this.InstantMessagingCall_StateChanged;
    
            // Subscribe for the flow created event; the flow will be used to
            // send the media (here, IM).
            // Ultimately, as a part of the callback, the messages will be 
            // sent/received.
            _instantMessaging.InstantMessagingFlowConfigurationRequested +=
              this.InstantMessagingCall_FlowConfigurationRequested;
    
            // Get the sip address of the far end user to communicate with.
            
            //String _calledParty = "sip:" +
            //  UCMASampleHelper.PromptUser(
            //  "Enter the URI of the user logged onto Microsoft Lync, in the User@Host format => ",
            //  "RemoteUserURI");
    
            // Place the call to the remote party, without specifying any 
            // custom options. Please note that the conversation subject 
            // overrides the toast message, so if you want to see the toast 
            // message, please set the conversation subject to null.
            
              // _instantMessaging.Conversation()
    
              _instantMessaging.BeginEstablish(useruri, new ToastMessage("Hello Toast"), null,
                CallEstablishCompleted, _instantMessaging);
            }
          }
          catch (InvalidOperationException iOpEx)
          {
            // Invalid Operation Exception may be thrown if the data provided
            // to the BeginXXX methods was invalid/malformed.
            // TODO (Left to the reader): Write actual handling code here.
            ex = iOpEx;
          }
          finally
          {
            if (ex != null)
            {
              // If the action threw an exception, terminate the sample, 
              // and print the exception to the console.
              // TODO (Left to the reader): Write actual handling code here.
              Console.WriteLine(ex.ToString());
              Console.WriteLine("Shutting down platform due to error");
              _helper.ShutdownPlatform();
            }
          }
    
          // Wait for sample to complete
          _sampleCompletedEvent.WaitOne();
    
        }
    
        // Just to record the state transitions in the console.
        void InstantMessagingCall_StateChanged(object sender, CallStateChangedEventArgs e)
         {
          Console.WriteLine("Call has changed state. The previous call state was: " + e.PreviousState +
            "and the current state is: " + e.State);
        }
    
        // Flow created indicates that there is a flow present to begin media 
        // operations with, and that it is no longer null.
        public void InstantMessagingCall_FlowConfigurationRequested(object sender,
          InstantMessagingFlowConfigurationRequestedEventArgs e)
        {
          Console.WriteLine("Flow Created.");
          _instantMessagingFlow = e.Flow;
    
          // Now that the flow is non-null, bind the event handlers for State 
          // Changed and Message Received. When the flow goes active, 
          // (as indicated by the state changed event) the program will send 
          // the IM in the event handler.
          _instantMessagingFlow.StateChanged += this.InstantMessagingFlow_StateChanged;
    
          // Message Received is the event used to indicate that a message has
          // been received from the far end.
          _instantMessagingFlow.MessageReceived += this.InstantMessagingFlow_MessageReceived;
    
          // Also, here is a good place to bind to the 
          // InstantMessagingFlow.RemoteComposingStateChanged event to receive
          // typing notifications of the far end user.
          _instantMessagingFlow.RemoteComposingStateChanged +=
                              this.InstantMessagingFlow_RemoteComposingStateChanged;
        }
    
        private void InstantMessagingFlow_StateChanged(object sender, MediaFlowStateChangedEventArgs e)
        {
          Console.WriteLine("Flow state changed from " + e.PreviousState + " to " + e.State);
    
          // When flow is active, media operations (here, sending an IM) 
          // may begin.
          if (e.State == MediaFlowState.Active)
          {
            // Send the message on the InstantMessagingFlow.
            _instantMessagingFlow.BeginSendInstantMessage(_messageToSend, SendMessageCompleted,
              _instantMessagingFlow);
          }
        }
    
        private void InstantMessagingFlow_RemoteComposingStateChanged(object sender,
                                        ComposingStateChangedEventArgs e)
        {
          // Prints the typing notifications of the far end user.
          Console.WriteLine("Participant "
                    + e.Participant.Uri.ToString()
                    + " is "
                    + e.ComposingState.ToString()
                    );
        }
    
        private void InstantMessagingFlow_MessageReceived(object sender, InstantMessageReceivedEventArgs e)
        {
          // On an incoming Instant Message, print the contents to the console.
          Console.WriteLine(e.Sender.Uri + " said: " + e.TextBody);
    
          // Shutdown if the far end tells us to.
          if (e.TextBody.Equals("bye", StringComparison.OrdinalIgnoreCase))
          {
            // Shutting down the platform will terminate all attached objects.
            // If this was a production application, it would tear down the 
            // Call/Conversation, rather than terminating the entire platform.
            _instantMessagingFlow.BeginSendInstantMessage("Shutting Down...", SendMessageCompleted,
              _instantMessagingFlow);
            _helper.ShutdownPlatform();
            _sampleCompletedEvent.Set();
          }
          else
          {
            // Echo the instant message back to the far end (the sender of 
            // the instant message).
            // Change the composing state of the local end user while sending messages to the far end.
            // A delay is introduced purposely to demonstrate the typing notification displayed by the 
            // far end client; otherwise the notification will not last long enough to notice.
            _instantMessagingFlow.LocalComposingState = ComposingState.Composing;
            Thread.Sleep(2000);
    
            //Echo the message with an "Echo" prefix.
            _instantMessagingFlow.BeginSendInstantMessage("Echo: " + e.TextBody, SendMessageCompleted,
              _instantMessagingFlow);
          }
    
        }
    
        private void CallEstablishCompleted(IAsyncResult result)
        {
          InstantMessagingCall instantMessagingCall = result.AsyncState as InstantMessagingCall;
          Exception ex = null;
          try
          {
            instantMessagingCall.EndEstablish(result);
            Console.WriteLine("The call is now in the established state.");
          }
          catch (OperationFailureException opFailEx)
          {
            // OperationFailureException: Indicates failure to connect the 
            // call to the remote party.
            // TODO (Left to the reader): Write real error handling code.
            ex = opFailEx;
          }
          catch (RealTimeException rte)
          {
            // Other errors may cause other RealTimeExceptions to be thrown.
            // TODO (Left to the reader): Write real error handling code.
            ex = rte;
          }
          finally
          {
            if (ex != null)
            {
              // If the action threw an exception, terminate the sample, 
              // and print the exception to the console.
              // TODO (Left to the reader): Write real error handling code.
              Console.WriteLine(ex.ToString());
              Console.WriteLine("Shutting down platform due to error");
              _helper.ShutdownPlatform();
            }
          }
        }
    
        private void SendMessageCompleted(IAsyncResult result)
        {
          InstantMessagingFlow instantMessagingFlow = result.AsyncState as InstantMessagingFlow;
          Exception ex = null;
          try
          {
            instantMessagingFlow.EndSendInstantMessage(result);
            Console.WriteLine("The message has been sent.");
          }
          catch (OperationTimeoutException opTimeEx)
          {
            // OperationFailureException: Indicates failure to connect the 
            // IM to the remote party due to timeout (called party failed to
            // respond within the expected time).
            // TODO (Left to the reader): Write real error handling code.
            ex = opTimeEx;
          }
          catch (RealTimeException rte)
          {
            // Other errors may cause other RealTimeExceptions to be thrown.
            // TODO (Left to the reader): Write real error handling code.
            ex = rte;
          }
          finally
          {
            // Reset the composing state of the local end user so that the typing notifcation as seen 
            // by the far end client disappears.
            _instantMessagingFlow.LocalComposingState = ComposingState.Idle;
            if (ex != null)
            {
              // If the action threw an exception, terminate the sample, 
              // and print the exception to the console.
              // TODO (Left to the reader): Write real error handling code.
              Console.WriteLine(ex.ToString());
              Console.WriteLine("Shutting down platform due to error");
              _helper.ShutdownPlatform();
            }
          }
        }
        #endregion
      }
    }
    
    

    So i want to call run method when new entry is added in the database . and this application is not like any asp.net form application . In form application we display data when programs loads as like your RefreshDataWithSqlDependency function . But in my application my application will run (send IM) when any new entry is added in the database . As I need running application to achieve notification , what kind of changes are required?


    Monday, August 29, 2011 8:44 AM
  •  But in my application my application will run (send IM) when any new entry is added in the database . 
    As I need running application to achieve notification , what kind of changes are required?

    If you don't want the application ro run as an interactive console application, another option is to run the app as a Windows service.  This will not allow the user to interact directly with the application, though.  I'm not familar with inc but it seems the purpose of the code is to just respond to IM messages without user interaction so maybe the service approach will work for you.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Monday, September 5, 2011 8:15 AM
    Tuesday, August 30, 2011 12:23 AM
  • Thanks for the reply . I am writing Windows service .:)
    Monday, September 5, 2011 11:05 AM
  • Thanks for this example!
    Friday, January 10, 2014 1:55 PM
  • Hi J. Please check SnipeDB.com framework as a great alternative to SQLDependency


    Wednesday, January 15, 2014 10:53 PM