Progress messages from a procedure

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Execute.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server connection string.");

await new RealCase6_ProgressMessages().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.Execute.RealCases;

/// <summary>
/// The messages a procedure sends while it runs, in two scenarios: <c>PRINT</c> and <c>RAISERROR</c> of severity 10 or
/// less (information, not errors; SQL Server reports their severity as 0) come
/// back in <see cref="ArxExecuteResult.Messages"/>, in order, for the application to show or log as it chooses (ARX never
/// writes them to a log); <c>CollectMessages = false</c> leaves them out.
/// </summary>
public sealed class RealCase6_ProgressMessages : ISample
{
    public string Name => "execute-real-6-progress-messages";

    public string Title => "Progress messages from a procedure";

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_TheMessagesInOrderAsync(dbContext);

        await Scenario2_NotCollectedAsync(dbContext);
    }

    private static ArxProcedureExpression Import(int rows)
    {
        var procedure = new ArxProcedureExpression("sp_import_progress");
        procedure.AddParameter("p_rows", rows);
        return procedure;
    }

    /// <summary>1. An import of 1,000 rows: a progress message every 250 rows, then one because it's large.</summary>
    private static async Task Scenario1_TheMessagesInOrderAsync(SampleContext dbContext)
    {
        Step("1. The messages, in order");

        var result = await dbContext.Service.ExecuteAsync(Import(1_000));

        foreach (var message in result.Messages)
        {
            Show($"{message.Severity,-7} {message.Text}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, type_desc
         * FROM sys.objects
         * WHERE name = 'sp_import_progress';
         * -- Expect: sp_import_progress, SQL_STORED_PROCEDURE (the messages themselves are only seen by the session that ran the call).
         * ===========================================================
         */
    }

    /// <summary>2. The same call without collecting them.</summary>
    private static async Task Scenario2_NotCollectedAsync(SampleContext dbContext)
    {
        Step("2. Not collected");

        var result = await dbContext.Service.ExecuteAsync(Import(1_000), new ArxExecuteOptions { CollectMessages = false });
        Show($"{result.Messages.Count} messages");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT COUNT(*)
         * FROM sys.procedures
         * WHERE name = 'sp_import_progress';
         * -- Expect: 1.
         * ===========================================================
         */
    }
}

  > 1. The messages, in order
      0       imported 250 of 1000 rows
      0       imported 500 of 1000 rows
      0       imported 750 of 1000 rows
      0       imported 1000 of 1000 rows
      0       a large import: 1000 rows
  > 2. Not collected
      0 messages