Run Sql Scripts from Web Functions

WebFunction base class APIs

SqlScriptService

The SqlScriptService is the API for executing SQL Scripts defined in your Solution and is available if your Web Function class inherits from the WebFunction base class.

Example calling a SqlScriptService method.

More examples are available in the Samples window in the InVision Desktop Designer

public async Task<IActionResult> Run(HttpRequest request)
{
    //Create the script arguments from the request payload.
    //When called from a Workbook, the request payload is an object with properties set by calls to
    //for example: HttpPost("api/webfunctions/functionid", {"ItemId" : "myValue"});


    dynamic requestBodyObject = JObject.Parse(await request.Content.ReadAsStringAsync());
    var scriptArguments = new Dictionary<string,object>
    {
        {"@ItemID", requestBodyObject.ItemId.Value}
    };

    // Executing SQL Script that returns an integer and expects an @ItemID SQL parameter
    var value = await this.SqlScriptService.ExecuteScalarByIdAsync<int>("scriptId", scriptArguments);
}

Methods

ExecuteNonQueryByIdAsync
Asynchronusly executes a SQL Script, specified by its object id, and returns the number of rows affected by the query.

Task<int> ExecuteNonQueryByIdAsync(string scriptId, Dictionary<string, object> paramValues = null)

ExecuteNonQueryByNameAsync
Asynchronusly executes a SQL Script, specified by its object name, and returns the number of rows affected by the query.

Task<int> ExecuteNonQueryByNameAsync(string scriptName, Dictionary<string, object> paramValues = null)

ExecuteScalarByIdAsync
Asynchronusly executes a SQL Script, specified by its object id, and returns the value in the first column in the first row from the result set.

Note

The SQL Script must be configured to return data.

Task<T> ExecuteScalarByIdAsync<T>(string scriptId,  Dictionary<string, object> paramValues = null)

ExecuteScalarByNameAsync
Asynchronusly executes a SQL Script, specified by its object name, and returns the value in the first column in the first row from the result set.

Note

The SQL Script must be configured to return data.

Task<T> ExecuteScalarByNameAsync<T>(string scriptName, Dictionary<string,object> paramValues = null)

LoadByNameAsync
Asynchronusly executes a SQL Script, specified by its object name, and returns the data set from the query.

Note

The SQL Script must be configured to return data.

Task<DataTable> LoadByNameAsync(string scriptName,  Dictionary<string, object> paramValues = null)

LoadByNameAsync
Asynchronusly executes a SQL Script, specified by its object id, and returns the data set from the query.

Note

The SQL Script must be configured to return data.

Task<DataTable> LoadByIdAsync(string scriptId,  Dictionary<string, object> paramValues = null)