How to Create and Manipulate Tables Using OSIsoft AF SDK in C#

The OSIsoft AF SDK (Asset Framework Software Development Kit) is a powerful tool for developers working with the PI System. It allows for robust interaction with the PI Server, enabling the creation and manipulation of various AF objects programmatically. One such object is the AFTable, which can store tabular data within the AF Database.

In this blog post, we will explore how to:

• Connect to an AF Server and Database.
• Create a new AFTable.
• Add columns to the AFTable.
• Insert data into the AFTable.
• Save changes back to the AF Server.

Whether you're automating data management tasks or building custom applications, understanding how to work with AFTables using AF SDK is essential.

---

Prerequisites

Before we dive in, ensure you have the following:

• Development Environment: Visual Studio or any C# IDE.
• AF SDK Reference: Installed and added to your project references.
• Basic Knowledge of C#: Familiarity with classes, methods, and data types.
• Access to PI System: Credentials to connect to your AF Server and Database.

---

Step 1: Setting Up Your Project

First, create a new C# Console Application in Visual Studio:

1. Open Visual Studio.
2. Click on File > New > Project.
3. Select Console App (.NET Framework).
4. Name your project (e.g., AFTableExample).

Adding AF SDK Reference

To interact with the PI System, add the AF SDK reference:

1. Right-click on References in the Solution Explorer.
2. Choose Add Reference.
3. Navigate to Assemblies > Extensions.
4. Check OSIsoft.AFSDK.
5. Click OK.

---

Step 2: Connecting to the AF Server and Database

Let's start by connecting to your AF Server and Database.

using OSIsoft.AF;
using OSIsoft.AF.Asset;
using OSIsoft.AF.Table;
using System;

class Program
{
    static void Main()
    {
        // Connect to the AF Server
        PISystems piSystems = new PISystems();
        PISystem piSystem = piSystems["YourAFServer"];
        piSystem.Connect();

        // Connect to the AF Database
        AFDatabase afDatabase = piSystem.Databases["YourAFDatabase"];

        // Your code will go here

        // Disconnect from the AF Server
        piSystem.Disconnect();

        Console.WriteLine("Process completed successfully.");
    }
}

Explanation:

• PISystems: Represents a collection of PI System connections.
• PISystem: Represents a connection to a specific AF Server.
• AFDatabase: Represents a specific database within the AF Server.

Note: Replace "YourAFServer" and "YourAFDatabase" with the actual names.

---

Step 3: Creating a New AFTable

Now, let's create a new table within the AF Database.

// Create a new AFTable
AFTable afTable = afDatabase.Tables.Add("MyNewTable");

Explanation:

• afDatabase.Tables.Add("MyNewTable"): Adds a new table named "MyNewTable" to the database.

---

Step 4: Adding Columns to the AFTable

Define the structure of your table by adding columns with specific data types.

// Define columns for the table
afTable.Table.Columns.Add("ProductID", typeof(int));
afTable.Table.Columns.Add("ProductName", typeof(string));
afTable.Table.Columns.Add("Quantity", typeof(int));
afTable.Table.Columns.Add("LastUpdated", typeof(DateTime));

Explanation:

• afTable.Table.Columns.Add: Adds a new column to the table.
• "ColumnName": The name of the column.
• typeof(DataType): The data type for the column (e.g., int, string, DateTime).

---

Step 5: Inserting Data into the AFTable

With the table structure in place, we can now insert data.

// Create rows of data
object[] row1 = { 1, "Widget", 100, DateTime.Now };
object[] row2 = { 2, "Gadget", 50, DateTime.Now };
object[] row3 = { 3, "Doodad", 75, DateTime.Now };

// Add rows to the table
afTable.Table.Rows.Add(row1);
afTable.Table.Rows.Add(row2);
afTable.Table.Rows.Add(row3);

Explanation:

• object[] row: Represents a row of data matching the columns' data types.
• afTable.Table.Rows.Add(row): Inserts the row into the table.

Tip: Ensure the data types in the object[] match the columns' data types.

---

Step 6: Saving Changes to the AF Server

To persist the changes, you need to check in the changes to the AF Server.

// Save changes to the AF Database
afDatabase.CheckIn();

Explanation:

• afDatabase.CheckIn(): Commits all the changes made to the database objects.

---

Step 7: Full Code Example

Putting it all together, here's the complete code:

using OSIsoft.AF;
using OSIsoft.AF.Asset;
using OSIsoft.AF.Table;
using System;

class Program
{
    static void Main()
    {
        try
        {
            // Connect to the AF Server
            PISystems piSystems = new PISystems();
            PISystem piSystem = piSystems["YourAFServer"];
            piSystem.Connect();

            // Connect to the AF Database
            AFDatabase afDatabase = piSystem.Databases["YourAFDatabase"];

            // Create a new AFTable
            AFTable afTable = afDatabase.Tables.Add("MyNewTable");

            // Define columns for the table
            afTable.Table.Columns.Add("ProductID", typeof(int));
            afTable.Table.Columns.Add("ProductName", typeof(string));
            afTable.Table.Columns.Add("Quantity", typeof(int));
            afTable.Table.Columns.Add("LastUpdated", typeof(DateTime));

            // Create rows of data
            object[] row1 = { 1, "Widget", 100, DateTime.Now };
            object[] row2 = { 2, "Gadget", 50, DateTime.Now };
            object[] row3 = { 3, "Doodad", 75, DateTime.Now };

            // Add rows to the table
            afTable.Table.Rows.Add(row1);
            afTable.Table.Rows.Add(row2);
            afTable.Table.Rows.Add(row3);

            // Save changes to the AF Database
            afDatabase.CheckIn();

            // Disconnect from the AF Server
            piSystem.Disconnect();

            Console.WriteLine("Table created, columns added, and data inserted successfully.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("An error occurred: " + ex.Message);
        }
    }
}

Remember to replace:

• "YourAFServer" with the name of your AF Server.
• "YourAFDatabase" with the name of your AF Database.

---

Step 8: Running the Application

1. Build the solution to ensure there are no compilation errors.
2. Run the application.
3. Check your AF Database to see if the new table "MyNewTable" has been created with the specified columns and data.

---

Conclusion

In this blog post, we've demonstrated how to:

• Connect to an AF Server and Database using AF SDK.
• Create a new AFTable within the AF Database.
• Add columns with specific data types to the AFTable.
• Insert data into the AFTable.
• Save changes back to the AF Server.

By leveraging the power of AF SDK, you can programmatically manage your PI System data, automate tasks, and integrate with other systems. This approach enhances efficiency and opens up possibilities for advanced data handling within the PI System.

---

Additional Resources

• OSIsoft AF SDK Documentation: AF SDK Reference Guide
• Community Forums: PI Square
• Learning Courses: OSIsoft Learning

---

Author's Note: Always ensure you have the necessary permissions to modify the AF Database and that you are following your organization's best practices for interacting with the PI System.

Happy coding!