Difference between revisions of "QuickOPC.NET: How to log OPC Data Access item changes into a SQL Server database"

From OPC Labs Knowledge Base
Jump to navigation Jump to search
 
(12 intermediate revisions by the same user not shown)
Line 1: Line 1:
[[Category:.NET]]
+
[[Category:.NET]] [[Category:C-sharp]] [[Category:Console application]] [[Category:Example]] [[Category:How to]] [[Category:Logging]] [[Category:OPC DA]] [[Category:SQL]] [[Category:SQL Server]] [[Category:Visual Studio]]
[[Category:OPC DA]]
 
 
 
 
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]].
 
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:
+
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 [http://opclabs.doc-that.com/files/onlinedocs/QuickOpc/Latest/User%27s%20Guide%20and%20Reference-QuickOPC/webframe.html#OpcLabs.EasyOpcClassic~OpcLabs.EasyOpc.DataAccess.EasyDAClient.html EasyDAClient] object:
  
 
         <span style='color:#800000; font-weight:bold; '>static</span> <span style='color:#800000; font-weight:bold; '>void</span> Main<span style='color:#808030; '>(</span><span style='color:#808030; '>)</span>
 
         <span style='color:#800000; font-weight:bold; '>static</span> <span style='color:#800000; font-weight:bold; '>void</span> Main<span style='color:#808030; '>(</span><span style='color:#808030; '>)</span>
Line 88: Line 86:
 
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.
 
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.
+
[[:Category:OPC A&E|OPC Alarms and Events]] notifications can be logged using the same approach, with '''EasyAEClient''' component.
 
 
OPC Alarms and Events notifications can be logged using the same approach, with EasyAEClient component.
 
  
  

Latest revision as of 18:25, 8 February 2018

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:

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.

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.