C# 如何从 Proficy Historian 查询原始数据？

Question

提问by Michael Haren

How can I retrieve raw time-series data from a Proficy Historian/iHistorian?

如何从 Proficy Historian/iHistorian 中检索原始时间序列数据？

Ideally, I would ask for data for a particular tag between two dates.

理想情况下，我会要求两个日期之间的特定标签的数据。

Answer 1

采纳答案by Brian Gideon

There are several different sampling modes you can experiment with.

您可以尝试多种不同的采样模式。

Raw
Interpolated
Lab
Trend
Calculated

生的
内插
实验室
趋势
计算

These modes are available using all of the following APIs.

这些模式可使用以下所有 API 使用。

User API (ihuapi.dll)
SDK (ihsdk.dll)
OLEDB (iholedb.dll)
Client Acess API (Proficy.Historian.ClientAccess.API)

用户 API (ihuapi.dll)
SDK (ihsdk.dll)
OLEDB (iholedb.dll)
客户端访问 API (Proficy.Historian.ClientAccess.API)

Of these the trend sampling mode is probably what you want since it is specifically designed for charting/trending. Though, lab and interpolated may be useful as well.

其中趋势采样模式可能是您想要的，因为它是专门为图表/趋势设计的。不过，lab 和 interpolated 也可能有用。

Read the electronic book for more information on each sampling mode. On my machine it is stored as C:\Program Files\GE Fanuc\Proficy Historian\Docs\iHistorian.chmand I have version 3.5 installed. Pay particular attention to the following sections.

有关每种采样模式的更多信息，请阅读电子书。在我的机器上它被存储为C:\Program Files\GE Fanuc\Proficy Historian\Docs\iHistorian.chm并且我安装了 3.5 版。请特别注意以下部分。

Using the Historian OLE DB Provider
Advanced Topics | Retrieval

使用 Historian OLE DB 提供程序
高级主题 | 恢复

Here is how you can construct an OLEDB to do trend sampling.

以下是构建 OLEDB 以进行趋势采样的方法。

set 
    SamplingMode = 'Trend',
    StartTime = '2010-07-01 00:00:00',
    EndTime = '2010-07-02 00:00:00',
    IntervalMilliseconds = 1h
select 
    timestamp, 
    value, 
    quality 
from 
    ihRawData 
where 
    tagname = 'YOUR_TAG'

Showing the equivalent methods using the User API and the SDK are complex (more so with the User API) since they require a lot of plumbing in the code to get setup. The Client Access API is newer and uses WCF behind the scenes.

使用 User API 和 SDK 显示等效方法很复杂（使用 User API 更复杂），因为它们需要在代码中进行大量管道才能进行设置。客户端访问 API 较新，并在幕后使用 WCF。

By the way, there are a few limitations with the OLEDB method though.

顺便说一下，虽然 OLEDB 方法有一些限制。

Despite what the documentation says I have neverbeen able to get native query parameters to work. That is a showstopper if you want to use it with SQL Server Reporting Services for example.
You cannot write samples into the archive or in any way make changes to the Historian configuration including adding/changing tags, writing messages, etc.
It can be a little slow in some cases.
It has no provision for crosstabbing multiple tagnames into the columns and then carrying forward samples so that a value exists for each timestamp and tag combination. The trend sampling mode gets you halfway there, but still does not crosstab and does not actually load raw samples. Then again the User API and SDK cannot do this either.

尽管文件说什么，我都从来没有能够得到本地查询参数的工作。例如，如果您想将它与 SQL Server Reporting Services 一起使用，那就太棒了。
您不能将样本写入存档或以任何方式更改 Historian 配置，包括添加/更改标签、写入消息等。
在某些情况下，它可能会有点慢。
它没有规定将多个标记名交叉表到列中，然后传送样本，以便为每个时间戳和标记组合存在一个值。趋势采样模式让您完成了一半，但仍然没有交叉表，也没有实际加载原始样本。然后用户 API 和 SDK 也不能这样做。

Answer 2

回答by Michael Haren

A coworker of mine put this together:

我的一个同事把这个放在一起：

In web.config:

在 web.config 中：

<add name="HistorianConnectionString" 
     providerName="ihOLEDB.iHistorian.1" 
     connectionString="
       Provider=ihOLEDB.iHistorian;
       User Id=;
       Password=;
       Data Source=localhost;"
/>

In the data layer:

在数据层：

public DataTable GetProficyData(string tagName, DateTime startDate, DateTime endDate)
{
    using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
    {
        cn.ConnectionString = webConfig.ConnectionStrings.ConnectionStrings["HistorianConnectionString"];
        cn.Open();

        string queryString = string.Format(
                "set samplingmode = rawbytime\n select value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' and value > 0 order by timestamp",
                tagName.Replace("'", "\""), startDate, endDate);

        System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        DataSet ds = new DataSet();

        adp.Fill(ds);
        return ds.Tables[0];
    }
}

Update:

更新：

This worked well but we ran into an issue with tags that don't update very often. If the tag didn't update near the start or end of the requested startDate and endDate, the trends would look bad. Worse, still were cases where there were no explicit points during the window requested--we'd get no data back.

这很有效，但我们遇到了不经常更新的标签问题。如果标记没有在请求的 startDate 和 endDate 的开始或结束附近更新，则趋势看起来会很糟糕。更糟糕的是，仍然存在在请求的窗口期间没有明确点的情况——我们不会得到任何数据。

I resolved this by making three queries:

我通过提出三个查询解决了这个问题：

The previous value beforethe start-date
The points between startDate and endDate
The next value afterthe endDate

开始日期之前的前一个值
startDate 和 endDate 之间的点
endDate之后的下一个值

This is a potentially inefficient way to do it but It Works:

这是一种可能效率低下的方法，但它有效：

public DataTable GetProficyData(string tagName, DateTime startDate, DateTime endDate)
{
    DataSet ds = new DataSet();
    string queryString;
    System.Data.OleDb.OleDbDataAdapter adp;

    using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection())
    {
        cn.ConnectionString = proficyConn.ConnectionString;
        cn.Open();

        // always get a start value
        queryString = string.Format(
             "set samplingmode = lab\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
            tagName.Replace("'", "\""), startDate.AddMinutes(-1), startDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        // get the range
        queryString = string.Format(
             "set samplingmode = rawbytime\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
            tagName.Replace("'", "\""), startDate, endDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        // always get an end value
        queryString = string.Format(
             "set samplingmode = lab\nselect value as theValue,Timestamp from ihrawdata where tagname = '{0}' AND timestamp between '{1}' and '{2}' order by timestamp",
        tagName.Replace("'", "\""), endDate.AddMinutes(-1), endDate);
        adp = new System.Data.OleDb.OleDbDataAdapter(queryString, cn);
        adp.Fill(ds);

        return ds.Tables[0];
    }
}

And yes, I know, those queries should be parameterized.

是的，我知道，这些查询应该被参数化。

Answer 3

回答by reallyJim

Michael--in IP21 there is an "Interpolated" table, as well as the "actual" data point table. Does Proficy have that as well?

Michael——在 IP21 中有一个“插值”表，以及“实际”数据点表。Proficy也有吗？

Answer 4

回答by rolls

We wrote a wrapper DLL that looked like this like this:

我们写了一个像这样的包装 DLL：

[DllImport("IHUAPI.dll", CallingConvention = CallingConvention.StdCall, EntryPoint = "ihuReadRawDataByTime@24")]
public static extern int ihuReadRawDataByTime(int serverhandle, string tagname, ref IHU_TIMESTAMP startTime, ref IHU_TIMESTAMP endTime, ref int noOfSamples, ref IHU_DATA_SAMPLE* dataValues);
...
private int _handle;

public HistorianTypes.ErrorCode ReadRawByTime(string tagName, DateTime startTime, DateTime endTime,
                                              out double[] timeStamps, out double[] values, out IhuComment [] comments)
{
    var startTimeStruct = new IhuApi.IHU_TIMESTAMP();  //Custom datetime to epoch extension method
    var endTimeStruct = new IhuApi.IHU_TIMESTAMP();

    int lRet = 0;
    int noOfSamples = 0;
    startTimeStruct = DateTimeToTimeStruct(dstZone.ToUniversalTime(startTime));
    endTimeStruct = DateTimeToTimeStruct(dstZone.ToUniversalTime(endTime));
    IhuApi.IHU_DATA_SAMPLE* dataSample = (IhuApi.IHU_DATA_SAMPLE*)new IntPtr(0);

    try {
        lRet = IhuApi.ihuReadRawDataByTime
            (
                _handle, // the handle returned from the connect
                tagName, // the single tagname to retrieve
                ref startTimeStruct, // start time for query
                ref endTimeStruct, // end time for query
                ref noOfSamples, // will be set by API
                ref dataSample // will be allocated and populated in the user API
            );
            ....

Some notes are that iFIX will check if the DLL is loaded on startup so you need to do things like dynamically load/unload the DLL so that other applications don't crash. We did this by deleting/adding registry keys on the fly.

一些注意事项是 iFIX 将检查 DLL 是否在启动时加载，因此您需要执行诸如动态加载/卸载 DLL 之类的操作，以免其他应用程序崩溃。我们通过动态删除/添加注册表项来做到这一点。

Another one is if you poll 10,000 samples and 1 of the samples are corrupted it will drop all 10,000 samples. You need to implement a bad data handler that will start at either side of the bad data and increment in steps to get all data either side of the bad sample.

另一种情况是，如果您轮询 10,000 个样本并且其中 1 个样本已损坏，它将丢弃所有 10,000 个样本。您需要实现一个错误数据处理程序，该处理程序将从错误数据的任一侧开始并逐步递增以获取错误样本两侧的所有数据。

There are several C header files that contain all of the error codes and the function header for the DLL.

有几个 C 头文件包含所有错误代码和 DLL 的函数头文件。

C# 如何从 Proficy Historian 查询原始数据？

提问by Michael Haren

采纳答案by Brian Gideon

回答by Michael Haren

回答by reallyJim

回答by rolls

相关推荐

最近更新

标签

C# 如何从 Proficy Historian 查询原始数据？

提问by Michael Haren

采纳答案by Brian Gideon

回答by Michael Haren

回答by reallyJim

回答by rolls

相关推荐

如何从 C# 中的泛型方法返回 NULL？

使用不带 TO 的 BCC 在 C# 中通过 SMTP 发送邮件

C# 您如何仅找到同时具有 getter 和 setter 的属性？

C# 如何获取实现给定接口的所有加载类型的所有实例？

相关推荐

最近更新

标签