For the Help Desk dashboard to keep track of the current Support Tickets, we need to store and retrieve that information from a database.  In our case, we’ll utilize SQL Azure because cloud stuff is awesome!

Really, our database is incredibly simple.  I won’t cover creating a SQL Azure database in depth since there are many, many tutorials available on the Internet for this.  Our particular application just requires a single table, the SupportTicket table, which looks like this:

The columns store data as follows

• MessageID: the Exchange Online message ID of the original e-mail message.
• AssignedTo:  the e-mail address of the Help Desk Operator.
• Status:  the status of the Support Ticket – just a simple ‘Open’ or ‘Closed’.
• Title:  the subject of the original e-mail message.
• Description:  the body of the original e-mail message.

Note:  If you don’t have the Design option available in SQL Server Management Studio, then you’re probably still running against SQL Server 11.  This can be upgraded in the Azure portal.  The upgrade can take from a few hours to a few days, depending on the size of the database.  However, the database is still accessible and functional during this upgrade.

To access the database, we’ll use Entity Framework.  For this application, we’ll just add a Data folder to the project since the model is going to be so simple and straight forward.  However, in a full scale application, it would probably make a lot of sense to create a full scale data layer here.  Once the Data folder has been created, we can right click and Add –> New Item…

In the window that opens, select ‘Data’ in the left column, choose ‘ADO.NET Entity Data Model’, and give the model a name:

On the next screen, choose ‘Code First from database’ and click ‘Next’:

If the database connection is already listed in the drop down, select that.  Otherwise, click ‘New Connection…’:

In the window that opens, enter the SQL Azure Server name, select ‘Use SQL Server Authentication’, enter the User name and Password, click the drop down under ‘Select or enter a database name’, choose the appropriate database name, and click OK:

If this connection was successful, the previous screen should now be loaded with the database information.  In this demo application, we’ll just choose ‘Yes, include the sensitive data in the connection string.’.  However, in a production setting, there may be better choices.  Click ‘Next’.

The wizard will read the various metadata of the database server and present options to include in the model.  We’ll expand Tables and then dbo, select the SupportTicket table, then click ‘Finish’.  Note, leaving the ‘Pluralize or singularize generated object names’ will allow Entity Framework to name the collection ‘SupportTickets’ on the database context.

If all goes well, we’ll find two new files in our Data folder, SupportTicket.cs and HelpDeskDatabase.cs.  SupportTicket.cs is the C# object that represents a record in the SupportTicket table.  HelpDeskDatabase.cs is the DbContext that actually manages all of the operations against the database.

This operation has loaded a handful of references to the project.  It has also added a ‘HelpDeskDatabase’ connection string to the web.config file.

That’s it!  The application is now wired up to the database and can perform all of the standard CRUD operations against our SupportTicket table.  Sweet!

The Help Desk Demo

• The Help Desk demo, Part 1 – Creating the project
• The Help Desk demo, Part 2 – Azure Active Directory
• The Help Desk demo, Part 3 – Authentication
• The Help Desk demo, Part 4 – Microsoft Graph
• The Help Desk demo, Part 5 – SQL Azure
• The Help Desk demo, Part 6 – SharePoint
• The Help Desk demo, Part 7 – Wiring it all up
• The Help Desk demo, Part 8 – Deploying to Azure

The entire source code for the Help Desk demo can be found here https://github.com/OfficeDev/PnP/tree/dev/Solutions/BusinessApps.HelpDesk/, in the Office 365 Dev PnP GitHub repository.