SharePoint Framework: Application Customizers interaction with Azure SQL Data using Azure Functions
Introduction
SharePoint Framework has redefined the way we develop solutions for SharePoint and Office 365. With SPFx we can not only interact with SharePoint Data, but we can connect to external systems and implement logic. In this article we will take a practical scenario in which SharePoint Application Customizer Extension will interact with Azure SQL Data through Azure Functions.
To give more info on the exact requirement that we are going to implement, We will fetch the Current LoggedIn user name of the current user and call an Azure Function which will check if the logged in User is part Azure SQL Table. The function will return back the data to the Application Customizer and if the user is present in the SQL Table, we will Show the Header and a custom message within it, else we will keep the header hidden.
This is just a sample requirement that we have taken up to see how we can use SPFx in Unison with External System data by leveraging Azure Functions.
Azure SQL as Back End Data Store
We will be using Azure SQL as the back end data store that will hold the login names. We will be using Azure Functions to check the SPLogins table for the current user’s logged in name. We have already created the SPLogins Table with the Azure SQL DB and have not populated any records yet.
Azure Function as Middleware
We will be utilizing Azure Function to act as a bridge between SPFx Application Customizer and Azure SQL Table. Azure Function will do the plumbing of code by accepting the Login Name from SPFx and checking it against Azure SQL Table using basic ADO.NET C# Code.
We have already setup the Azure Function named CheckUser.
Azure Function Code
Azure Function accepts HTTPRequest as the input parameter from which we can extract the body and deserialize it using NewstonSoft.JSON. This will contain the Login Name that we will be checking against the Azure SQL Table
The Connection string is saved in the Application Settings of the Function App, using which it creates the SQL Connection. We will then get the count of records from the SPLogins table where the UserName column contains the LoginName. If the count is > 0, it means the Login Name is present and we will return the custom message “User is present in DB” . We will be checking for this return value within SPFx solution based on which we will decide whether to show the header and custom message.
Enable CORS for Function App
The Azure functions run in a different domain than the Office 365 domain within which SPFx solutions are running. By default, cross-domain calls are not allowed in SharePoint . So as to workaround this, we will have to enable CORS (Cross-Origin Resource Sharing) in the function App. Lets do this by heading over to Platform Features -> CORS
Add the SharePoint Domain within the Allowed Origins section
Secure Azure Function
Azure functions can be secured either by selecting the Authorization level at the time of creation of the function or we can secure the entire Function App using Azure AD Authentication which will require us to sign in with our organizational account to access it. For the demo, we are not securing the Function with Azure AD, however you can get the details to do this from here . In this demo we have used Function Authorization Level to secure the Azure Function.
SPFx Application Customizer
Application Customizer Extension has been used to inject header to the page based on the user presence in Azure SQL Table. We will not be looking into the step by step creation of Application customizer, it can be found here .
Get Current Logged User
We have utilized the REST API /_api/web/currentUser/Title to get the login name of the current user.Using it with the spHttpClient will fetch us the LoginName which is being passed to the function checkUserinDB for further processing.
private getSPData(): void {
let currentWebUrl = this.context.pageContext.web.absoluteUrl;
let requestUrl = currentWebUrl.concat("/_api/web/currentUser/Title")
this.context.spHttpClient.get(requestUrl, SPHttpClient.configurations.v1)
.then((response: SPHttpClientResponse) => {
if (response.ok) {
response.json().then((responseJSON) => {
if (responseJSON!=null && responseJSON.value!=null){
let userName:string = responseJSON.value.toString();
this.checkUserinDB(userName);
}
});
}
});
}
Check if User is present in Table
The retrieved Login Name is passed to the Azure Function by invoking the function url using a POST request with the http client. This will in turn run the azure function and the related Ado.NET code to check the table for the presence of the login name. Post processing, the azure function will return a string about user presence back to the solution where the Async program waits for the return value for further processing.
private checkUserinDB(userName: string): void {
var functionUrl: string = "https://checkuserinsql.azurewebsites.net/api/CheckUser?code=gOwUvSehSR9/s8XVotAodZm06rCDOW9Q6VmpmFgFe4v7lfnePXrVBQ==";
const requestHeaders: Headers = new Headers();
requestHeaders.append("Content-type", "text/plain");
requestHeaders.append("Cache-Control", "no-cache");
var siteUrl: string = this.context.pageContext.web.absoluteUrl;
console.log(`SiteUrl: '${siteUrl}', UserName: '${userName}'`);
const postOptions: IHttpClientOptions = {
headers: requestHeaders,
body: `{ name: '${userName}' }`
};
let responseText: string = "";
let isUserPresent :string ="";
this.context.httpClient.post(functionUrl, HttpClient.configurations.v1, postOptions).then((response: HttpClientResponse) => {
response.text().then((responseText: string) => {
isUserPresent = responseText;
this.renderData(isUserPresent);
})
.catch ((response: any) => {
let errMsg: string = `Error = ${response.message}`;
Dialog.alert(errMsg);
console.log(errMsg);
});
});
}
Render the Header and Message
We have already added the below div to the default available topPlaceholder where the header will be shown.
Based on the return string from the azure function, it check if the user is present in the DB. If the user is present, it gets the headerContainer element which is the a div within the topPlaceholder DOM element and assigns the text ‘User is present in DB’. If the user is not present, it sets the innerHTML of the entire topPlaceHolder to null which will in turn hide the entire header.
private renderData(userPresence: string): void {
if( userPresence == "User is not Present in DB")
{
this._topPlaceholder.domElement.innerHTML = "";
}
else{
let resultMsg: HTMLElement = document.getElementById("headerContainer");
resultMsg.innerText = userPresence;
}
}
Testing the Solution
So as to test the extension we have added the SharePoint Online URL in the ServeConfiguration so that it opens up the CheckUser.aspx page where we will be testing the solution
Now lets test the solution by running gulp serve in node command prompt.
It opens up the ChekUser.aspx page but it does not show any Header in the page, this is because the SQL Table is currently empty and does not have any login names
So lets add the Login Name ‘Priyaranjan KS’ to the SQL Table
If you have not stopped gulp serve, refresh the page and we can see that the Header has appeared with the custom message ‘User is present in DB’
Summary
Thus we saw how we can utilize SharePoint Framework Application Customizers to interact with Azure SQL Data using Azure Functions as the middle-ware
References
See Also
private renderData(userPresence: string): void {
``if( userPresence == "User is not Present in DB")
``{
``this._topPlaceholder.domElement.innerHTML = "";
``}
``else{
``let resultMsg: HTMLElement = document.getElementById("headerContainer");
``resultMsg.innerText = userPresence;
``}
``}