7 Replies Latest reply on Feb 24, 2016 1:24 PM by pthivierge

    PiPoinList Summaries to dataset/datatable using c#

    NEERAJ

      Hi,

      I am able to fetch PiPoinList Summaries but i need to convert the result to save into dataset,I am using c#,is there any function which does this directly.

       

      var vals = ptlist.Summaries(timerange, span, AFSummaryTypes.All, AFCalculationBasis.TimeWeighted, 0, config).ToList();

       

      I need vals to be pushed on datatable or dataset.

       

      Thanks!

        • Re: PiPoinList Summaries to dataset/datatable using c#
          gregor

          Hello Neeraj,

           

          I am not 100% sure if e.g. ADO.Net offers the ability to create a recordset, dataset or datatable from any kind of object but I am pretty certain there's no functionality for this in AF SDK. I am afraid, you will have to iterate through the Summary call result set.

          • Re: PiPoinList Summaries to dataset/datatable using c#
            Marcos Vainer Loeff

            Hello Neeraj,

             

            Yeah, Gregor is right. PI AF SDK does not provide this functionality. Nevertheless, it is not difficult to implement it by writing an extension method. Please refer to the example below:

             

                        IEnumerable<IDictionary<AFSummaryTypes, AFValues>> ptListSummaries = ptList.Summaries(timeRange, new AFTimeSpan(hours: 1), AFSummaryTypes.All, AFCalculationBasis.TimeWeighted, AFTimestampCalculation.Auto, config).ToList();
                        DataTable dt = ptListSummaries.GetDataTable();
            

             

            In order to create the extension method GetDataTable(), we need to know that the type of ptListSummaries is IEnumerable<IDictionary<AFSummaryTypes, AFValues>> as var is too generic. Then, we will create an static class called AFSDKExt with the GetDataTable() static method as shown below:

             

                public static class AFSDKExt
                {
                    public static DataTable GetDataTable(this IEnumerable<IDictionary<AFSummaryTypes, AFValues>> ptListSummaries)
                    {
                        DataTable dt = new DataTable();
                        dt.Clear();
                        dt.Columns.Add("PIPointName");
                        dt.Columns.Add("SummaryType");
                        dt.Columns.Add("Value");
                        dt.Columns.Add("TimeStamp");
            
            
                        foreach (IDictionary<AFSummaryTypes, AFValues> ptSummaries in ptListSummaries)
                        {
                            foreach (KeyValuePair<AFSummaryTypes, AFValues> ptSummary in ptSummaries)
                            {
                                AFSummaryTypes summaryTypes = ptSummary.Key;
                                AFValues values = ptSummary.Value;
                                foreach (AFValue val in values)
                                {
                                    DataRow row = dt.NewRow();
                                    row["PIPointName"] = val.PIPoint.Name;
                                    row["SummaryType"] = summaryTypes.ToString();
                                    row["Value"] = val.Value.ToString();
                                    row["TimeStamp"] = val.Timestamp.LocalTime.ToString();
                                    dt.Rows.Add(row);
                                    //Console.WriteLine("{0} {1} {2} {3}", val.PIPoint.Name, summaryTypes.ToString(), val.Value.ToString(), val.Timestamp.LocalTime.ToString());
                                }
            
            
            
            
                            }
                        }
                        return dt;
                    }
                }
            

             

             

            This worked on my environment but please try on yours and let us know if it works properly. Feel free to add or remove columns and rows from the datatable.

             

            Hope it helps!

            3 of 3 people found this helpful
              • Re: PiPoinList Summaries to dataset/datatable using c#
                Rhys Kirk

                Marcos Vainer Loeff beat me to it...but I did it differently by transposing the summary types. Anyway, the OP has multiple code samples to use!

                 

                            PIPointList pointList = new PIPointList(PIPoint.FindPIPoints(pi, "sinu*"));
                            var vals = pointList.Summaries(new AFTimeRange("*-1h", "*"), new AFTimeSpan(0, 0, 0, 0, 15, 0, 0), OSIsoft.AF.Data.AFSummaryTypes.All, OSIsoft.AF.Data.AFCalculationBasis.TimeWeighted, OSIsoft.AF.Data.AFTimestampCalculation.Auto, new PIPagingConfiguration(PIPageType.TagCount, 10000));
                
                            DataTable dt = new DataTable("Summaries");
                            dt.Columns.Add("PI Point");
                            dt.Columns.Add("Timestamp");
                            foreach(string sumName in Enum.GetNames(typeof(AFSummaryTypes)))
                            {
                                if (!(sumName.ToLower().StartsWith("all") || sumName.ToLower().StartsWith("none")))
                                {
                                    dt.Columns.Add(sumName);
                                    dt.Columns[sumName].AllowDBNull = true;
                                }
                            }
                            dt.PrimaryKey = new DataColumn[] {dt.Columns["PI Point"], dt.Columns["Timestamp"]};
                
                            foreach (IDictionary<AFSummaryTypes, AFValues> val in vals)
                            {
                                foreach (string sumName in Enum.GetNames(typeof(AFSummaryTypes)))
                                {
                                    if (!(sumName.ToLower().StartsWith("all") || sumName.ToLower().StartsWith("none")))
                                    {
                                        AFValues sumVals = val[(AFSummaryTypes)Enum.Parse(typeof(AFSummaryTypes), sumName)];
                                        foreach (AFValue sumVal in sumVals)
                                        {
                                            string pntName = sumVal.PIPoint.Name;
                                            if (dt.Rows.Contains(new object[] { pntName, sumVal.Timestamp }))
                                                dt.Select(String.Format("[PI Point]='{0}' AND [Timestamp]=#{1}#", pntName, sumVal.Timestamp)).FirstOrDefault()[sumName] = sumVal.Value.ToString();
                                            else
                                                dt.Rows.Add(pntName, sumVal.Timestamp)[sumName] = sumVal.Value.ToString();
                                        }
                                    }
                                }
                            }
                
                3 of 3 people found this helpful