Efficent way to watch a SQL table for new inserts

Question

I have a push service that inserts incoming data into a SQL table, i need to make an application that would be listening to this table to see for the new incoming data.

Is there a way to be listening to this table without having to be constantly SELECT ing the database for new changes? and if there is no use for this data shoud i just delete the row after reading it or is it better to do a periodical clean?

---

UPDATE2

---

This is my actual code which is not giving me any notifications on an insert to the table, am i doing something wrong?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;

namespace Navman_SPI_Service
{
    public partial class Service1 : ServiceBase
    {
        SqlConnection con = new SqlConnection();
        String passAvl = Properties.Settings.Default.AVLPass;
        String usuarioAvl = Properties.Settings.Default.AVLuser.ToString();
        String servidor = Properties.Settings.Default.Server.ToString();
        String DataB = Properties.Settings.Default.DB.ToString();
        String SQLuser = Properties.Settings.Default.DBuser.ToString();
        String SQLpass = Properties.Settings.Default.DBpassword.ToString();
        SqlCommand dependencyCommand = new SqlCommand();
        String connectionString = "";


        public Service1()
        {
            InitializeComponent();
        }
        public String getQuery()
        {
            return "SELECT MessageID FROM dbo.navman_ic_api_message WHERE Proccessed IS NULL";
        }

        public void OnDebug()
        {
            OnStart(null);
        }

        protected override void OnStart(string[] args)
        {
            dependencyCommand.CommandText = getQuery();
            connectionString = "Data Source=";
            connectionString += servidor + ";Initial Catalog=FOO;Persist Security Info=True;User ID=";
            connectionString += SQLuser + ";Password=";
            connectionString += SQLpass + ";Initial Catalog=" + DataB;

            con = new SqlConnection(connectionString);
            try
            {
                con.Open();
            }
            catch (Exception f)
            {
                var logging = new StringBuilder();
                var filePath = @"c:\temp\log.txt";
                String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
                logging.Append(timestamp + Environment.NewLine + Environment.NewLine + connectionString + Environment.NewLine + Environment.NewLine + f.ToString() + Environment.NewLine + Environment.NewLine);
                File.AppendAllText(filePath, logging.ToString());
            }
            if (con.State == ConnectionState.Open)
            {
                Initialization();
                dependencyStarter();
                var logging = new StringBuilder();
                var filePath = @"c:\temp\test.txt";
                String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
                logging.Append(timestamp + Environment.NewLine + Environment.NewLine + "SUCCESS" + Environment.NewLine + Environment.NewLine);
                File.AppendAllText(filePath, logging.ToString());
            }

        }

        protected override void OnStop()
        {
            Termination();
        }

        void dependencyStarter()
        {
            // Assume connection is an open SqlConnection.

            // Create a new SqlCommand object.
            using (SqlCommand command = new SqlCommand(getQuery(),con))
            {

                // Create a dependency and associate it with the SqlCommand.
                SqlDependency dependency = new SqlDependency(dependencyCommand);
                // Maintain the refence in a class member.

                // Subscribe to the SqlDependency event.
                dependency.OnChange += new
                   OnChangeEventHandler(OnDependencyChange);

                // Execute the command.
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    // Process the DataReader.
                }
            }
        }
        void OnDependencyChange(object sender, SqlNotificationEventArgs e)
        {
            // Handle the event (for example, invalidate this cache entry).
            var logging = new StringBuilder();
            var filePath = @"c:\temp\test.txt";
            String timestamp = DateTime.Now.ToString("[yyyy:MM:dd][HH:mm:ss]");
            logging.Append(timestamp + Environment.NewLine + Environment.NewLine + sender.ToString() + Environment.NewLine + Environment.NewLine);
            File.AppendAllText(filePath, logging.ToString());
        }
        void Termination()
        {
            // Release the dependency.

            SqlDependency.Stop(connectionString);
        }
        void Initialization()
        {
            // Create a dependency connection.
            SqlDependency.Stop(connectionString);
            SqlDependency.Start(connectionString);
        }
    }
}

Thanks,

Answer 1

Try using a SqlDependency. Detecting Changes with SqlDependency

I've used these in a number of projects and they seem to work pretty well. This uses QueryNotifications which is part of the SQL Server Service Broker.

If the Broker is not already active, you will have to enable it. Get a DBA to run something like the following:

USE master;
GO

ALTER DATABASE MyDatabase SET ENABLE_BROKER;
GO

Sample code from the article:

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Start(connectionString, queueName);
}

void SomeMethod()
{
    // Assume connection is an open SqlConnection.

    // Create a new SqlCommand object.
    using (SqlCommand command=new SqlCommand(
        "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", 
        connection))
    {

        // Create a dependency and associate it with the SqlCommand.
        SqlDependency dependency=new SqlDependency(command);
        // Maintain the refence in a class member.

        // Subscribe to the SqlDependency event.
        dependency.OnChange+=new
           OnChangeEventHandler(OnDependencyChange);

        // Execute the command.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process the DataReader.
        }
    }
}

// Handler method
void OnDependencyChange(object sender, 
   SqlNotificationEventArgs e )
{
  // Handle the event (for example, invalidate this cache entry).
}

void Termination()
{
    // Release the dependency.
    SqlDependency.Stop(connectionString, queueName);
}

Basically, you setup a callback in SQL Server that gets called anytime the results for the given query change. Then it's up to you to do something about it.

You'll probably want to have a column on the table so you can tell if the data's new or not. Something like "CreateDate" or "Processed". Exclude that from the dependency query. Then you can use it later to grab only the new stuff.

I should note that if the table is changing frequently, it might be better to go with polling periodically and just grabbing all the changes as a single batch instead of trying to handle them one at a time. SqlDependency is great for refreshing cached data. It's not great for processing a table that's being used as a transaction queue.

I wouldn't delete the data when you're done. Just ignore it in the next query or maybe move it to an archive table. Deleting information is generally a bad idea in case you need to troubleshoot it later.