Detect SQL event with C# application

Question

I have the code below (a literal copy/paste from MSDN website just to be clear), which seems to connect as it should (except for an "Access Denied" error which is okay because my security requests haven't gone through yet). What I need to do is detect when our sql server has executed an insert or update operation. Basically this application should be running 24/7 and perform certain functions when an operation as such comes across the listener. I'm not asking for the code to be laid out in front of me, but I am asking where to start. This is something I have no clue how to do at this point and am being told I have roughly a week to figure it out and get it done. Can someone point me in the right direction? Thank you in advance!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading;

namespace Connect_Server
{
    class Program
    {
        static string output = "";

        static void Main(string[] args)
        {
            createListener();
        }

        static public void createListener()
        {
            // Create an instance of the TcpListener class.
            TcpListener tcpListener = null;
            IPAddress ipAddress = Dns.GetHostEntry("localhost").AddressList[0];
            try
            {
                // Set the listener on the local IP address 
                // and specify the port.
                tcpListener = new TcpListener(ipAddress, 80);
                tcpListener.Start();
                output = "Waiting for a connection...";
            }
            catch (Exception e)
            {
                output = "Error: " + e.ToString();
                Console.Write(output);
            }
            while (true)
            {
                // Always use a Sleep call in a while(true) loop 
                // to avoid locking up your CPU.
                Thread.Sleep(10);
                // Create a TCP socket. 
                // If you ran this server on the desktop, you could use 
                // Socket socket = tcpListener.AcceptSocket() 
                // for greater flexibility.
                TcpClient tcpClient = tcpListener.AcceptTcpClient();
                // Read the data stream from the client. 
                byte[] bytes = new byte[256];
                NetworkStream stream = tcpClient.GetStream();
                stream.Read(bytes, 0, bytes.Length);
                SocketHelper helper = new SocketHelper();
                helper.processMsg(tcpClient, stream, bytes);
            }
        }

    }
}

@apomene Accordign to MSDN "A SqlDependency object can be associated with a SqlCommand in order to detect when query results differ from those originally retrieved." I don't believe this is what I need to do, but could be wrong. I need to know when an OUTSIDE application modifies the db. It won't be modified within the application I am writing. — Volearix
– Volearix, Commented Sep 4, 2014 at 12:31
Outside source will send data to the db, my program needs to detect data is sent, do operations, then update the data sent. — Volearix
– Volearix, Commented Sep 4, 2014 at 12:32

simon at rcl · Accepted Answer · 2014-09-04 12:42:49Z

2

Check out SQL Server Notifications. This will send a signal to your app when some underlying dataset changes. I'm not sure how heavy this is for the server, so if you have a large number of clients waiting for a notification you should load-test it carefully....

answered Sep 4, 2014 at 12:42

simon at rcl

7,3691 gold badge21 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Detect SQL event with C# application

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related