How To: Create a SmartObject to connect to a SQL Database

You can use an advanced SmartObject to access data from a SQL Server database. For example, you can create a SmartObject of a table in a SQL database, and then create a SmartForm view to read and edit the data stored in that database.

Knowledge of Microsoft SQL Server or Azure SQL Server is required as this scenario requires you to create a SQL database to integrate with the SQL server instance. Contact a member of your IT support team for help in creating the database in your environment, if necessary.

SmartForm view of the Sales - Customer data from a SQL database table

Scenario

Your company has a SQL database used by your Sales organization to store customer content. You need to create a list view of customers from the customer database table.

Steps

First, create a SQL database and then create a new SQL Server Service Instance to connect to the SQL database. Next, you create an advanced SmartObject for the tables/views/stored procedures in the database that you want to interact with. There are two ways you can create the advanced SmartObject:

Once the SmartObject is created, you create a SmartForm to display the data.

Setup Steps

The following steps set up the scenario from start to finish. If you want to skip the setup steps and view the topic step, go to Create an advanced SmartObject to access the SQL data.

  1. Create a database to connect to. This database must be on the same server where your K2 database is hosted. This is to avoid setting additional security and permissions.
    1. For this example, you must create a SQL database called K2Learning. Alternatively, you can use the following SQL script to create the database. Ensure the K2 Service Account has db owner rights to the database.
    2. The database requires the following:
      Table Name
      TableCustomer

      Column NameData TypeColumn Setting
      ColumnsCustomerIdint (Autonumber)Primary key
      CustomerNamenvarchar(50) 
      ContactPersonnvarchar(50) 
      Telephonenvarchar(50) 
      Emailnvarchar(50) 
      DiscountRatedecimal(4, 2) 
      BillingAddressLine1nvarchar(50) 
      BillingAddressLine2nvarchar(50) 
      BillingAddressCitynvarchar(50) 
      BillingAddressCountryint 
      BillingAddressPostalCodenchar(10) 
      Ratingint 
      AccountManagerint 
      DenallixRegionint 
    3. Create entries in the database. If you used the SQL script, entries already exist. Ensure you assign rights to your K2 Service Account.
  2. Use the table settings below to configure a new service instance of the SQL Server Service service type in K2 Management. This data source provides look-up values for the sales categories.
    Field NameSetting
    ADisplay NameK2 Tutorial Sample SQL Database
    BDescriptionThis is the service instance of the sample database used in the K2 Learning tutorial.
     Service TypeSQL Server Service (default)
    CAuthentication ModeImpersonate (Ensure Impersonate is selected)
    DOn Different SQL Serverfalse
    ECommand Timeout90
    FDatabaseK2Learning
    GServerLOCALHOST (Alternatively your SQL server name)
    HGenerate SmartObjects for this Service InstanceUNCHECKED
    1. In the Management menu, expand the Integration node, then click Service Types. You will see current service types displayed in the central pane. Scroll through the service types until you locate SQL Server Service. You can also use the search option instead of scrolling through the pages. (You may see more than one result returned.)
    2. Select SQL Server Service, then click the New Instance button in the toolbar.
    3. Use the table below to configure the service instance. There is an image below the table for reference as well. If a setting is not indicated in the table, assume the default value. Click OK when you have entered the configuration settings, and then click OK for the confirmation dialog. (It may take a few seconds for the confirmation dialog box to appear).
      Field NameSetting
      ADisplay NameK2 Tutorial Sample SQL Database
      BDescriptionThis is the service instance of the sample database used in the K2 Learning tutorial.
       Service TypeSQL Server Service (default)
      CAuthentication ModeImpersonate (Ensure Impersonate is selected)
      DOn Different SQL Serverfalse
      ECommand Timeout90
      FDatabaseK2Learning
      GServerLOCALHOST (Alternatively your SQL server name)
      HGenerate SmartObjects for this Service InstanceUNCHECKED

    4. Click OK.
    5. Click OK to close the service instance pop up.

Create an advanced SmartObject to access the SQL data.

The steps below explain how to create an advanced SmartObject in K2 Designer, mapping through your new SQL Server Service instance to the SQL Sales - Customer data table.

  1. In K2 Designer, create an advanced SmartObject named Sales-Customers and map it to the K2 Tutorial Sample Database SQL Server Service to the List method of the [Sales].[Customer] table.
    1. In K2 Designer click the create SmartObject option.
    2. Specify Sales-Customers as the SmartObject name.
    3. Specify a Description and select a Category. In this scenario the category is Denallix.
    4. Select Advanced SmartObject as the type. Click Create.
    5. Select the Methods tab.
    6. Click Add.
    7. Select the List method of the K2 Tutorials Sample SQL Database > Tables > [Sales].[Customer] table.
    8. Click Next.
    9. On the Inputs and Outputs screen click Create All. This creates the SmartObject properties and maps them to the properties of the SQL table.
    10. Click Next.
    11. Click Finish.
    12. Select the Properties tab. Your SmartObject properties should look similar to the following image:
    13. Click Finish.
  2. Create a List View of your Sales-Customers SmartObject. If you are not familiar with creating views, see How To: Create an Item View and a List View.
    1. On the Sales Customers SmartObject page click Design a new View.
    2. Specify Customer List as the name.
    3. Specify a Description and select a Category.
    4. Select List View as the View Type.
    5. Click Create.
    6. Click Create Labels and Controls.
    7. Check the All Fields check box to select all the SmartObject fields.
    8. Click OK.
    9. Your view should look similar to the image below:
    10. Click Finish.
  3. Create a form to host the Customer List view. If you are not familiar with creating forms, see How To: Create a Form.
    1. On your Custom List view page click Design a new Form.
    2. Specify Custom List Form as the name.
    3. Specify a Description and select a Category.
    4. In the form title, specify Sales Customers.
    5. Click Finish.
  4. Test your SmartForm and view the list of customers from SQL.
    1. On your Customer List Form page click Run.
    2. Your SmartForm should load and show the list of customers, as shown in the following image:

Create an advanced SmartObject using the Generate SmartObjects option from the service instance

You can create the Sales - Customers SmartObject in K2 Management using the Generate SmartObjects option on the service instance page.

Generating SmartObjects is a quick and easy way to create SmartObjects for Service Instances, but you will not have as much control over the naming of the SmartObject and the properties/methods in the generated SmartObject as you would if you manually create the SmartObject in K2 Designer.
  1. In K2 Management navigate to Integration > Service Instances > K2 Tutorial Sample SQL Database and use the Generate SmartObjectsoption to create a SmartObject of the [Sales].[Customer] table.
    1. In K2 Management navigate to Integration > Service Instances.
    2. Select the K2 Tutorial Sample SQL Database SQL Server Service instance.
    3. Click Generate SmartObjects.
    4. Expand the Tables node and enable the [Sales].[Customer] table check box.
    5. Click OK.
  2. Rename the generated SmartObject to Sales-Customer and move the SmartObject to the Denallix category.
    1. In K2 Designer, browse your system and navigate to All Items > SQL Server Service > K2 Tutorial Sample SQL Database > Tables and select the [Sales].[Customer] SmartObject.
    2. Right click the SmartObject and click Rename.
    3. Specify Sales-Customer as the SmartObject name.
    4. Right click the SmartObject and click Move To.
    5. Click the ... ellipses button and select Denallix from the category folders.
  3. You can now follow the same steps from Step 3 above to create a similar view and form to show the contents of the SQL table.
Review

You created a new SQL Server Service instance to connect to a SQL database. Then you created an advanced SmartObject in K2 Designer for the Sales - Customers table and displayed the data from that SmartObject in a SmartForm. Finally you created a similar SmartObject using the Generate SmartObject option in K2 Management and renamed and moved it to the same location as the one created in K2 Designer.