Difference between revisions of "How to log OPC Unified Architecture data changes into a SQL Server database"
Jump to navigation
Jump to search
(Created page with "Category:.NET Category:C-sharp Category:Console applicationCategory:Logging Category:OPC UA Category:SQL Category:Visual Studio See also: QuickOP...") |
|||
(4 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | [[Category:.NET]] [[Category:C-sharp]] [[Category:Console application]][[Category:Logging]] [[Category:OPC UA]] [[Category:SQL]] [[Category:Visual Studio]] | + | [[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: | + | 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: | 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. | |
− | |||
− |
Latest revision as of 18:21, 8 February 2018
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:
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.");
}
The database creation script is here:
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
And (part of) the resulting data may look like this:
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.