Implementing an Event Handler C#

Question

A c# application is writting on a MS SQL DB table at several times. the records of this table must be read and procceessed by another c# application.

At the moment I have implemented I Threading timer which looks (every 2 secs) if the table has rows and proccess the data:

System.Threading.Timer checkTimer;

checkTimer = new System.Threading.Timer(Callback, null, 2000, 500);

private void InitSQLCon()
{
    Con = new SqlConnection(ConectionString);
    Con.Open();
}        

private void Callback(Object state)
{
    string queryString = "Select [Wi_Customer ID],[Wi_License_plate_ID] from " + Properties.Settings.Default.Table1 + " where Wi_Car_Rfid='" + s + "'";
    SqlCommand command = new SqlCommand(queryString, Con);

    SqlDataReader reader = command.ExecuteReader();
    if (reader.HasRows)
    {
      ///...make neccessary operations
    }       
}

My problem is that the current implementation isn't effecient. Checking the table with a timer is resource consuming. I would like to do it in an event driven way. Ideally I would like to implement and event handler raised by the add record to table1 event. If this possible (since I have never implemented an event handler) I would appreciate any feedbacks on how this can be done.

possible duplicate of Is there anything in C# that can be used as database Trigger — Adriano Repetti
– Adriano Repetti, Commented Feb 20, 2014 at 9:12
Also - why are you creating new SqlConnection every time instead of closing and reopening it again? — Tarec
– Tarec, Commented Feb 20, 2014 at 9:13
Who's inserting the record? Your application or another 3rd party? — BatteryBackupUnit
– BatteryBackupUnit, Commented Feb 20, 2014 at 9:14
@Tarec well in this case IMO it's not bad, process is long time running and it's used few milliseconds every two seconds... — Adriano Repetti
– Adriano Repetti, Commented Feb 20, 2014 at 9:14
@BatteryBackupUnit He wrote that he has 2 applications - 1 that writes to DB and 1 that reads from it, which code is presented. — Tarec
– Tarec, Commented Feb 20, 2014 at 9:18

Marc Gravell · Accepted Answer · 2014-02-20 09:27:17Z

There are some change tracking features in SQL Server, most notable exposed via SqlDependency - however, frankly I think you'd do better to look at a separate notification mechanism. For example, I'm a big fan of redis pub/sub, because it is ridiculously simple to set up (heck, a dedicated pub-sub server doesn't even need persistence, so the "bgsave"/"fork" issue that makes redis tricky on windows doesn't apply, so you could just use the redis-server available on nuget). Then you just have your worker subscribe to a named channel, and you have the other parts of the system broadcast a message to that named channel when they add work. Simple and efficient. For robustness, you'd also want to periodically poll manually - but probably on a much slower poll - maybe every 30 seconds or something.

Here's an example of pub/sub using redis via BookSleeve (you will also need a redis-server running on the local machine):

using System;
using System.Text;
using BookSleeve;

static class Program
{
    static void Main()
    {
        // IMPORTANT: the "pub" and "sub" can be on entirely separate machines,
        // as long as they are talking to the same server. They are only shown
        // together here for convenience
        using (var sub = new RedisSubscriberConnection("localhost"))
        using (var pub = new RedisConnection("localhost"))
        {
            sub.Open();
            pub.Open();

            sub.Subscribe("busytime", (queue,payload) =>
            {
                // you don't actually need the payload, probably 
                var received = Encoding.UTF8.GetString(payload);
                Console.WriteLine("Work to do! Look busy!: " + received);
            });

            string line;
            Console.WriteLine("Enter messages to send, or q to quit");
            while((line = Console.ReadLine()) != null && line != "q")
            {
                pub.Publish("busytime", line);
            }
        }
    }
}

Collectives™ on Stack Overflow

Implementing an Event Handler C#

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related