4 Replies Latest reply on Jun 22, 2017 9:03 PM by Chris_Hawkins

    Working with Tables using the PI AF SDK

    Chris_Hawkins

      Could someone point me to any sample code / examples of how to create a table in AF and then write to / read from this Table via C# or VB.NET.

       

      Thanks

       

      Chris

        • Re: Working with Tables using the PI AF SDK
          Rick Davin

          Hi Chris,

           

          I'll see what is over on GitHub.  If nothing is there, I will make a short example for you.

           

          For reading and writing data, it's important to use the AFTable.Table property, which returns a System.Data.DataTable.  This means you would use standard Microsoft System.Data calls to read or write data.

          • Re: Working with Tables using the PI AF SDK
            Rick Davin

            You will need to add OSIsoft.AF.Asset and System.Data to your usings:

             

            private static void TableExample(AFDatabase database)
            {
                var aftable = CreateTable(database);
                PopulateTable(aftable);
            }
            
            private static AFTable CreateTable(AFDatabase database)
            {
                // use System.Data calls
                var sourceTable = new DataTable();
                sourceTable.Columns.Add("Engineer", typeof(string));
                sourceTable.Columns.Add("Power", typeof(int));
            
                // use AF SDK calls
                var aftable = new AFTable("Magical Powers");
                aftable.Table = sourceTable;
                database.Tables.Add(aftable);
            
                if (database.IsDirty)
                    database.CheckIn();
            
                return aftable;
            }
            
            private static void PopulateTable(AFTable aftable)
            {
                var sourceTable = aftable.Table;
                var row = sourceTable.NewRow();
            
                // By column name
                row["Engineer"] = "Chris";
                row["Power"] = 100;
                sourceTable.Rows.Add(row);
            
                // By column index
                row = sourceTable.NewRow();
                row[0] = "Rick";
                row[1] = 200;
                sourceTable.Rows.Add(row);
            
                // mixture
                row = sourceTable.NewRow();
                row[0] = "Anna";
                row["Power"] = 300;
                sourceTable.Rows.Add(row);
            
                // Or as params object[] and skip row variable
                sourceTable.Rows.Add("Paul", 400);
                sourceTable.Rows.Add("Marcos", 500);
                sourceTable.Rows.Add("Gregor", 600);
            
                if (aftable.IsDirty)
                    aftable.CheckIn();
            }
            

             

            Creates this:

             

            2017-06-22 13_58_49-__RDAVIN7250_Pipeschd Tables - PI System Explorer.png

             

            And this bit of code:

             

            private static void ReadTableExample(AFTable aftable)
            {
                var sourceTable = aftable.Table;
                foreach (DataRow row in sourceTable.Rows)
                {
                    Console.WriteLine("Name: {0}, Power: {1}", row[0], row[1]);
                }
            }
            

             

            Produces:

             

            Name: Chris, Power: 100

            Name: Rick, Power: 200

            Name: Anna, Power: 300

            Name: Paul, Power: 400

            Name: Marcos, Power: 500

            Name: Gregor, Power: 600

             

            You can refer to a column by index, name, or column object in memory but that's all Microsoft System.Data and nothing to do with AFTable.