Loading...

Creating Web Functions

When you create a new Web Function, the default template will look like this:

namespace Custom.WebFunctions
{
using System;
using System.Net;
using System.Text;
using System.Data;
using System.Linq;
using System.Dynamic;
using System.Net.Http;
using System.Collections.Generic;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Profitbase.Invision.WebFunctions;
public sealed class MyWebFunction : WebFunction
{
public async Task<HttpResponseMessage> Run(HttpRequestMessage request)
{
return request.CreateResponse(HttpStatusCode.OK);
}
}
}

As you can see, this is just a normal C# class with a single method **Run**, that accepts a single argument and returns a HTTP response. A Web Function must follow 3 simple rules, but besides that, you can do whatever you want:
  1. A Web Function must always implement a public Run method,
  2. The Run method must accepts a single argument, the HttpRequestMessage,
  3. The Run method must return a HttpResponseMessage.

A Web Function can inherit from the WebFunction base class. This gives you access to built-in APIs from InVision.


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<HttpResponseMessage> Run(HttpRequestMessage 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);
}

SqlScriptService methods

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

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


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

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


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

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 that the SQL Script must be configured to return data.


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

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 that the SQL Script must be configured to return data.


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

Asynchronusly executes a SQL Script, specified by its object name, and returns the data set from the query. Note that the SQL Script must be configured to return data.


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

Asynchronusly executes a SQL Script, specified by its object id, and returns the data set from the query. Note that the SQL Script must be configured to return data.


SqlCommandService

The SqlCommandService is the API for executing arbitrary SQL commands from a Web Function and is available if your Web Function class inherits from the WebFunction base class.


Example calling a SqlCommandService method.

More examples are available in the Samples window in the Invision Desktop Designer.

public async Task<HttpResponseMessage> Run(HttpRequestMessage request)
{
var customerName = await this.SqlCommandService.ExecuteScalarAsync<string>("connectionString",
"SELECT Name
FROM Customers
WHERE CustomerID = @customerId, new SqlParameter("@customerId", "theCustomerId"));
}

SqlCommandService methods


Task<DataTable> LoadTableAsync(string connectionString,
string query, params SqlParameter[] sqlParameters)

Asynchronusly executes a SQL query and returns the result as a .NET DataTable.


Task<int> ExecuteNonQueryAsync(string connectionString,
string query, params SqlParameter[] sqlParameters)

Asynchronusly executes a SQL query and returns the number of rows affected by the query.


Task<T> ExecuteScalarAsync<T>(string connectionString,
string query, params SqlParameter[] sqlParameters)

Asynchronusly executes a SQL query and returns the value of the first column in the first row from the result.