Writing data from the windows service to SQL Server 2012

Question

Is it possible to take data from the RFID reader . Which starts with Windows Service and then write the data directly to the sql server 2012 ?

If this is possible how can I do that. Write code in the service or the Sql Server?

Thank you in advance

Answer 1

Let's first talk about structure. In the OnStart method of your service, I would suggest creating a Thread object that manages the database interaction. The OnStop method will need a way to signal this thread to stop running when the service stops. To do this, I use a ManualResetEvent. Here's the basic structure.

using System.ServiceProcess;
using System.Threading;

public partial class Service1 : ServiceBase
{
    private ManualResetEvent _shutdownEvent;
    private Thread           _thread;

    public Service1()
    {
        InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
        /* This WaitHandle will allow us to shutdown the thread when
           the OnStop method is called. */
        _shutdownEvent = new ManualResetEvent(false);

        /* Create the thread.  Note that it will do its work in the
           appropriately named DoWork method below. */
        _thread = new Thread(DoWork);

        /* Start the thread. */
        _thread.Start();
    }

    protected override void OnStop()
    {
        /* Set the WaitHandle, which signals the thread to stop. */
        _shutdownEvent.Set();

        /* Wait for the thread to exit. */
        _thread.Join();
    }

    /* This is the method that will be invoked when the thread is started
       in the OnStart method. */
    private void DoWork()
    {
        /* To keep the thread running, use a while loop, checking the
           status of the ManualResetEvent each time through the loop.
           When the OnStop method is called, the ManualResetEvent will be
           triggered, thereby exiting the loop.  The thread will end as
           soon as this method exits. */
        while (!_shutdownEvent.WaitOne(0))
        {
            /* Sleep for one second.  We'll modify this later, but for
               now, it will keep the processor from being used at 100%. */
            Thread.Sleep(1000);
        }
    }
}

Now let's focus on the DoWork method. I'm going to assume that you already have your SQL Server Express 2012 database created, and that the required tables are already in existence. There are ways to do both of these things programmatically, but I'm not going to worry about that here for the sake of simplicity. Also, I'm going to use syntax used for a SQL Server Express LocalDB 2014 instance. This is what I'm using, but it should be very close (if not identical) to SQL Server Express 2012 syntax. The connection string might need to change, and you can look at ConnectionStrings to find examples.

The first thing you'll want to do is connect to your database. Then, inside the while loop, you'll want to add data to the database. Since I don't know exactly how your RFID data collection works, that'll be something you have to do, but this should give you a general idea.

using System.Data.SqlClient;

private void DoWork()
{
    /* Define the connection string. */
    string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=master;Integrated Security=True";

    /* Define the command text used for inserting the data into the
       database.  Note that this will be very specific to your database
       schema.  I'm assuming you have a table named 'RFID' with a single
       column named 'Data' of type INTEGER.  */
    string commandText = @"INSERT INTO RFID (Data) VALUES (@data)";

    /* Create the connection object.  The using statement will ensure that
       the object is disposed when we exit. */
    using (var connection = new SqlConnection(connectionString))
    {
        /* Open the connection. */
        connection.Open();

        /* Enter the while loop to keep the thread running. */
        int count = 0;
        while (!_shutdownEvent.WaitOne(0))
        {
            /* TODO:  Someway, somehow, you'll have to acquire the RFID
               data.  In this example, I'll just increment a counter. */
            int data = count++;

            /* Create the command for inserting the data into the
               database. */
            using (var command = new SqlCommand(commandText, connection))
            {
                /* Add the parameter. */
                command.Parameters.AddWithValue("@data", data);

                /* Execute the command. */
                command.ExecuteNonQuery();
            }
        }
    }
}

Please note that this is a very rough example. You'll probably want to bullet-proof the DoWork method by catching and logging any exceptions that may occur. Obviously, how you acquire the RFID data and how you insert it into your database will up to your specific requirements.

HTH