QuickOPC.NET: How to log OPC Data Access item changes into a SQL Server database: Difference between revisions
mNo edit summary |
No edit summary |
||
(5 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
[[Category:.NET]] [[Category:C-sharp]] | [[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]] | ||
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 85: | Line 85: | ||
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. | ||
[[:Category:OPC A&E|OPC Alarms and Events]] notifications can be logged using the same approach, with '''EasyAEClient''' component. | [[:Category:OPC A&E|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:
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.