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

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#

Intro 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));
				}
			}
		}
	}
}

Databases

By CoderDojo Linz

Made with Slides.com

Databases

  • 391

More from CoderDojo Linz