Part 7 - Server Side Database Setup for your Mobile App

Now that the client side app is in fairly decent shape, we should start preparing the back-end to accept requests from the client side mobile app.  In this post, we’ll replicate our client side view models on the server and create database objects using the code-first method.

Open Visual Studio 2017 to the invoicing solution we setup in Part 2 of this mini-series.  We’ll first get the easy part out of the way by duplicating our client side View Models.

Let’s recall what our LoginViewModel.ts from the client side looks like.



Now, to replicate on the server side.  Right-click on the ViewModels folder in the Invoicing.Core project and click Add -> Class.  Name the class LoginViewModel.cs to match our client side class name.



Next, add the two properties from the client side ViewModel into the server side ViewModel and add "public" in front of your class so it is accessible from other classes.



Save your changes, and continue through the rest of your ViewModels until all are replicated.











Piece of cake.  Now, let’s get the database setup.  First let’s add the necessary dependencies for the Entity Framework using the NuGet Package Manager.  You can start the NuGet Package Manager from the Tools menu.  Select NuGet Package Manager, then Manage NuGet Packages for Solution.



Under the Browse tab of the "NuGet – Solution" tab, search for "Microsoft.EntityFrameworkCore" and click the first package that shows (also shown below in case your results vary).



Select both projects on the right and click Install and accept any user agreements or dependency requirements that pop up.





Next, search and install the following in the same manner.

Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.Sqlite
Microsoft.VisualStudio.Web.CodeGeneration.Tools
Newtonsoft.Json

You should now be able to expand the Dependencies folder and then the NuGet folder in your solution to check if everything is installed properly.  As you may have guessed, if we were to be connecting to SQL Server, you would instead add Microsoft.EntityFrameworkCore.SqlServer.  If you start with Sqlite now to follow along, you can easily deploy to SQL Server by changing a (yes, just one) line of code later on.



Now that we have the dependencies added to your projects, right click on the Data folder in your Invoicing.Core project and add a new class.  Name the class ApplicationDbContext.cs.  We’ll then inherit from DbContext in the Microsoft.EntityFrameworkCore namespace. This class will serve as your "Database".



Next, we’ll create a base class for our entity objects.  These objects will serve as your tables in your database.  The purpose of creating a base class is to provide consistency among your database tables.  We’ll want all of our tables to have an Id column, Created date/time, and Modified date/time.  This will also help prevent duplication among all of our entity classes.

Right-click on the Models folder and add a new class called EntityBase.cs.  Add the above mentioned properties and refer to the below image for additional annotations (brackets above the properties) that need added.  Resolve the using statements as we did with the DbContext above.



Next add a new class for ApplicationUser in the Models folder to represent the table to hold the users for your application.  We’ll need a field for Email and a field for Password.  Be sure to make the class public and inherit from the EntityBase object to get our default columns.  We’ll also specify any additional database constraints here using annotations.



Do similar for a Client entity and Invoice entity.  One twist to this is that we need to add a reference for ApplicationUser such that we know which user the clients belong to.  We'll skip adding an ApplicationUser reference to the Invoice since we can simply reference the attached client, but this can be done as well to provide simplicity down the road.





Now that we have defined our entities, we need to make sure they are added to our database (ApplicationDbContext).  So let’s open that back up and get them added.



There are a couple last things to wrap this up prior to creating our database.  We need to create a factory to build our ApplicationDbContext object.  This can be done right inside the ApplicationDbContext.cs file to keep everything together.  The gist of this extra class is to provide an access point to build the database with the included connection string.  The connection string in this case points to a file.

Note: The optionsBuilder.UseSqlite line is the line that can be changed to publish to SQL Server instead.  You'd specify UseSqlServer and provide the corresponding connection string.

Now that we have a way to create the ApplicationDbContext, we use it to create a migration file.  The migration file contains the code needed to create or upgrade your database to the latest version.  Each time we make changes to the entities, we’ll create a new migration file and then use it to publish to the database.  If you publish your project in multiple locations, the migrations will be tracked for each database and missing migrations will be applied in sequence to ensure your database is updated properly.

To create a migration file and update the database, we’ll run a few commands from the Package Manager Console.  You can access this similar to how we accessed the Package Manager earlier, via the Tools menu.  Select NuGet Package Manager and Package Manager Console.



When the Console opens up, ensure you select the correct project in the “Default project:” drop down at the top of the console window.  Since we’ll be running commands to setup our database that we added to the Invoicing.Core project, we need to ensure that is the project we have selected in the Default project box.



Run the following command to create your first migration file.

Add-Migration FirstMigration



Next publish all unpublished migrations to the database by running the following command (we only have one at this point).

Update-Database



Since we didn’t specify a direct path in our connection string, the database was created in the default project directory.  At this point you should have the following file.

C:\Users\Micha\Source\Repos\Invoicing\Invoicing.API\invoices.db

You can browse this file with the “DB Browser for SQLite” application by clicking Open Database and locating the file.

DB Browser for SQLite: https://sqlitebrowser.org/

We can now see that our database appears to be in good order.



Next we’ll start working towards building up our services, repositories, and UnitOfWork pattern to manage our application's back-end.