Skip navigation
All Places > PI Developers Club > Blog > 2008 > December
2008

Our customers and partners often express interest in creating their own web parts that can connect to RtWebParts. If you’re interested, the first thing that you should know is that RtWebParts are based on the SharePoint web part base class, and not the ASP.NET web part base class. When RtWebParts was first introduced, the ASP.NET web part base class did not yet exist. We have not yet migrated to the newer ASP.NET web part base class, primarily because we’re fond of some of the features that are only available from the SharePoint web part base class, most notably the support for client-side connections. Though it is more difficult to find documentation and examples, you are more likely to successfully create web parts that connect to RtWebParts if you too use the SharePoint web part base class.

 

Speaking of client-side connections, you might have noticed that in the past RtWebParts supported identical data via two different connection interfaces: IRowProvider, and IParametersOutProvider. One reason for this was to open the possibility to connecting to as many third-party parts as we could. However, though both interfaces provide the same data, there is a slight difference in implementation: our implementation of the IParametersOutProvider interface supports both client- and server-side connections, whereas the IRowProvider implementation supports only server-side connections. There are two important implications of this. First, when you’re creating a web part page and want to connect two RtWebParts together, using the IParametersOutProvider interface to connect them will cause the connection to occur on the client side, whereas using the IRowProvider interface will require server-side connections, meaning the entire page will refresh when data is sent from the provider part to the consumer part. Second, when creating your own custom web parts, if you want to connect them to RtWebParts and support client-side connections in doing so, you must implement the IParametersOutProvider interface for client-side connections, because that’s the only interface that RtWebParts can consume for client-side connections.

 

Note that the IRowProvider and IParametersOutProvider interfaces are both associated with the original SharePoint web part base class, and not the ASP.NET web part base class. Don’t confuse them with ASP-NET-related web part connection interfaces like IWebPartRow.

 

One last note on connections – as of RtWebParts version 2.0, our web parts that can serve as providers in a web part connection can also act as filter parts, providing data to consuming web parts using the ASP.NET ITrasnformableFilterValues interface. This is particularly useful in connecting RtWebParts that provide context, such as RtTimeRange, to the Excel Web Access web part, which can be used with our DataLink for Excel Services product to display DataLink spreadsheets in a SharePoint web part page.

Introduction

Getting data from a PI/OLEDB Provider is a no brainer for most of us, but assigning row numbers to each row according to some criteria is quite a challenge and it is something customers request frequently. I have learned the hard way that there is no "easy" way to do this and that in order to achieve this you need to make some sacrifices: some of them come in the shape of internal functions, external code that manages the visualisation layer or (in this case) having a linked server inside SQL Server 2005/2008. This may not be the best way to do this (performance-wise) but its simplicity is enough to give it a try.

Overview

The way this works is that the SQL Server will get the data from the PI Server through the PI OLEDB Provider and will then add the Row Numbers before returning this dataset to the final client. This final client can be anything that can connect to the SQL Data provider. Note that we need a client able to issue the row_number() function so the use of "SQL Server Native Client 10.0" is strongly recommended.

Adding a Linked Server in SQL Server 2008

First we need to get our PI Server listed into the SQL Server, and we need to start up the Microsoft SQL Server Management Studio (or the MSSMS, too many M's and S's if you ask me). Once in that program and after we connected to our desired SQL Server we...

  1. Expand 'Server Objects'.
  2. Right Click on 'Linked Servers'.
  3. Select 'New linked Server'. A dialog will pop up.
    RN_5F00_00.png
  1. Fill 'Linked Server' with a name of your choice. "LOCALHOST" was used in this example.
  2. Select in 'Provider:' dropdown "PI OLE DB Provider".
  3. Fill 'Product Name:' with a value of your choice. "piserver" was used in this example.
  4. Fill 'Data source:' with the name or ip of your pi server as it appears on the PI SDK Connections. "localhost" would work in this example.
  5. Click 'OK'.
    RN_5F00_01.png

Building a query

After we have added our linked server we will need to create a new query and write the query we want to execute.

  1. Click on the 'New Query' button. A new, empty, query will appear.
  2. Write the query you want to run in the new window:

WITH OrderedValues AS
(
SELECT TOP 10000
ROW_NUMBER()OVER (ORDER BY [time] DESC) AS RowNumber
,[tag]
,[time]
,[value]
,[status]
FROM [LOCALHOST].[piarchive]..[picomp]
WHERE tag like'cdt158'
)
SELECT *
FROM OrderedValues
WHERE RowNumber BETWEEN 1 AND 10

  1. Now click on 'Execute' and you should get a list of results that will not be exactly those displayed but will look pretty much alike.
    RN_5F00_02.png

Please note that you can change the tag name, select more than one tag, select a different number of rows (I used from rows 8829 up to 8838 in this example), you could even use parameters in this query. I specifically used these values to show the same range of data in all three windows so you could compare them.

Verifying the values

So far we got a linked server and some data, now we'll verify that we have the same values in both places, in the PI OLEDB Tool and in the SQL Linked Server, we will be using the infamous CDT158 random tag for this test, and the query we'll use is SELECT [tag],[time],[value],[status] FROM [picomp] WHERE tag like 'cdt158'
The query can be seen in the lower part of the screenshot from the program.

 

RN_5F00_03.png

Using the values

Now we will be consuming the values connecting to the SQL Server. Note that we could do this step with any tool that supports acquiring data from SQL (such as Excel, SharePoint, ProcessBook or any other zillion applications). However, for ease of use and since the dialogs for the configuration are almost the same in all of them we'll be connecting with Excel.

  1. Create a blank workbook in Excel
  2. Select 'Data', From other sources, From the 'Data Connection Wizard'
    RN_5F00_04.png
  3. Select 'Other/Advanced' and click 'Next'
    RN_5F00_05.png
  4. Under 'Provider', Select the 'SQL Server Native Client 10.0' (I have SQL 2008, that's why it says 10, it may say 9 if you have SQL 2005)
    RN_5F00_06.png
  5. Under 'Connection', enter your SQL Server name (LOCALHOST\SQLEXPRESS in this example)
  6. Select the appropiate log on type ('Use Windows NT Integrated security' in this example)
  7. Select any database (PIAF_Database in this example), note that the linked server's databases do not show up, so any database will do for now.
  8. 'Test' your connection and if everything succeeds...
  9. Click 'OK'
    RN_5F00_07.png
  10. In the next dialog check 'Connect to specific' table
  11. Select any table (AFTableView in this example)
  12. Click 'Finish'
    RN_5F00_08.png
  13. It may complain about a file that already exists (even more so if you have done this step before), be sure to overwrite it.
    RN_5F00_09.png
  14. In the 'Import Dialog' I left everything as is, your preferences may change, it is ok to change this.
  15. Click 'Properties'
    RN_5F00_11.png
  16. In 'Connection name' any name will do (PIoledb on this example)
  17. Under 'Definition', for the 'Command Type' select 'SQL' from the dropdown list.
  18. In 'Command Text' paste the query you build before (ref.: Building query, Step 2 in this example)
  19. Click 'OK'
    RN_5F00_12.png
  20. Excel will complain about the connection having changed from the one in the configuration file and warn you about removing the link to the external file (wait, does that mean that we won't need the .odc file anymore? that's correct! can you believe it?) be sure to click 'OK'
    RN_5F00_13.png
  21. Back on the Import data dialog, click 'OK'
    RN_5F00_14.png
  22. And voila! we've got data numbered on our spreadsheet, thanks to the PI OLEDB Provider and SQL Server.
    RN_5F00_15.png

Last bits

  • You’ll notice that even getting this amount of data can be done in less than 6 seconds on a low-end computer running the PI Server and the SQL Server (which is not recommended)
  • There are some other interesting numbering functions available in SQL 2005/2008 like DENSE_RANK(), RANK() and NTILE()
  • You could try to do this with the SQL Server Native Client instead, but it will fail miserably when changing the query as that connector won't understand the new functions.
  • We added too much complexity for numbering these rows, that could be easily done with a programmatic interface after we have collected the values.
  • The index in this example will change from query to query, which is less than optimal. But then again, if it didn't change why would we need dynamic numbering anyway?
  • This can be used to do some pagination-like feature on some places, like RtWebparts, or a datagrid.

Filter Blog

By date: By tag: