A simple way to connect Business Central with One Drive

Posted on by

Hello, today I want to share with you a simple way to connect Business Central with One Drive and Business Central.

As you may have already noticed, I’m a bit of a fan of Azure Functions. We use the Azure project to connect to the Graph API and with it, in turn, to our One Drive.

We could update the Azure project to connect to SharePoint or maybe Dropbox or some SFTP.

In the following links are the repositories used:

–Business Center.
–AzureFunctions.

Azure Functions

For the development of the Azure Functions Project, we have used Microsoft.Graph Nuget package

FileModel:

This is the model that we have created with the information that we will store from the OneDrive items.

public class FileModel
{
public string Id { get; set; }
public string Name { get; set; }
public string Size { get; set; }
public string ExtensionType1 { get; set; }
public string ExtensionType2 { get; set; }
public bool Folder { get; set; }
public byte[] FileArray { get; set; }
}
view raw FileModel.cs hosted with ❤ by GitHub

As you can see I created 2 similar fields, ExtensionType1 and ExtensionType2.

This was out of curiosity to see how he behaved. I recently installed Github Copilot, and it automatically created this code for me “file.File.MimeType.Split(‘/’)[1]” to get the extension when I was creating the Azure Functions, seeing that it worked I left it in the model.

ConfigurationsValues:

We use this table to get all the necessary Oauth2 and Graph API settings like UserID and FolderID.

public class ConfigurationsValues
{
public string Clientid { get; set; }
public string Tenantid { get; set; }
public string ClientSecret { get; set; }
public string UserID { get; set; }
public string FolderID { get; set; }
}
view raw ConfigurationsValues.cs hosted with ❤ by GitHub

FileDownloader:

This class will help us download the file and additionally convert it to a Byte[]

public class FileDownloader
{
public static async Task<byte[]> DownloadFile(GraphServiceClient _graphServiceClient, string UserID, string FileId)
{
IDriveItemContentRequest request = _graphServiceClient.Users[UserID].Drive.Items[FileId].Content.Request();
Stream stream = await request.GetAsync();
return ReadToEnd(stream);
}
public static byte[] ReadFully(Stream input)
{
byte[] buffer = new byte[16 * 1024];
using (MemoryStream ms = new())
{
int read;
while ((read = input.Read(buffer, 0, buffer.Length)) > 0)
{
ms.Write(buffer, 0, read);
}
return ms.ToArray();
}
}
}
view raw FileDownloader.cs hosted with ❤ by GitHub

GetItemsByFolder:

Next, our Azure Functions is in charge of making the connection with the Graph API and taking the elements of a specific folder.

I would say that it has 4 parts:

  • 1) Connect with Oauth2 to the Graph API.
  • 2) List the items given a UserID and a FolderId.
  • 3) Convert the content to a Byte[]
  • 4) Store the information in our FileModel class that will be the one we will send as JSON when executing the function.
public static class GetItemsByFolder
{
[FunctionName("GetItemsByFolder")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
try
{
//1) Connect with Oauth2 to the Graph API.
ConfigurationsValues configValues = readEnviornmentVariable();
GraphServiceClient _graphServiceClient = getGraphClient(configValues);
List<FileModel> Items = new();
//2) List the items given a UserID and a FolderId.
// List all elements of the Business Centrals Folder
IDriveItemChildrenCollectionRequest request = _graphServiceClient
.Users[configValues.UserID]
.Drive
.Items[configValues.FolderID]
.Children
.Request();
IDriveItemChildrenCollectionPage results = await request.GetAsync();
foreach (DriveItem file in results)
{
bool IsFile = file.Folder == null;
if (IsFile)
{
//3) Convert the content to a Byte[]
byte[] FileArray = await Helper.FileDownloader.DownloadFile(_graphServiceClient, configValues.UserID, file.Id);
//4) Store the information in our FileModel class that will be the one we will send as JSON when executing the function.
Items.Add(new FileModel
{
Id = file.Id,
Name = file.Name,
Size = file.Size.ToString(),
ExtensionType1 = file.File.MimeType.Split('/')[1],
ExtensionType2 = Path.GetExtension(file.Name),
Folder = !IsFile,
FileArray = FileArray
});
}
Console.WriteLine("File Id " + file.Id + "\n" +
"File Name" + file.Name + "\n" +
"File Size" + file.Size + "\n" +
"File Folder" + IsFile + "\n");
}
return new OkObjectResult(Items);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return new BadRequestObjectResult(ex.Message);
}
}
view raw GetItemsByFolder.cs hosted with ❤ by GitHub

Specials Notes:

  1. Runtime Error:

In the tests I did, I realized that I was getting the following error: 

Code: BadRequest Message: /me request is only valid with delegated authentication flow.

To fix it, I had to replace: _graphServiceClient.Me.Drive with _graphServiceClient.Users[UserID].Drive

To get my UserID, use the following Web Site: https://developer.microsoft.com/en-us/graph/graph-explorer

2. Business Central Folder:

For this post and demonstration purposes, I only worked with a single folder called BusinessCentral inside my OneDrive, which I obtained its Id in the following way.

3. Oauth2:

We use Outh2 as an authentication mechanism, because my account has double-pass verification and additionally because it is an API-type project.

With Outh2, we need 3 values to configure authentication:
1) Tenant.
2) ClientSecret.
3) Clientid.

To obtain them, we need to create an App Registration.

This previous post explains how to configure it, but we will need to change 2 things: The Redirect URL in the application registration and the Permissions

  • Redirect Url:

We select Mobile and Desktop Applications

We select the first check, and in Customs Redirects URIs we put “http://localhost”

  • Permissions:

In the Microsoft Learning Path you can find more information about how to connect to the Graph API and other configurations.

4. Use of Variables:
All the variables that were used in the project were stored in the Azure configurations.

Business Central

Tables

OneDrive

In Business Central I call it OneDrive, but it’s exactly the same model we use in the Azure project FileModel

table 50500 "OneDrive"
{
Caption = 'OneDrive';
DataClassification = ToBeClassified;
fields
{
field(1; Id; Code[50])
{
Caption = 'Id';
DataClassification = ToBeClassified;
}
field(2; Name; Text[100])
{
Caption = 'Name';
DataClassification = ToBeClassified;
}
field(3; Size; Decimal)
{
Caption = 'Size';
DataClassification = ToBeClassified;
}
field(4; ExtensionType1; Text[200])
{
Caption = 'ExtensionType1';
DataClassification = ToBeClassified;
}
field(5; ExtensionType2; Text[10])
{
Caption = 'ExtensionType2';
DataClassification = ToBeClassified;
}
field(6; Folder; Boolean)
{
Caption = 'Folder';
DataClassification = ToBeClassified;
}
field(7; FileArray; Blob)
{
Caption = 'FileArray';
DataClassification = ToBeClassified;
}
}
keys
{
key(PK; Id)
{
Clustered = true;
}
}
}
view raw OneDrive.al hosted with ❤ by GitHub

Codeunits:

GetFilesFromOneDrive:

This is the core of the project in AL, here we read our Azure Functions API, undo the result and store it in Business Central.

I have to confess, it took me a while to convert the data to Blob but I finally managed it with this wonderful line of code:

Base64Convert.FromBase64(FileArrayBase64, OutStream);
procedure GetFilesFromOneDrive()
var
OneDrive: Record OneDrive;
Base64Convert: Codeunit "Base64 Convert";
httpClient: HttpClient;
httpContent: HttpContent;
httpHeader: HttpHeaders;
httpResponse: HttpResponseMessage;
JsonArray: JsonArray;
JsonObject: JsonObject;
JsonToken: JsonToken;
OutStream: OutStream;
FileArrayBase64: text;
OutPut: Text;
begin
httpContent.GetHeaders(httpHeader);
httpClient.Post(GetUrl, httpContent, httpResponse);
httpResponse.Content().ReadAs(OutPut);
if (httpResponse.HttpStatusCode <> 200) then begin
Error(OutPut);
end;
if not JsonArray.ReadFrom(OutPut) then begin
Error('Problem reading Json.');
end;
foreach JsonToken in JsonArray do begin
JsonObject := JsonToken.AsObject();
OneDrive.Init();
OneDrive.Id := GetJsonToken(JsonObject, 'id').AsValue().AsText();
OneDrive.Name := GetJsonToken(JsonObject, 'name').AsValue().AsText();
OneDrive.Size := GetJsonToken(JsonObject, 'size').AsValue().AsDecimal() / 1024;
OneDrive.ExtensionType1 := GetJsonToken(JsonObject, 'extensionType1').AsValue().AsText();
OneDrive.ExtensionType2 := GetJsonToken(JsonObject, 'extensionType2').AsValue().AsText();
OneDrive.Folder := GetJsonToken(JsonObject, 'folder').AsValue().AsBoolean();
FileArrayBase64 := GetJsonToken(JsonObject, 'fileArray').AsValue().AsText();
OneDrive.FileArray.CreateOutStream(OutStream);
Base64Convert.FromBase64(FileArrayBase64, OutStream);
if not OneDrive.Insert() then begin
OneDrive.Modify();
end;
end;
end;
view raw GetFilesFromOneDrive.al hosted with ❤ by GitHub

DownloadFromCloud:

With this process, we download the content to our local computer by clicking on the file name.

procedure DownloadFromCloud(Id: Code[50])
var
OneDrive: Record OneDrive;
Base64Convert: Codeunit "Base64 Convert";
Istream: InStream;
FileArrayBase64: text;
Filename: Text;
begin
OneDrive.Get(Id);
Filename := OneDrive.Name;
OneDrive.CalcFields(FileArray);
if OneDrive.FileArray.HasValue then begin
OneDrive.FileArray.CreateInStream(Istream);
FileArrayBase64 := Base64Convert.ToBase64(Istream);
DownloadFromStream(Istream, 'Export', '', 'All Files (*.*)|*.*', Filename);
end;
end;
view raw DownloadFromCloud.al hosted with ❤ by GitHub

Pages:

This page will show us all the elements that we have in our folder configured in One Drive.

page 50501 OneDrivePage
{
ApplicationArea = All;
Caption = 'One Drive';
PageType = List;
SourceTable = OneDrive;
UsageCategory = Lists;
DeleteAllowed = false;
Editable = false;
InsertAllowed = false;
layout
{
area(content)
{
repeater(General)
{
field(Name; Rec.Name)
{
Caption = 'File Name';
ApplicationArea = All;
trigger OnDrillDown()
var
Question: Label 'Are you sure you want to download the file %1??';
Confirmed: Boolean;
begin
Confirmed := Dialog.Confirm(Question, false, Rec.Name);
if not Confirmed then
exit;
OneDriveCU.DownloadFromCloud(Rec.Id);
end;
}
field(Size; Rec.Size)
{
Caption = 'Size(KB)';
ApplicationArea = All;
}
field(ExtensionType2; Rec.ExtensionType2)
{
Caption = 'Extension Type';
ApplicationArea = All;
}
}
}
}
actions
{
area(Processing)
{
action(Reload)
{
Caption = 'Reload';
Image = Refresh;
Promoted = true;
PromotedCategory = Process;
PromotedIsBig = true;
PromotedOnly = true;
ApplicationArea = All;
trigger OnAction();
begin
OneDriveCU.GetFilesFromOneDrive();
end;
}
}
}
trigger OnOpenPage()
begin
OneDriveCU.GetFilesFromOneDrive();
end;
var
OneDriveCU: Codeunit OneDriveCU;
}
view raw OneDrivePage.al hosted with ❤ by GitHub

Testing:

As we have activated the OnDrillDown trigger in the File Name field, when we click on it, we will be asked if we want to download the said file to our pc.

Video:

Conclusion

This was a small project to demonstrate the power of Azure to integrate with cloud file management services like One Drive.

The project undoubtedly has many limitations and is not intended to be used as a final project.

Among the improvements that could be integrated:
1) Create a module to Upload/Update files.
2) Remove the “OneDrive.DeleteAll();” which is used in the Reload and improve the synchronization logic. (A bad practice to recreate everything again).

If you want, feel free to create PR and make improvements.

Finally, before finishing, last night I saw a Tweet that I liked, of a project that is being created with native APIs in Business Central to connect to Sharepoint.

I hope this helps you.

Post Views: 2,942

Leave a Reply

Your email address will not be published. Required fields are marked *