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