It can be easy to upload channel values into a database. This FAQ covers how to send data from an ED device to an SQL database using Brainboxes’ free API and a little knowledge of C# programming language.

One way to get the most from your ED remote IO device is to record the data it generates. Using a database enables you to store data over time and report trends. Being able to analyse trends in data enables insight and for manufacturing this is invaluable for steering improvements such as predictive maintenance.

We have seen customers able to make considerable cost and time savings after examining just one week of production data. Once the data has been stored it can then be used in other applications, and even with machine learning tools or AI. The applications and possibilities are increasingly sophisticated, but to start with we just need to send information from our ED device to our database.

ED devices can be wired to a wide range of sensors or other machine signals. Digital devices register changes between two states, high/low, one/zero and analogue devices can register any value between 4-20mAmp/0-10Volt to 3 decimal places. Your ED device will come with software to help you configure your IO lines and set the values you want to use:

A database will log these values against a time stamp. Your favourite data visualization program or even Excel can be used
to help make these values into something more meaningful. To do this we need to write a script that sends information from the ED device to the database. We have written a worked through example below.

• This example was created in Visual Studio
• It will work with Windows operating systems
• Use with any Brainboxes Remote IO Module (E.g. ED-549, ED-588)
• You will need some basic C# programming knowledge
• Download the free Brainboxes.IO API

First make sure you have opened Visual Studio, created a new C# Console Project, and added the Brainboxes.IO API via NuGet: https://www.nuget.org/packages/Brainboxes.IO/

Follow each of these steps to write our C# application:

1. Connect to your ED. Tell the application to keep a connection from the device to the database open until the
   application is closed.
2. Set up a timer with an interval (e.g. 10 seconds). This tells the application how often to send information to the database. Consider how much detail you need and how quickly you want to know about a change. Digital devices can sample hundreds of times but analogue devices can make 8 samples per second.
3. Create your connection; every X seconds of this timer, open a connection to the database
4. Read each of the current analogue or digital input values, and add it to the database

using Brainboxes.IO;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

namespace BrainboxesSQLUpload
{
    public class RemoteIOtoDatabase
    {
        static void Main(string[] args)
        {
            // For setting your ED IP address
            string ipAddress = "192.168.127.254";
            // For setting how often you'd like to record data in seconds
            TimeSpan recordingInterval = TimeSpan.FromSeconds(10);
            Console.WriteLine("Brainboxes data logger");
            Console.WriteLine($"Will Connect to ED Device @ {ipAddress} and record io line values every {recordingInterval}");

            // [1]
            // First connect to your ED using the EDDevice.Create
            // function, and passing your units IP address
            Console.WriteLine($"Connecting to IP address '{ipAddress}'...");
            using (EDDevice ed = EDDevice.Create(ipAddress))
            {
                Console.WriteLine($"Connected to {ed}");
                // Give your ED device a label for easy debugging
                ed.Label = "Sensor Reading";

                // [2]
                // Create an interval timer which runs the
                // logToDB method every recordingInterval
                // This also passes the EDDevice we created, so we can use it
                Timer intervalTimer = new Timer(e => logToDB(ed),
                                                null,
                                                TimeSpan.Zero,
                                                recordingInterval);
                // This will continuously run unless a key is entered into the console window
                Console.ReadKey();
                intervalTimer.Change(Timeout.Infinite, Timeout.Infinite);
                Console.WriteLine("Disconnecting from ED...");
            }
            Console.WriteLine("Disconnected");
            Console.WriteLine("Press any key to end...");
            Console.ReadKey();
        }

        private static void logToDB(EDDevice ed)
        {
            string yourConnectionString = "Data Source=EXAMPLEDATABASE;Initial Catalog=DATABASE;Integrated Security=True";
            Console.WriteLine($"{DateTime.Now} Attempting SQLConnection to database...");

            // [3]
            // Create your SQLConnection to your Database
            using (SqlConnection dbConnect = new SqlConnection(yourConnectionString))
            {
                dbConnect.Open();
                Console.WriteLine("SQLConnection has been opened.");
                // 'cmd' will be used for executing SQL commands, passing data into the database
                SqlCommand cmd = new SqlCommand()
                {
                    Connection = dbConnect,
                    CommandType = CommandType.Text
                };

                // [4]
                // For every IO Line, we will pass the line information to the database
                foreach (IOLine l in ed.IOLines)
                {
                    // Get the line value, if its an analog line then get its analog value
                    //                     if its a digital line then get its digital value
                    string lineValue = l.IOType == IOType.Analog ? l.AValue.ToString() : l.Value.ToString();
                    Console.Write($"Channel: {l.IONumber} = {lineValue}");
                    // This is the SQLQuery command used for sending your line data to the Database
                    // We are recording the date in UTC(GMT). This may differ from local time.
                    cmd.CommandText = $"INSERT INTO log (timestamp, ioline, value) VALUES ('{DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fff")}',  {l.IONumber}, {lineValue} )";
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("   Data upload success.");
                }
                // After we're finished, close the connection.
                dbConnect.Close();
            }
            Console.WriteLine("SQLConnection has been closed.");
        }
    }
}

If you have any questions about this code please get in touch with Brainboxes technical support: [email protected]

The information that’s written to the database might not look like much at first. But with a little software, whether integration into a proprietary program, ERP system or a customised dashboard, built using APIs, the data can start to give insight:

Here are a few examples of what you could do with the kinds of data harvested from common sensors and existing equipment. Track uptime across different shifts to inform OEE metrics: