Create a FAQ Bot using Microsoft Bot Framework

What is the Microsoft Bot framework?

The Microsoft Bot Framework is a set of APIs that simplifies the process of creating a chat bot using C#. The bot framework us set up a web service or Azure function that allows for interacting with a user via chat. The bot framework has several features for integrating chat bots with Microsoft Teams, Skype, and other Microsoft products. While the framework is geared toward Microsoft integration with Teams, it can be used as a stand alone bot system that can control: user flow, group chats, etc. 

Bots use JSON to write back and forth to the web service, but interpreters like Teams will recognize certain JSON objects, such as cards, and display them in a unique way without any styling. The framework also has a feature that allows for conversation flow called dialogs. This will manage a user's conversation flow and allow for more complex interactions like booking a hotel room, or implementing a pizza ordering system. 

Create a Bot in 5 Minutes

This example is going to build off of a previous example where we developed a NLP faq application. If you have not read the NLP example, please do so before beginning since we will use a lot of the codebase from this example. The NLP example can be found here: https://fiveminutecoder.blogspot.com/2020/08/using-mlnet-to-create-natural-language.html.  Before creating our chatbot, we need to get the bot templates. There are several starting templates to choose from, for this example we will use the echo bot. This bot template just writes back to the chat what you type in which is perfect for our demo since we do not need any dialog flow. To install the template we just need to run the following commands.

dotnet new -i Microsoft.Bot.Framework.CSharp.EchoBot
dotnet new echobot -n BotFrameworkFAQBot

Once we have our framework setup, we need to install the Nuget package Microsoft.ML. This will allow us to use ML.NET to process our questions and post an answer.

From the NLP example we will want to bring over several items. The first being our trained machine learning model which we saved earlier called FAQModel.zip. We will also need our Prediction data model and our FAQ data model. 

using Microsoft.ML.Data;

namespace EchoBot.Bots
{
    public class FAQModel
    {
        [ColumnName("Question"), LoadColumn(0)]
        public string Question {get;set;}
        [ColumnName("Answer"), LoadColumn(1)]
        public string Answer {get;set;}
    }
}

using Microsoft.ML.Data;

namespace EchoBot.Bots
{
    public class PredictionModel
    {
        [ColumnName("PredictedAnswer")]
        public string PredictedAnswer {get;set;}
        [ColumnName("Score")]
        public float[] Score {get;set;}
    }
}

With our data moved from the previous project, we can go ahead and rename the EchoBot.cs to FAQBot.cs. This will break any dependency injection that is setup by our service, so we will need to go to the startup.cs and change the services.AddTransient<IBot, Bots.EchoBot>() to services.AddTransient<IBot, Bots.FAQBot>() 

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
	services.AddControllers().AddNewtonsoftJson();

	// Create the Bot Framework Adapter with error handling enabled.
	services.AddSingleton();

	// Create the bot as a transient. In this case the ASP Controller is expecting an IBot.
	services.AddTransient();
}

Our machine learning model is pretrained, so since this information is static we will create a singleton instance of our prediction engine . This way we do not have to read the file from filestream every time we want to predict an answer. 

//context or our machine learning model
static MLContext context;
//used to read and predict our questions
static PredictionEngine predictionEngine;

//creating a private staitc constructor
//Our model is not changing so it doesnt make sense to keep opening it and reading the zip for performance
static FAQBot()
{
	//structure of our data model
	DataViewSchema modelSchema;
	//the model loaded for prediction
	ITransformer trainedModel;
	context = new MLContext();

	//load our file
	using(Stream s = File.Open("FAQModel.zip", FileMode.Open))
	{
		trainedModel = context.Model.Load(s, out modelSchema);
	}

	//creates our prediction engine
	predictionEngine = context.Model.CreatePredictionEngine(trainedModel);

}

Now that our constructor on class are in place and loaded, all we need to do is call the predict function for our question and display the outputs. We want to ensure that we have some confidence in our predictions so we will check the score and only display the output if the prediction score over 60% confident. If not we will give the user a list of choices to choose from. 

protected override async Task OnMessageActivityAsync(ITurnContext turnContext, CancellationToken cancellationToken)
{
	//creates our FAQ model
	FAQModel question = new FAQModel()
	{
		Question = turnContext.Activity.Text,//gets text from bot
		Answer = ""
	};

	//uses our trained model to predict our answer
	PredictionModel prediction = FAQBot.predictionEngine.Predict(question);

	//accuracy of prediction
	float score = prediction.Score.Max() * 100;

	//gonna check if we were accurate,if below a threshold we will ask them to clarify
	if(score > 60)
	{
		//sends our answer back to the bot
		await turnContext.SendActivityAsync(MessageFactory.Text(prediction.PredictedAnswer), cancellationToken);
		await turnContext.SendActivityAsync(MessageFactory.Text($"We think our answer to your question is this accurate: {score}%"), cancellationToken);
	}
	else
	{
		//sends them suggestions that are clickable
		await turnContext.SendActivityAsync(MessageFactory.Text("Sorry, we didnt understand the question, please try selecting a question below"), cancellationToken);
		string[] actions = {"What are your hours?","How can I reach you?", "What payments do you accept?"};
		await turnContext.SendActivityAsync(MessageFactory.SuggestedActions(actions), cancellationToken);
	}
}

Our chatbot is now finished. SendActivityAsync allows us to send a message back to the user. this can be called at any time during the process and is helpful for long running processes. Microsoft also provides us a set of activities, found in the MessageFactory, for interacting with users. We can easily send images, cards, or attachments using the different types found in the factory.

Testing the Chatbot

With our bot endpoint setup, we now need to test it out. Microsoft provides a tool for emulating a bot system which you can find here: https://github.com/microsoft/BotFramework-Emulator/releases. The emulator will emulate a Teams chat so you can see how the responses will interact with Teams and other Microsoft products. Download the latest version, and run the application. Once the emulator is running, open your site by the URL http://localhost:{port}/api/messages. You should see a successful connection and the message "Hello and welcome!" This comes from our bot's "OnMemberAddesAsync" function found in the FAQBot.cs file. The final step is to ask the bot a question and test out the functionality.

Clone the project

You can find the complete project here: https://github.com/fiveminutecoder/blogs/tree/master/FAQBot

Microsoft, Bot, Chatbot, Bot Framework, Teams, Microsoft Teams, ML.NET, Machine Learning, AI, Artificial Intelligence, C#,C Sharp, NLP, Natural Language Programming, Robot

Create an Azure Document Queue for Loading and Tagging SharePoint Documents - Part 2

 

Previously...

In my last post, we created a client application that reads our file systems and creates an Azure queue item for uploading our data to SharePoint which can be found here: https://fiveminutecoder.blogspot.com/2020/10/creating-azure-document-queue-for.html 

We did this to get around limitations in SharePoint Online that cause throttling when trying to create large batches of document uploads. The second part will cover creating a web job that continuously checks the queue for new items and when it discovers these items the job will perform the upload to SharePoint.

**It is worth noting, that .NET 5 has recently came out and this post uses it. Unfortunately .NET standard and .NET 5 do not support cookie based authentication, username/password, anymore and require Azure AD integrations. So this solution turned from a 5 minute demo to a 10 minute demo. Mostly because of the configuration in Azure, and switching from CSOM . The code for Graph adds additional steps because you need site and list ids instead of using the name which CSOM supported. This is the way Microsoft recommends so I updated the code to reflect those changes**

Prepping SharePoint

Before we can create our job we need a destination. If you do not have a SharePoint environment please sign up for a free account here: https://developer.microsoft.com/en-us/microsoft-365/dev-program . Once you have an account, we need to create our library. To do this go to site contents -> new -> document library and create a new document library called "CustomerDocs". With our library created, we need to create our 3 columns"State, City, AccountNumber". Please make sure to use list settings to create the columns. If you use the new column button on the library home page the internal column names will not match and you will need to find them. Creating the columns in library settings our internal columns names will match what we type in. It is also important to point out to not use spaces when creating the columns. This will cause some URL encoding in the column name making it harder to develop. Once the column is created you can change the name without affecting the internal name.

Prepping Azure

Azure AD comes free with your O365 subscription, and this is where we will register our app. Login to Azure and go to Azure Active Directory. From there you will find "App Registrations". This is where we will register our new app that will upload our documents.

we will name our app something we will remember, I chose Graph API Document Upload. We will then set our supported account type. This setting will not matter for us since we will use a token to access the API, so choose "Accounts in this organizational directory only" so that it is secure. You can leave the web blank, we will set that up next.

Once your app is created note the Application ID and Directory ID in the overview section, we will need these for our app to authenticate. Moving down the left hand navigation click Authentication. If you don't have a platform, click add platform, and select web.

It will then ask for a redirect URL. We can put anything in here since our app will not be using Azure AD credentials to login, we want the token. So I just put https://localhost. What we want form our platfomr is the implicit grant token. Find the section that says Implicit grant and check the boxes for access tokens and ID tokens, and save.

Continuing down the left navigation click on Certificates and secrets. From here, click click New Client Secret. This is a one time code, if you do not capture it when creating you will need to create another one.

Finally we can give our app permissions to SharePoint. In the left navigation, go to API permissions. You should see the default permission for graph "User.Read" we will click add permission. Find Graph API. 

We want Application permission and then search Sites. Expand and give Sites.ReadWrite.All. Manage all will also work if you do not want to use the preview.

Finally, we need to grant the app permission. At the top next to "Add a permission" click Grant admin consent for...

Azure is now configured for our application.

 Creating a Web Job in 5 minutes

A web job is just a console application that runs on a background process of an azure web application. These jobs can be set to run in intervals or continuously. Jobs will also scale with the azure web service, giving you more instances as you scale out your web application.

Since we will be creating a web job in 5 minutes, this blog post will not go into details about configuring the job using code, we will focus on configuration using the portal instead.

To start, we will create a new console application and name is "SharePointMIgration_WebJob". Our web job will require several nuget packages. We will use O365 Graph API to perform our document upload operations. 

Packages to install:

• Microsoft.Graph.Core
• Microsoft.Graph
• Microsoft.Identity.Client
• Azure.Storage.Blobs
• Azure.Storage.Queues

Now that we have our nuget packages installed, we can start developing our web job. For the most part, our process will just be the reverse of adding items to the queue. We will need to manage the queue by popping items when finished uploading to SharePoint. We will need to bring over our CustomerMetadata model we created in the first part of this blog since it represents our queue data.

namespace SharePointMigration_WebJob
{
    public class CustomerMetadata
    {
        public string State {get; set;}
        public string City {get;set;}
        public string AccountNumber {get;set;}
        public string FileName {get;set;}
    }
}

Now that we have our data model covered, we will want to pull our data from our queue. The Azure queue has some interesting features, we can peek or receive an item in the queue. Peek allows us to look without a locking the item, while receive will put a temporary hold on the item so other jobs cannot receive or pop the item from the queue. The queue also allows us to do batch requests, this will help with throttling the queue. Our queue function will take two parameters one for the number of messages we want to receive from the queue and how long we want to lock the queue. In our example we will want to load a document every 10 seconds, which comes to approximately 30 documents every 5 minutes. In our function the parameters will be set to the defaults of 1 message and a lock of 30 seconds. When we call the function, it will pull a larger batch.

public static async Task GetFileMetaDataFromQueue(int MessageCount=1, int QueueLock=60)
{
	//calls the queue and pulls messages for processing
	QueueMessage[] queueMessages = await queue.ReceiveMessagesAsync(MessageCount, TimeSpan.FromSeconds(QueueLock));
	
	return queueMessages;
}

Now that we have our queue items, we will need to get the document that is associated with the queue. Documents are returned as streams, and the Graph API expects a stream to write to SharePoint. So we will just return the stream from our blob storage.

public static async Task GetFileFromAzureBlob(string FileName)
{
	BlobClient blobClient = new BlobClient(cs, "customer", FileName);

	using (BlobDownloadInfo downloadInfo = await blobClient.DownloadAsync())
	{
		MemoryStream stream = new MemoryStream();
		
		downloadInfo.Content.CopyTo(stream);
		return stream;
		
	}
}

Pulling metadata and documents from Azure queue is straight forward, the Graph API is not. In older SharePoint libraries (SSOM, CSOM, JSOM) you could get sites and libraries by title. With Graph everything is ID based. That means we need to get our site, library, and folder ids before we can add/update SharePoint. These do not result in additional calls, CSOM required us to pull them first as well, but it does make it more challenging to code. Before we can get our IDs we need to setup Graph to use our new app credentials. To do this we will create a function that authenticates to Azure and returns our token.

public static async Task GetGraphAPIToken()
{
	string tenantId = ""; //realm
	//some service account to upload docs. Documents cannot use app
	string clientId = ""; 
	//service account password
	string clientSecret = ""; 
	string[] scopes = new string[] {"https://graph.microsoft.com/.default" };
	IConfidentialClientApplication app = ConfidentialClientApplicationBuilder.Create(clientId)
				.WithClientSecret(clientSecret)
				.WithAuthority(new Uri("https://login.microsoftonline.com/" + tenantId))
				.Build();

		AuthenticationResult result = await app.AcquireTokenForClient(scopes).ExecuteAsync();

		//result contains an ExpiresOn property which can be used to cache token
		return result.AccessToken;
}

Now that we can pull our token, we will create a function that pulls our Ids. We only need to do this once and will call it at the beginning our our main function to reduce the amount of calls to the Graph API. The Ids are stored in fields so they can be accessed by other functions.

public static async Task GetSiteandListIDs()
{
	//Crate our graph client
	GraphServiceClient graphClient = new GraphServiceClient("https://graph.microsoft.com/v1.0", new DelegateAuthenticationProvider(
		async(requestMessage) =>{
			string token = await GetGraphAPIToken();
			requestMessage.Headers.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", token);
		}
	)); 

	//Gets the root site, if your library lives somewhere else you will need the collection and find it.
	var sites = await graphClient.Sites.Root.Request().GetAsync();
	siteId = sites.Id;

	//gets all libraries. Since our app is written in 5 minutes it is easier to filter the entire collection
	var libraries = await graphClient.Sites[siteId].Drives.Request().GetAsync();
	var library = libraries.First(f => f.Name == "CustomerDocs");

	libraryId = library.Id;

	// gets root folder of our library
	var rootFolder = await graphClient.Sites[siteId].Drives[libraryId].Root.Request().GetAsync();

	rootFolderId = rootFolder.Id;
}

We are ready to start uploading documents to SharePoint. We will follow similar steps for uploading documents as we did for getting our Ids. It is important to note that O365 now refers to libraries as drives. This is because the same functionality can be applied to OneDrive and Teams, which are all backed by SharePoint libraries.

public static async Task UploadDocumentToSharePoint(Stream FileStream, CustomerMetadata Metadata)
{
	//Gets graph client. We get this each time to make sure our token is not expired
	GraphServiceClient graphClient = new GraphServiceClient("https://graph.microsoft.com/v1.0", new DelegateAuthenticationProvider(
		async(requestMessage) =>{
			string token = await GetGraphAPIToken();
			requestMessage.Headers.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", token);
		}
	)); 

	//Uploads our file to our library
	DriveItem createDocument = await graphClient.Sites[siteId].Drives[libraryId].Items[rootFolderId].ItemWithPath(Metadata.FileName).Content.Request().PutAsync(FileStream);
	
	//Our metadata for our document
	FieldValueSet custData = new FieldValueSet{
		AdditionalData = new Dictionary()
		{
			{"State", Metadata.State},
			{"City", Metadata.City},
			{"AccountNumber", Metadata.AccountNumber}
		}
	};

	//sets the metada properites for our item
	await graphClient.Sites[siteId].Drives[libraryId].Items[rootFolderId].ItemWithPath(Metadata.FileName).ListItem.Fields.Request().UpdateAsync(custData);

	//try checking in file, some libraries it is required 
	try
	{
		await graphClient.Sites[siteId].Drives[libraryId].Items[createDocument.Id].Checkin().Request().PostAsync();
	}
	catch(Exception ex){
		//ignoring this error becuase library is not set for checkin/out
		if(!ex.Message.Contains("The file is not checked out"))
		{
			throw ex;
		}
	}
}

Our documents now sit in SharePoint with metadata. The last thing to do is clean up our queues. Remember Microsoft charges an average amount that is in our blob storage, so keeping it with minimal documents as possible will keep costs down. We will use our PopReceipt and MessageId to remove any queue items. We dont want them back in the queue looking for items again, that will break our loop.

public static async Task RemoveItemFromQueue(string MessageId, string Receipt)
{
	await queue.DeleteMessageAsync(MessageId, Receipt);
}

public static async Task RemoveDocumentFromQueue(string FileName)
{
	BlobClient blobClient = new BlobClient(cs, "customer", FileName);
	await blobClient.DeleteAsync();
}

Now that we have all our functions we can put our main loop together. This is a continuous job, so all of our logic will sit in a while loop to keep it running forever. We will get our Ids from the Graph API, and then pull from the queue. Once we have the queue we loop through our items and upload to SharePoint. We will sleep every once and awhile to make sure our app runs at an appropriate speed for SharePoint and eliminate any throttling.

//making this a property so we do not have to keep recreating the object
static QueueClient queue; 
//connection string to our storage account, it is shared between blobs and queues.
static string cs = "";

//The site ID our library lives in
static string siteId = "";
//The ID of the library
static string libraryId = "";
//The ID of the root folder in the library
static string rootFolderId = "";

static async Task Main(string[] args)
{
	queue = new QueueClient(cs, "customer");
	int batchMessageCount = 30; //number of items to pull from queue at once
	int queueLock = batchMessageCount * 10; //number of batches time 10 since each message will take 10 seconds to process.

	//
	await GetSiteandListIDs();

	//Creating an infinite loop for our continuous job
	while(true)
	{
		DateTime startTime = DateTime.Now;
		try
		{
			Console.WriteLine("Getting queue");
			QueueMessage[] messages = await GetFileMetaDataFromQueue(batchMessageCount, queueLock);
			Console.WriteLine("Found {0} items in the queue", messages.Length);
			foreach(QueueMessage message in messages)
			{
				//our cleint job encoded the message, this will decode it
				string data = HttpUtility.UrlDecode(message.MessageText);
				CustomerMetadata customer = JsonConvert.DeserializeObject(data);

				Console.WriteLine("Pulling document {0}", customer.FileName);
				using(MemoryStream document = await GetFileFromAzureBlob(customer.FileName))
				{
					Console.WriteLine("Uploading document {0}", customer.FileName);
					await UploadDocumentToSharePoint(document, customer);
				}

				Console.WriteLine("Upload was successful, removing {0} from the queue", customer.FileName);
				//remove message from queue so it doesnt get pulled again since we were successful
				await RemoveItemFromQueue(message.MessageId, message.PopReceipt);
				//remove document from storage
				await RemoveDocumentFromQueue(customer.FileName);

				//sleep 10 seconds before next call to sharepoint to prevent throttling.
				System.Threading.Thread.Sleep(10000);
			}
		}
		catch(Exception ex)
		{
			Console.WriteLine("Error writing queue to SharePoint: " + ex.Message);
		}

		Console.WriteLine("Finished with current queue list, will wait 5 minutes from last call");
		//we want our job to sleep if it takes less than 5 minutes to process the queue. This is to prevent throttling
		DateTime endTime = DateTime.Now;
		double totalMinutes = endTime.Subtract(startTime).TotalMinutes;

		if(totalMinutes < 5)
		{
			double sleepTime = (5-totalMinutes) * 60000;
			System.Threading.Thread.Sleep(Convert.ToInt32(sleepTime));
		}
	}
	
}

Deploying our Web Job

With our web job complete we need to publish it to Azure. To do do this, we just need to create a release and zip the contents. To create a realease, run the command below:

dotnet publish -c Release

Next we need to add our web job to our Azure web app. If you do not have a web app in Azure you can create one for free. One thing to note, is a web job runs on a schedule so for this demo a free account will work to test our job. A web job will not run in a production environment unless your app is set to be "always on". Which is not allowed in a free version.

Once your application is created, find the web jobs section under setting in the left navigation.

Select "Add" at the top and enter a friendly name; I chose SPSync. Next select the file to upload. From the upload screen go to where you published the .NET project, which should be the bin folder of your web job project if you followed this tutorial. In the bin folder you will see a release folder. Click in and depending on your dotnet core version you should see another file, mine says ".net 5.0.Zip". Zip this file up and upload the zip.

Lastly, let us set the job to continuous so it will continuously check our queue for new documents.

Once our job publishes, you will see its status changed to running. Click into the logs to review that items are loading. Go back to your queue and the documents will be gone!

Clone the project

You can find the full project on my GitHub site here https://github.com/fiveminutecoder/blogs/tree/master/SharePointMigration_WebJob

Azure, SharePoint, O365, SP, C#, Storage, Storage Account, Blob Storage, Azure Blob, Azure Blob Storage, Azure Storage Account, Azure Blob Storage Account, Azure Queue, Azure Queue Account, Queue, Serverless development, devleopment c sharp, 429, throttling, SharePoint Online, Office 365

Create an Azure Document Queue for Loading and Tagging SharePoint Documents - Part 1

 

Why do we need a document queue?

SharePoint Online limitations

SharePoint is not known for being the most developer friendly platform, in fact the Stack Overflow survey indicates it being the most dreaded platform by developers https://insights.stackoverflow.com/survey/2018 . With so many companies moving to O365 and SharePoint online, developers have had even more struggles designing custom solutions on top of SharePoint.

One of the most frustrating of those is the dreaded 429 error, or when SharePoint throttles you. It is expected that using a software as a service product will come with some throttling. Microsoft does not want us bombarding their servers with requests, but there is no clear point on what throttles their system. This is a guess for all, and Microsoft determines this at random times. The best way to reduce throttling is to limit your calls. When dealing with list items this can be done by batching requests either using the REST or Graph API batch calls, but uploading documents does not have this luxury.  

Documents require several steps to upload a document with it's metadata which makes it very prone to throttling if you have several documents to upload. Looking at a typical CSOM call for a SharePoint upload we see about 5 calls  to upload and tag a document:

1. Call to get the list

2. Call to add document

3. Call to get the document after uploaded for list item properties

4. update the list item properties.

5. call to check in document

We can reduce these calls down to 3 using the REST API, but it still requires more calls than a list item. Uploading 1 or 2 documents at a time will not throttle your requests, but what if you need to migrate a shared drive, or you have an application that compiles refunds into a document that can be sent back to your customers? This is when throttling will begin. The solution: limit the number of calls to SharePoint. From experience, it seems 30 documents a minute is the maximum before you start to get throttled, this of course depends on how many total documents you are uploading. 30 documents once a day is fine, but if you are doing batches of 500, 30 documents continuously over a 20 minute period will start to throttle your user account. 

Getting performance out of my SharePoint app while limiting calls to SharePoint.

Knowing I need to make at least 3 calls a document how can I create an app that is performant but does not cause a system to throttle? The answer is Azure. Azure does have throttling limits but they are significantly higher thresholds than SharePoint. Using a storage account, we can create an intermediary between our application and SharePoint. Blob storage is fairly cheap in Azure. At the time of this writing, Azure charges for the average storage used not the total. What this means if  I am storing 2 GB worth of documents at a time I am charged for 2GB worth of storage. Even that means I load 2 GB's a day and remove it. 

A storage account also has several other features like creating a queue which can be used to hold our metadata. We can push and pop into the queue to reference our document,  and metadata by creating a simple JSON string. 

Once our documents are in Azure, we don't care how long it takes. Our users can continue on while we trickle load our documents into SharePoint using a web job.

Creating a SharePoint migration tool in 5 minutes - Part 1

Since our application is going to be split up into two parts, this post will be split up into two parts as well. Part one will cover creating a tool that will upload our documents to Azure. While Part 2 will cover the job that uploads and tags our documents in SharePoint.

The Data

Our data is simple, we are going to assume we have a file share that is storing our customer records. Our folder structure will be setup as so:

\\customers\{state}\{city}\{account number}

With this file folder setup I can extract my tags for state, city and account number for my documents.

Setup Azure Storage Account

If you do not have an Azure account please create a free one before continuing. To get started click Create Resource and search for "Storage Account". Create the resource and follow the prompts to create a new resource group, and storage account name. For the purposes of this blog, you can leave the rest as the default values.

Once our account is created we can see the options for blob and queues.

First click into containers, and at the top click "+ Container". We will name our container "customer". Repeat the same step for Queue but click "+ Queue" and name it "customer". Azure is now ready for us to start loading documents.

In order to authenticate to Azure, we will need a connection string. In the left hand navigation find Access Keys. Click into Access Keys and select "Show Keys" to expose your connection string. Once we have our connection string we can setup our application.

Creating the Project

For our migration tool, we will create 2 project. The first project covered in this blog is a console application that will run on our local machine and upload the documents to Azure. Our second project, will be a web job that runs in Azure and will continuously look for objects in the queue and try to upload them to SharePoint.

Let us start off by creating our first project called "SharePointMigration_Client". Once our console app is created we need to install 3 nuget packages (CTRL+ALT+P in VSCode). The 3 packages we need are:

• Azure.Storage.Queues 
• Azure.Storage.Blobs
• Newtonsoft.JSON

The firsts two will give us references for accessing azure storage, and Newtonsoft is just a convienent way to serialize our object for the queue. This project will also require a reference System.Threading.Tasks to allow for async calls to Azure storage. Now that are project and references are created, we need to add a new class object to represent our SharePoint metadata. I called this file simply "CustomerMetadata.cs"

namespace SharePointMigration_Client
{
    public class CustomerMetadata
    {
        public string State {get; set;}
        public string City {get;set;}
        public string AccountNumber {get;set;}
        public string FileName {get;set;}
    }
}

Now that we have our customer object, we can define to properties; one for our queue, and another for our connection string to Azure. I define the queue as a property just so we can open the queue once, the blob container needs to be opened per document. The connection string is the same for our blob container and our queue so referecing it as property allows me to define it once and move on.

//making this a property so we do not have to keep recreating the object
static QueueClient queue; 
//connection string to our storage account, it is shared between blobs and queues.
static string cs = "Connection string from Azure";

The first function to define is our directory reader. This is a recursive function that will read all of the documents and sub directories found in our root directory "customers". When it finds a file it will upload it to Azure otherwise it will continue onto the next directory.

//Recurssive function for iterating directory and sub directories
public static async Task GetFilesInDirectory(string FileDirectory)
{
	Console.WriteLine("Looking for files in " + FileDirectory);
	string[] files = Directory.GetFiles(FileDirectory);

	foreach(string file in files)
	{
		Console.WriteLine(file);
		await UploadFileToAzureBlob(file);
		await UploadFileMetaDataToQueue(file);
	}

	string[] subdirectories = Directory.GetDirectories(FileDirectory);

	foreach(string subdirectory in subdirectories)
	{
		//Recursion for going thorugh all directories
		await GetFilesInDirectory(subdirectory);
	}
}

Once a file is found, we will need to upload the file to Azure. I prefer to upload the file before adding it to our queue since file uploads have more of a chance of breaking due to file size. The call to Azure is pretty straight forward:

public static async Task UploadFileToAzureBlob(string FilePath)
{
	string fileName = Path.GetFileName(FilePath);
	Console.WriteLine("File name {0}", fileName);
	//customer is the name of our blob container where we can view documents in Azure
	//blobs require us to create a connection each time we want to upload a file
	BlobClient blob  = new BlobClient(cs, "customer", fileName); 

	//Gets a file stream to upload to Azure
	using(FileStream stream = File.Open(FilePath, FileMode.Open))
	{
		await blob.UploadAsync(stream);
	}
}

Our file is now loaded into Azure, you can go back to your Azure storage account and view it in our customers container. Next we will upload our metadata. Since this is a five minute tutorial we can assume our directory is setup correctly and we do not need to parse or check our directory for the metadata. We will just split the directory by '\' and reference the position in the array. In a production environment, we would want to check to make sure our array is filled correctly. Also, note in this function I use "HTTPUtility.UrlEncode". I have found that Azure is picky about characters and can break if we do not encode the string. Specifically around non-UTC-8 characters.

public static async Task UploadFileMetaDataToQueue(string FilePath)
{
	string[] metadata = FilePath.Split('\\');

	//we know our metadata position because of our file structure. 
	CustomerMetadata customerMetadata = new CustomerMetadata()
	{
		State = metadata[2],
		City = metadata[3],
		AccountNumber = metadata[4],
		FileName = metadata[5]
	};

	//create a string for our queue
	string data = JsonConvert.SerializeObject(customerMetadata); 
	//We do this to ensure any non UTC-8 characters are safe for the web service
	data = HttpUtility.UrlEncode(data); 

	//adds message to back of the queue
	await queue.SendMessageAsync(data);
}

That's it, we just need to await our file directory sync and watch as files are switfly uploaded to Azure.

static async Task Main(string[] args)
{
	
	//create our queue client
	queue = new QueueClient(cs, "customer"); //customer is the name of our queue where we can view the items uploaded in Azure
	
	await GetFilesInDirectory("c:\\customers");
	Console.Read();
}

Results

After a successful run, you should see your files in your blob and metadata in your queue.

Continue to Part 2

In the next part, we will create a web job that reads the Azure Queue and uploads the documents to SharePoint.

Creating an Azure Document Queue for Loading and Tagging SharePoint Documents - Part 2

Clone the project

You can find the full project on my GitHub site here https://github.com/fiveminutecoder/blogs/tree/master/SharePointMigration_Client

Azure, SharePoint, O365, SP, C#, Storage, Storage Account, Blob Storage, Azure Blob, Azure Blob Storage, Azure Storage Account, Azure Blob Storage Account, Azure Queue, Azure Queue Account, Queue, Serverless development, devleopment c sharp, 429, throttling, SharePoint Online, Office 365