Example with Web API

This is a WebAPI application using the SqlTableDependency on Customer table used as cache. The application execute an initial database read, in order to fill the cache. Then, using the SqlTableDependency we keep the cache in sync with database table, simply using the event handler:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
using TableDependency.Enums;
using TableDependency.Events;
using TableDependency.SqlClient;
using WebApplication.Models;

namespace WebApplication
{
    public class WebApiApplication : HttpApplication
    {
        private static string CONNECTION_STRING = 
            "Data Source=.;Initial Catalog=TableDependencyDB;Integrated Security=True";
        private static IList<Customer> _customers;
        private SqlTableDependency<Customer> _sqlTableDependency = 
            new SqlTableDependency<Customer>(CONNECTION_STRING, "Customer");

        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            GlobalConfiguration.Configure(WebApiConfig.Register);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            this.Disposed += Application_Dispose;

            _sqlTableDependency.OnChanged += _sqlTableDependency_OnChanged;
            _sqlTableDependency.Start();
        }

        // Stop the sqlTableDependency, in order to 
        // correctly remove all database objects used to receive notifications
        protected void Application_Dispose(object sender, EventArgs e)
        {
            _sqlTableDependency.Stop();
        }

        // Here, instead of executing a SELECT to load all the records, 
        // I simply can modify my local cache
        private void _sqlTableDependency_OnChanged(
            object sender, 
            RecordChangedEventArgs<Customer> e)
        {
            if (_customers != null)
            {
                switch (e.ChangeType)
                {
                    case ChangeType.Delete:
                        _customers.Remove(
                            _customers.FirstOrDefault(c => c.Id == e.Entity.Id));
                        break;
                    case ChangeType.Insert:
                        _customers.Add(e.Entity);
                        break;
                    case ChangeType.Update:
                        var customerIndex = _customers.IndexOf(
                            _customers.FirstOrDefault(c => c.Id == e.Entity.Id));
                        if (customerIndex >= 0) _customers[customerIndex] = e.Entity;
                        break;
                }
            }
        }

        // First and only database read operation
        internal static IEnumerable<Customer> Customers
        {
            get
            {
                if (_customers == null)
                {
                    _customers = new List<Customer>();
                    using (var connection = 
                              new SqlConnection(CONNECTION_STRING))
                    {
                        var command = new SqlCommand(
                            "SELECT Id, Name, Surname FROM Customer", connection);
                        connection.Open();

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                _customers.Add(new Customer 
                                { 
                                    Id = reader.GetInt32(0), 
                                    Name = reader.GetString(1), 
                                    Surname = reader.GetString(2) 
                                });
                            }
                        }
                    }
                }

                return _customers;
            }
        }
    }
}

HTML page:

<script type="text/javascript">
    function GetAll() {
        $.ajax({
            url: '/api/values',
            type: 'GET',
            dataType: 'json',
            success: function (data) {
                WriteResponse(data);
            },
            error: function () {
                alert('Error');
            }
        });
    }

    function WriteResponse(customers) {
        $("#divResult").empty();
        var strResult = "<table border=1 style='width:100%'><th>ID</th><th>Name</th><th>Surname</th>";
        $.each(customers, function (index, customer) {
            strResult += "<tr><td>" + customer.Id + "</td><td> " + customer.Name + "</td><td>" + customer.Surname + "</td></tr>";
        });
        strResult += "</table>";
        $("#divResult").html(strResult);
    }
</script>

<br />
<button onclick="GetAll();return false;">Get All Customers</button>
<p>
    <div id="divResult">
    </div>
</p>

WebAPI:

using System.Collections.Generic;
using System.Web.Http;
using WebApplication.Models;

namespace WebApplication.Controllers
{
    public class ValuesController : ApiController
    {
        public IEnumerable<Customer> Get()
        {
            return WebApiApplication.Customers;
        }
    }
}

Customer class:

namespace WebApplication.Models
{
    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Surname { get; set; }
    }
}

Last edited Jun 21, 2016 at 12:08 PM by delbianco, version 3