ADO Query Syntax

The K2 SmartObject ADO.NET Provider supports the SQL-92 standard syntax for writing ADO queries. However, the semantics for the query syntax may differ from that of other data providers. To use the provider effectively, you should understand how ADO.NET concepts are mapped to SmartObjects. The main difference is that List methods are exposed as ADO.NET tables while Scalar methods are exposed as Stored Procedures. The following table illustrates how K2 blackpearl maps ADO.NET concepts to SmartObject concepts

ADO.NET Concept SmartObject Concept
Table Results from a List Method
Table Column Return Property from a List Method
Stored Procedure Scalar Method (e.g. Create, Read, Update, Delete)
Stored Procedure Parameters Input Properties on scalar Method
Stored Procedure Columns Return Properties on scalar Method
When writing ADO queries, you should use the System names for the SmartObject name, Method name and Property name.
If a SmartObject name, method, parameter or property display name has spaces, the equivalent System name will usually use underscores instead of those spaces. You may use the SmartObject Service Tester Utility to determine the System names for the target SmartObject.

SmartObject Method Types

When creating a SmartObject, you need to set the Method Type for each method. The method types are used by the ADO.NET provider when determining which method to execute. If a SmartObject has only one List or Create method, you do not need to specify the method name in the query; K2 will automatically execute the only available query type for that SmartObject that matches the SQL syntax (for example, if you write a SELECT statement, K2 will attempt to execute the LIST method for that SmartObject). If a SmartObject has more than one List or Create method you will need to specify the method name in the query.

Setting the method type when designing a SmartObject

The sections below illustrate the basics of the ADO query syntax. For examples of more advanced query syntax, please refer to the sub-topics below this topic.

SELECT statements

The ADO.NET Provider retrieves data and performs filters on the data through a SQL SELECT statement. SELECT statements are executed against SmartObject List methods. The table below provides some examples of basic SELECT statements in the appropriate syntax.

Query Syntax Example Query Notes
Simple SELECT statement SELECT *
FROM [SmartObjectName.ListMethodName]
WHERE [PropertyName] = '[somevalue]'
ORDER BY  [PropertyName]
SELECT *
FROM ADUser.GetList
WHERE Department = ‘Sales’
ORDER BY EmployeeId
Returns results from a SmartObject List Method, with optional filtering and sorting applied. You can apply filters with WHERE clauses and sorting with ORDER BY clauses.
The example query returns all ADUser SmartObjects where the Department property is “Sales", ordered by Employee Id ascending
SELECT...JOIN statement SELECT *
FROM [SmartObject1Name.ListMethodName]
INNER JOIN [SmartObject2Name.ListMethodName]
ON [SmartObject1Name.PropertyName] = [SmartObject2Name.PropertyName]
SELECT *
FROM AD_User.GetList
JOIN SQL_Employee.List
ON AD_User.Name = SQL_Employee.LoginName
Joins the results from two list methods based on a common property. You can perform both INNER and OUTER JOINs.
In the example query, we are joining the results of the Active Directory User SmartObject with an SQL database Employee SmartObject. The common property is [Name] in the AD_User SmartObject and LoginName in the SQL_Employee SmartObject.
SELECT.. statement with an input parameter SELECT *
FROM [SmartObjectName.ListMethodName]
HAVING [InputParameterName] = '[Value]'
SELECT *
FROM AD_User.List_Users_for_Department
HAVING Department_Name = 'Finance'
Returns the results from a List method that has an input parameter. Use a HAVING clause to set the value of the input parameter.
In the example query we are executing a list method called List_Users_For_Department which has an input parameter called Department_Name, in this case we are returning all the users for the "Finance" department

Notes regarding SELECT statements:

EXEC(UTE) Syntax

The ADO.NET data provider executes scalar SmartObject methods (e.g. Update, Delete, Read) through the SQL EXEC statement. You can use the EXEC statement to call Create, Update, Delete and Execute SmartObject method types.

The table below provides some examples of basic EXEC(UTE) statements in the appropriate syntax.

Query Syntax Example Query Notes
EXEC Statement, no parameter EXEC [SmartObjectName.ScalarMethodName] EXEC AD_User.UpdateCache

Executes a Scalar Method for a SmartObject, with any return properties returned as Stored Procedure columns.

In this example, we are executing a UpdateCache method for a hypothetical AD_User SmartObject. (It is unlikely that a Scalar method would not require input properties, however. )

EXEC Statement with Parameter EXEC [SmartObjectName.ScalarMethodName] @Parameter1= '[somevalue]' EXEC AD_User.Read_User @UserName = "Anthony" Executes a Scalar Method for a SmartObject, with any return properties returned as Stored Procedure columns. Input parameters or input properties for the method are passed in as stored procedure parameters.
In this example, we are executing a Read_User method to return a single AD_User SmartObject for the user “Anthony". The method has a required input parameter called “UserName"

Notes regarding EXECUTE statements:

INSERT Syntax

The K2 blackpearl ADO.NET Data Provider supports inserting of new data to a SmartObjects object via a SQL INSERT statement. The INSERT statement will be translated to calling a Method of method type ‘create’.

The table below provides an example of basic INSERT statement in the appropriate syntax.

Query Syntax Example Query Notes
INSERT statement INSERT INTO
[SmartObjectName.CreateMethodName]
([Property1], [Property2])
VALUES (‘[Value1]’, ‘[Value2]’)
INSERT INTO
[Employee.Create_Employee]
(FirstName,LastName)
VALUES ('John', 'Doe')
Executes a Create method for a SmartObject. You should set the values of all the input properties with the VALUES clause.
In this example we are calling the Create method to insert a new record into the Employee SmartObject.

Notes regarding INSERT statements:

K2 blackpearl Developers Reference4.7
Video Links Learn Support
No videos found for this article
K2 on YouTube
No Additional links found for this article
No self-learning content for this article
Try some scenarios...
No relevant support links available for this article