ScaleOut SqlDependency Sample

SqlDependency.cs

/* 
 * © Copyright 2003-2017 by ScaleOut Software, Inc.
 *
 * LICENSE AND DISCLAIMER
 * ----------------------
 * This material contains sample programming source code ("Sample Code").
 * ScaleOut Software, Inc. (SSI) grants you a nonexclusive license to compile, 
 * link, run, display, reproduce, and prepare derivative works of 
 * this Sample Code.  The Sample Code has not been thoroughly
 * tested under all conditions.  SSI, therefore, does not guarantee
 * or imply its reliability, serviceability, or function. SSI
 * provides no support services for the Sample Code.
 *
 * All Sample Code contained herein is provided to you "AS IS" without
 * any warranties of any kind. THE IMPLIED WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGMENT ARE EXPRESSLY
 * DISCLAIMED.  SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED
 * WARRANTIES, SO THE ABOVE EXCLUSIONS MAY NOT APPLY TO YOU.  IN NO 
 * EVENT WILL SSI BE LIABLE TO ANY PARTY FOR ANY DIRECT, INDIRECT, 
 * SPECIAL OR OTHER CONSEQUENTIAL DAMAGES FOR ANY USE OF THE SAMPLE CODE
 * INCLUDING, WITHOUT LIMITATION, ANY LOST PROFITS, BUSINESS 
 * INTERRUPTION, LOSS OF PROGRAMS OR OTHER DATA ON YOUR INFORMATION
 * HANDLING SYSTEM OR OTHERWISE, EVEN IF WE ARE EXPRESSLY ADVISED OF
 * THE POSSIBILITY OF SUCH DAMAGES.
 */

/*
    ScaleOut StateServer .NET API Library: SqlDependency.OnChange event example

    Copyright ©, 2003-2017 by ScaleOut Software, Inc. All rights reserved.

    Summary 
    -------
    This sample illustrates how an application can monitor a SQL Server 
    table for changes (using ADO.NET's SqlDependency class) and then update 
    the SOSS distributed cache when a change occurs. In production, this 
    logic might reside in a Windows service that runs on the database 
    server. It makes use of SQL Server's query notification facilities, and 
    it only needs to be one on a single machine since changes to the SOSS 
    cache from one application are immediately visible to all other SOSS 
    client apps. 

    This approach of having a single application instance monitoring SQL 
    Server works well with SQL's query notification feature, 
    which is relatively expensive and does not scale well. 

    Usage Notes
    -----------
    The sample monitors a single table for changes: 

        CREATE TABLE [dbo].[Products](
            [ProductId] [int] NOT NULL,
            [ProductName] [nvarchar](200) NOT NULL,
            [ProductDesc] [nvarchar](1000) NULL
        ) ON [PRIMARY] 

    Make sure SQL Server's Service Broker is enabled on the database (it's 
    disabled by default), otherwise notifcations won't work. For example: 

        ALTER DATABASE DependencyDemoDB SET ENABLE_BROKER 

    When run, the application will update the SOSS cache whenever the 
    Products table is modified in the DB by another client (such as the SQL 
    Server Management Studio).

    Compile with references to soss_namedcache.dll and soss_svcdotnet.dll.
 
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

using Soss.Client;

namespace Soss.Samples.SqlDependencySample
{
    static class Program
    {
        const string ConnectionString = "data source=localhost;database=DependencyDemoDB;User Id=sa;password=password_1;Pooling=False;";

        private static SqlCommand _command;

        private static SqlConnection CreateConnection()
        {
            // This is an FxCop CA2000-compliant SqlConnection factory method.
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection( "Data Source=localhost;Initial Catalog=BackingStoreSample;Integrated Security=true" );
                connection.Open();
                return connection;
            }
            catch(Exception)
            {
                connection.Dispose();
                throw;
            }
        }

        /// <summary>
        /// Entry point into the application
        /// </summary>
        static void Main()
        {
            // Remove any existing dependency connection, then create a new one.
            SqlDependency.Stop( ConnectionString );
            SqlDependency.Start( ConnectionString );

            using( SqlConnection connection = CreateConnection() )
            using( _command = connection.CreateCommand() )
            {
                // Note that there are a number of restrictions on SQL notification queries:
                // http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx
                _command.CommandText = "SELECT ProductId, ProductName, ProductDesc FROM dbo.Products";

                // Initial call to populate the SOSS cache and start query notifications:
                RefreshCacheFromDB();

                // Wait and let dependency_OnChange handle SQL notifications until the user decides to exit:
                Console.WriteLine("Press [Return] to exit.");
                Console.ReadLine();
                
                SqlDependency.Stop( ConnectionString );
            }
        }
        
        /// <summary>
        /// Updates the SOSS distributed cache with the latest data from the DB.
        /// </summary>
        static void RefreshCacheFromDB()
        {
            // Make sure the SqlCommand does not already have
            // a notification object associated with it.
            _command.Notification = null;

            // Create and bind the SqlDependency object to the command object.
            SqlDependency dependency = new SqlDependency( _command );
            dependency.OnChange += new OnChangeEventHandler( SqlDependency_OnChange );

            using( DataTable productTable = new DataTable() )
            using( SqlDataAdapter adapter = new SqlDataAdapter( _command ) )
            {
                productTable.Locale = CultureInfo.InvariantCulture;
                adapter.Fill( productTable );

                // Update SOSS with the latest DataTable:
                NamedCache cache = CacheFactory.GetCache("My app's cache");
                cache["product datatable"] = productTable;
            }
        }

        /// <summary>
        /// Event handler for SQL notifications.
        /// </summary>
        static void SqlDependency_OnChange(Object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;
            
            // A notification will only work once, so remove the existing subscription to allow a new one to be added:
            dependency.OnChange -= SqlDependency_OnChange;

            RefreshCacheFromDB();
        }
    }
}