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 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:

Create an Advanced SmartObject in K2 Designer
Use the Generate SmartObjects option in K2 Management > Integrations > Service Instances

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.

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.

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.

The database requires the following:

	Table Name
Table	Customer

	Column Name	Data Type	Column Setting
Columns	CustomerId	int (Autonumber)	Primary key
	CustomerName	nvarchar(50)
	ContactPerson	nvarchar(50)
	Telephone	nvarchar(50)
	Email	nvarchar(50)
	DiscountRate	decimal(4, 2)
	BillingAddressLine1	nvarchar(50)
	BillingAddressLine2	nvarchar(50)
	BillingAddressCity	nvarchar(50)
	BillingAddressCountry	int
	BillingAddressPostalCode	nchar(10)
	Rating	int
	AccountManager	int
	DenallixRegion	int

Create entries in the database. If you used the SQL script, entries already exist. Ensure you assign rights to your K2 Service Account.

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 Name	Setting
A	Display Name	K2 Tutorial Sample SQL Database
B	Description	This is the service instance of the sample database used in the K2 Learning tutorial.
	Service Type	SQL Server Service (default)
C	Authentication Mode	Impersonate (Ensure Impersonate is selected)
D	On Different SQL Server	false
E	Command Timeout	90
F	Database	K2Learning
G	Server	LOCALHOST (Alternatively your SQL server name)
H	Generate SmartObjects for this Service Instance	UNCHECKED

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.)
Select SQL Server Service, then click the New Instance button in the toolbar.

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 Name	Setting
A	Display Name	K2 Tutorial Sample SQL Database
B	Description	This is the service instance of the sample database used in the K2 Learning tutorial.
	Service Type	SQL Server Service (default)
C	Authentication Mode	Impersonate (Ensure Impersonate is selected)
D	On Different SQL Server	false
E	Command Timeout	90
F	Database	K2Learning
G	Server	LOCALHOST (Alternatively your SQL server name)
H	Generate SmartObjects for this Service Instance	UNCHECKED

Click OK.
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.

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.
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.
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.
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.

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.
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.
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.