Marcos Vainer Loeff

Developing an CLR assembly with PI SDK

Blog Post created by Marcos Vainer Loeff Employee on May 12, 2014

On this blog post, I will share with you my finding about how to create an CLR assembly stored it in the  SQL Server database to communicate with your PI Data Archive with PI SDK. But I want to warn you that creating a new project with PI SDK is not a recommended solution since it is based on an old technology. On the other hand, PI AF SDK which is  the recomemnded technology cannot be used on an CLR assembly as it is shown next.

Background

There are two major building blocks in Microsoft platform: DLL and exe. One is useful to support execution and the other is executed on an individual level. Basically SQL Server also provides the facility to create assembly files whose methods could be accessed from a SQL Server assembly. Here an assembly (DLL) file is created, stored in the database and accessed via a Stored Procedure.

 

I am currently using Visual Studio 2013 on a client machine and SQL Server 2012 SP1 x64 on a Server machine. PI SDK 1.4.2 x64 is installed on both machines.

Creating the Class Library

Open Visual Studio and create a SQL Server Database Project and shown on Figure 1.

 

 

 

2330.fig1.jpg

 

Figure 1 – Creating a new SQL Server Database project.

 

 

 

On Solution Explorer, right click on the project and choose Add-->New Item… Click first on “SQL CLR C#” on the left pane and on the option “SQL CLR C# User Defined Function” on the right pane.

 

 

 

5852.fig2.jpg

 

Figure 2 – Creating a new SQL CLR C# User Defined Function.

 

Add the 64-bit assemblies of OSIsoft.PISDK and OSIsoft.PISDKCommon. Make  sure they are located on the 64 bit PIPC folder (C:\Program Files\PIPC  by default). Check the properties from both files. Make sure to change the option called “Copy Local” to “True” as shown on the Figure 2.

 

 

 

3107.fig3.jpg

 

Figure 3 – Properties of PI SDK references.

 

 

 

Copy the content below and paste it on the file PISDK_CLR_StoredProcedure.cs.

 

 

 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using PISDK;


public partial class StoredProcedures
{

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void InsertSnapshotValuePISDK(SqlString piServerName, SqlString piPointName, SqlString snapshotValue)
    {
        Server myPIServer = null;

        try
        {
            PISDK.PISDK sdkroot = new PISDK.PISDK();
            myPIServer = sdkroot.Servers[piServerName.ToString()];
            myPIServer.Open();
            PIPoint myPIPoint = myPIServer.PIPoints[piPointName.ToString()];
            myPIPoint.Data.UpdateValue(snapshotValue.ToString(), DateTime.Now);
        }

        catch (Exception ex)
        {
            SqlContext.Pipe.Send("An error occured" + ex.Message + ex.StackTrace);
        }

        finally
        {
            if (myPIServer != null)
            {
                myPIServer.Close();
            }
        }
    }
}

 

 

Build the project and open the Debug folder. Make sure that there are four files with DLL extension (3 of them related to PI SDK).

 

 

 

6082.fig4.jpg

 

Figure 4 – Files created on the debug folder after building the solution

 

 

 

Copy all the DLL files to the SQL Server machine and paste it where you find it appropriate. In my case, I have pasted them on the folder C:\Program Files\PIPC\CustomLibraries\CLR_Assemblies\PISDK_CLR_Sample.

Create the assembly in SQL Server with a specified path

 

 

Open SQL Server Management Studio (SSMS) and create a new database on the SQL Server to stored your assemblies. In my case, I have created and named the new database SampleCLRs.

 

Make sure the CLR is enabled by executing the following code snippet:

 

 

 
sp_configure 'clr enabled', 1;
GO

 

 

Since PI SDK access external resources, you need to give access to your SQL Database to store this type of library. The following SQL query might be used to achieve this goal:

 

 

 
ALTER DATABASE SampleCLRs SET TRUSTWORTHY ON;

 

 

After following these steps, you can register the PISDK_CLR_StoredProcedure.dll:

 

Create the assembly in SQL Server with a specified path:

 

 

 
Create Assembly PISDK_CLR
From 'C:\Program Files\PIPC\CustomLibraries\CLR_Assemblies\PISDK_CLR_Sample\PISDK_CLR_StoredProcedure.dll'
WITH PERMISSION_SET = UNSAFE
GO

In order for the command above to work, it requires that the SysAdmin privilege to enable the SQL CLR, since it is an instance wide configuration change. Also, an assembly marked as “UNSAFE” will likely trigger a strong response from the DBA.

 

And also create the stored procedure:

 

 

 
CREATE PROCEDURE dbo.InsertValuesPISDK
(
 @piServerName nvarchar(50),
 @piPointNamee nvarchar(50),
 @snapshotValue nvarchar(50)
)
AS EXTERNAL NAME PISDK_CLR.StoredProcedures.InsertSnapshotValuePISDK;
GO

 

 

Finally, execute the procedure:

 

 

 
EXECUTE [dbo].[InsertValuesPISDK]
'piserver_name’,'pipoint_name','snapshot_value'
GO 

 

 

For this specific test, you will have to create a test PI Point whose point type is string.

Why not using PI AF SDK instead?

 

 

Since PI AF SDK is our most modern technology and considering the fact that this reference  is able to communicate with the PI Server directly, why is this post about PI SDK and not PI AF SDK instead?

 

The answer is simple: it is not possible to do a similar procedure with PI AF SDK. When you execute the CREATE ASSEMBLY command on the SQL Server, even if you have copied all the required .NET libraries to the custom assembly folder, you will ultimately receive the following error message:

 

"Msg 6544, Level 16, State 1, Line 1

 

CREATE ASSEMBLY for assembly 'System.ServiceModel' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub."

 

According to this article, since some of PI AF SDK referenced libraries are not pure .NET assemblies, this procedure can only be used with PI SDK.

Conclusions

 

 

Although a custom CLR assembly could be developed with PI SDK, since OSIsoft is developing its newer products on top of PI AF SDK, it is likely that this solution won't be used long-term. PI SDK misses out on RDA performance which comes with PI AF SDK starting on version 2012 (2.5).

 

PI OLEDB Enterprise should be used as the product to access the PI System from the SQL Server.

 

Nevertheless, if there are good reasons to develop a custom CLR assembly, I would try to develop it on top of PI Web API which wraps PI AF SDK. I am not sure if this would work, but definitely it would be my first option to try. In this case, you would have access to the PI AF Server and also to the PI Data Archive.  It would be possible to send values to the PI Server. Even if you upgrade PI Web API, you would not have issues with the assemblies. IMHO, the disadvantage would be low performance when compared to the PI OLEDB Enterprise but tests should be carried out before drawing conclusions.

 

I hope you enjoyed this blog post and stay tuned for the next ones!

Outcomes