Executing SQL queries and SQL batches

The SQL Editor tab is the central place to edit and execute SQL queries or SQL batches right from inside the LLBLGen Pro designer.. This tab is opened by selecting the Tools -> SQL Editor option in the main menu or by clicking the button on the toolbar.

It constists of the following parts:

• The top half, which is the SQL editor in which you can specify your query or queries.
• The bottom half, which contains the execution configuration elements like connection string, which database to connect to, and the results (messages and resultsets) of executing the query or queries.

Specifying SQL queries and batches

The SQL Editor tab offers the ability to execute a single SQL query or to execute multiple SQL queries in a batch. To specify a query, simply type it as text. To specify a batch of SQL queries, make sure you use the query delimiter for the database you're targeting. For MS Access and SQL Server this is the GO statement on a separate line. For other databases this is a ; at the end of a SQL query. The LLBLGen Pro driver for the database you're targeting will make sure the queries run all in one command or separately, in the case the database ADO.NET provider doesn't support batching.

You can execute any type of query, be it a SELECT query or DDL SQL statements, an insert statement or other. If you want to execute a stored procedure you have to specify EXEC or the equivalent for the database you're targeting.

Comments

The SQL Editor tab supports both single line comments starting with -- and also multi-line comments wrapped with \* *\. Comments are stripped out before a statement is executed.

Saving and loading the editor contents

You can save the editor contents to a file. To do so, click the File -> Save Edited File As... option in the main menu. By default if you open an new SQL Editor tab it will create a temp file. Using Save on the file will save to that temp file. To load a sql file into the editor, simply drag a .sql file onto the designer's user interface and it will be opened in a SQL Editor tab.

You can also copy SQL statements from another editor into the SQL Editor tab to execute them. E.g. if you copy a SQL query from ORM Profiler to the clipboard, you can paste it in a SQL Editor tab and execute it immediately.

Executing SQL queries and batches

After you've specified your query or queries, you can execute them. The designer will offer per database type in your project all catalogs/databases available in selectors and per database a connection string to edit. This connection string is initially the one that's generated into the generated code, and can be edited using the Edit... button next to the connection string textbox.

Using the Execute... drop down button, you have two options to execute SQL statements:

• Execute as Batch (Ctrl-Alt-F)
• Execute as Single Statement (Ctrl-Shift-Alt-F)

If nothing is selected, the option you'll choose will use the entire editor pane's contents. If you select a part of the editor pane's contents, only that part will be used for the option you'll choose.

To run everything inside a transaction, check the Transaction checkbox. It will wrap the SQL to execute in a ReadCommitted transaction. All queries are executed in the background so the designer keeps responsive and you can cancel the queries at any time.

Execute as Batch

Executing a set of statements as a batch means that the editor pane (or the selection) is considered to contain multiple SQL queries which are delimited by the delimiter of the target database (; by default, GO on a separate line for SQL Server / MS Access). The driver will separate each SQL statement at that delimitor and execute them separately, sequentially. Execute as Batch is ideal for generated DDL SQL statements as it's often required one statement has been completed before another can be executed.

For batch executions it's recommended to use the Transaction checkbox to make sure they're rolled back if one fails.

Execute as Single Statement

Executing the editor pane's contents as a single statement means that the entire contents of the editor pane (or the active selection if there's any) is considered a single statement and executed as such as a single DbCommand. The text isn't split on delimiters.

Cancelling execution

When the execution of a statement or statements has been started, the Execute... button will change into a Cancel button. If your query takes a long time and you want to break it off, you can do so by clicking the Cancel button. The query is aborted, and everything is cleaned up.

Timeouts

As there's an option to cancel the execution of a query or queries, the timeout of the DbCommand or DbCommands being executed is set to 0 (infinite). This makes sure long-running queries don't timeout after e.g. 30 seconds (the ADO.NET default).

Messages and results

When you execute a query or batch of queries, the designer will report as much information as possible back to the designer which is displayed in the Messages tab at the bottom of the SQL Editor tab: it will report the start time, how many queries have been executed, the # of returned rows per query in which resultset and the time the execution ended (which contains the time to consume a resultset).

Example

Consider this query, using the ; separator, on SQL Server

select * from sales.salesorderheader;
select * from sales.salesorderdetail;

When this is executed as a single statement, the Messages tab contains:

Execution started on (local time): Tuesday, 7 April 2020 08:52:17
----------------------------------------------------------------------
Execution completed. Number of queries executed: 1
Query 1::Command executed successfully. Number of result-sets returned: 2
    Resultset 1 contains 31465 rows.
    Resultset 2 contains 121317 rows.

----------------------------------------------------------------------
Execution ended on (local time): Tuesday, 7 April 2020 08:52:18

The Results tab contains the two result-sets in a grid, as well as the schematable with the types per column:

If we modify the query a bit and use a GO separator:

select * from sales.salesorderheader
GO
select * from sales.salesorderdetail

and execute it as a batch instead, the Messages tab contains:

Execution started on (local time): Tuesday, 7 April 2020 08:55:45
----------------------------------------------------------------------
Execution completed. Number of queries executed: 2
Query 1::Command executed successfully. Number of result-sets returned: 1
    Resultset 1 contains 31465 rows.

Query 2::Command executed successfully. Number of result-sets returned: 1
    Resultset 1 contains 121317 rows.

----------------------------------------------------------------------
Execution ended on (local time): Tuesday, 7 April 2020 08:55:46

as it executes two queries, as they're separated by the delimiter for this particular database.

The Results tab contains the same result-sets and schema tables.