Professional OPC
Development Tools

logos

Online Forums

Technical support is provided through Support Forums below. Anybody can view them; you need to Register/Login to our site (see links in upper right corner) in order to Post questions. You do not have to be a licensed user of our product.

Please read Rules for forum posts before reporting your issue or asking a question. OPC Labs team is actively monitoring the forums, and replies as soon as possible. Various technical information can also be found in our Knowledge Base. For your convenience, we have also assembled a Frequently Asked Questions page.

Do not use the Contact page for technical issues.

Log multiple item changes into an SQL database using OPCDA.

More
29 May 2012 20:31 #874 by jeancg
Hello.

Great job, tested and working perfect. This will certainly help me a lot. had many doubts before this help, now things will become clear in my mind.

I will do more tests using LogAsStringToSql liked this scheme, my tables will be legible and easy understanding.

Thank you very much.

Best regards.

Please Log in or Create an account to join the conversation.

More
29 May 2012 13:09 #873 by support
Hello.
Here is an example that logs all values as strings:

// LogAsStringToSql: Logs OPC Data Access item changes into an SQL database, using a subscription. Values of all data types are
// stored in a single NVARCHAR column.

// The database creation script is in the ExamplesNet\MSSQL\QuickOPCExamples.sql file under the product installation
// directory. The example assumes that the database is already created.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using OpcLabs.EasyOpc.DataAccess;

namespace LogAsStringToSql
{
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 LogAsString", connection);
var dataSet = new DataSet();
adapter.FillSchema(dataSet, SchemaType.Source, "LogAsString");
adapter.InsertCommand = new SqlCommandBuilder(adapter).GetInsertCommand();
DataTable table = dataSet.Tables["LogAsString"];

Console.WriteLine("Logging for 30 seconds...");
// Subscribe to an OPC item, using an anonymous method to process the notifications.
int[] handles = EasyDAClient.DefaultInstance.SubscribeMultipleItems(
new[]
{
new DAItemGroupArguments("", "OPCLabs.KitServer.2", "Simulation.Incrementing (1 s)", 100, null),
new DAItemGroupArguments("", "OPCLabs.KitServer.2", "Simulation.Ramp (10 s)", 1000, null),
new DAItemGroupArguments("", "OPCLabs.KitServer.2", "Simulation.Register_BSTR", 1000, null),
new DAItemGroupArguments("", "OPCLabs.KitServer.2", "Simulation.Register_BOOL", 1000, null)
},
(_, 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; // The DataRow will make the conversion to a string.
row["Timestamp"] = (eventArgs.Vtq.Timestamp < (DateTime) SqlDateTime.MinValue)
? (DateTime)SqlDateTime.MinValue
: eventArgs.Vtq.Timestamp;
row["Quality"] = (short)eventArgs.Vtq.Quality;
table.Rows.Add(row);

// Update the underlying DataSet using an insert command.
adapter.Update(dataSet, "LogAsString");
}
}
);
System.Threading.Thread.Sleep(30*1000);
Console.WriteLine();

Console.WriteLine("Shutting down...");
EasyDAClient.DefaultInstance.UnsubscribeMultipleItems(handles);
}

Console.WriteLine("Finished.");
}
}
}


And here is an example that logs values of different types into their respective columns:

// LogAsUnionToSql: Logs OPC Data Access item changes into an SQL database, using a subscription. Values of different data types
// are stored in separate columns.

// The database creation script is in the ExamplesNet\MSSQL\QuickOPCExamples.sql file under the product installation
// directory. The example assumes that the database is already created.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using OpcLabs.EasyOpc.DataAccess;

namespace LogAsUnionToSql
{
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 LogAsUnion", connection);
var dataSet = new DataSet();
adapter.FillSchema(dataSet, SchemaType.Source, "LogAsUnion");
adapter.InsertCommand = new SqlCommandBuilder(adapter).GetInsertCommand();
DataTable table = dataSet.Tables["LogAsUnion"];

Console.WriteLine("Logging for 30 seconds...");
// Subscribe to an OPC item, using an anonymous method to process the notifications.
int[] handles = EasyDAClient.DefaultInstance.SubscribeMultipleItems(
new[]
{
new DAItemGroupArguments("", "OPCLabs.KitServer.2", "Simulation.Incrementing (1 s)", 100, null),
new DAItemGroupArguments("", "OPCLabs.KitServer.2", "Simulation.Ramp (10 s)", 1000, null),
new DAItemGroupArguments("", "OPCLabs.KitServer.2", "Simulation.Register_BSTR", 1000, null),
new DAItemGroupArguments("", "OPCLabs.KitServer.2", "Simulation.Register_BOOL", 1000, null)
},
(_, 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;

if (eventArgs.Vtq.Value != null)
{
Type type = eventArgs.Vtq.Value.GetType();
// Store into a corresponding column.
// The DataRow will make the conversion to a string.
if (type == typeof(Int16) || (type == typeof(Int32)) || type == typeof(Int64))
row["IntegerValue"] = eventArgs.Vtq.Value;
else if (type == typeof(Single) || type == typeof(Double))
row["FloatValue"] = eventArgs.Vtq.Value;
else if (type == typeof(string))
row["StringValue"] = eventArgs.Vtq.Value;
else if (type == typeof(Boolean))
row["BooleanValue"] = eventArgs.Vtq.Value;
}

row["Timestamp"] = (eventArgs.Vtq.Timestamp < (DateTime) SqlDateTime.MinValue)
? (DateTime)SqlDateTime.MinValue
: eventArgs.Vtq.Timestamp;
row["Quality"] = (short)eventArgs.Vtq.Quality;
table.Rows.Add(row);

// Update the underlying DataSet using an insert command.
adapter.Update(dataSet, "LogAsUnion");
}
}
);
System.Threading.Thread.Sleep(30*1000);
Console.WriteLine();

Console.WriteLine("Shutting down...");
EasyDAClient.DefaultInstance.UnsubscribeMultipleItems(handles);
}

Console.WriteLine("Finished.");
}
}
}


The table structures look differently for each example; here is the database schema with tables for all 3 types of logging:

-- QuickOPCExamples.sql: Creates a database with tables needed to run QuickOPC examples.

CREATE DATABASE QuickOPCExamples;
GO

USE QuickOPCExamples;
GO

-- The "SimpleLog" table stores values of all data types in a single SQL_VARIANT column.
CREATE TABLE SimpleLog(
ItemID nvarchar(50) NULL,
Value sql_variant NULL,
[Timestamp] datetime NULL,
Quality int NULL
);
GO

-- The "LogAsString" table stores values of all data types in a single NVARCHAR column.
CREATE TABLE LogAsString(
ItemID nvarchar(50) NULL,
Value nvarchar(100) NULL,
[Timestamp] datetime NULL,
Quality int NULL
);
GO

-- The "LogAsUnion" table stores values of different data types in separate columns.
CREATE TABLE LogAsUnion(
ItemID nvarchar(50) NULL,
IntegerValue bigint NULL,
FloatValue float NULL,
StringValue nvarchar(max) NULL,
BooleanValue bit NULL,
[Timestamp] datetime NULL,
Quality int NULL
);
GO


I hope this helps. These examples will be included with upcoming builds of the product, too.
Best regards

Please Log in or Create an account to join the conversation.

More
28 May 2012 06:29 #870 by support
Hello,
I will provide examples to the two remaining methods; just please give me some days before this gets done and posted.
It is easier to store data as variants (the existing example) or as strings. Storing them into columns by type needs more code. But the simplicity of the code is not the only criteria. For example, storing into columns by type gives better type checking and is resilient to problems such as different number formats with different regional (country/culture) settings - which may become an issue with storing everything as strings. I actually see the SQL_VARIANT as the best compromise between code size and safety, and that's also why it has been included as the primary example.
Best regards

Please Log in or Create an account to join the conversation.

More
25 May 2012 13:01 #866 by jeancg
Hello.

Very nice what you are suggesting me. The two methods are very interesting and been appropriate for my requirements.
I would be grateful if possible to show how to use the second method, and most thankful to show both methods.

I am starting a project to a datalogger supervisory system, this will be a great feature and add significant value to my system, I would like an opinion which of these two methods would be easier to implement?

Best regards and thank you again.

Please Log in or Create an account to join the conversation.

More
25 May 2012 06:44 #865 by admin
Hello,
there are several ways this can be done. For example,

You can use multiple columns, each with appropriate type, and possibly an additional column to store information about the data type itself (which value column is actually used). The code would then dynamically look at the data type, and use the proper column.
You can use a string data type such as NVARCHAR for the value column, and store all data types converted to strings.
You can use Microsoft SQL's data type SQL_VARIANT for the value column. This data type allows storage of many types of data; see msdn.microsoft.com/en-us/libra....

The SimpleLogToSql example actually assumes that the method 3) is used, and refers to a file (QuickOPCExamples.sql) with the table definition. However it appears that this file might actually not be installed with the product (I will make sure that it gets fixed), so here is how it is supposed to look like:
-- QuickOPCExamples.sql: 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

If you prefer to use method 1) or 2), let me know and I will prepare an example for that as well.
Best regards

Please Log in or Create an account to join the conversation.

More
24 May 2012 12:37 #864 by jeancg
Hi

I'm doing some tests on QuickOPC-Classic 5.1 (. NET) and I am very pleased with the possibilities and functionality of QuickOPC, I am interested in purchasing this version (. NET), but need to do some more tests. I am need of an example that can show me how to write some items within an SQL database.

Considering that I can have different data types, I'm using in my project types (Real), (Int), (Boolean).

I'm trying to follow the example (SimpleLogToSql) that comes with QuickOPC-Classic.NET, but I can not visualize a way to persist these different data types.

Any suggestions or example of how I do it?
Thank you so much.

Please Log in or Create an account to join the conversation.

Moderators: support
Time to create page: 0.122 seconds