QuickOPC.NET Product Version 5.1 Application Note 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: // SimpleLogToSql: Logs OPC Data Access item changes into an SQL database, using a subscription. // The database creation script is in the QuickOPCExamples.sql file. The examples that the database is already created. using System; using System.Data; using System.Data.SqlClient; using OpcLabs.EasyOpc.DataAccess; namespace SimpleLogToSql { class Program { 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"]; CODE Consulting and Development, s.r.o. , Bolevecka naves 27, 323 00 Plzen, Czech Republic e-mail: [email protected], Web: www.opclabs.com, tel. +420 603 214 412, fax +420 378 600 795 QuickOPC.NET-7-How to log OPC Data Access item changes into an SQL database.docx; 2013-11-21 Page 1 of 5 QuickOPC.NET Product Version 5.1 Application Note Console.WriteLine("Logging for 30 seconds..."); // Subscribe to an OPC item, using an anonymous method to process the notifications. int handle = EasyDAClient.DefaultInstance.SubscribeItem( "", "OPCLabs.KitServer.2", "Simulation.Incrementing (1 s)", 100, (_, eventArgs) => { 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. table.Rows.Clear(); DataRow row = table.NewRow(); row["ItemID"] = eventArgs.ItemDescriptor.ItemId; row["Value"] = eventArgs.Vtq.Value; row["Timestamp"] = eventArgs.Vtq.Timestamp; row["Quality"] = (short)eventArgs.Vtq.Quality; 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.DefaultInstance.UnsubscribeItem(handle); } Console.WriteLine("Finished."); CODE Consulting and Development, s.r.o. , Bolevecka naves 27, 323 00 Plzen, Czech Republic e-mail: [email protected], Web: www.opclabs.com, tel. +420 603 214 412, fax +420 378 600 795 QuickOPC.NET-7-How to log OPC Data Access item changes into an SQL database.docx; 2013-11-21 Page 2 of 5 QuickOPC.NET Product Version 5.1 Application Note } } } 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 CODE Consulting and Development, s.r.o. , Bolevecka naves 27, 323 00 Plzen, Czech Republic e-mail: [email protected], Web: www.opclabs.com, tel. +420 603 214 412, fax +420 378 600 795 QuickOPC.NET-7-How to log OPC Data Access item changes into an SQL database.docx; 2013-11-21 Page 3 of 5 QuickOPC.NET Product Version 5.1 Application Note And (part of) the resulting data may look like this: This example will be included with the product starting with build 5.12.1305.1. 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. CODE Consulting and Development, s.r.o. , Bolevecka naves 27, 323 00 Plzen, Czech Republic e-mail: [email protected], Web: www.opclabs.com, tel. +420 603 214 412, fax +420 378 600 795 QuickOPC.NET-7-How to log OPC Data Access item changes into an SQL database.docx; 2013-11-21 Page 4 of 5 QuickOPC.NET Product Version 5.1 Application Note 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. As of build 5.12.1309.1, we are 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. CODE Consulting and Development, s.r.o. , Bolevecka naves 27, 323 00 Plzen, Czech Republic e-mail: [email protected], Web: www.opclabs.com, tel. +420 603 214 412, fax +420 378 600 795 QuickOPC.NET-7-How to log OPC Data Access item changes into an SQL database.docx; 2013-11-21 Page 5 of 5
© Copyright 2024