mikeloria

Visual Studio Example for PI OLEDB Enterprise

Discussion created by mikeloria on Feb 21, 2012
Latest reply on Feb 27, 2012 by spilon

I did not really see anything in the manual or on this forum. I am using PISQLCommander to make some really awesome queries and I now need to run these queries programmically using C#. can anyone point me in the right direction or provide a small amount of code snippets as to which libraries need to be added, how to set up the connection, and how to execute the query? I am using the latest and greatest OLEDB 2010 R3 and running against a collective 2010 R3 servers. Here is how I normally have queired PI with the classic OLEDB provider but I need to know what I need to do for enterprise....   Thank you in advance

 

 

 
//this is my code snippet for oledb provider. Is there a better way? how is oledb enterprise different? please provide a working example if possible. Thanks again

using System.Data.OleDb;

private string connString = "Provider=PIOLEDB; Data Source=GLW-PI; UserId=piadmin; Password=;";

        private OleDbCommand cmd;
        private OleDbConnection cnn;

        public PIDA()
        {
            cnn = new OleDbConnection(connString);
            cmd = new OleDbCommand();
            cmd.Connection = cnn;

            try
            {
                cnn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
 
        }

        string lineSpeedSql = string.Format("SELECT tag, time, value, status "
                + "FROM piarchive..pisnapshot"
                + " where tag = '{0}'" ,lineSpeedPiTag);
 
         // for testing.... Console.WriteLine(lineSpeedSql);
         try
         {
                if (cnn.State != System.Data.ConnectionState.Open)
                {
                    cnn.Open();
                }

                DateTime current = DateTime.Now;
                //get the line status (running or not)
                cmd.CommandText = lineStatusSql;
                OleDbDataReader rdr = cmd.ExecuteReader();
                rdr.Read();

                try
                {
                    oGlwRtData.LineStatusTime = rdr.GetDateTime(1);
                }
                catch
                {
                    Console.WriteLine("Failed to correctly read/process line status time for {0}", oGlwRtData.UnitID);
                    Console.WriteLine("Setting line status time to curent for {0}", oGlwRtData.UnitID);
                    oGlwRtData.LineStatusTime = current;
                }

 

 

 

 

Note: Here is query I wish to run:

 
SELECT eh.Path + eh.Name Element, eh.ElementVersionID, eh.ElementVersionEffective, tc.*
FROM [GLW-AF-1].[Asset].[vElementHierarchy] eh
CROSS APPLY [GLW-AF-1].[DataT].[vTransposeSnapshot_Plant StatusTemplate](eh.ElementVersionID) tc
WHERE eh.Path = N'\XXX\YYY\00 CMN Plant Monitor\'
OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

Outcomes