SqlDependencyEx - TableListener Example

CreateTables.sql

/****** Object:  Table [dbo].[Table1]    Script Date: 6/22/2017 10:42:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table1](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nchar](10) NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Table2]    Script Date: 6/22/2017 10:42:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table2](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nchar](10) NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into table1 ([description]) values ('abc')
insert into table2 ([description]) values ('def')

Program.cs

  public class Program
  {
    private const string Connection = "Data Source=(local);Initial Catalog=master;Integrated Security=True";
    private const string Database = "TestDatabase";

    private static void Main(string[] args)
    {
      var listener = TableUpdateListener("Table1", 1, FirstTableUpdated);
      var listener2 = TableUpdateListener("Table2", 2, SecondTableUpdated);
      listener.Start();
      listener2.Start();
      Console.WriteLine("______________Escuchando_______________");
      Console.ReadKey();
      listener.Stop();
      listener2.Stop();
    }

    private static SqlDependencyEx TableUpdateListener(string tableName, int identity, EventHandler<TableChangedEventArgs> handler)
    {
      var listener = new SqlDependencyEx(
        Connection,
        Database,
        tableName,
        listenerType: NotificationTypes.Update,
        identity: identity);
      listener.TableChanged += handler;
      return listener;
    }

    private static void SecondTableUpdated(object o, SqlDependencyEx.TableChangedEventArgs e)
    {
      Console.WriteLine($"SecondTableUpdated {e.Data}");
    }

    private static void FirstTableUpdated(object o, SqlDependencyEx.TableChangedEventArgs e)
    {
      Console.WriteLine($"FirstTableUpdated {e.Data}");
    }
  }

Thanks a lot man. working great now. i am going to send u my class that i use as upper layer to access to ur code. that will be a suggestion to add new funtionalities to ur code if that is fine to u.
as i am workig with a client where i have to install many window services listening different tables. so every time i create a new litener i have to consult db to check how many listeners r aded before to give a new number not repeated to new litener. well to avoid that i created a new class as upper layer to acces ur litener and also change type of return XML to an EF entity. this is just an idea. thanks for ur help. My appriciations. thanks again

DbListener.cs

public class DbListener
{
public string conexion { get; private set; }
public string dbName { get; private set; }
public string table { get; private set; }
public string SchemaName { get; private set; }
public NotificationTypes NotificaionTypes { get; private set; }
public bool DetailsIncluded { get; private set; }
public int Identity { get; private set; }

    public Action<T, T, string> CallBack;

    private SqlDependencyEx SqlListener = null;
    public bool Active { get; private set; }
    public bool ReceiveDetails { get; private set; }

    private static Logger logger = LogManager.GetCurrentClassLogger();
    /// <summary>
    /// Contructor
    /// </summary>
    /// <param name="Conexion"></param>
    public DbListener(string Conexion)
    {
        this.conexion = GetConexionString(Conexion);
    }

    /// <summary>
    /// Subscribe method complete listener instanse and Starts the listener
    /// </summary>
    /// <param name="DbName"></param>
    /// <param name="Table"></param>
    /// <param name="NotifyTypes"></param>
    /// <param name="SchemaName"></param>
    /// <param name="ReceiveDetails"></param>

    public void Subscribe(string Table, string DbName = "",
                                       string NotifyTypes = "",
                                       string SchemaName = "dbo",
                                       bool ReceiveDetails = true)
    {
        this.dbName = GetTableNameAndIdentity(conexion); ;
        this.table = Table;
        this.SchemaName = SchemaName;
        this.NotificaionTypes = getType(NotifyTypes);
        this.DetailsIncluded = ReceiveDetails;
        this.ReceiveDetails = ReceiveDetails;
    }


    public void Start()
    {
        try
        {
            // identity will be coming from [GetTableNameAndIdentity()]
            this.SqlListener = new SqlDependencyEx(this.conexion, this.dbName, table, this.SchemaName, this.NotificaionTypes, this.ReceiveDetails, this.Identity);
            this.SqlListener.TableChanged += (o, e) => ChangeEvent(o, e);

            SqlListener.Start();
            this.Active = true;
        }
        catch (Exception exp)
        {

            throw;
        }

    }

    private void ChangeEvent(object o, TableChangedEventArgs e)
    {
        var deleted = false; var inserted = false;

        try
        {
            if (e.NotificationType != NotificationTypes.None)
            {
                List<T> list = new List<T>();
                deleted = e.Data.Descendants("deleted").FirstOrDefault() != null ? true : deleted;
                inserted = e.Data.Descendants("deleted").FirstOrDefault() != null ? true : inserted;

                if (inserted)
                {
                    XElement insData = e.Data.Descendants("inserted").Descendants("row").FirstOrDefault();
                    var res = FromXElement<T>(insData);
                    list.Add(res);
                }
                if (deleted)
                {
                    XElement insData = e.Data.Descendants("deleted").Descendants("row").FirstOrDefault();
                    var res = FromXElement<T>(insData);
                    list.Add(res);
                }
                CallBack.Invoke(list[0], list[1], e.NotificationType.ToString());
            }

        }
        catch (Exception exp)
        {
            logger.Error("ChangeEvent=>  " + exp.Message);
        }

    }

    public void Stop()
    {
        SqlListener.Stop();
        this.Active = false;
    }

    /// <summary>
    /// Assign the Fucntion CallBack
    /// </summary>
    /// <param name="onChange"></param>
    /// 


    public void OnChange(Action<T, T, string> onChange)
    {
        CallBack += onChange;
    }

    private static T FromXElement<T>(XElement xElement)
    {
        var xmlSerializer = new XmlSerializer(typeof(T));
        var desz = xmlSerializer.Deserialize(xElement.CreateReader());
        return (T)desz;
    }


    private string GetConexionString(string conexion)
    {
        try
        {
            var conxStr = ConfigurationManager.ConnectionStrings[conexion].ConnectionString;
            if (conxStr == "")
            {
                logger.Error("GetConexionString=> " + "No connection String Avialable");
            }
            return conxStr;

        }
        catch (Exception exp)
        {
            logger.Error("GetConexionString=> " + exp.Message);
            throw;
        }

    }
    private string GetTableNameAndIdentity(string conexion)
    {
        var res = 0;
        using (SqlConnection connection = new SqlConnection(conexion))
        {
            try
            {
                connection.Open();

                /*Buscamos count de triggers que hay in base de datos para no repetir siempre +1*/
                SqlCommand command = new SqlCommand("select COUNT(*) from sys.triggers where name Like 'tr_Listener_%'", connection);
                SqlDataReader sqlReader = command.ExecuteReader();
                while (sqlReader.Read())
                {
                    res = sqlReader.GetInt32(0);
                }
                this.Identity = res + 1;
                return connection.Database;
            }
            catch (Exception exp)
            {
                logger.Error("GetTableNameAndIdentity=> " + exp.Message);
                throw;
            }

        }
    }


    private NotificationTypes getType(string notificaionType)
    {
        if (notificaionType != null)
        {
            var str = notificaionType.ToLower();
        }

        NotificationTypes notifyTipo;
        switch (notificaionType)
        {
            case "insert":
                notifyTipo = NotificationTypes.Insert;
                break;
            case "update":
                notifyTipo = NotificationTypes.Update;
                break;
            case "delete":
                notifyTipo = NotificationTypes.Delete;
                break;
            default:
                notifyTipo = NotificationTypes.Insert | NotificationTypes.Update | NotificationTypes.Delete;
                break;
        }
        return notifyTipo;
    }

}

}

Program.cs

class Program
{
private static string conx = "Connection"; // is name of conx in config as using EF context
private static DbListener listener = null;
private static DbListener listener2 = null;
static void Main(string[] args)
{
try
{
listener = new DbListener(conx);
listener.Subscribe(Table: "Table1", NotifyTypes: "update");
listener.OnChange(OnChange_L1);
listener.Start();

            listener2 = new DbListener<Table2>(conx);
            listener2.Subscribe(Table: "Table2", NotifyTypes: "update");
            listener2.OnChange(OnChange_L2);
            listener2.Start();


            Console.WriteLine("______________Escuchando_______________");

            Console.ReadKey();
        }
        catch (Exception exp)
        {
            if (listener.Active == true)
            {
                listener.Stop();
            }
            if (listener2.Active == true)
            {
                listener2.Stop();
            }
            throw;
        }
    }

    private static void OnChange_L1<T>(T inserted, T deleted, string action)
    {
        var added = inserted as Table1;
    }

    private static void OnChange_L2<T>(T inserted, T deleted, string action)

    {
        var added = deleted as Table2;
    }
}

This is considered as a bad practice to use SqlDependencyEx as a root for Client-Server applications. A good solution here would be to have only 1 listener per table and place it in a service which shares events with clients via other protocols like WCF/WebSockets etc. See working best practices here and here.

I would agree that sharing events from the say a console application or a windows service through other events will make your application

• Easier to understand
• Debug
• Composable
• Testable

I wouldn't recommend WCF as it is so complicated every time I try to debug it. WebSockets yes and Server Sent Events in ServiceStack as an example are in my experience easier to understand and readily cross platform.