Introduction

Learning Web Technology is essential today because Internet has become the
number one source to information, and many of the traditional software applications have
become Web Applications. Web Applications have become more powerful and can fully
replace desktop application in most situations.

That’s why you need to know basic Web Programming, including HTML, CSS and
JavaScript. To create more powerful Web Sites and Web Applications you also need to know
about Web Servers, Database Systems and Web Frameworks like PHP, ASP.NET, etc.

It all started with Internet (1960s) and the World Wide Web - WWW (1991). The first Web
Browser, Netscape, came in 1994. This was the beginning of a new era, where everything is
connected on internet, the so-called Internet of Things (IoT).

Applications
We can separate applications in different categories:

• Desktop Applications
• Web Applications
• Mobile Applications
These will shortly be described below. In this textbook we will focus on creating web
applications using ASP.NET Core.

Desktop Applications
Desktop Applications comes in many flavors:

• Windows WinForms Desktop Applications (Windows Forms App)

• WPF Desktop Applications
• Universal Applications

Windows Forms Apps are the oldest but are still very popular.

Web Applications
Web Applications use a mix of different technologies and different programming
languages.

We have:

• Static Web Pages: HTML, CSS, JavaScript

• Dynamic Web Applications: PHP, ASP.NET, Django, etc.
 MobileApplications
So-called Apps has been very popular since the release of the first iPhone in 2007. Today we
have Apple products using iOS and we have Android devices.

We have:

• iOS
• Android • iPadOS
• etc.

The great thing about Web Applications is that they also work on Mobile platforms. Native
apps for Android phones and Apple devices typically need to be developed in a strict
environment and with specific programming languages and they only work for the platform
they are developed for. For Android development you typically will use the Kotlin
programming language in combination with Android Studio. While for development for the
Apple platform you will need a Mac computer and use the Swift programming language as
part of the Xcode development environment.

NET
The .NET is a development platform from Microsoft.

Previously we had the following:

• .NET Framework (latest version is .NET Framework 4.x)

• .NET Core (latest version is .NET Core 3.x)

.NET Framework was first introduces in 2002 and works only for the Windows operating
system, while .NET Core was introduced in 2016. .NET Core is cross-platform, meaning it
will work on Windows, Linux and macOS.

The newly released .NET 5 has merged .NET Framework and .NET Core into a more
unified package.

.NET Core and the newly released .NET 5 (that is built on .NET Core) will be the future
of .NET. Microsoft is still committed to support the previous .NET Framework for years to
come, but for new applications your choice should be .NET 5 either you want to develop
desktop applications (“Windows Forms App”) or web applications (“ASP.NET Core Web
App”).

You can use many different programming languages with .NET, but the “default” language
is C#, which is the language we will use in this textbook.

The .NET 5 (and newer) is aiming to be cross-platform, and it is supported on Windows,

macOS, and Linux when possible.

The .NET web site: https://dotnet.microsoft.com

3
Web
We have 2 types of web pages:

• Static web pages

• Dynamic web pages

Static web pages are pure HTML web pages where the contents is written in manually and it
doesn’t change unless the user updates the contents.

Dynamic Web Pages typically get contents from a Database and have deeper interaction with
the user.

Dynamic Web Pages using e.g., ASP.NET or PHP Executes Code on the Server-side and
Generates HTML Code that is sent to the Client (Web Browser). This is known as Server-
side code.

You can also create Dynamic content on the Client using JavaScript.

Static Web Pages typically contains Text and Images that is not changing (unless a person
changes the page and upload a new version). Static Web Pages are Pure HTML pages
Video:

Web Programming Overview: https://youtu.be/plRBYKbQSuE

ASP.NET
ASP.NET is an open-source web framework, created by Microsoft, for building
web apps and services using the .NET Framework or the .NET Core. We have both
ASP.NET and ASP.NET Core. ASP.NET Core is the new approach built on .NET Core.

Figure 2-1 shows the concept of ASP.NET.

4
Figure 2-1: ASP.NET

ASP.NET comes in many flavors:

• ASP.NET Web Forms - The same programming model as WinForms. If you already
know WinForms, this is an easy access to the world of web programmi
 • ASP.NET MVC (Model-View Controller). If you are familiar with the MVC
approach, this could be your choice.
• ASP.NET with Razor Pages - This is the latest and recommended way. This has
become the "default" approach for ASP.NET today. It mixes the best from all the
others combined with PHP like syntax (PHP is probably the most popular Web
Framework today)

This textbook will focus on ASP.NET Core with Razor Pages.

ASP.NET Web Forms

The same programming model as WinForms. If you already know WinForms, this
is an easy access to the world of web programming.

This textbook will focus on ASP.NET Core with Razor Pages.

Still, if you want to get an overview of ASP.NET Web Forms you may take a look at the
following:

Introduction to ASP.NET Web Programming using Web Forms:

https://youtu.be/R7VuJt6TqA8

ASP.NET Core with Razor

Again, we have different options we can use:

• Razor with MVC

• Razor Single Page Model (comparable to PHP but using C# syntax instead)
• Razor with Page Model (Code and Layout are separated in different Files)

This textbook will focus on Razor with Page Model (Code and Layout are separated in
different Files).

An ASP.NET Razor page has the “.cshtml” (e.g., “Index.cshtml”) file extension. This file
contains a mix of HTML and Razor syntax. The Razor syntax is actually C# code mixed
together with the HTML code.

The Razor parts of the file are executed on the web server before the page is sent to the client
(your web browser).
Database Systems

The Razor page may also have a C# code file linked to it, this file has the extension
“.cshtml.cs” (e.g., “Index.cshtml.cs”). The “.cshtml.cs” file is called the Page Model.

In Razor with Page Model each Razor page is a pair of files:

• A “.cshtml” file that contains HTML markup with C# code using Razor syntax.
• A “.cshtml.cs” (“code behind”) file that contains C# code that handles page events.

In this textbook we will go through ASP.NET Core in detail. In Part 3: Web Fundamentals
we go through the foundations for creating web applications in general, while in Part 4:
ASP.NET Core we start creating ASP.NET Core Web Applications. If you cannot wait to
start with ASP.NET Core, you take sneak peek at the introduction videos below.

Videos:

Below you find some short introduction videos to ASP.NET Core:

ASP.NET Core - Hello World: https://youtu.be/lcQsWYgQXK4

ASP.NET Core – Introduction: https://youtu.be/zkOtiBcwo8s

19
Visual Studio
Microsoft Visual Studio is an integrated development environment (IDE) from
Microsoft. It can be used to develop console and graphical user interface applications along
with Windows Forms applications, web sites, web applications, and web services, etc.

Home page of Visual Studio: http://www.microsoft.com/visualstudio Figure

3-1 shows Visual Studio.

Visual Studio macOS

Visual Studio for macOS supports .NET Core and Web programming (both
ASP.NET Core and ordinary ASP.NET). It does not support ordinary Windows Desktop
Programming, but you can create macOS desktop applications, iOS applications, tvOS
applications, etc.
If you want to make Web Applications and you have a Mac, the Visual Studio for macOS
has become an excellent choice.

You can create:

.NET Framework:

• ASP.NET Web Forms

• ASP.NET MVC

.NET Core:

• ASP.NET Core Web Application (Razor) – This is the default and recommended
option.
• ASP.NET Core Web Application (MVC)
• Angular Web Applications
• React.js Web Applications • Web API
• etc.

This means you have all the necessary tools available to create great web applications using
C# on your Mac.
Desktop Applications
In the latest version of Visual Studio 2019 we can develop WinForms Desktop
Applications and WPF Desktop Applications for both .NET Framework (4.x) and .NET Core
(3.x).

.NET WinForms vs. .NET Core WinForms: Note that .NET Core is cross-platform,
but .NET Core WinForms Desktop Application will (of course) only work on Windows.

Windows Forms App

This is the default approach. This is the number one method for creating Windows
desktop applications with Visual Studio.

WPF Application
This is a newer approach for creating desktop applications in Visual Studio. It has a
deeper separation of the GUI and the code.

WPF – Windows Presentation Foundation.

Universal Applications
This is an attempt to make universal application that works on any kind of
devices from desktops to mobile phones.

They call it Universal Windows Platform applications, or UWP.

 Introduction to ASP.NETCore
ASP.NET Core is based on the .NET Core Framework (not the
ordinary .NET Framework).

We have:

• ASP.NET Core MVC (Model-View Controller). If you are familiar with the MVC
approach, this could be your choice.
• ASP.NET Core with Razor Pages. This is the latest and recommended way. This has
become the "default" approach for ASP.NET today. It mixes the best from all the
others combined with PHP like syntax (PHP is probably the most popular Web
Framework today)

This textbook will focus on this latest and newest approach, namely ASP.NET Core with
Razor Pages.

Figure 11-1 shows a ASP.NET Core project in Visual Studio.

Important Folders and Files:

11
 • appSettings.json – This file contains configuration data, such as connection strings.

• Program.cs – This file contains the entry point for the program.

• Startup.cs - This file contains code that configures app behavior.

• wwwroot folder - Contains static files, such as Images, HTML files, JavaScript files,
and CSS files.

• Pages folder – Here you are supposed to put your ASP.NET (".cshtml") web pages

In addition, it is standard to have a folder called “Models”. This folder contains C# classes
that takes care of the data. The data can, e.g., be a database or a file, e.g., a JSON file.

In addition, we have what we call Supporting files. Supporting files have names that begin
with an underscore (_).

• _Layout.cshtml file configures UI elements common to all pages. You can use this
file to set up the navigation menu at the top of the page

Resources
Here are some important ASP.NET Core resources:

• ASP.NET Core fundamentals (Microsoft):

https://docs.microsoft.com/enus/aspnet/core/fundamentals/
• Introduction to Razor Pages in ASP.NET Core (Microsoft):
https://docs.microsoft.com/en-us/aspnet/core/razor-pages
• Tutorial: Create a Razor Pages web app with ASP.NET Core (Microsoft):
https://docs.microsoft.com/en-us/aspnet/core/tutorials/razor-pages/
• Razor syntax reference for ASP.NET Core (Microsoft):
https://docs.microsoft.com/en-us/aspnet/core/mvc/views/razor

Hello World Application

Let us start by creating the “compulsory” “Hello World” application.

ASP.NET Core Hello World: https://youtu.be/lcQsWYgQXK4

We start by creating a New Project in Visual Studio, see Figure 11-2.

12
13
14
Code:
@page
@model IndexModel

15
@{
ViewData["Title"] = "Home page";
}
<div class="text-center">
<h1 class="display-4">Hello World</h1> </div>

ASP.NET Core with Razor

Razor is a markup syntax for embedding server-based code into webpages. The
Razor syntax consists of Razor markup, C#, and HTML. Files containing Razor generally
have a .cshtml file extension.

16
Basic Examples
Razor supports C# and uses the @ symbol to transition from HTML to C#. Razor
evaluates C# expressions and renders them in the HTML output.

Code:
@page
@model IndexModel
@{
ViewData["Title"] = "Home page";
}
<div class="text-center">
<h1 class="display-4">Hello World</h1>
</div>
<div>
The current time is: @DateTime.Now </div>

Run the example in your web browser:

17
Let’s add a message in addition to the datetime.

Code for “Index.cshtml.cs”:

18
using System;
using System.Collections.Generic; using
Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages; using
Microsoft.Extensions.Logging; namespace HelloWorld.Pages
{
public class IndexModel : PageModel
{
private readonly ILogger<IndexModel> _logger; public string message =
"How are you today?"; public IndexModel(ILogger<IndexModel> logger)
{
_logger = logger;
}
public void OnGet()
{
}
}
}

Code for “Index.cshtml”:

@page
@model IndexModel
@{
ViewData["Title"] = "Home page";
}
<div class="text-center">
<h1 class="display-4">Hello World</h1>
</div>
<div>
The current time is: @DateTime.Now
<br />
@Model.message </div>

19
 Query String Data
Typically, we need to send data between different web pages or between the same web page.
In this case we typically send data or information through the query string.

In order to manage that, we can use Request.Query["name"].

Example:
public void OnGet()
{

bookId = Convert.ToInt16(Request.Query["bookid"]);
Book book = new Book();

connectionString =
_configuration.GetConnectionString("ConnectionString");

bookdb = book.GetBookData(connectionString, bookId);

}
In this example the URL in the web browser could look something like this

https://BookApp/EditBook?bookid=4

If we want to send more than one variable, we use &, for example:

https://BookApp/EditBook?bookid=4&booktitle=Python&author=hamsun

20
 Form Data
Typically, the user enter data into different fields on a web page. In order to send
these data to the server for some kind of processing (e.g., store the data in a database) we use
Form Data.

In order to manage that, we can use Request.Form["name"].

Example
public void OnPost()
{
Book book = new Book();

book.BookId = Convert.ToInt16(Request.Form["bookId"]); book.Title =

Request.Form["bookTitle"]; book.Isbn = Request.Form["bookIsbn"];
book.PublisherName = Request.Form["bookPublisher"]; book.AuthorName =
Request.Form["bookAuthor"]; book.CategoryName = Request.Form["bookCategory"];

connectionString =
_configuration.GetConnectionString("ConnectionString");

book.EditBook(connectionString, book);

Response.Redirect("./Books"); }

HTML Code:
<form name="bookForm" id="bookForm" method="post">

Title:
<br />
<input name="bookTitle" type="text" class="form-control input-lg" autofocus required />
<br />

21
 ISBN:
<br />
<input name="bookIsbn" type="text" class="form-control input-lg" required />
<br />

Publisher:
<br />
<input name="bookPublisher" type="text" class="form-control input-lg" required />
<br />

Author:
<br />
<input name="bookAuthor" type="text" class="form-control input-lg" required />
<br />

Category:
<br />
<input name="bookCategory" type="text" class="form-control input-lg" required />

<br />

<input id="saveButton" type="submit" value="Save" class="btn btn-info" />

</form>

22
ASP.NET Core Fundamentals

You have the following important folders:

Web Programming - ASP.NET Core

• wwroot
• Models
• Pages

In the Models folder you suppose to put your C# Classes.

In the Pages folder you should put your web pages.

In the wwwroot you should put files like CSS files (in the css folder), JavaScript files (in
your js folder), Images in your images folder. Different libraries like Bootstrap, JQuery, etc.
should be put in the lib folder.

In addition, you have the following important files:

• appsettings.json
• Program.cs
• Startup.cs
• _Layout.cshtml

They will be explained below.

Startup Class
Initial code for your application.

Web root
The web root is by default the “wwwroot” folder.

The web root is the base path to public, non-code, static resource files, such as:

• Stylesheets (.css) – Here you should put your CSS style sheets
• JavaScript (.js) – Here you should put your JavaScript code files
• Images (.png, .jpg, etc.) – This is the folder where you should put all your images

Static files are only served by default from the web root directory (and sub-directories).

In Razor (.cshtml) files, the tilde-slash (~/) points to the web root. A path beginning with ~/ is
referred to as a virtual path.

appsettings.json
This file contains configuration data, such as connection strings.

The default “appsettings.json” looks like this:

{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*" }

Typically, you want to put your connection string here:

24
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",

"ConnectionStrings": {
"ConnectionString": "DATA SOURCE=xxx;UID=xxx;PWD=xxx;DATABASE=xxx" }

Just replace “xxx” with the information for your database.

"ConnectionStrings": {
"ConnectionString": "DATA SOURCE=xxx;UID=xxx;PWD=xxx;DATABASE=xxx" }

Inside “ConnectionStrings” you can have one or more connection strings, let say you have a
development database, a test database and a customer database. This makes it easy to switch
between different connection strings.
"ConnectionStrings": {
"DevelopmentDB": "DATA SOURCE=xxx;UID=xxx;PWD=xxx;DATABASE=xxx"
"TestDB": "DATA SOURCE=xxx;UID=xxx;PWD=xxx;DATABASE=xxx" }

Shared Pages
The shared pages have an underscore in their names, e.g., _Layout.cshtml.

Folder: ./Pages/Shared

Layout
The default layout file in ASP.NET Core is “_Layout.cshtml”. You can modify this file so it
fits the ways you want to present your files. This layout will by default be added to al your
web pages (.cshtml files).

The default _Layout.cshtml looks like this:

25
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>@ViewData["Title"] - WebApplication</title>
<link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" />
<link rel="stylesheet" href="~/css/site.css" />
</head>
<body>
<header>
<nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3">
<div class="container">
<a class="navbar-brand" asp-area="" asp-page="/Index">WebApplication</a> <button class="navbar-toggler"
type="button" data-toggle="collapse" datatarget=".navbar-collapse" aria-controls="navbarSupportedContent"
aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="navbar-collapse collapse d-sm-inline-flex flex-sm-row-reverse">
<ul class="navbar-nav flex-grow-1">
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asp-page="/Index">Home</a> </li>
<li class="nav-item">
<a class="nav-link text-dark" asp-area="" asppage="/Privacy">Privacy</a>
</li>
</ul>
</div>
</div>
</nav>
</header>
<div class="container">
<main role="main" class="pb-3">
@RenderBody()
</main>
</div>

<footer class="border-top footer text-muted">

<div class="container">
&copy; 2019 - WebApplication - <a asp-area="" asp-page="/Privacy">Privacy</a>
</div>
</footer>

<script src="~/lib/jquery/dist/jquery.min.js"></script>
<script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script> <script src="~/js/site.js" asp-
append-version="true"></script>
@RenderSection("Scripts", required: false)
</body>
</html>

A “clean” (removing “everything”, just leave the minimum) _Layout.cshtml may look like
this
<!DOCTYPE html> <html
lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>@ViewData["Title"] - WebApplication</title>
<link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" />
<link rel="stylesheet" href="~/css/site.css" />
</head>
<body>

<div class="container">
<main role="main" class="pb-3">
@RenderBody()
</main>
</div>

<script src="~/lib/jquery/dist/jquery.min.js"></script>
<script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script>
<script src="~/js/site.js" asp-append-version="true"></script>
@RenderSection("Scripts", required: false)
</body>

26
</html>

If you don’t want to use the _Layout.cshtml on a specific file, set the following on top of your
.cshtml web page:

@
{

Layout = "";
You
} can also have multiple Layout files. If you want to use another Layout file than the
default for a specific web page (.cshtml file), you can do something like this:

Layout = "~/Pages/Shared/_Layout2.cshtml";

Models
In the Models folder you suppose to put your C# Classes. You don’t need to name this folder
Models, but that is the recommended name. In that way it will be easier to understand and
modify programs made by others.

Razor Pages
Razor pages should be placed in the default folder called “Pages”. Typically, your start page
should be named “Index.cshtml”.

27
 All Razor pages needs to start with the @page directive.

The line “@model TestModel” points to the Page Model file.

While the following are Razor syntax:

@{ ..
}

The Page Model file (Test.cshtml.cs) or “code behind” file will look something like this:
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages; using
Microsoft.Extensions.Logging;

namespace DemoApp.Pages
{
public class TestModel : PageModel
{
private readonly ILogger<IndexModel> _logger;

public IndexModel(ILogger<IndexModel> logger)

{
_logger = logger;
}

public void OnGet()

{

}
}
}

Sending data from the Page Model to the Razor File

Typically we need to send data between the Page Model (.cshtml.cs) and the Razor File
(.cshtml). Below you see a basic example.

Page Model File (.cshtml.cs):

using System;
using System.Collections.Generic; using
Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages; using
Microsoft.Extensions.Logging;

namespace DemoApp.Pages
{
public class TestModel : PageModel
{ public string name;

private readonly ILogger<IndexModel> _logger;

public IndexModel(ILogger<IndexModel> logger)

{
_logger = logger;
}

public void OnGet()

{ name = "Hans-Petter Halvorsen";

}
}
}

In this example we have declared a public variable. Then later we want to display the value in
our web page (Razor Page (.cshtml)).

28
You typically put code that needs to run before the web page is sent to the client in the
OnGet() method.

Razor Page (.cshtml):

@page
@model TestModel
@{
ViewData["Title"] = "Home page";
}

<div class="text-center">
<h1 class="display-4">Welcome</h1>

<p>This is a Razor Page</p>

<p>My Name is @Model.name</p>

</div>

As you see we need to use @Model.xxx where xxx is the name of a public variable in the
Page Model file.

Additional Resources
ASP.NET Core fundamentals:
https://docs.microsoft.com/enus/aspnet/core/fundamentals/

29
Razor
Razor is a markup syntax for embedding server-based code into webpages. The Razor syntax
consists of Razor markup, C#, and HTML. Files containing Razor generally have a .cshtml
file extension.

The default Razor language is HTML. Rendering HTML from Razor markup is no different
than rendering HTML from an HTML file. HTML markup in .cshtml Razor files is rendered
by the server unchanged.

ASP.NET Documentation from Microsoft: https://docs.microsoft.com/aspnet/

Introduction to Razor Pages in ASP.NET Core:
https://docs.microsoft.com/aspnet/core/razor-pages/

Razor Syntax
Razor supports C# and uses the @ symbol to transition from HTML to C#. Razor evaluates
C# expressions and renders them in the HTML output.

All code blocks must appear within @{ ... } brackets.

Example:
@{
var number = 1;
}

The value of variables is rendered into HTML by prefixing them with the @ sign.

Example:
The number is @number

We can use standard C# features and built-in Classes and Methods.

Example:

Web Programming - ASP.NET Core

13. Razor

The time is @DateTime.Now

A foreach loop is very handy for looping through data.

 Example:
@{
var numbers = Enumerable.Range(1, 10); //Get numbers from 1 - 10 foreach(var number in numbers)
{
@number
}
}

Or this alternative way:

@{ var numbers = Enumerable.Range(1, 10); }

@foreach(var number in numbers){

@number }

Comments can be used in different ways, either // for a single line or /* */ for multiple lines.

Example:
// This is a comment
…

/* … */

/*
Multiple Lines
…
…
*/

Model
Using Model inside a foreach:

Example:
@foreach (var measurement in Model.measurementParameterList)
{
<tr>
<td> @measurement.MeasurementId</td>
<td> @measurement.MeasurementName</td>
<td> @measurement.MeasurementUnit</td> </tr>

84

13. Razor

}
 85

Database Systems
A Database is a structured way to store lots of information. The information is stored in
different tables.

Some of the most popular Database Systems today are:

• SQL Server
• MySQL • MariaDB
• Etc.

ER Diagram (Entity-Relationship Diagram) is used for design and modeling of databases. It

specifies tables and relationship between them (Primary Keys and Foreign Keys), see Figure
14-1.

Introduction to Database Systems: https://youtu.be/n75iPNrzN-o

SQL Server
SQL Server is a Database System from Microsoft. SQL Server comes in different editions,
for basic, personal use SLQ Server Express is recommended because it is simple to use and
it is free.
SQL Server consists of a Database Engine and a Management Studio (and lots of other stuff which
we will not mention here). The Database engine has no graphical interface - it is just a service
running in the background of your computer (preferable on the server). The Management Studio is
graphical tool for configuring and viewing the information in the database. It can be installed on
the server or on the client (or both).

QL Server Express Installation: https://youtu.be/hhhggAlUYo8

Introduction to SQL Server: https://youtu.be/SlR4KOhAG1U

SQL Server Management Studio

SQL Server Management Studio (SSMS) is used to manage your databases, including
creating, updating, deleting, etc. You can insert tables, create views, stored procedures, etc.

33
It is quite simple to create a new database in Microsoft SQL Server. Just right-click on the
“Databases” node and select “New Database…”

34
There are lots of settings you may set regarding your database, but the only information you must
fill in is the name of your database:

You may also use the SQL language to create a new database, but sometimes it is easier to just
use the built-in features in the Management Studio.

In order to make a new SQL query, select the “New Query” button from the Toolbar.

35
Here we can write any kind of queries that is supported by the SQL language.

Structured Query Language (SQL)

SQL (Structured Query Language) is a database computer language designed for managing data
in relational database management systems (RDBMS).

In SQL, we have 4 different types of queries:

• INSERT
• SELECT
• UPDATE
• DELETE

What can SQL do?

• SQL can execute queries against a database

• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database

• SQL can create new databases

• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete
data.

The acronym CRUD refers to all of the major functions that need to be implemented in a relational
database application to consider it complete. Each letter in the acronym can be mapped to a
standard SQL statement:

Operation SQL Description

Create INSERT INTO inserts new data into a database

Read (Retrieve) SELECT extracts data from a database

Update UPDATE updates data in a database

Delete (Destroy) DELETE deletes data from a database

36
Additional Resources:

Do you want to learn more about SQL?

Database Systems: https://www.halvorsen.blog/documents/technology/database/

Tables
In Figure 14-7 we see an example of some tables for a university or a school. These tables store
information regarding the students, the teacher, the courses, the grades, etc.

Best practice rules:

Here are some “Best practice” recommendations for creating tables in a database system:

• Tables: Use upper case and singular form in table names – not plural, e.g., “STUDENT”
(not students)
• Columns: Use Pascal notation, e.g., “StudentId”
• Primary Keys:
– If the table name is “COURSE”, name the Primary Key column “CourseId”, etc.
– “Always” use Integer and Identity(1,1) for Primary Keys
• Specify Required Columns (NOT NULL) – i.e., which columns that need to have data or
not
• Data Types: Standardize on these Data Types: int, float, varchar(x), datetime, bit

37
 • Use English for table and column names
• Avoid abbreviations! (Use RoomNumber – not RoomNo, RoomNr, ...)

The CREATE TABLE statement is used to create a table in a database.

We want to create a table called “CUSTOMER” which has the following columns and data
types:

CREATE TABLE CUSTOMER

(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,
)
GO

Typically, you also want to sue a tool for modelling the database, e.g., Erwin.

Primary keys:

As you see we use the “Primary Key” keyword to specify that a column should be the Primary
Key.

Setting Primary Keys in the Designer Tools:

38
If you use the Designer tools in SQL Server, you can easily set the primary Key in a table just
by right-click and select “Set primary Key”.

The primary Key column will then have a small key in front to illustrate that this column is
a Primary Key.

Foreign Keys:

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Example:

We will create a CREATE TABLE script for these tables:

SCHOOL:
CREATE TABLE SCHOOL
(
SchoolId int IDENTITY(1,1) PRIMARY KEY,
SchoolName varchar(50) NOT NULL UNIQUE,

39
 Description varchar(1000) NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,
PostCode varchar(50) NULL,
PostAddress varchar(50) NULL,
)
GO
CLASS:
CREATE TABLE CLASS
(
ClassId int IDENTITY(1,1) PRIMARY KEY,
SchoolId int NOT NULL FOREIGN KEY REFERENCES SCHOOL (SchoolId),
ClassName varchar(50) NOT NULL UNIQUE,
Description varchar(1000) NULL,
)
GO

The FOREIGN KEY constraint is used to prevent actions that would destroy links between
tables.

The FOREIGN KEY constraint also prevents that invalid data from being inserted into the
foreign key column, because it has to be one of the values contained in the table it points to.

Setting Foreign Keys in the Designer Tools:

If you want to use the designer, right-click on the column that you want to be the Foreign Key
and select “Relationships…”:

The following window pops up (Foreign Key Relationships):

40
Click on the “Add” button and then click on the small “…” button. Then the following
window pops up (Tables and Columns):

Here you specify the primary Key Column in the Primary Key table and the Foreign Key
Column in the Foreign Key table.

NOT NULL / Required Columns:

The NOT NULL constraint enforces a column to NOT accept NULL values.

41
The NOT NULL constraint enforces a field to always contain a value. This means that you
cannot insert a new record or update a record without adding a value to this field.

If we take a closer look at the CUSTOMER table created earlier:

CREATE TABLE [CUSTOMER]
(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,
)
GO

We see that “CustomerNumber”, “LastName” and “FirstName” is set to “NOT NULL”, this
means these columns needs to contain data. While “AreaCode”, “Address” and “Phone” may
be left empty, i.e, they don’t need to be filled out.

Note! A primary key column cannot contain NULL values.

Setting NULL/NOT NULL in the Designer Tools:

In the Table Designer you can easily set which columns that should allow NULL or not:

UNIQUE:
The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and
PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of
columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note! You can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.

42
If we take a closer look at the CUSTOMER table created earlier:
CREATE TABLE [CUSTOMER]
(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,
)
GO

We see that the “CustomerNumber” is set to UNIQUE, meaning each customer must have a unique
Customer Number. Example:

Setting UNIQUE in the Designer Tools:

If you want to use the designer, right-click on the column that you want to be UNIQUE and select
“Indexes/Keys…”:

Then click “Add” and then set the “Is Unique” property to “Yes”:

43
AUTO INCREMENT or IDENTITY:

Very often we would like the value of the primary key field to be created automatically every
time a new record is inserted.

Example:
CREATE TABLE CUSTOMER
(
CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone varchar(50) NULL,
)
GO
As shown below, we use the IDENTITY() for this. IDENTITY(1,1) means the first value will be
1 and then it will increment by 1.

Setting identity(1,1) in the Designer Tools:

We can use the designer tools to specify that a Primary Key should be an identity column that
is automatically generated by the system when we insert data in to the table.

Click on the column in the designer and go into the Column Properties window:

44
Views
Views are virtual table for easier access to data stored in multiple tables.

Example:

We use the SCHOOL and CLASS tables as an example for our View. We want to create a View
that lists all the existing schools and the belonging classes.

We create the VIEW using the CREATE VIEW command:

CREATE VIEW SchoolView
AS

SELECT
SCHOOL.SchoolName,

45
CLASS.ClassName
FROM
SCHOOL
INNER JOIN CLASS ON SCHOOL.SchoolId = CLASS.SchoolId

Note! In order to get information from more than one table, we need to link the tables together
using a JOIN.

Database Views and Stored Procedures: https://youtu.be/SHELF_iQUeU

Stored Procedures
A Stored Procedure is a precompiled collection of SQL statements. In a stored procedure you
can use if sentence, declare variables, etc.

Triggers
A database trigger is code that is automatically executed in response to certain events on a particular
table in a database.

46
ure 37-4 and Figure 37-6 shows Web-based Data Management for setting up Devices and
respective Tags for the different Devices. Here we have configured the Device Weather
Station to have the Tag Temperature.

Figure 37-4: Web-based Data Management – Devices

48