View source for How to log OPC Unified Architecture data changes into a SQL Server database

[[Category:.NET]] [[Category:C-sharp]] [[Category:Console application]] [[Category:Example]] [[Category:How to]] [[Category:Logging]] [[Category:OPC UA]] [[Category:SQL]] [[Category:SQL Server]] [[Category:Visual Studio]]
See also: [[QuickOPC.NET: How to log OPC Data Access item changes into an SQL database]].

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:

<syntaxhighlight lang="c#">
        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 UASimpleLog", connection);
                var dataSet = new DataSet();
                adapter.FillSchema(dataSet, SchemaType.Source, "UASimpleLog");
                adapter.InsertCommand = new SqlCommandBuilder(adapter).GetInsertCommand();
                DataTable table = dataSet.Tables["UASimpleLog"];
                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 = EasyUAClient.SharedInstance.SubscribeDataChange(
                    "http://opcua.demo-this.com:51211/UA/SampleServer",   // or "opc.tcp://opcua.demo-this.com:51210/UA/SampleServer"
                    "nsu=http://test.org/UA/Data/;i=10853",
                    100,
                    (_, eventArgs) =>
                    {
                        Debug.Assert(eventArgs != null);
                        Console.Write(".");

                        // In this example, we only log valid data. Production logger would also log errors.
                        if (eventArgs.AttributeData != 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["NodeID"] = eventArgs.Arguments.NodeDescriptor.NodeId;
                            row["Value"] = eventArgs.AttributeData.Value;
                            row["SourceTimestamp"] = (eventArgs.AttributeData.SourceTimestamp < (DateTime)SqlDateTime.MinValue)
                                                   ? (DateTime)SqlDateTime.MinValue
                                                   : eventArgs.AttributeData.SourceTimestamp;
                            row["ServerTimestamp"] = (eventArgs.AttributeData.ServerTimestamp < (DateTime)SqlDateTime.MinValue)
                                                   ? (DateTime)SqlDateTime.MinValue
                                                   : eventArgs.AttributeData.ServerTimestamp;
                            row["StatusCode"] = eventArgs.AttributeData.StatusCode.InternalValue;

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

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

                Console.WriteLine("Shutting down...");
                EasyUAClient.SharedInstance.UnsubscribeMonitoredItem(handle);
            }

            Console.WriteLine("Finished.");
        }
</syntaxhighlight>

The database creation script is here:

<syntaxhighlight lang="tsql">
CREATE DATABASE QuickOPCExamples;
GO

USE QuickOPCExamples;
GO

-- The "UASimpleLog" table stores values of all data types in a single SQL_VARIANT column, for OPC Unified Architecture.
CREATE TABLE UASimpleLog(
	NodeID nvarchar(100) NULL,
	Value sql_variant NULL,
	SourceTimestamp datetime NULL,
	ServerTimestamp datetime NULL,
	StatusCode int NULL
);
GO
</syntaxhighlight>

And (part of) the resulting data may look like this:
[[File:howtologopcunifiedarchitecturedatachangesintoansqldatabase.png|center]] 

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