Difference between revisions of "QuickOPC.NET: How to log OPC Data Access item changes into a SQL Server database"
(16 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
+ | [[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]]. | ||
− | 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:#800080; '>{</span> | ||
+ | <span style='color:#800000; font-weight:bold; '>const</span> <span style='color:#800000; font-weight:bold; '>string</span> connectionString <span style='color:#808030; '>=</span> | ||
+ | <span style='color:#800000; '>"</span><span style='color:#0000e6; '>Data Source=(local);Initial Catalog=QuickOPCExamples;Integrated Security=true</span><span style='color:#800000; '>"</span><span style='color:#800080; '>;</span> | ||
+ | |||
+ | Console<span style='color:#808030; '>.</span>WriteLine<span style='color:#808030; '>(</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>Starting up...</span><span style='color:#800000; '>"</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | <span style='color:#800000; font-weight:bold; '>using</span> <span style='color:#808030; '>(</span><span style='color:#800000; font-weight:bold; '>var</span> connection <span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>new</span> SqlConnection<span style='color:#808030; '>(</span>connectionString<span style='color:#808030; '>)</span><span style='color:#808030; '>)</span> | ||
+ | <span style='color:#800080; '>{</span> | ||
+ | connection<span style='color:#808030; '>.</span>Open<span style='color:#808030; '>(</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | |||
+ | <span style='color:#696969; '>// Create all necessary ADO.NET objects.</span> | ||
+ | <span style='color:#800000; font-weight:bold; '>var</span> adapter <span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>new</span> SqlDataAdapter<span style='color:#808030; '>(</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>SELECT * FROM SimpleLog</span><span style='color:#800000; '>"</span><span style='color:#808030; '>,</span> connection<span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | <span style='color:#800000; font-weight:bold; '>var</span> dataSet <span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>new</span> DataSet<span style='color:#808030; '>(</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | adapter<span style='color:#808030; '>.</span>FillSchema<span style='color:#808030; '>(</span>dataSet<span style='color:#808030; '>,</span> SchemaType<span style='color:#808030; '>.</span>Source<span style='color:#808030; '>,</span> <span style='color:#800000; '>"</span><span style='color:#0000e6; '>SimpleLog</span><span style='color:#800000; '>"</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | adapter<span style='color:#808030; '>.</span>InsertCommand <span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>new</span> SqlCommandBuilder<span style='color:#808030; '>(</span>adapter<span style='color:#808030; '>)</span><span style='color:#808030; '>.</span>GetInsertCommand<span style='color:#808030; '>(</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | DataTable table <span style='color:#808030; '>=</span> dataSet<span style='color:#808030; '>.</span>Tables<span style='color:#808030; '>[</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>SimpleLog</span><span style='color:#800000; '>"</span><span style='color:#808030; '>]</span><span style='color:#800080; '>;</span> | ||
+ | Debug<span style='color:#808030; '>.</span>Assert<span style='color:#808030; '>(</span>table <span style='color:#808030; '>!</span><span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>null</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | |||
+ | Console<span style='color:#808030; '>.</span>WriteLine<span style='color:#808030; '>(</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>Logging for 30 seconds...</span><span style='color:#800000; '>"</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | <span style='color:#696969; '>// Subscribe to an OPC item, using an anonymous method to process the notifications.</span> | ||
+ | <span style='color:#800000; font-weight:bold; '>int</span> handle <span style='color:#808030; '>=</span> EasyDAClient<span style='color:#808030; '>.</span>SharedInstance<span style='color:#808030; '>.</span>SubscribeItem<span style='color:#808030; '>(</span> | ||
+ | <span style='color:#800000; '>"</span><span style='color:#800000; '>"</span><span style='color:#808030; '>,</span> | ||
+ | <span style='color:#800000; '>"</span><span style='color:#0000e6; '>OPCLabs.KitServer.2</span><span style='color:#800000; '>"</span><span style='color:#808030; '>,</span> | ||
+ | <span style='color:#800000; '>"</span><span style='color:#0000e6; '>Simulation.Incrementing (1 s)</span><span style='color:#800000; '>"</span><span style='color:#808030; '>,</span> | ||
+ | <span style='color:#008c00; '>100</span><span style='color:#808030; '>,</span> | ||
+ | <span style='color:#808030; '>(</span>_<span style='color:#808030; '>,</span> eventArgs<span style='color:#808030; '>)</span> <span style='color:#808030; '>=</span><span style='color:#808030; '>></span> | ||
+ | <span style='color:#800080; '>{</span> | ||
+ | Debug<span style='color:#808030; '>.</span>Assert<span style='color:#808030; '>(</span>eventArgs <span style='color:#808030; '>!</span><span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>null</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | Console<span style='color:#808030; '>.</span>Write<span style='color:#808030; '>(</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>.</span><span style='color:#800000; '>"</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | |||
+ | <span style='color:#696969; '>// In this example, we only log valid data. Production logger would also log errors.</span> | ||
+ | <span style='color:#800000; font-weight:bold; '>if</span> <span style='color:#808030; '>(</span>eventArgs<span style='color:#808030; '>.</span>Vtq <span style='color:#808030; '>!</span><span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>null</span><span style='color:#808030; '>)</span> | ||
+ | <span style='color:#800080; '>{</span> | ||
+ | <span style='color:#696969; '>// Fill a DataRow with the OPC data, and add it to a DataTable.</span> | ||
+ | Debug<span style='color:#808030; '>.</span>Assert<span style='color:#808030; '>(</span>table<span style='color:#808030; '>.</span>Rows <span style='color:#808030; '>!</span><span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>null</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | table<span style='color:#808030; '>.</span>Rows<span style='color:#808030; '>.</span>Clear<span style='color:#808030; '>(</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | DataRow row <span style='color:#808030; '>=</span> table<span style='color:#808030; '>.</span>NewRow<span style='color:#808030; '>(</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | row<span style='color:#808030; '>[</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>ItemID</span><span style='color:#800000; '>"</span><span style='color:#808030; '>]</span> <span style='color:#808030; '>=</span> eventArgs<span style='color:#808030; '>.</span>Arguments<span style='color:#808030; '>.</span>ItemDescriptor<span style='color:#808030; '>.</span>ItemId<span style='color:#800080; '>;</span> | ||
+ | row<span style='color:#808030; '>[</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>Value</span><span style='color:#800000; '>"</span><span style='color:#808030; '>]</span> <span style='color:#808030; '>=</span> eventArgs<span style='color:#808030; '>.</span>Vtq<span style='color:#808030; '>.</span>Value<span style='color:#800080; '>;</span> | ||
+ | row<span style='color:#808030; '>[</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>Timestamp</span><span style='color:#800000; '>"</span><span style='color:#808030; '>]</span> <span style='color:#808030; '>=</span> <span style='color:#808030; '>(</span>eventArgs<span style='color:#808030; '>.</span>Vtq<span style='color:#808030; '>.</span>Timestamp <span style='color:#808030; '><</span> <span style='color:#808030; '>(</span>DateTime<span style='color:#808030; '>)</span>SqlDateTime<span style='color:#808030; '>.</span>MinValue<span style='color:#808030; '>)</span> | ||
+ | ? <span style='color:#808030; '>(</span>DateTime<span style='color:#808030; '>)</span>SqlDateTime<span style='color:#808030; '>.</span>MinValue | ||
+ | <span style='color:#808030; '>:</span> eventArgs<span style='color:#808030; '>.</span>Vtq<span style='color:#808030; '>.</span>Timestamp<span style='color:#800080; '>;</span> | ||
+ | row<span style='color:#808030; '>[</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>Quality</span><span style='color:#800000; '>"</span><span style='color:#808030; '>]</span> <span style='color:#808030; '>=</span> <span style='color:#808030; '>(</span><span style='color:#800000; font-weight:bold; '>short</span><span style='color:#808030; '>)</span>eventArgs<span style='color:#808030; '>.</span>Vtq<span style='color:#808030; '>.</span>Quality<span style='color:#800080; '>;</span> | ||
+ | |||
+ | Debug<span style='color:#808030; '>.</span>Assert<span style='color:#808030; '>(</span>table<span style='color:#808030; '>.</span>Rows <span style='color:#808030; '>!</span><span style='color:#808030; '>=</span> <span style='color:#800000; font-weight:bold; '>null</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | table<span style='color:#808030; '>.</span>Rows<span style='color:#808030; '>.</span>Add<span style='color:#808030; '>(</span>row<span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | |||
+ | <span style='color:#696969; '>// Update the underlying DataSet using an insert command.</span> | ||
+ | adapter<span style='color:#808030; '>.</span>Update<span style='color:#808030; '>(</span>dataSet<span style='color:#808030; '>,</span> <span style='color:#800000; '>"</span><span style='color:#0000e6; '>SimpleLog</span><span style='color:#800000; '>"</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | <span style='color:#800080; '>}</span> | ||
+ | <span style='color:#800080; '>}</span> | ||
+ | <span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | System<span style='color:#808030; '>.</span>Threading<span style='color:#808030; '>.</span>Thread<span style='color:#808030; '>.</span>Sleep<span style='color:#808030; '>(</span><span style='color:#008c00; '>30</span><span style='color:#808030; '>*</span><span style='color:#008c00; '>1000</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | Console<span style='color:#808030; '>.</span>WriteLine<span style='color:#808030; '>(</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | |||
+ | Console<span style='color:#808030; '>.</span>WriteLine<span style='color:#808030; '>(</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>Shutting down...</span><span style='color:#800000; '>"</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | EasyDAClient<span style='color:#808030; '>.</span>SharedInstance<span style='color:#808030; '>.</span>UnsubscribeItem<span style='color:#808030; '>(</span>handle<span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | <span style='color:#800080; '>}</span> | ||
+ | |||
+ | Console<span style='color:#808030; '>.</span>WriteLine<span style='color:#808030; '>(</span><span style='color:#800000; '>"</span><span style='color:#0000e6; '>Finished.</span><span style='color:#800000; '>"</span><span style='color:#808030; '>)</span><span style='color:#800080; '>;</span> | ||
+ | <span style='color:#800080; '>}</span> | ||
The database creation script is here: | The database creation script is here: | ||
Line 22: | 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. | ||
− | + | [[: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:
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.