2 Replies Latest reply on May 22, 2016 6:36 AM by mhalhead

    Getting tags from SQL database through AF SDK

    prshinde

      Hi,

       

      I have a SQL database which will have values in table.

      First column is the wonderware instrument tag, 2nd column is the DateTime and 3rd column is for PI tag.

       

      I need to get first the wonderare instrument tag from SQL database through AF SDK and then it should search PI tag against wonderware instrument tag and write the PI tagg in the third column.

       

      How we can do it through AF SDK...?

       

      Appreciate your response...

       

       

      Thnaks,

       

      Prasad

        • Re: Getting tags from SQL database through AF SDK
          Marcos Vainer Loeff

          Hello Presad,

           

          Before we start, it is good to point out that you don’t necessary need to use PI AF SDK in order to communicate with a SQL Server. In some situations, you might want to just connect to the SQL Server directly using ADO.NET Data Provider or Entity Framework.

           

           

          If you want to connect to SQL Server though PI AF SDK, you should use AFTables and there are two options:

           

          • Link the table – which will save the connection string on the AF database and the client will use it in order to connect to the SQL Server. The advantage of this method is that you always get updated data since the data is not duplicated.
          • Import the table – this will copy the SQL table data to AF. The advantage is performance but the data is duplicated.

           

          There are two interesting videos about this topic on YouTube:

           

          https://www.youtube.com/watch?v=JnhodkkyHks

          https://www.youtube.com/watch?v=MmXmnac969s

           

          The videos show how to import and link AFTables using PI System Explorer. Below there is an example using PI AF SDK in C#:

           

          https://pisquare.osisoft.com/message/31160#comment-31160

           

          Please let us know if this help you get started!

          2 of 2 people found this helpful
          • Re: Getting tags from SQL database through AF SDK
            mhalhead

            Hi Prasad,

             

            If I understand correctly you are populating a SQL table with the PI Point names? Is this a once off exercise, something that will run often?

            To be honest while I've used the AF SDK quite a bit and like it I probably wouldn't both in this case (although it can be done). Have you considered using a tool like SQL Server Integration Services (SSIS) or even T-SQL with the PI OLE DB provider?

            If you have to use the AF SDK you would basically need to create a list of the PI Point and the InstrumentTag attribute and then use something like LINQ to get the intersection of the two lists.