Databases

What is a Database?

Organized collection of data
Database Management System (DBMS) = interface between end users/applications and database
Types
- Relational Databases
- Non-relational Databases (aka NoSQL or NewSQL)

Relational Database

Database consists of tables (=relations)
Table has attributes (=columns)
Table has tuples (=rows)
Tables can refer to each other

RDBMS Examples

Microsoft SQL Server
- No open source software
- Free option: SQL Server Express LocalDB
- Nice UI: SSMS
- Widely used, from single devs to large enterprises
SQLite
- Light-weight, free, open source
- Must used DB in the world
- Available on practically every mobile phone
- https://www.sqlite.org/download.html
PostgreSQL
- Open Source
- https://www.postgresql.org/

Challenge: Database Design

Microsoft SQL Server "Northwind" Sample

SQL in Visual Studio

Server: ng-workshop.database.windows.net

DB: northwind

User: coderdojo

Pwd: master!c0der

Tipp: https://www.connectionstrings.com/microsoft-data-sqlclient/

SQL

Command	Description	Docs
INSERT	Insert rows	https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15
UPDATE	Update attributes in rows	https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15
DELETE	Delete rows	https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver15
SELECT	Query	https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15

SQL

-- SIMPLE QUERIES ---------------------------------------------------------
SELECT * FROM Employees;

SELECT FirstName, LastName
FROM Employees;

SELECT FirstName, LastName
FROM Employees
ORDER BY LastName;

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY Title ASC, LastName DESC;

SELECT Title, FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative';

SELECT FirstName, LastName
FROM Employees
WHERE LastName BETWEEN 'J' AND 'M';

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy IN ('Ms.','Mrs.');

SELECT FirstName, LastName
FROM Employees
WHERE Region IS NULL;

SQL

SELECT City, CompanyName, ContactName
FROM Customers
WHERE City < 'C'
ORDER BY ContactName DESC;

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M%';

SELECT FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative'
	AND TitleOfCourtesy = 'Mr.';

SELECT FirstName + ' ' + LastName
FROM Employees;

SELECT OrderID, Freight, Freight * 1.1
FROM Orders
WHERE Freight >= 500;

SQL

-- AGGREGATIONS -----------------------------------------------------------
SELECT COUNT(*) AS NumEmployees
FROM Employees;

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;

SELECT MIN(HireDate) AS FirstHireDate,
       MAX(HireDate) AS LastHireDate
FROM Employees;

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City;

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City
HAVING COUNT(EmployeeID) > 1;

SQL

-- SUBQUERIES AND JOINS ---------------------------------------------------
SELECT CompanyName
FROM Customers
WHERE CustomerID = (SELECT CustomerID
			FROM Orders
			WHERE OrderID = 10290);

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.OrderDate
FROM Employees 
	JOIN Orders ON (Employees.EmployeeID = Orders.EmployeeID)
ORDER BY Orders.OrderDate;

SELECT o.OrderID, c.CompanyName, e.FirstName, e.LastName
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
	JOIN Customers c ON (c.CustomerID = o.CustomerID)
WHERE o.ShippedDate > o.RequiredDate AND o.OrderDate > '1-Jan-1998'
ORDER BY c.CompanyName;

SQL in C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace PokemonDatabase.Relational
{
    class Program
    {
        static async Task Main()
        {
            using var conn = new SqlConnection("Server=tcp:ng-workshop.database.windows.net,1433;Initial Catalog=northwind;Persist Security Info=False;User ID=coderdojo;Password=master!c0der;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
            await conn.OpenAsync();

            await ExecuteSqlWithoutResult(conn);
            Console.WriteLine(await ExecuteSqlWithSingleResult(conn));
            await ExecuteSqlWithTabularResult(conn);
        }

        static async Task ExecuteSqlWithoutResult(SqlConnection conn)
        {
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "-- Here you could do an INSERT, UPDATE, DELETE, CREATE etc. statement";
            cmd.CommandType = CommandType.Text;
            await cmd.ExecuteNonQueryAsync();
        }

SQL in C#

        static async Task<string> ExecuteSqlWithSingleResult(SqlConnection conn)
        {
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT CompanyName FROM dbo.Customers WHERE CustomerID = 'ALFKI'";
            cmd.CommandType = CommandType.Text;
            return await cmd.ExecuteScalarAsync() as string;
        }

        static async Task ExecuteSqlWithTabularResult(SqlConnection conn)
        {
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT CustomerID, CompanyName, ContactName FROM dbo.Customers";
            cmd.CommandType = CommandType.Text;
            using var reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                Console.WriteLine($"{reader["CustomerID"]}, {reader["CompanyName"]}, {reader["ContactName"]}");
            }
        }
    }
}

Non-Relational Database

Document-oriented DB
- Collection of "documents" (e.g. JSON, XML)
- No strict schema, every doc can be different
- Some have SQL or SQL-like query language
Key/Value DB
- Retrieve value (e.g. JSON) for a given key

Non-Relational DB Examples

Azure CosmosDB
- Super professional
- Free for small projects
- Emulator for local development
- Supports different APIs
MongoDB
- Open Source
- Widely used (also in CoderDojo backend)
Redis
- In memory or persisted
- Key/value DB

CosmosDB

Using CosmosDB from C#

I ntro Sample from CoderDojo

CosmosDB and C#

using Microsoft.Azure.Cosmos;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Runtime.CompilerServices;
using System.Runtime.Serialization;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;

namespace CosmosDemo {
	class CosmosSamples {
		public async Task GetData() {
			var client = CosmosDBProvider.Client;

			var container = client.GetContainer("CoderDojoDemo", "CoderDojoDemo");
			var iterator = container.GetItemQueryIterator<dynamic>("select * from c");
			var docs = await iterator.ReadNextAsync();
			foreach(var doc in docs) {
				await Console.Out.WriteLineAsync(JsonConvert.SerializeObject(doc, Formatting.Indented));
			}
			
		}

CosmosDB and C#


		public async Task SelectData() {
			var client = CosmosDBProvider.Client;

			var container = client.GetContainer("CoderDojoDemo", "CoderDojoDemo");
			var iterator = container.GetItemQueryIterator<dynamic>("select * from c where c.coder > 25");
			var docs = await iterator.ReadNextAsync();
			foreach (var doc in docs) {
				await Console.Out.WriteLineAsync($"{doc.coder,3} {doc.dojo.date,12} {doc.dojo.location}");
			}

		}

CosmosDB and C#


		public async Task DeleteData() {
			var client = CosmosDBProvider.Client;

			var container = client.GetContainer("CoderDojoDemo", "CoderDojoDemo");
			var iterator = container.GetItemQueryIterator<dynamic>("select * from c");
			while(iterator.HasMoreResults) {
				var docs = await iterator.ReadNextAsync();
				foreach(var doc in docs) {
					string id = doc.id;
					string pk = doc.dojo.date;
					await container.DeleteItemAsync<dynamic>(id, new PartitionKey(pk));
				}
			}
		}
	}
}