OData

• OData Overview
• OData Functions
• OData Generation Tool

OData Overview

OData is a protocol that standardizes data access methods in web applications. Specifications were developed mainly by Microsoft, IBM, SAP, and Citrix.

Forguncy only supports OData. It retrieves table data using OData resource paths. Table data here refers to table or view data. It also supports some OData query options, operators and functions. There are four ways to retrieve data using OData.

Below is the sample of using C# code to get authentication for the Forguncy application and retrieving table data using OData by URL:

Form Authentication

public string GetOData( string baseUrl, string odataParam, CookieContainer loginCookie)
{
HttpWebRequest rq = HttpWebRequest.Create(baseUrl + “OData/GetData/” + odataParam) as HttpWebRequest;
rq.CookieContainer = loginCookie;
rq.Method = WebRequestMethods.Http.Get;
var response = rq.GetResponse();
return new StreamReader(response.GetResponseStream()).ReadToEnd();
}
CookieContainer GetLoginCookie( string baseUrl, string userName, string passWord)
{
var login = baseUrl + “Account/Login” ;
HttpWebRequest rq = HttpWebRequest.Create(login) as HttpWebRequest;
rq.CookieContainer = new CookieContainer();
rq.Method = WebRequestMethods.Http.Post;
rq. Accept = “application/json” ;
rq.ContentType = “application/json” ;
var loginStr = “{userName:"” + userName + “", password:"” + passWord + “",rememberMe:true }” ;
var data = Encoding. UTF8.GetBytes(loginStr);
using (Stream stream = rq.GetRequestStream())
{
stream.Write(data, 0, data.Length);
}
var response = rq.GetResponse();
return rq.CookieContainer;
}
//test code
var baseUrl = “http://localhost:42515/Forguncy/” ;
var loginCookie = GetLoginCookie(baseUrl, “Administrator” , “123456” );
Console.WriteLine(GetOData(baseUrl, “table” , loginCookie));
Console.WriteLine(GetOData(baseUrl, “table(2)” , loginCookie));

Windows Authentication

public static string GetODataWindows( string baseUrl, string odataParam, string userName, string password, string domain)
{
HttpWebRequest rq = HttpWebRequest.Create(baseUrl + “OData/GetData/” + odataParam) as HttpWebRequest;
rq.Credentials = new NetworkCredential(userName, password, domain);
rq.Method = WebRequestMethods.Http.Get;
var response = rq.GetResponse();
return new StreamReader(response.GetResponseStream()).ReadToEnd();
}
// test code
Console.WriteLine(GetOData(baseUrl, “table” , “budoutaro” , “123456” , “abccompany” ));

OData Functions

OData functions are specific functions in Forguncy that do not exist in Excel.

When setting an OData function to a cell, it is possible to construct an OData path using the GUI by using the OData generation tool.

OData Generation Tool

You can create a new OData path or read and change the OData path written in the active cell.

If you open the OData Generator with multiple OData functions in the active cell, only the first one will be loaded and the rest will be discarded. If there is an error in the OData function or an exception error occurs while loading, nothing will be loaded when you open the OData generation tool.

When changing the OData path written in the active cell, if the path contains an else condition of IF / IsBlank, the path may not be analyzed correctly and an incorrect result may be displayed. Such as:

• Go to Formula tab and click OData Generation Tool.

• Select the target table.

• On the Select tab, choose Column, Count, or Value. Select Column to retrieve by specifying the field. All fields are checked by default. Uncheck if necessary. An OData path of the form “$select=ID” is generated. If you select multiple fields, the field names are separated by commas.

  

  Select Count to get the number of columns in the table. An OData path of the form $count is generated.

  

  To retrieve only values, select Get Values ​​Only and set the target field and value. An OData path of the form Value is generated.

  

• On the Query tab, specify your query criteria. Click New Condition and specify the query criteria. An OData path of the form $filter is generated.

  

• On the Top tab, specify the first record to retrieve. An OData path of the form $top is generated.

  

• On the OrderBy tab, specify the sorting for the displayed table. Click New Condition and select the column and order for sorting. An OData path of the form $orderby is generated.

  

• Click the Copy button to copy the OData path to the clipboard.
• Click OK to set the cell as an OData function.