Greetings and Salutations, we are going to start our full-stack developer journey by building a site that can serve as a showcase for our skills both in software development and in general.

The Git repo where you can find resources can be found here.

The idea behind:

• We need a presentation site that can show both our skills as we say they are, but also show them as we know they are
• We have so many options / templates, but we want to show that we are full-stack developers
• We want to have a control over the pages, elements and versions, but not with always changing the app’s code
• IDEA: Let’s build a CRM for our Site and the actual Site based on the content

Disclaimer: We will do a lot of templating and since we won’t secure our app in this tutorial it is prone to SQL Injection, but we will build the site to be immune to it. (or minimize the damage)

Prerequisites

We are going to need some software in order to create my proposed project:

• Microsoft SQL Server, we are going to start local and in a future post we will learn how to deploy to Azure our local setup.
  • We can install it locally or work with Docker, I suggest docker to make it portable.
  • To manage it I suggest using SSMS
• An ASP.NET Core Web Application (MVC is my suggestion) that will use the DB information and display an amazing site with 2 angles
  • The first one is a simple CRM for us to manage our site further without any additional codding
  • The second one is the result of the CRM that will be the actual site for any user

Mandatory

Those are the minimum software pieces that we need in order to accomplish building this website.

SQL Server on Docker

After you have installed Docker basically we are going to pull the image for SQL Server 2019 by running (in PowerShell ideally):

sudo docker pull mcr.microsoft.com/mssql/server:2019-latest

Now we need to run the command that will create a container for us with SQL Server 2019 where we will start working and for that we need to accept the EULA and configure the authentication and ports by running (in PowerShell ideally): :

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=parolaAiaPuternic4!" -p 1433:1433 --name msDemo -h msDemo -d mcr.microsoft.com/mssql/server:2019-latest

After this our SQL Server 2019 on Docker is up and running, ready for our DB commands.

Visual Studio Community

We will use this IDE to develop our web application and for this we are going to need the following toolkits:

• ASP.NET and Web Development
• .NET cross-platform Development
• “Azure development” – OPTIONAL

EF Core CLI

We will start with the database and then use this tool to map it to our web app code. Entity Framework Core will help us talk to the database by using C#.

Nice to Have

• SSMS is my choice of IDE to work with SQL Server
• PowerBI Desktop is a nice tool for Visually Rich Reports

Building the Database

Connecting

Now we are going to start working with the database and for this I will show you a few pieces but you have the whole script to create the database in the Git Repository. I will work using SSMS and to connect to your (running) docker instance we will need to have the login screen setup like in the following image (you know the username and password from the command that created the Docker Container):

Creating a new database for our project

In general it is recommended to have a database/project but for cost-saving reasons sometimes we are forced to run multiple projects on the same DB, but since we are doing this setup locally we can create a new DB. Sure, we can use a command (you have it in the script), but we can also use the UI:

Right Click > New Database > Use the name “aboutMe” (will help us later when running commands to have the same name)

Building our Tables

After thinking a bit, we want to achieve the following DB Architecture:

We have 3 core components and some historical tables for 2 of them:

• MyInfo is basically the “Settings” part of the app
• Navigation will help us link the content to pages to app logic
• PageContent will help us change pieces of our site without writing any code to do it (once we have written enough code)

Time to create them and let’s start with MyInfo:

Right Click on Tables > New > Table

After filling some information (you have the types and the names of the columns in the script) we will get something like:

To set the primary key we need to: Right Click the Cell before “InfoName” > Set Primary Key (and now we should have a key Icon on the left of the InfoName)

And to change the name of the table we need to press F4 and a tab will pop from the right with the properties of the table and we can change the name:

Now we can save by pressing “CTRL” and “S“.

Time to create all the other tables by doing the same thing and to add a Foreign Key we first need to make sure that the table with the Primary Key exists and then, similar to what we did for the Primary Key:

Right Click the Cell before desired column > Relationships… > Add > Tables and Columns Specifications >

And now we set what we want to set.

Time for some History

As in historical tables… Historical tables are nice tables that reflect the continuous evolution of our tables in time and they are usually present when we have LIVE tables and to build them on any change (sounds like a TRIGGER word) we put the changes in the historical table as well with a versioning helper and to do that we need some code… like the following code (taken from the script, of course):

CREATE TRIGGER [dbo].[HistoricalNavigation]
   ON  [dbo].[Navigation]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    INSERT INTO [dbo].[NavigationHistory]
    SELECT [Id]
      , [MenuName]
      , [MenuOrder]
      , [NavigationPath]
      , (SELECT COUNT(*)
        FROM [PageContentHistory] PCH
        WHERE PCH.[Id] = inserted.[Id])[Version]
    FROM inserted

    DECLARE @MaxVersion INT, @SecondMaxVersion INT;

    SELECT @MaxVersion = COALESCE(MAX([Version]), 0)
    FROM [NavigationHistory];

    SELECT @SecondMaxVersion = COALESCE(MAX([Version]), 0)
    FROM [NavigationHistory]
    WHERE [Version] <> @MaxVersion;

    IF (@MaxVersion - @SecondMaxVersion > 1)
		BEGIN
        INSERT INTO [dbo].[NavigationHistory]
        SELECT [Id]
		  , [MenuName]
		  , [MenuOrder]
		  , [NavigationPath]
		  , (SELECT COUNT(*)
            FROM [NavigationHistory] NS
            WHERE NS.[Id] = [Navigation].[Id])[Version]
        FROM [dbo].[Navigation]
        WHERE [Id] NOT IN (SELECT [Id]
        FROM [NavigationHistory]
        WHERE [Version] = @MaxVersion);
    END
END
GO
ALTER TABLE [dbo].[Navigation] ENABLE TRIGGER [HistoricalNavigation]
GO

And to explain it, because only the first insert is “at hand”

• For versioning we will use a trick, counting how many previous versions we have for the specific element + 1
• When having a difference between elements greater that 1 we need to draw a line for all unchanged elements, CASE 1
• To do so, we need to get the max version and the second max, compare them and if the gap is too big make sure that we align the versions as good as we can so we can at most have a difference of 1 between elements
• Aligning means that we use the latest version available for the elements that do not have it

We will do the same for elements.

CASE 1

We want to display the site by version and we basically have to cover 2 cases:

• Latest Version: cool, we go to the LIVE table
• Previous Versions: now we have to go to the Historical table and grab the elements for that version or for some version

Now the elements will also have versions and with the same logic so what happens when we want V7 for the elements and only one has V7, another 2 have V6 and the rest have at most V5. Do we get V7 for the elements that we can and V5 for the rest because that is aligned? We get the max version less or equal than V7?

To simplify the reasoning behind the decision we will have at most 1 version difference in the elements meaning that if we want V7 and for an element we don’t have it we will sure have V6.

Procedures…Procedures

For fetching the data we will need some stored procedures that will fetch us the required elements for the required version and for that we will…code once again (or take it from the script, of course) :

CREATE PROCEDURE [dbo].[GetSiteForVersion]
    @VersionNumber INT NULL = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF @VersionNumber IS NULL
	BEGIN
        SELECT [Id]
		  , [MenuName]
		  , [MenuOrder]
		  , [NavigationPath]
        FROM [aboutMe].[dbo].[Navigation];
    END
	ELSE
	BEGIN
        SELECT [Id]
			  , [MenuName]
			  , [MenuOrder]
			  , [NavigationPath]
        FROM [dbo].[NavigationHistory] NH
        WHERE [Version] = (SELECT MAX([Version])
        FROM [NavigationHistory]
        WHERE [Version] <= @VersionNumber
            AND NH.[Id] = [Id])
    END
END

The trick here is that if we won’t provide a parameter for the version we will always go with the latest (LIVE) and if we do provide we go in the Historical table with the same logic for Versioning.

Same for Page Elements.

Building the Web App

Now it is the final part (and the one that requires mostly clicks and copy-paste because we won’t build the templates together, I will just drop them to you) which means that we need to go to Visual Studio.

Creating the Project

We will go with the following setup (make sure you pick the C# one):

Create new Project > ASP.NET Core Web App (Model-View-Controller) > Next

Now chose a Project name, you can go with anything (no spaces or special characters) and I will go with Showcase.Web (.Web is to help us separate the projects in the future) and place the Solution and the Project in the same folder to save path length.

Next

Now I am using the following configuration:

And after this we can click Create and we are done, our project is being created.

Getting the DB in Code

For this we will be using EF Core, you have it installed, by going (in Visual Studio) to the following menu:

Tools > Command Line > Developer PowerShell

This will launch PS in the root of our project and we can now stat giving commands to start scaffolding our DB, but first some requirements :

dotnet add package Microsoft.EntityFrameworkCore.Design -v 5.0.12
dotnet add package Microsoft.EntityFrameworkCore.SqlServer -v 5.0.12

Those will add the requirements, Design for scaffolding and SQLServer for the DB client. After they are done being added we can run the actual command:

dotnet ef dbcontext scaffold "Data Source=localhost,1433;Initial Catalog=aboutMe;Persist Security Info=True;User ID=SA;Password=parolaAiaPuternic4!" Microsoft.EntityFrameworkCore.SqlServer -o DB --force

This will create a new folder, or replace the content of it if files with the same name exist in the folder, called DB ( “-o DB –force“) and use the connection string for our created DB in the Docker. (if you have changed the username, password or catalog you need to adapt the above code to match yours)

Now we should have something like this in our solution:

Now there is only 1 thing stopping us for completing this step and that is adding the dependency for our database in Startup.cs:

services.AddTransient<aboutMeContext>();

This will make sure that every time someone asks for the database they will get it.

Scaffolding Controllers with Views

Now it is the time to generate a huge amount of code using the power of VS and EF Core:

Right Click on Controllers > Add > New Scaffolded Item > MVC Controller with views, using Entity Framework

From now on we will create pages for our important items using the recently scaffolded data context (aboutMeContext) for our important models:

• MyInfo
• Navigation
• PageContent

To create the components for MyInfo the setup should look like:

They will have some default naming conventions for the Controller Name, but the important one is that the name ends with Controller (default routing reasons). After the operation is completed we can see that we have a new Controller and a new set of Views for our Model:

Now we can press F5 and see how our solution looks for now. To access our recently created Controller we need to add /MyInfo at the end of the opened URL so in the end could look something like:

https://localhost:5001/MyInfo

And we should get a simple, but good-looking page that will help us build our site:

Now it is time to generate the components for the other 2 tables. You can also do the same steps for the Historical Tables, but for them it is recommended only to have Read options because they are generated by our logic.

At the end of this step we should have something like (a controller + a set of views for each scaffolded item):

Preparing the Templates

We are going to create a controller that can route to templates (I am using the plural because I have created 2 of them) while getting the proper data and then we are going to create the associated views for them.

The main idea for them is to write the things as generic as possible with well-defined blocks of code that we are going to fill from the database. There are also some helpers that will help us along the way, LINQ, data structures and the previously defined procedures.

The actual code is kind-of self-explanatory and reading/understanding it is not necessarily the scope of this, but if you have questions you can ping me.

The more interesting thing that we are going to do is in a shared component (the most important shared component in my opinion), we are going to edit _Layout.cshtml. We are generating the menus in a dynamical way based on our DB and for that we need to:

• Get the Navigation menus based on the Versioning Procedure
• Check the parameters and their numbers, some templates have more than others
• Create Links to our Routes (the trick is to write the routes Properly when inserting in the db)

The main piece looks like:

foreach (Navigation tab in allTabs.OrderBy(x => x.MenuOrder))
{
    string[] pathComponents = tab.NavigationPath.Split('/');
    int? tableTemplateId = pathComponents.Length > 2
        ? Convert.ToInt32(pathComponents[2])
        : null;
    
    if (tableTemplateId.HasValue)
    {
        <li class="nav-item">
            <a class="nav-link text-dark" asp-area=""
                asp-controller="@pathComponents[0]"
                asp-action="@pathComponents[1]"
                asp-route-tableId="@tableTemplateId">
                    @tab.MenuName
            </a>
        </li>
    }
    else
    {
        <li class="nav-item">
            <a class="nav-link text-dark" asp-area=""
                asp-controller="@pathComponents[0]"
                asp-action="@pathComponents[1]">
                    @tab.MenuName
            </a>
        </li>
    }
}

Data Filling

Now that the fun part, the code, is done, time to fill-in some data:

• For adding menus we need to go to our Navigation Controller (How you named it the excluding the word Controller needs to be added at the end of the URL)
• For adding page content we need to go to our PageContent Controller (How you named it the excluding the word Controller needs to be added at the end of the URL)

In the end my site looks like this:

Conclusion and Future Work

For paper readers this is no surprise. After our experimental results we draw some conclusion and point us towards the future. It was cool building this solution, but it also has some uncool parts.

Conclusions

I hope you enjoyed building the solution as much as I did, it was easy or at most decent, with or without my helpers because the IDEs helped us with a lot of code, routing and setup. Following the templating offered by ASP.NET and Scaffolding we were able to create some templates of our own that will make good use of our app.

As for what is not nice is that each time we need a new type of template, we need to write some code again. But this will our skills up to date.

What’s Next

There are many things that can be done and here we are going to talk about each, short, and I hope that I will follow up on each part.

App Side

We need to add some security, mostly for access to make sure that normal users can’t access our CRM part and administrative users can.

Other than that we need to add more templates and style them a bit more.

Going Online

The easiest way, for me, would be migrating those to Azure because it implies just some clicks and filling in some forms, but there are more classical ways to do it (basically exposing our site to the internet via Public Ip and then also using a DNS).

PowerBI

“You put it under Optional, where do we use it?”

We don’t or at least not in this post, maybe when this is presented LIVE, but in future posts, maybe.

Closing Notes

Thank you for reading up to this point and feel free to leave feedback/contact me for any question/request and I will try to fulfil them as time allows.