Canceling ADO.NET queries

You can cancel a running vsql query by calling the .Cancel() method of any Command object. The SampleCancelTests class demonstrates how to cancel a query after reading a specified number of rows. It implements the following methods:

• SampleCancelTest() executes the Setup() function to create a test table. Then, it calls RunQueryAndCancel() and RunSecondQuery() to demonstrate how to cancel a query after it reads a specified number of rows. Finally, it runs the Cleanup() function to drop the test table.
• Setup() creates a database for the example queries.
• Cleanup() drops the database.
• RunQueryAndCancel() reads exactly 100 rows from a query that returns more than 100 rows.
• RunSecondQuery() reads all rows from a query.

using System;
using Vertica.Data.VerticaClient;

class SampleCancelTests
{
    // Creates a database table, executes a query that cancels during a read loop,
    // executes a query that does not cancel, then drops the test database table.
    // connection: A connection to a Vertica database.

    public static void SampleCancelTest(VerticaConnection connection)
    {
        VerticaCommand command = connection.CreateCommand();

        Setup(command);

        try
        {
            Console.WriteLine("Running query that will cancel after reading 100 rows...");
            RunQueryAndCancel(command);
            Console.WriteLine("Running a second query...");
            RunSecondQuery(command);
            Console.WriteLine("Finished!");
        }
        finally
        {
            Cleanup(command);
        }
    }

    // Set up the database table for the example.
    // command: A Command object used to execute the query.
    private static void Setup(VerticaCommand command)
    {
        // Create table used for test.
        Console.WriteLine("Creating and loading table...");
        command.CommandText = "DROP TABLE IF EXISTS adocanceltest";
        command.ExecuteNonQuery();
        command.CommandText = "CREATE TABLE adocanceltest(id INTEGER, time TIMESTAMP)";
        command.ExecuteNonQuery();
        command.CommandText = @"INSERT INTO adocanceltest
        SELECT row_number() OVER(), slice_time
            FROM(
                    SELECT slice_time FROM(
                    SELECT '2021-01-01'::timestamp s UNION ALL SELECT '2022-01-01'::timestamp s
                    ) sq TIMESERIES slice_time AS '1 second' OVER(ORDER BY s)
            ) sq2";
        command.ExecuteNonQuery();
    }

    // Clean up the database after running the example.
    // command: A Command object used to execute the query.
    private static void Cleanup(VerticaCommand command)
    {
        command.CommandText = "DROP TABLE IF EXISTS adocanceltest";
        command.ExecuteNonQuery();
    }

    // Execute a query that returns many rows and cancels after reading 100.
    // command: A Command object used to execute the query.
    private static void RunQueryAndCancel(VerticaCommand command)
    {
        command.CommandText = "SELECT COUNT(id) from adocanceltest";
        int fullRowCount = Convert.ToInt32(command.ExecuteScalar());

        command.CommandText = "SELECT id, time FROM adocanceltest";
        VerticaDataReader dr = command.ExecuteReader();
        int nCount = 0;
        try
        {
            while (dr.Read())
            {
                nCount++;
                if (nCount == 100)
                {
                    // After reaching 100 rows, cancel the command
                    // Note that it is not necessary to read the remaining rows
                    command.Cancel();
                    return;
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            dr.Close();
            // Verify that the cancel stopped the query
            Console.WriteLine((fullRowCount - nCount) + " rows out of " + fullRowCount + " discarded by cancel");
        }
    }

    // Execute a simple query and read all results.
    // command: A Command object used to execute the query.
    private static void RunSecondQuery(VerticaCommand command)
    {
        command.CommandText = "SELECT 1 FROM dual";
        VerticaDataReader dr = command.ExecuteReader();
        try
        {
            while (dr.Read())
            {
                ;
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            Console.WriteLine("Warning: no exception should be thrown on query after cancel");
        }
        finally
        {
            dr.Close();
        }
    }
}