Last active
June 4, 2023 22:34
-
-
Save markwaterman/34335e240dd6a7bc7953760d59230bee to your computer and use it in GitHub Desktop.
ScaleOut SqlDependency Sample
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* © 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(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment