QuickOPC.NET: How to log OPC Data Access item changes into a SQL Server database

From OPC Labs Knowledge Base
Revision as of 14:45, 31 July 2016 by User (talk | contribs)
Jump to navigation Jump to search

It is often required that a status of certain process variable is logged into a SQL database. Following example shows how to do that with QuickOPC:

       static void Main()
       {
           const string connectionString = 
               "Data Source=(local);Initial Catalog=QuickOPCExamples;Integrated Security=true";
               
           Console.WriteLine("Starting up...");
           using (var connection = new SqlConnection(connectionString))
           {
               connection.Open();

               // Create all necessary ADO.NET objects.
               var adapter = new SqlDataAdapter("SELECT * FROM SimpleLog", connection);
               var dataSet = new DataSet();
               adapter.FillSchema(dataSet, SchemaType.Source, "SimpleLog");
               adapter.InsertCommand = new SqlCommandBuilder(adapter).GetInsertCommand();
               DataTable table = dataSet.Tables["SimpleLog"];
               Debug.Assert(table != null);

               Console.WriteLine("Logging for 30 seconds...");
               // Subscribe to an OPC item, using an anonymous method to process the notifications.
               int handle = EasyDAClient.SharedInstance.SubscribeItem(
                   "", 
                   "OPCLabs.KitServer.2",
                   "Simulation.Incrementing (1 s)", 
                   100,
                   (_, eventArgs) =>
                       {
                           Debug.Assert(eventArgs != null);
                           Console.Write(".");

                           // In this example, we only log valid data. Production logger would also log errors.
                           if (eventArgs.Vtq != null)
                           {
                               // Fill a DataRow with the OPC data, and add it to a DataTable.
                               Debug.Assert(table.Rows != null);
                               table.Rows.Clear();
                               DataRow row = table.NewRow();
                               row["ItemID"] = eventArgs.Arguments.ItemDescriptor.ItemId;
                               row["Value"] = eventArgs.Vtq.Value;
                               row["Timestamp"] = (eventArgs.Vtq.Timestamp < (DateTime)SqlDateTime.MinValue)
                                                      ? (DateTime)SqlDateTime.MinValue
                                                      : eventArgs.Vtq.Timestamp;
                               row["Quality"] = (short)eventArgs.Vtq.Quality;

                               Debug.Assert(table.Rows != null);
                               table.Rows.Add(row);

                               // Update the underlying DataSet using an insert command.
                               adapter.Update(dataSet, "SimpleLog");
                           }
                       }
                   );
               System.Threading.Thread.Sleep(30*1000);
               Console.WriteLine();

               Console.WriteLine("Shutting down...");
               EasyDAClient.SharedInstance.UnsubscribeItem(handle);
           }

           Console.WriteLine("Finished.");
       }

The database creation script is here:

-- Creates a database with tables needed to run QuickOPC examples.
CREATE DATABASE QuickOPCExamples;
GO
USE QuickOPCExamples;
GO
CREATE TABLE SimpleLog (
 ItemID nvarchar(50) NULL,
 Value sql_variant NULL,
 [Timestamp] datetime NULL,
 Quality int NULL
);
GO

And (part of) the resulting data may look like this:

Howtologopcdataaccessitemchangesintoansqldatabase.png

This example is included with the product. Please use the example from the product itself for the most up-to-date code of the example.

General information about integration of QuickOPC and Microsoft SQL Server is here.

OPC Alarms and Events notifications can be logged using the same approach, with EasyAEClient component.


Update: The original example (SimpleLogToSql) stores values of all data types in a single SQL_VARIANT column. We are now including two additional examples of SQL logging with the product:

  • LogAsString: Values of all data types are stored in a single NVARCHAR column.
  • LogAsUnion: Values of all data types are stored in separate columns.