Exploring the SmartObject Service Tester

In this tutorial, you will explore the SmartObject Service Tester and become familiar with the functions that are used most often.

Context

Topics that will be covered in this tutorial include:

Duration

This tutorial should take around 20 minutes to complete.

Audience

Users working through this tutorial should already have a basic concept of K2 data components. For more information on Service Objects, Service Brokers and SmartObjects, review the learning materials that cover the basics of data providers and data consumers. One example is the Leave Request tutorial.

In this step, you will register a new SQL Server Service Object. You will then edit the Service Object, then refresh the Service Object. The data you will connect to is located in an Azure SQL Server database, which will demonstrate how you can connect to and interact with data found in external sources.

If you get any errors when creating the Service Instance, confirm that you have typed the Service Instance configuration values exactly as shown. If you are attempting this tutorial from within a firewalled environment, you may not be able to connect to the target SQL database. In this case, speak to your network administrator to determine if access can be opened to the target SQL database over port 1433 as described in this Microsoft KB article: http://support.microsoft.com/kb/287932 , or alternatively you will need to create the database internally in a SQL server in your own environment. You can download a script to create the SQL database from the following location: http://help.k2.com/files/8553 . Please contact your SQL Database Administrator for more information and help.

Step 1 Tasks

  1. Launch the SmartObject Server Tester.
    (C:\Program Files (x86)\K2 blackpearl\Bin\ SmartObject Service Tester.exe)
  2. Register a new SQL Server Service Instance with the following configuration:
    3. Step Setting Value
    A Authentication Mode Static
    B User Name
    		 K2LearningUser
    Password K2LearningPass
    C On Different SQL Server true
    D Command Timeout 90
    E Database
    		 K2Learning
    Server uh8ydarb4m.database.windows.net
    F Use Native SQL Execution false

  3. For the Display Name, enter
    Sales Customers
    4. If you get an error indicating the System Name K2Learning already exists, change the System Name to
    K2Learning2

  4. Create a SmartObject from the [Sales].[Customer] table and change the Name to
    SalesCustomer
    then edit the Main Category so that it is now
    K2Learning\Advanced SmartObjects
    Be sure to check the name before publishing the SmartObject.
  5. If you have not edited a Service Instance before, return to the ServiceInstance Explorer, right-click the Sales Customers Service Instance and select Edit. Notice the edit configuration screen is the same screen that is used for creating the Service Instance.
  6. Refresh the Sales Customers Service Instance in the same manner.
  1. Launch the SmartObject Server Tester located in the
    (Start > All Programs > K2 blackpearl > SmartObject Service Tester.exe) menu.
    2. If you do not see the tool listed in the K2 blackpearl menu, you can navigate to it here: (C:\Program Files (x86)\K2 blackpearl\Bin\ SmartObject Service Tester.exe)

  2. Expand the ServiceObject Explorer, then locate the SQL Server Service Object. Right-click and select Register ServiceInstance.

  3. In the Add Service Instance screen, enter/change the following settings: (See the image below the table for reference.) Click Next to continue.
    StepSettingValue
    AAuthentication ModeStatic
    BUser Name
    		K2LearningUser
    PasswordK2LearningPass
    COn Different SQL Servertrue
    DCommand Timeout90
    EDatabase
    		K2Learning
    Serveruh8ydarb4m.database.windows.net
    FUse Native SQL Executionfalse

  4. For the Display Name, enter
    Sales Customers
    then click Add. Click OK to close the Service Instance successfully added dialogue.
    5. If you get an error indicating the System Name K2Learning already exists, change the System Name to
    K2Learning2
    then click Add.

    Notice that the new Sales Customers Service Instance has been added under the SQL Server Service ServiceObject. Expand the Sales Customers node, then expand the Tables node. Expand the [Sales].[Customer] table and review the properties and methods that were discovered when the Service Instance was registered.

    In the next few steps, you will create a SmartObject from the Sales Customers Service Instance. Creating a SmartObject will allow you to leverage the properties and methods from within SmartForms and K2 workflows.

  5. Right-click the [Sales].[Customer] table and select Create SmartObject.

    6. Now you will change the name of the SmartObject so that it is easier to read. You will also assign a new category and sub-category for your SmartObject location.

  6. Change the Name of the SmartObject to
    SalesCustomer
    then click the Check Name button to confirm this SmartObject name doesn't already exist and it meets the K2 SmartObject naming convention.

  7. In the Category drop-down, enter the following category and sub-category
    K2 Learning\Advanced SmartObjects
    then click Publish SmartObject. Click OK when you see the success dialogue. Exit the Publish SmartObject screen.

    8. Occasionally, you may have to edit the Service Instance. For example, perhaps the password has been changed and your connection is no longer working. Use the following steps if you need to edit a Service Instance.

  8. Right-click the Service Instance (in this example, use the Sales Customers Service Instance) and select Edit Service Instance.

  9. Edit the settings as needed. Click Next, then Update to commit the changes. Click OK to close the success dialogue.

    10. Anytime you edit the Service Instance or make a change to the data source structure, you must refresh the Service Instance. This in turn, updates any associated SmartObjects. For example, suppose a new column was added to the [Sales].[Customer] table on the SQL server. The SmartObject that was created from this table will not see the new column until the Service Instance has been refreshed.

  10. To refresh a Service Instance (and the associated SmartObjects of that Service Instance), right-click the Service Instance and select Refresh Service Instance.

    11. Before you move on to Step 2, you need to create a SmartObject from the [Denallix].[Region] table. This SmartObject will be used when you write an ADO.NET query.

  11. Right-click the [Denallix].[Region] table and select Create SmartObject.

  12. Change the Name to
    DenallixRegion
    then edit the Category so that it reflects the same categories as the SalesCustomer SmartObject:
    K2 Learning\Advanced SmartObjects
    then click Publish SmartObject. Exit the Publish SmartObject screen.

Step 1 Review

In this step, you used the SmartObject Service Tester utility to create a new SQL Server Service Instance. You configured the connection string to point to an external SQL Server, in this case, an Azure SQL Server. Once the Service Instance was in place, you created a SmartObject based off of the [Sales].[Customer] table that was discovered. Whenever the data source is changed (for example, a new column is added), the Service Instance must be refreshed so that the associated SmartObject is refreshed. Service Instances can also be edited as well for example, to update a password.

Now that you have successfully created a new SQL Server-based SmartObject, you should test the SmartObject to confirm data is being returned. This will allow you edit and refresh the Service Instance if there are any connection string changes that are needed. You will want to confirm the SmartObject prior to using it in other K2 elements.

Step 2 Tasks

  1. Execute the SalesCustomer SmartObject. For the first test, execute the List method without any properties.
  2. For the second test, execute the List method and filter the results by DenallixRegion. Enter
    2
    for the region value.
  3. For the third test, execute the Read method and enter
    1
    for the CustomerId parameter.
  1. In the SmartObject Explorer, locate and expand the K2 Learning node, then the Advanced SmartObjects node. (These are the new categories that you created in the previous step.) Right-click the SalesCustomer SmartObject, then click Execute SmartObject.

    2. You will test the SmartObject using two different methods, LIST and READ. You will also enter properties to demonstrate how you can filter the results.

  2. For the first test, change the value for Method to Execute to List, then click Execute. Review the results that are returned.

  3. Now you will enter a property so that the List results are filtered. In the Input Properties pane, locate the DenallixRegion property and enter
    2
    for the value. Then click the Execute button. Scroll over the results and notice that all of the results have a Region value of '2'.

    4. The two tests you just conducted used the List method, which returned a list of results based on the property (filters) being empty at first, then filled in. In the next test, you will change the method to the Read method, then filter the records based on the record ID. This will return the details from just one record, instead of a list.

  4. Change the Method to Execute value to Read. In the Input Properties pane, locate the CustomerId parameter (notice that it is required) and enter
    1
    for the parameter value, then click the Execute button. Review the results that are returned for just one record.

Step 2 Review

In this step, you tested the SalesCustomer SmartObject by executing two different method types. By entering a property for the List method, you observed how the results returned were filtered by the property (in this case, the region). The Read method returned the results of one record, which you specified by entering the CustomerId parameter. This nicely demonstrates how the list method returns a list of records and the read method returns the details of one record.

In this step, you will write and execute an ADO.NET query. If you are familiar with SQL, the ADO.NET query is very similar to a SQL query with the main difference being the ADO.NET statement is querying a SmartObject, where the SmartObject data could be from a variety of sources. For example, one SmartObject might have a SQL data source, another has an Oracle data source and still another has a web service that returns data to a SmartObject. The ADO.NET query allows you to query all of these SmartObjects and return data from multiple sources. Just like a SQL query, the ADO.NET query allows you to specify specific properties (think columns in a table), sort the data, filter the data, etc.

Step 3 Tasks

  1. Launch the ADO Query tool for the SalesCustomer SmartObject.
  2. Execute a few test queries and review the results returned. Filter the results. One example might be to sort the results by the CustomerName.
  3. Execute the following query that returns data from two different SmartObjects:
    SELECT
    Customer.CustomerName,
    Customer.ContactPerson,
    Customer.Email AS "Contact Email",
    Region.RegionName AS "Sales Region"
    FROM
    SalesCustomer AS Customer
    JOIN
    DenallixRegion AS Region ON Customer.DenallixRegion = Region.RegionId
    WHERE
    Region.RegionName NOT LIKE '%EME%'
    ORDER BY
    Customer.CustomerName ASC
    4. If you used different names when creating the SmartObjects (in Step 1) than are shown in the query, you will want to update the query to reflect the correct SmartObject names.

  1. Right-click the SalesCustomer SmartObject and select ADO Query.

  2. Because you have launched the ADO Query builder directly from the SalesCustomer SmartObject, you are presented with a default SELECT (all) query of the SmartObject data source. Click the Execute button, or press F5, to execute the select-all query. Review the results returned.

  3. Add an ORDER BY clause to the query so that results are sorted by the CustomerName. Review the results.

    4. In the next few steps, we will execute a more complex query. This time, we will query multiple SmartObjects (once again, multiple sources of data) and observe the results that are returned.

    Sample query requirements
    Show me a list customers, contact people and regions, for customers in all regions other than EMEA, sorted by the customer name. (Behind the scenes, the regions data resides in a separate SmartObject than the customer data.)

    As a reminder, data can come from a variety of sources. For example, SQL, Oracle, SharePoint, even a web service that returns data of some kind. As long as there is a Service Object that is returning data, you can create a SmartObject. From there, you can query multiple SmartObjects in one query statement. In our example, we are sending a query to two SQL-based SmartObjects, but you could query a SQL-based SmartObject and a Web Service based SmartObject from within one query if you needed to. By creating SmartObjects, you can write SQL-like statements against multiple sources of data.
  4. Highlight the ADO query and delete it. Enter the following statement into the query canvas:
    SELECT
    Customer.CustomerName,
    Customer.ContactPerson,
    Customer.Email AS "Contact Email",
    Region.RegionName AS "Sales Region"
    FROM
    SalesCustomer AS Customer
    JOIN
    DenallixRegion AS Region ON Customer.DenallixRegion = Region.RegionId
    WHERE
    Region.RegionName NOT LIKE '%EME%'
    ORDER BY
    Customer.CustomerName ASC
  5. Execute the query. Review the results that are returned.

    6. If you used different names when creating the SmartObjects (in Step 1) than are shown in the query, you will want to update the query to reflect the correct SmartObject names.
Step 3 Review

In this step, you explored the ADO Query tool and learned how you can create JOIN statements to return data from multiple SmartObjects. What is significant is that SmartObjects can return data from a variety of sources (for example, SQL, Oracle, SharePoint and even Web Services). This means that you can return data from multiple data sources from within one ADO.NET query.

K2 uses the SmartObject's system name and not the display name when identifying the SmartObject throughout any applications it is associated with. System names are unique and do not contain any spaces or invalid characters. This ensures that SmartObjects can be used across the platform. You can use the SmartObject Service Tester to view the SmartObject’s XML properties, which represent the full definition of the SmartObject including the system and display names.

Step 4 Tasks

  1. Use the SmartObject Service Tester to view the XML properties and raw XML for the SalesCustomer SmartObject. Compare the DisplayName and Name properties.
  2. If time allows, navigate through other SmartObject categories until you find one that contain spaces in the display name. View the XML. Compare the DisplayName and Name properties.
  1. Right-click the SalesCustomer SmartObject and select View Xml. The Property Pane for this SmartObject will open on the right side of the screen. Scroll through the settings and locate the DisplayName property. Locate the Name property (which is the system name). The raw XML can be found at the bottom of the property pane.

  2. If time allows, open a few other SmartObject categories and find a SmartObject that has a display name containing spaces. View the XML for that SmartObject. Locate the DisplayName and Name properties. Notice that K2 automatically inserts underscores in place of the spaces for the system name. (In the image below, we have located a SmartObject called Leave Request SmartObject. You may not see the same SmartObjects on your computer. Any SmartObject that has spaces in the display name will work for this step.)

Step 4 Review

In this step, you used the View Xml tool to view the properties of a SmartObject, along with the raw XML that is generated. Because K2 converts the display name to a valid system name, it can be useful to know where to find the K2-generated system name. The system name will appear in logs and of course, is a staple for developers working with K2 applications.

Another very useful feature of the SmartObject Service Tester is the ability to export Service Objects as .sodx files. From here, you can open and edit the SmartObject using a K2 design tool such as K2 Studio or K2 for Visual Studio.

Step 5 Tasks

  1. Export the SalesCustomer SmartObject as a .sodx file. Open the file using K2 Studio.
  1. Right-click the SalesCustomer SmartObject and select To Sodx. Save the file to your desktop.

  2. Launch K2 Studio. (Start > All Programs > K2 blackpearl > K2 Studio)

  3. First, we will create a new project to store our SmartObject in. Click File, then New. For the K2 Type, select K2 Project. For the K2 Template, select K2 Project. Name the project
    SmartObjectTest
    then click Create.

  4. Now, you can add the SalesCustomer SmartObject (.sodx) to the K2 Studio project. In the Solution Explorer, right-click the SmartObjectTest project name, then select Add > Existing project Item.

  5. Navigate to your desktop. Change the file type to K2 SmartObject Files (*.sodx), then select and Open the SalesCustomer.sodx file.

  6. In the Solution Explorer, double-click the SalesCustomer SmartObject to open it. Notice now, you can add and edit the properties and methods from the options found in the SmartObject ribbon. (We won't actually make any changes as this is not the scope of this tutorial. Just know that this is one method for exporting a Service Object and editing it in a design tool.)

Step 5 Review

In this step, you exported the SalesCustomer SmartObject as a .sodx file. You opened the file using K2 Studio, then observed how you might edit the properties and methods using the options found in SmartObject ribbon.