Understanding modern databases

Two of the most common places to store data are in a Relational Database Management System (RDBMS) such as SQL Server, PostgreSQL, MySQL, and SQLite, or in a NoSQL database such as Azure Cosmos DB, MongoDB, Redis, and Apache Cassandra.

In this chapter, we will focus on the most popular RDBMS for Windows, which is SQL Server. This product is also available in a version for Linux. For cross-platform development, you can use either Azure SQL Database, which stores the data in the cloud, or Azure SQL Edge, which can run in a Docker container on Windows, macOS, or Linux.

Using a sample relational database

To learn how to manage an RDBMS using .NET, it would be useful to have a sample one so that you can practice on a database that has a medium complexity and a decent number of sample records. Microsoft offers several sample databases, most of which are too complex for our needs, so instead, we will use a database that was first created in the early 1990s known as Northwind.

Let’s take a minute to look at a diagram of the Northwind database and its eight most important tables. You can use the diagram in Figure 2.1 to refer to as we write code and queries throughout this book:

Figure 2.1: The Northwind database tables and relationships

Note that:

• Each category has a unique identifier, name, description, and picture. The picture is stored as a byte array in JPEG format.
• Each product has a unique identifier, name, unit price, number of units in stock, and other columns.
• Each product is associated with a category by storing the category’s unique identifier.
• The relationship between Categories and Products is one-to-many, meaning each category can have zero, one, or more products.
• Each product is supplied by a supplier company, indicated by storing the supplier’s unique identifier.
• A quantity and unit price of a product is stored for each detail of an order.
• Each order is made by a customer, taken by an employee, and shipped by a shipping company.
• Each employee has a name, address, contact details, birth and hire dates, a reference to their manager (except for the boss whose ReportsTo field is null), and a photo stored as a byte array in JPEG format. The table has a one-to-many relationship to itself because one employee can manage many other employees.

Connecting to a SQL Server database

To connect to a SQL Server database, we need to know multiple pieces of information, as shown in the following list:

• The name of the server (and the instance if it has one). This can include the protocol, IP address, and port number if connecting over a network.
• The name of the database.
• Security information, such as the username and password, or if we should pass the currently logged-on user’s credentials automatically using Windows Authentication.

We specify this information in a connection string.

For backward compatibility, there are multiple possible keywords we can use in a SQL Server connection string for the various parameters, as shown in the following list:

• Data Source, server, or addr: These keywords are the name of the server (and an optional instance). You can use a dot . to mean the local server.
• Initial Catalog or database: These keywords are the name of the database.
• Integrated Security or trusted_connection: These keywords are set to true or SSPI to pass the thread’s current user credentials using Windows Authentication.
• User Id and Password: These keywords are used to authenticate with any edition of SQL Server. This is important for Azure SQL Database or Azure SQL Edge because they do not support Windows Authentication. The full edition of SQL Server on Windows supports both username with password, and Windows Authentication.
• Authentication: This keyword is used to authenticate by using Azure AD identities that can enable password-less authentication. Values can be Active Directory Integrated, Active Directory Password, and Sql Password.
• Persist Security Info: If set to false, this keyword tells the connection to remove the Password from the connection string after authenticating.
• Encrypt: If set to true, this keyword tells the connections to use SSL to encrypt transmissions between client and server.
• TrustServerCertificate: Set to true if hosting locally and you get the error “A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)”
• Connection Timeout: This keyword defaults to 30 seconds.
• MultipleActiveResultSets: This keyword is set to true to enable a single connection to be used to work with multiple tables simultaneously to improve efficiency. It is used for lazy loading rows from related tables.

As described in the list above, when you write code to connect to a SQL Server database, you need to know its server name. The server name depends on the edition and version of SQL Server that you will connect to, as shown in the following table:

SQL Server edition	Server name \ Instance name
LocalDB 2012	(localdb)\v11.0
LocalDB 2016 or later	(localdb)\mssqllocaldb
Express	.\sqlexpress
Full/Developer (default instance)	.
Full/Developer (named instance)	.\apps-services-net7
Azure SQL Edge (local Docker)	tcp:127.0.0.1,1433
Azure SQL Database	tcp:[custom server name].database.windows.net,1433

Good Practice: Use a dot . as shorthand for the local computer name. Remember that server names for SQL Server can be made up of two parts: the name of the computer and the name of a SQL Server instance. You provide instance names during custom installation.

Installing and setting up SQL Server

Microsoft offers various editions of its popular and capable SQL Server product for Windows, Linux, and Docker containers. If you have Windows, then you can use a free version that runs standalone, known as SQL Server Developer Edition. You can also use the Express edition or the free SQL Server LocalDB edition that can be installed with Visual Studio 2022 for Windows.

If you do not have a Windows computer or if you want to use a cross-platform database system, then you can skip ahead to Setting up Azure SQL Database or Installing Azure SQL Edge in Docker. Be sure to read the Creating the Northwind sample database section to learn where to find the SQL scripts that create the sample database.

Installing SQL Server Developer Edition for Windows

On Windows, if you want to use the full edition of SQL Server instead of the simplified LocalDB or Express editions, then you can find all SQL Server editions at the following link: https://www.microsoft.com/en-us/sql-server/sql-server-downloads.

To download and configure SQL Server Developer Edition, use the following steps:

1. Download the Developer edition.
2. Run the installer.
3. Select the Custom installation type.
4. Select a folder for the installation files and then click Install.
5. Wait for the 1.5 GB of installer files to download.
6. In SQL Server Installation Center, click Installation, and then click New SQL Server stand-alone installation or add features to an existing installation.
7. Select Developer as the free edition and then click Next.
8. Accept the license terms and then click Next.
9. Review the Microsoft Update options, and then click Next.
10. Review the install rules, fix any issues, and then click Next.
11. In Feature Selection, select Database Engine Services, and then click Next.
12. In Instance Configuration, select Default instance, and then click Next. If you already have a default instance configured, then you could create a named instance, perhaps called net7book.
13. In Server Configuration, note the SQL Server Database Engine is configured to start automatically. Set the SQL Server Browser to start automatically, and then click Next.
14. In Database Engine Configuration, on the Server Configuration tab, set Authentication Mode to Mixed, set the sa account password to a strong password, click Add Current User, and then click Next.
15. In Ready to Install, review the actions that will be taken, and then click Install.
16. In Complete, note the successful actions taken, and then click Close.
17. In SQL Server Installation Center, in Installation, click Install SQL Server Management Tools.
18. In the browser window, click to download the latest version of SSMS.

    The direct link to download SSMS is as follows: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

19. Run the SSMS installer and click Install.
20. When the installer has finished, click Restart if needed or Close.

    Azure Data Studio (ADS) is automatically installed alongside SSMS. ADS is cross-platform and open-source, so you can use it to work with SQL Server databases on any desktop operating system.

Visual Studio Code extension for working with SQL Server

There are many tools that make it easy to work with SQL Server. If you are using Visual Studio Code, then you can install the SQL Server (mssql) ms-mssql.mssql extension.

If you install the extension, it adds a new view to the Primary Side Bar titled SQL Server, as shown in Figure 2.2:

Figure 2.2: SQL Server (mssql) extension for Visual Studio Code

Creating the Northwind sample database on Windows

Now we can run a database script to create the Northwind sample database on Windows using SQL Server Management Studio (SSMS):

1. If you have not previously downloaded or cloned the GitHub repository for this book, then do so now using the following link: https://github.com/markjprice/apps-services-net7/.
2. In your apps-services-net7 folder, create a folder named Chapter02.
3. Copy the script to create the Northwind database for SQL Server from the following path in your local Git repository: /sql-scripts/Northwind4SQLServer.sql into the Chapter02 folder.
4. Start SQL Server Management Studio.
5. In the Connect to Server dialog, for Server name, enter . (a dot), meaning the local computer name, and then click Connect.

   If you had to create a named instance, like net7book, then enter .\net7book.

6. Navigate to File | Open | File....
7. Browse to select the Northwind4SQLServer.sql file and then click Open.
8. In the toolbar, click Execute, and note the Command(s) completed successfully message.
9. In Object Explorer, expand the Northwind database, and then expand Tables.
10. Right-click Products, click Select Top 1000 Rows, and note the returned results, as shown in Figure 2.3:

Figure 2.3: The Products table in SQL Server Management Studio

11. In the Object Explorer toolbar, click the Disconnect button.
12. Exit SQL Server Management Studio.

We did not have to use SQL Server Management Studio to execute the database script. We can also use tools in Visual Studio 2022, including the SQL Server Object Explorer and Server Explorer, or cross-platform tools like the Visual Studio Code extension for SQL Server, or Azure Data Studio, which you can download and install from the following link: https://aka.ms/getazuredatastudio.

Setting up Azure SQL Database

If you do not have a Windows computer, then you can create a cloud-hosted instance of SQL Server. You will need an Azure account. You can sign up at the following link: https://signup.azure.com.

1. Log in to your Azure account: https://portal.azure.com/.
2. Navigate to https://portal.azure.com/#create/hub.
3. Search for Resource group and then click the Create button.
4. Enter a resource group name of apps-services-net7 and select a suitable region close to you, and then click the Review + create button.
5. Review your choices and then click the Create button.
6. Create another resource, search for SQL Database, and click Create.
7. In the Create SQL Database page, in the Basics tab, for the Database name enter Northwind, and select the resource group that you created before.
8. In the Server section, click Create New.
9. Enter the following details for the SQL Database server, as shown in Figure 2.4:
   • Server name: apps-services-net7-[your initials] or something else entirely. The server name must be globally unique because it becomes part of a public URL.
   • Location: A region close to you. I chose (Europe) UK South. Not all regions support all types of resource. You will see an error if the region you select does not support SQL Database server resources.
   • Authentication method: Use SQL authentication.
   • Server admin login: [Your email or another username], for example, I entered markjprice.
   • Password/Confirm password: [Enter a strong password].

Figure 2.4: Entering the server details for a SQL Database instance

10. Click OK.
11. In the Create SQL Database page, in the Compute + storage section, click Configure database.
12. For Service tier, select Basic (For less demanding workloads). Note the maximum database size is 2 GB and the estimated cost is about $6.23 per month. You can delete the resources as soon as you have completed this chapter to reduce the cost further.
13. Click Apply.
14. In the Create SQL Database page, set Backup storage redundancy to Locally-redundant backup storage.
15. Click the Next : Networking button.
16. In the Network connectivity section, select Public endpoint.
17. In the Firewall rules section, set Add current client IP address to Yes.
18. Click the Next : Security button.
19. Review the options but leave them as the defaults.
20. Click the Next : Additional settings button.
21. Review the options but leave them as the defaults.
22. Click the Review + create button.
23. Click the Create button.
24. Wait for the deployment, as shown in Figure 2.5:

Figure 2.5: Deployment progress for SQL Database

25. Click Go to resource.
26. Click Overview and note the database details, as shown in Figure 2.6:

Figure 2.6: SQL Database details

27. Click Show database connection strings.
28. Copy the ADO.NET connection string to your clipboard.
29. Start Notepad or your preferred plain text editor, paste the connection string, and add carriage returns after each semicolon to separate each part to make them easier to work with, as shown in the following text:

    Server=tcp:apps-services-net7.database.windows.net,1433;
    Initial Catalog=Northwind;
    Persist Security Info=False;
    User ID=markjprice;
    Password={your_password};
    MultipleActiveResultSets=False;
    Encrypt=True;
    TrustServerCertificate=False;
    Connection Timeout=30;
    

    Your Server value will be different because the custom server name part, for example, apps-services-net7, is public and must be globally unique.

30. Use your preferred database tool to connect to the SQL server:
    • In Visual Studio 2022, view Server Explorer.
    • On Windows, start SQL Server Management Studio.
    • In Visual Studio Code, view the SQL Server tool. You can install the SQL Server (mssql) extension if you have not done so already: https://marketplace.visualstudio.com/items?itemName=ms-mssql.mssql.
    • Alternatively, you can use the cross-platform Azure Data Studio. You can install it from the following link: https://aka.ms/getazuredatastudio.
31. Add a data connection, and fill in the dialog box, as shown in Figure 2.7:

    

    Figure 2.7: Connecting to your Azure SQL database from Visual Studio

    In Visual Studio Server Explorer, you might also be prompted to Choose Data Source. Choose Microsoft SQL Server. You can select a checkbox to always use this selection.

32. Right-click the data connection and choose New Query.
33. Copy and paste the contents of the Northwind4AzureSQLdatabase.sql file into the query window and execute it.

    The main difference between the Northwind4SQLServer.sql and Northwind4AzureSQLdatabase.sql scripts is that the local SQL Server script will delete and recreate the Northwind database. The Azure SQL database script will not, because the database needs to be created as an Azure resource. You can download SQL script files from the following link: https://github.com/markjprice/apps-services-net7/tree/main/sql-scripts.

34. Wait to see the Command completed successfully message.
35. In Server Explorer, right-click Tables and select Refresh, and note that 13 tables have been created, for example, Categories, Customers, and Products. Also note that dozens of views and stored procedures have also been created.

You now have a running Azure SQL database that you can connect to from a .NET project.

Installing Azure SQL Edge in Docker

If you do not have a Windows computer, and you do not want to pay for Azure resources, then you can install Docker and use a container that has Azure SQL Edge, a cross-platform minimal featured version of SQL Server that only includes the database engine.

The Docker image we will use has Azure SQL Edge based on Ubuntu 18.4. It is supported with the Docker Engine 1.8 or later on Linux, or on Docker for Mac or Windows. Azure SQL Edge requires a 64-bit processor (either x64 or ARM64), with a minimum of one processor and 1 GB RAM on the host.

1. Install Docker from the following link: https://docs.docker.com/engine/install/.
2. Start Docker.
3. At the command prompt or terminal, pull down the latest container image for Azure SQL Edge, as shown in the following command:

   docker pull mcr.microsoft.com/azure-sql-edge:latest
   

4. Note the results, as shown in the following output:

   latest: Pulling from azure-sql-edge
   2f94e549220a: Pull complete
   830b1adc1e72: Pull complete
   f6caea6b4bd2: Pull complete
   ef3b33eb5a27: Pull complete
   8a42011e5477: Pull complete
   f173534aa1e4: Pull complete
   6c1894e17f11: Pull complete
   a81c43e790ea: Pull complete
   c3982946560a: Pull complete
   25f31208d245: Pull complete
   Digest: sha256:7c203ad8b240ef3bff81ca9794f31936c9b864cc165dd187c23c5bfe06cf0340
   Status: Downloaded newer image for mcr.microsoft.com/azure-sql-edge:latest
   mcr.microsoft.com/azure-sql-edge:latest
   

5. At the command prompt or terminal, run the container image for Azure SQL Edge with a strong password and name the container azuresqledge, as shown in the following command:

   docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=s3cret-Ninja' -p 1433:1433 --name azuresqledge -d mcr.microsoft.com/azure-sql-edge
   

   Good Practice: The password must be at least 8 characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, digits, and symbols; otherwise, the container cannot set up the SQL Edge engine and will stop working.

6. If your operating system firewall blocks access, then allow access.
7. In Docker, confirm that the image is running, as shown in Figure 2.8:

Figure 2.8: SQL Edge running in Docker Desktop on Windows

8. At the command prompt or terminal, ask Docker to list all containers, both running and stopped, as shown in the following command:

   docker ps -a
   

9. Note the container is “Up” and listening externally on port 1433, which is mapped to its internal port 1433, as shown highlighted in the following output:

   CONTAINER ID   IMAGE                              COMMAND                  CREATED         STATUS         PORTS                              NAMES
   183f02e84b2a   mcr.microsoft.com/azure-sql-edge   "/opt/mssql/bin/perm…"   8 minutes ago   Up 8 minutes   1401/tcp, 0.0.0.0:1433->1433/tcp   azuresqledge
   

   You can learn more about the docker ps command at the following link: https://docs.docker.com/engine/reference/commandline/ps/.

10. Use your preferred database tool to connect to the SQL server:
    • In Visual Studio 2022, view Server Explorer.
    • On Windows, start SQL Server Management Studio.
    • In Visual Studio Code, view the SQL Server tool.
    • Alternatively, you can use the cross-platform Azure Data Studio.
11. Add a data connection, and fill in the dialog box, as shown in Figure 2.9:

Figure 2.9: Connecting to your Azure SQL Edge server from Visual Studio

12. Right-click the data connection and choose New Query.
13. Copy and paste the contents of the Northwind4AzureSQLedge.sql file into the query window and execute it.
14. Wait to see the Command completed successfully message.
15. In Server Explorer, refresh the data connection if needed, right-click Tables and select Refresh, and note that 13 tables have been created, for example, Categories, Customers, and Products. Also note that dozens of views and stored procedures have also been created.

You now have a running instance of Azure SQL Edge containing the Northwind database that you can connect to from a console app.