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

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:

• The FROM clause within the SELECT statement specifies the ADO "Table" concept; the "Table" concept for SmartObjects is represented by a List Method.
• If a List Method Name is not specified, the data provider will select the default List method or otherwise, if there is only one list method, then that method will be implied.
• Use the HAVING clause to provide values for Methods with input parameters
• Joins can be specified as with any other SQL-enabled database: INNER, OUTER, LEFT and RIGHT JOINS are all available.
• When a join is not specified explicitly, it might be performed implicitly when more than one SmartObject is referenced in the SELECT statement’s FROM clause. The implicit joining will occur if an association was defined in the SmartObject definition.

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:

• You may use EXEC syntax to execute List methods as well, by specifying the name of the List method to execute.

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:

• The Method name can be omitted in the INSERT statement; in this case the data provider will try to resolve the method name to the Create method for the targeted SmartObject