Create K2 SmartObject from any SQL Database

Create K2 SmartObject from any SQL Database walkthrough.

In K2 blackpearl you can access and maintain SQL table data via SmartObjects.
The most simple approach is to use so called SmartBoxes, which are SQL tables within the K2 database.

Sometimes this approach is not the best, in regards to security, performance, backup and restore or your data concept.

You can very easily create SmartObjects, that are connected to your own SQL databases.
The first thing you have to do is to create a new SQL Database. You can use any SQL Server you like and the SQL Database does not have to reside on the K2 Server.

Using the following example SQL query, a new SQL Database “K2Workflows_CustomDB” is created.

CREATE DATABASE [K2Workflows_CustomDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'K2Workflows_CustomDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\K2Workflows_CustomDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'K2Workflows_CustomDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\K2Workflows_CustomDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)
GO
ALTER DATABASE [K2Workflows_CustomDB] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [K2Workflows_CustomDB] SET ANSI_NULL_DEFAULT OFF
GOM
ALTER DATABASE [K2Workflows_CustomDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET ARITHABORT OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [K2Workflows_CustomDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [K2Workflows_CustomDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [K2Workflows_CustomDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET DISABLE_BROKER
GO
ALTER DATABASE [K2Workflows_CustomDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [K2Workflows_CustomDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [K2Workflows_CustomDB] SET READ_WRITE
GO
ALTER DATABASE [K2Workflows_CustomDB] SET RECOVERY FULL
GO
ALTER DATABASE [K2Workflows_CustomDB] SET MULTI_USER
GO
ALTER DATABASE [K2Workflows_CustomDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [K2Workflows_CustomDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [K2Workflows_CustomDB]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [K2Workflows_CustomDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

Make sure, that the user (here the K2 Service Account will be used) accessing the SQL Database via Service Instance has the appropriate permissions.

USE [K2Workflows_CustomDB]
GO
CREATE USER [DENALLIX\K2Service] FOR LOGIN [DENALLIX\K2Service]
GO
USE [K2Workflows_CustomDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DENALLIX\K2Service]
GO
USE [K2Workflows_CustomDB]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [DENALLIX\K2Service]
GO

The next step will be to create tables, where the data is stored.

In this example we will create three different tables:

  • [CustomDB_Main] for storing process main data
  • [CustomDB_Positions] for storing process details
  • [CustomDB_Attachments] for storing files, i.e. attachments for the process form.

It is essential, that you specify a primary key for the table. Otherwise, the SmartObject will not create methods like read, update or delete (only create and list).

USE [K2Workflows_CustomDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CustomDB_Main](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProcInstId] [int] NULL,
[Folio] [nvarchar](255) NULL,
[Status] [nvarchar](255) NULL,
[WorkflowStartDate] [datetime] NULL,
[WorkflowEndDate] [datetime] NULL,
[QriginatorFQN] [nvarchar](25) NULL,
[QriginatorSAMAccountName] [nvarchar](15) NULL,
[QriginatorDisplayName] [nvarchar](50) NULL,
CONSTRAINT [PK_CustomDB_Main] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomDB_Positions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProcInstId] [int] NULL,
[CustomDate] [datetime] NULL,
[CustomField] [nvarchar](50) NULL,

CONSTRAINT [PK_CustomDB_Details] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomDB_Attachments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[WorkflowName] [nvarchar](50) NULL,
[ProcessInstanceId] [int] NULL,
[BusinessObjectId] [int] NULL,
[ActivityName] [nvarchar](50) NULL,
[UserFQN] [nchar](50) NULL,
[UserDisplayName] [nvarchar](50) NULL,
[DateTime] [datetime] NULL,
[FileType] [nvarchar](50) NULL,
[FileName] [nvarchar](50) NULL,
[FileExtension] [nvarchar](5) NULL,
[FileBLOB] [nvarchar](max) NULL,
CONSTRAINT [PK_Attachments] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The following image displays the result.
CustomDB with tables
After the SQL tasks are completed, you will have to register a new Service Instance for the Database in SmartObject Service Tester.
SmartObject Service Tester - Register Service Instance
In this example, I use the K2 Service Account for accessing the Database.
SmartObject Service Tester - Add Service Instance
Then you will have to switch to Visual Studio.
Create a new empty K2 Solution and add new SmartObjects for each Database Table.
VS - Add SmartObject Main
Then you will have to remove all SmartObject Methods (as you can see in the following figure, they are all SmartBox Services).
VS -SmartObject - Advanced Mode
To do so, activate the "Advanced Mode".
VS -SmartObject - Enable Advanced Mode
Click on "Remove All". Then you can add new SmartObject Methods, the wizard will guide you through the steps.
Create one for each type, i.e. Create, Update, Read, List and Delete.
VS - Add SmartObject Method - Select Method
In the Service Object Method, browse in the Context Browser to the correct Method and add it.
VS - Add SmartObject Method - Select Service Object Method
Afterwards, when creating the first Method for the Service Instace, click on "Create All". For all following Methods you can use "Auto Map", as the mappings to the Smart Object Properties are already created.
Repeat for every Method. Then you should see all SmartObjects as displayed in the following figure.
VS -SmartObject - All SmartObjects
VS -SmartObject - All Methods
When done, deploy your K2 solution. In SmartObject Service Tester (after a "Refresh All") you should see your newly deployed SmartObjects.
SmartObject Service Tester - SmartObjects
Then you can use them as required. Move them between Categories if desired.