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

See also: QuickOPC.NET: How to log OPC Alarms and Events notifications into an XML file, QuickOPC.NET: How to log OPC Data Access item changes into an XML file.

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, using the EasyDAClient object:

       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:

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.

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.