Extracting PI System Data to file using PI SQL Client OLEDB via PI SQL DAS RTQP in .NET

Blog Post created by mfoerster Employee on Feb 13, 2019

In this post I will be leveraging OSISoft's PI SQL Client OLEDB to extract PI System Data via the PI SQL Data Access Server (RTQP).

To keep things simple and easy to reproduce, this post will focus how to extract data with this technology.



PI SQL Client OLEDB 2018

PI Data Archive 2018

PI AF Server 2018

PI SQL Data Access Server (RTQP Engine) 2018

Microsoft Visual Studio 2017


For simplicity a test AF database "testDB" with a single element "Element1" which has a single attribute "Attribute1" was created.

This attribute references the PIPoint "Sinusoid".



SQL Query used to extract 7 days of events from \\<AFServer>\testDB\Element1|Attribute1


SELECT av.Value, av.TimeStamp, av.IsValueGood
FROM [Master].[Element].[Archive] av
INNER JOIN [Master].[Element].[Attribute] ea ON av.AttributeID = ea.ID
WHERE ea.Element = 'Element1' AND ea.Name = 'Attribute1'
AND av.TimeStamp BETWEEN N't-7d' AND N't'


Example C# Code:


using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Data_Access_PI_SQL_Client_OLEDB
class Program
static void Main(string[] args)
DataTable dataTable = new DataTable();
using (var connection = new OleDbConnection())
using (var command = connection.CreateCommand())
connection.ConnectionString = "Provider=PISQLClient; Data Source=<AFServer>\\<AF_DB>; Integrated Security=SSPI;";

string SQL_query = "SELECT av.Value, av.TimeStamp, av.IsValueGood ";
SQL_query += "FROM [Master].[Element].[Archive] av ";
SQL_query += "INNER JOIN [Master].[Element].[Attribute] ea ON av.AttributeID = ea.ID ";
SQL_query += "WHERE ea.Element = 'Element1' AND ea.Name = 'Attribute1' ";
SQL_query += "AND av.TimeStamp BETWEEN N't-7d' AND N't' ";

command.CommandText = SQL_query;
var reader = command.ExecuteReader();
using (StreamWriter writer = new StreamWriter("<outputfilepath>"))
while (reader.Read())
writer.WriteLine("Timestamp: {0}, Value : {1}, isGood : {2}",
reader["Timestamp"], reader["Value"], reader["IsValueGood"]);

Console.WriteLine("Completed Successfully!");




Events were successfully written to flat file: