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 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.
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.
|Simple SELECT statement||SELECT *
WHERE [PropertyName] = '[somevalue]'
ORDER BY [PropertyName]
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 *
INNER JOIN [SmartObject2Name.ListMethodName]
ON [SmartObject1Name.PropertyName] = [SmartObject2Name.PropertyName]
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 *
HAVING [InputParameterName] = '[Value]'
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 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.
|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:
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.
|INSERT statement||INSERT INTO
VALUES (‘[Value1]’, ‘[Value2]’)
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: