Call a function: a value, rows, an effect

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Execute.Basics;

// 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 Basic3_CallAFunction().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

namespace ArxSamples.MSSQL.Operations.Execute.Basics;

/// <summary>
/// Calling a function, in three scenarios: one value (<c>Value&lt;T&gt;()</c>); rows (<c>ResultSets[0]</c>); and an
/// effect, which on SQL Server is a procedure's job. A function is the same <see cref="ArxProcedureExpression"/> with
/// <see cref="ArxProcedureType.Function"/>: ARX sends <c>SELECT * FROM fn(…)</c> for a table-valued function and
/// <c>SELECT fn(…)</c> for a scalar one, instead of <c>EXEC</c>. To filter, order or page a function's rows like a table,
/// Retrieve reads it as a row source (RetrieveMultiple's RealCase6, Advanced8).
/// </summary>
public sealed class Basic3_CallAFunction : ISample
{
    public string Name => "execute-basics-3-call-a-function";

    public string Title => "Call a function: a value, rows, an effect";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Function Fabrics";
        account["account_number"] = "ACC-E301";
        var accountId = await dbContext.Service.CreateAsync(account);

        foreach (var (first, title) in new[] { ("Fran", "Buyer"), ("Finn", "Engineer"), ("Faye", "Director") })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Fn-E301";
            contact["job_title"] = title;
            contact["account_id"] = new ArxEntityReference("account", accountId);
            await dbContext.Service.CreateAsync(contact);
        }

        await Scenario1_OneValueAsync(dbContext, accountId);

        await Scenario2_RowsAsync(dbContext);

        await Scenario3_AnEffectAsync(dbContext, accountId);
    }

    /// <summary>1. <c>fn_contact_count(p_account_id)</c> returns a bigint.</summary>
    private static async Task Scenario1_OneValueAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. One value");

        var function = new ArxProcedureExpression("fn_contact_count") { ProcedureType = ArxProcedureType.Function };
        function.AddParameter(accountId.AsInt64());

        var count = (await dbContext.Service.ExecuteAsync(function)).Value<long>();
        Show($"{count} contacts");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT dbo.fn_contact_count(a.id)
         * FROM account a
         * WHERE a.account_number = 'ACC-E301';
         * -- Expect: 3.
         * ===========================================================
         */
    }

    /// <summary>2. <c>fn_contacts_named(p_last_name)</c> returns a table: its rows are the first result set.</summary>
    private static async Task Scenario2_RowsAsync(SampleContext dbContext)
    {
        Step("2. Rows");

        var function = new ArxProcedureExpression("fn_contacts_named") { ProcedureType = ArxProcedureType.Function };
        function.AddParameter("Fn-E301");

        var rows = (await dbContext.Service.ExecuteAsync(function)).ResultSets[0];
        Show(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]} ({x["job_title"]})")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, job_title
         * FROM fn_contacts_named('Fn-E301')
         * ORDER BY first_name;
         * -- Expect: 3 rows: Faye Director, Finn Engineer, Fran Buyer.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. A SQL Server function can't change data: an effect is a procedure's job. <c>sp_rename_account</c>
    /// renames the account and sets modified_on; it returns no rows, and its RETURN value is 0.
    /// </summary>
    private static async Task Scenario3_AnEffectAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. An effect: a procedure, not a function");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_name", "Fabrikam Functions");

        var result = await dbContext.Service.ExecuteAsync(procedure);
        Show($"result sets: {result.ResultSets.Count}, RETURN value {result.Outputs[ArxMsSqlDbService.RETURN_VALUE]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, CASE WHEN modified_on IS NOT NULL THEN 1 ELSE 0 END AS touched
         * FROM account
         * WHERE account_number = 'ACC-E301';
         * -- Expect: Fabrikam Functions, 1.
         * ===========================================================
         */
    }
}

  > 1. One value
      3 contacts
  > 2. Rows
      Fran (Buyer), Finn (Engineer), Faye (Director)
  > 3. An effect: a procedure, not a function
      result sets: 0, RETURN value 0