Query Builder

The Query Builder is a tool that assists the developer with creating a successful SQL statement. The Query Builder is accessed by clicking the "Query Builder" button either on the " Data Source Wizard - Define Custom Statements or Stored Procedures" or the "TableAdapter Configuration Wizard - Enter a SQL Statement" screens. The Add Table window opens displaying the K2 SmartObjects that were selected in the data connection.

Fig. 1. Add Table screen

Feature What it is
Tables The tables selected will be used in to build the SQL statement.
Views This tab is disabled within this screen
Buttons
Refresh Refreshes the list of Tables displayed
Add Highlight the required tables and click Add to add their columns to the Query Builder Diagram pane.
Close Click Close to exit the "Add Table" window.

Create the SQL Statement by selecting the check box next to the required columns. Each table is labelled with the SmartObject name followed by the SmartObject Method used to return data on a Select statement. As the columns are selected the "SELECT" query is created automatically.

Fig. 2. Example of Select Statement built in the Query Builder

K2 ADO.NET table concepts consist of the combination of both the SmartObject name and the SmartObject Method name. If a query refers to the specific method that must be used as opposed to the default method then the “table” must be quoted. It is recommended that the square brackets are always used for quoting the identifier.

Select the "Execute Query" button to test the SQL Statement, ensuring that there are no errors in the statement and that the required information is returned. Below is an example of the data returned by the SQL statement.

Fig. 3. Example of Results when a Query is executed

Right- click on the top open area of the Query Builder, a menu will appear. Insert Results and Make Table cannot be used when building a query.

Examples of SQL Statements

Below are examples of SQL statements that will return the correct SmartObject data when executed:

Statement Example
GetList SELECT [SO1.GetList].* FROM [SO1.GetList]
Update UPDATE [SO1.Save] SET Memo = @Memo, Multivalue = @Multivalue, Number = @Number, Text = @Text, YesNo = @YesNo WHERE (ID = @ID)
Insert INSERT INTO [SO1.Create] (Memo, Multivalue, Number, Text, YesNo) VALUES (@Memo, @Multivalue, @Number, @Text, @YesNo)
Delete
  • DELETE FROM [SO1.Delete] WHERE (ID = @ID)

  • Specifying a DELETE with the List method, might result in unexpected behaviour.
    Rather change the GetList to the name of the delete method.

    For Example: DELETE FROM [Obj.Delete] WHERE ([Obj.Delete].Id = 1)

    And NOT DELETE FROM [Obj.GetList] WHERE ([Obj.GetList].Id = 1)
Load SELECT ID, Memo, Multivalue, Number, Text, YesNo FROM [SO1.GetList] WHERE (ID = @ID)

On completion of a SQL Statement selecting the Execute Query button might not function correctly. At Run time the statement will execute correctly.

Remember to add the parameter in the SelectQuery property and bind it to the textbox