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:

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.

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.

Transact-SQL (T-SQL) is SQL Server’s dialect of Structured Query Language (SQL). Some pronounce it tee-sequel, others tee-es-queue-el.

Unlike C#, T-SQL is not case-sensitive; for example, you can use int or INT to specify the 32-bit integer data type, and you can use SELECT or select to start a query expression. Text stored in SQL Server can be treated as case-sensitive or not depending on configuration.

T-SQL data types

T-SQL has data types that are used for columns, variables, parameters, and so on.

Documenting with comments

To comment out the rest of a line, use -- which is the equivalent of //.

To comment out a block, use /* and the start and */ at the end, just like in C#.

Declaring variables

Local variable names are prefixed with @ and they are defined using SET, SELECT, or DECLARE, as shown in the following code:

DECLARE @WholeNumber INT; -- Declare a variable and specify its type.
SET @WholeNumber = 3; -- Set the variable to a literal value.
SET @WholeNumber = @WholeNumber + 1; -- Increment the variable.
SELECT @WholeNumber = COUNT(*) FROM Employees; -- Set to the number of employees.
SELECT @WholeNumber = EmployeeId FROM Employees WHERE FirstName = 'Janet';

Global variables are prefixed with @@. For example, @@ROWCOUNT is a context-dependent value that returns the number of rows affected by a statement executed within the current scope, for example, the number of rows updated or deleted.

Specifying data types

Most types have a fixed size. For example, an int uses four bytes, a smallint uses two bytes, and a tinyint uses one byte.

For text and binary types, you can either specify a type prefixed with var or nvar (meaning variable size) that will automatically change its size based on its current value up to a maximum, as shown in the following example: varchar(40); or you can specify a fixed number of characters that will always be allocated, as shown in the following example: char(40).

For text types, the n prefix indicates Unicode, meaning it will use two bytes per character. Text types not prefixed with n use one byte per character.

Controlling flow

T-SQL has similar flow control keywords as C#, for example, BREAK, CONTINUE, GOTO, IF...ELSE, CASE, THROW, TRY...CATCH, WHILE, and RETURN. The main difference is the use of BEGIN and END to indicate the start and end of a block, the equivalent of curly braces in C#.

Operators

T-SQL has similar operators as C#, for example, = (assignment), +, -, *, /, %, <, >, <=, ==, !=, &, |, ^, and so on. It has logical operators like AND, OR, NOT, and LINQ-like operators like ANY, ALL, SOME, EXISTS, BETWEEN, and IN.

LIKE is used for text pattern matching. The pattern can use % for any number of characters. The pattern can use _ for a single character. The pattern can use [] to specify a range and set of allowed characters, for example, [0-9A-Z.-,].

Data Manipulation Language (DML)

DML is used to query and change data.

The most common statement in DML is SELECT, which is used to retrieve data from one or more tables. SELECT is extremely complicated because it is so powerful. This book is not about learning T-SQL, so the quickest way to get a feel for SELECT is to see some examples, as shown in the following table:

Use your favorite database querying tool, like Visual Studio Server Explorer or Visual Studio Code’s mssql extension, to connect to your Northwind database and try out some of the queries above, as shown in Figure 2.10 and Figure 2.11:

Figure 2.10: Executing T-SQL queries using Visual Studio’s Server Explorer

Figure 2.11: Executing T-SQL queries using Visual Studio Code’s mssql extension

DML for adding, updating, and deleting data

DML statements for adding, updating, and deleting data include:

Data Definition Language (DDL)

DDL statements change the structure of the database, including creating new objects like tables, functions, and stored procedures. The following table shows some examples of DDL statements to give you an idea, but the examples are simple and cannot be executed within the Northwind database.

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications. It is known as the Microsoft ADO.NET driver for SQL Server and Azure SQL Database.

The Microsoft.Data.SqlClient package supports the following .NET platforms:

• .NET Framework 4.6.2 and later.
• .NET Core 3.1 and later.
• .NET Standard 2.0 and later.

Understanding the types in ADO.NET

ADO.NET defines abstract types that represent minimal objects for working with data, like DbConnection, DbCommand, and DbDataReader. Database software manufacturers can inherit from and provide specific implementations that are optimized for and expose additional features for their database. Microsoft has done this for SQL Server. The most important types with their most used members are shown in the following table:

SqlConnection has two useful events: StateChange and InfoMessage.

All the ExecuteXxx methods will execute any command. The one you use depends on what you expect to get back:

• If the command includes at least one SELECT statement that returns a result set, then call ExecuteReader to execute the command. This method returns a DbDataReader-derived object for reading row-by-row through the result set.
• If the command does not include at least one SELECT statement, then it is more efficient to call ExecuteNonQuery. This method returns an integer for the number of rows affected.
• If the command includes at least one SELECT statement that returns XML because it uses the AS XML command, then call ExecuteXmlReader to execute the command.

Creating a console app for working with ADO.NET

First, we will create a console app project for working with ADO.NET:

1. Use your preferred code editor to create a new solution/workspace named Chapter02.
2. Add a console app project, as defined in the following list:
   • Project template: Console App/console
   • Workspace/solution file and folder: Chapter02
   • Project file and folder: Northwind.Console.SqlClient

   Good Practice: For all the projects that you create for this book, keep your root path short and avoid using # in your folder and file names, or you might see compiler errors like RSG002: TargetPath not specified for additional file. For example, do not use C:\My C# projects\ as your root path!

3. In the project file, treat warnings as errors, add a package reference for the latest version of Microsoft.Data.SqlClient, and statically and globally import System.Console, as shown highlighted in the following markup:

   <Project Sdk="Microsoft.NET.Sdk">
     <PropertyGroup>
       <OutputType>Exe</OutputType>
       <TargetFramework>net7.0</TargetFramework>
       <ImplicitUsings>enable</ImplicitUsings>
       <Nullable>enable</Nullable>
       <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
     </PropertyGroup>
     <ItemGroup>
       <PackageReference Include="Microsoft.Data.SqlClient" Version="5.0.0" />
     </ItemGroup>
     <ItemGroup>
       <Using Include="System.Console" Static="true" />
     </ItemGroup>
   </Project>
   

4. Build the project to restore the referenced package.
5. Add a new class file named Program.EventHandlers.cs, and modify its contents to define methods that will act as event handlers for a database connection state change by showing the original and current states, and for when the database sends an InfoMessage, as shown in the following code:

   using Microsoft.Data.SqlClient; // SqlInfoMessageEventArgs
   using System.Data; // StateChangeEventArgs
   partial class Program
   {
     static void Connection_StateChange(object sender, StateChangeEventArgs e)
     {
       ConsoleColor previousColor = ForegroundColor;
       ForegroundColor = ConsoleColor.DarkYellow;
       WriteLine($"State change from {e.OriginalState} to {e.CurrentState}.");
       ForegroundColor = previousColor;
     }
     static void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
     {
       ConsoleColor previousColor = ForegroundColor;
       ForegroundColor = ConsoleColor.DarkBlue;
       WriteLine($"Info: {e.Message}.");
       foreach(SqlError error in e.Errors)
       {
         WriteLine($"  Error: {error.Message}.");
       }
       ForegroundColor = previousColor;
     }
   }
   

6. In Program.cs, delete the existing statements. Add statements to connect to SQL Server locally, to Azure SQL Database, or to SQL Edge, using either SQL authentication with a user ID and password or Windows Authentication without a user ID and password, as shown in the following code:

   using Microsoft.Data.SqlClient; // SqlConnection and so on
   SqlConnectionStringBuilder builder = new();
   builder.InitialCatalog = "Northwind";
   builder.MultipleActiveResultSets = true;
   builder.Encrypt = true;
   builder.TrustServerCertificate = true;
   builder.ConnectTimeout = 10;
   WriteLine("Connect to:");
   WriteLine("  1 - SQL Server on local machine");
   WriteLine("  2 - Azure SQL Database");
   WriteLine("  3 – Azure SQL Edge");
   WriteLine();
   Write("Press a key: ");
   ConsoleKey key = ReadKey().Key;
   WriteLine(); WriteLine();
   if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
   {
     builder.DataSource = "."; // Local SQL Server
     // @".\net7book"; // Local SQL Server with an instance name
   }
   else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
   {
     builder.DataSource = // Azure SQL Database
       "tcp:apps-services-net7.database.windows.net,1433"; 
   }
   else if (key is ConsoleKey.D3 or ConsoleKey.NumPad3)
   {
     builder.DataSource = "tcp:127.0.0.1,1433"; // Azure SQL Edge
   }
   else
   {
     WriteLine("No data source selected.");
     return;
   }
   WriteLine("Authenticate using:");
   WriteLine("  1 – Windows Integrated Security");
   WriteLine("  2 – SQL Login, for example, sa");
   WriteLine();
   Write("Press a key: ");
   key = ReadKey().Key;
   WriteLine(); WriteLine();
   if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
   {
     builder.IntegratedSecurity = true;
   }
   else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
   {
     builder.UserID = "sa"; // Azure SQL Edge
       // "markjprice"; // change to your username
     Write("Enter your SQL Server password: ");
     string? password = ReadLine();
     if (string.IsNullOrWhiteSpace(password))
     {
       WriteLine("Password cannot be empty or null.");
       return;
     }
     builder.Password = password;
     builder.PersistSecurityInfo = false;
   }
   else
   {
     WriteLine("No authentication selected.");
     return;
   }
   SqlConnection connection = new(builder.ConnectionString);
   WriteLine(connection.ConnectionString);
   WriteLine();
   connection.StateChange += Connection_StateChange;
   connection.InfoMessage += Connection_InfoMessage;
   try
   {
     WriteLine("Opening connection. Please wait up to {0} seconds...", 
       builder.ConnectTimeout);
     WriteLine();
     connection.Open();
     WriteLine($"SQL Server version: {connection.ServerVersion}");
     connection.StatisticsEnabled = true;
   }
   catch (SqlException ex)
   {
     WriteLine($"SQL exception: {ex.Message}");
     return;
   }
   connection.Close();
   

7. Run the console app, select options that work with your SQL Server set up, and note the results, as shown in the following output:

   Connect to:
     1 - SQL Server on local machine
     2 - Azure SQL Database
     3 - Azure SQL Edge
   Press a key: 1
   Authenticate using:
     1 - Windows Integrated Security
     2 - SQL Login, for example, sa
   Press a key: 1
   Data Source=.;Initial Catalog=Northwind;Integrated Security=True;Multiple Active Result Sets=True;Connect Timeout=10;Encrypt=True;Trust Server Certificate=True
   Opening connection. Please wait up to 10 seconds...
   State change from Closed to Open.
   SQL Server version: 15.00.2095
   State change from Open to Closed.
   

   The following steps show the experience when connecting to Azure SQL Database or Azure SQL Edge, which require a username and password. If you are connecting to a local SQL Server using Windows Integrated Security, then you will not need to enter a password.

8. Run the console app, select either Azure SQL Database or Azure SQL Edge, enter your password, and note the result, as shown in the following output:

   Connect to:
     1 - SQL Server on local machine
     2 - Azure SQL Database
     3 - Azure SQL Edge
   Press a key: 3
   Authenticate using:
     1 - Windows Integrated Security
     2 - SQL Login, for example, sa
   Press a key: 2
   Enter your SQL Server password: s3cret-Ninja
   State change from Closed to Open.
   SQL Server version: 15.00.0041
   State change from Open to Closed.
   

9. Run the console app, enter a wrong password, and note the result, as shown in the following output:

   Enter your SQL Server password: silly-ninja
   SQL exception: Login failed for user 'sa'.
   

10. In Program.cs, change the server name to something wrong.
11. Run the console app and note the result, as shown in the following output:

    SQL exception: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)
    

Executing queries and working with data readers using ADO.NET

Now that we have a successful connection to the SQL Server database, we can run commands and process the results using a data reader.

1. In Program.cs, import the namespace for working with ADO.NET command types, as shown in the following code:

   using System.Data; // CommandType
   

2. Before the statement that closes the connection, add statements to define a command that selects the ID, name, and price from the Products table, executes it, and outputs the product IDs, names, and prices using a data reader, as shown in the following code:

   SqlCommand cmd = connection.CreateCommand();
   cmd.CommandType = CommandType.Text;
   cmd.CommandText = "SELECT ProductId, ProductName, UnitPrice FROM Products";
   SqlDataReader r = cmd.ExecuteReader();
   WriteLine("----------------------------------------------------------");
   WriteLine("| {0,5} | {1,-35} | {2,8} |", "Id", "Name", "Price");
   WriteLine("----------------------------------------------------------");
   while (r.Read())
   {
     WriteLine("| {0,5} | {1,-35} | {2,8:C} |",
       r.GetInt32("ProductId"),
       r.GetString("ProductName"),
       r.GetDecimal("UnitPrice"));
   }
   WriteLine("----------------------------------------------------------");
   r.Close();
   

   We format the unit price using the C format which uses your OS current culture to format currency values. My output uses £ because I am in the UK. You will learn how to control the current culture in Chapter 7, Handling Dates, Times, and Internationalization.

3. Run the console app and note the results, as shown in the following partial output:

   ----------------------------------------------------------
   |    Id | Name                                |    Price |
   ----------------------------------------------------------
   |     1 | Chai                                |   £18.00 |
   |     2 | Chang                               |   £19.00 |
   ...
   |    76 | Lakkalikööri                        |   £18.00 |
   |    77 | Original Frankfurter grüne Soße     |   £13.00 |
   ----------------------------------------------------------
   

4. In Program.cs, modify the SQL statement to define a parameter for the unit price and use it to filter the results to products that cost more than that unit price, as shown highlighted in the following code:

   Write("Enter a unit price: ");
   string? priceText = ReadLine();
   if(!decimal.TryParse(priceText, out decimal price))
   {
     WriteLine("You must enter a valid unit price.");
     return;
   }
   SqlCommand cmd = connection.CreateCommand();
   cmd.CommandType = CommandType.Text;
   cmd.CommandText = "SELECT ProductId, ProductName, UnitPrice FROM Products" 
     + " WHERE UnitPrice > @price";
   cmd.Parameters.AddWithValue("price", price);
   

5. Run the console app, enter a unit price like 50, and note the results, as shown in the following partial output:

   Enter a unit price: 50
   ----------------------------------------------------------
   |    Id | Name                                |    Price |
   ----------------------------------------------------------
   |     9 | Mishi Kobe Niku                     |   £97.00 |
   |    18 | Carnarvon Tigers                    |   £62.50 |
   |    20 | Sir Rodney's Marmalade              |   £81.00 |
   |    29 | Thüringer Rostbratwurst             |  £123.79 |
   |    38 | Côte de Blaye                       |  £263.50 |
   |    51 | Manjimup Dried Apples               |   £53.00 |
   |    59 | Raclette Courdavault                |   £55.00 |
   ----------------------------------------------------------
   

Working with ADO.NET asynchronously

You can improve the responsiveness of data access code by making it asynchronous. You will see more details of how asynchronous operations work in Chapter 4, Benchmarking Performance, Multitasking, and Concurrency. For now, just enter the code as instructed.

Let’s see how to change the statements to work asynchronously:

1. In Program.cs, change the statement to open the connection to make it asynchronous, as shown in the following code:

   await connection.OpenAsync();
   

2. In Program.cs, change the statement to execute the command to make it asynchronous, as shown in the following code:

   SqlDataReader r = await cmd.ExecuteReaderAsync();
   

3. In Program.cs, change the statements to read the next row and get the field values to make them asynchronous, as shown in the following code:

   while (await r.ReadAsync())
   {
     WriteLine("| {0,5} | {1,-35} | {2,8:C} |",
       await r.GetFieldValueAsync<int>("ProductId"),
       await r.GetFieldValueAsync<string>("ProductName"),
       await r.GetFieldValueAsync<decimal>("UnitPrice"));
   }
   

4. In Program.cs, change the statements to close the data reader and connection to make them asynchronous, as shown in the following code:

   await r.CloseAsync();
   await connection.CloseAsync();
   

5. Run the console app and confirm that it has the same results as before, but it would run better in a multithreaded system, for example, not blocking the user interface in a GUI app, and not blocking IO threads in a website.

Executing stored procedures using ADO.NET

If you need to execute the same query or another SQL statement multiple times, it is best to create a stored procedure, often with parameters, so that it can be precompiled and optimized. Parameters have a direction to indicate if they are inputs, outputs, or return values.

Let’s see an example that uses all three types of direction:

1. In your preferred database tool, connect to the Northwind database.
2. In your preferred database tool, add a new stored procedure. For example, if you are using SQL Server Management Studio, then right-click Stored Procedures and select Add New Stored Procedure.
3. Modify the SQL statements to define a stored procedure named GetExpensiveProducts with two parameters, an input parameter for the minimum unit price and an output parameter for the row count of matching products, as shown in the following code:

   CREATE PROCEDURE [dbo].[GetExpensiveProducts]
       @price money,
       @count int OUT
   AS
       SELECT @count = COUNT(*)
       FROM Products
       WHERE UnitPrice > @price
       SELECT * 
       FROM Products
       WHERE UnitPrice > @price
   RETURN 0
   

   The stored procedure uses two SELECT statements. The first sets the @count output parameter to a count of the matching product rows. The second returns the matching product rows.

4. Right-click in the SQL statements and select Execute.
5. Right-click Stored Procedures and select Refresh.
6. Expand GetExpensiveProducts and note the input and output parameters, as shown in Visual Studio’s Server Explorer in Figure 2.12:

Figure 2.12: Parameters of the GetExpensiveProducts stored procedure

7. Close the SQL query without saving changes.
8. In Program.cs, add statements to allow the user to choose between running the text command and the stored procedure. Add statements defining the stored procedure and its parameters, and then execute the command, as shown highlighted in the following code:

   SqlCommand cmd = connection.CreateCommand();
   WriteLine("Execute command using:");
   WriteLine("  1 - Text");
   WriteLine("  2 - Stored Procedure");
   WriteLine();
   Write("Press a key: ");
   key = ReadKey().Key;
   WriteLine(); WriteLine();
   SqlParameter p1, p2 = new(), p3 = new();
   if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
   {
     cmd.CommandType = CommandType.Text;
     cmd.CommandText = "SELECT ProductId, ProductName, UnitPrice FROM Products"
       + " WHERE UnitPrice > @price";
     cmd.Parameters.AddWithValue("price", price);
   }
   else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
   {
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.CommandText = "GetExpensiveProducts";
     p1 = new()
     {
       ParameterName = "price",
       SqlDbType = SqlDbType.Money,
       SqlValue = price
     };
     p2 = new()
     {
       Direction = ParameterDirection.Output,
       ParameterName = "count",
       SqlDbType = SqlDbType.Int
     };
     p3 = new()
     {
       Direction= ParameterDirection.ReturnValue,
       ParameterName = "rv",
       SqlDbType = SqlDbType.Int
     };
     cmd.Parameters.Add(p1);
     cmd.Parameters.Add(p2);
     cmd.Parameters.Add(p3);
   }
   SqlDataReader r = await cmd.ExecuteReaderAsync();
   

9. After the statement that closes the data reader, add statements to output the output parameter and the return value, as shown highlighted in the following code:

   await r.CloseAsync();
   WriteLine($"Output count: {p2.Value}");
   WriteLine($"Return value: {p3.Value}");
   await connection.CloseAsync();
   

   If a stored procedure returns result sets as well as parameters, then the data reader for the result sets must be closed before the parameters can be read.

10. Run the console app and note the results if the price entered is 60, as shown in the following output:

    Enter a unit price: 60
    Execute command using:
      1 - Text
      2 - Stored Procedure
    Press a key: 2
    ----------------------------------------------------------
    |    Id | Name                                |    Price |
    ----------------------------------------------------------
    |     9 | Mishi Kobe Niku                     |   £97.00 |
    |    18 | Carnarvon Tigers                    |   £62.50 |
    |    20 | Sir Rodney's Marmalade              |   £81.00 |
    |    29 | Thüringer Rostbratwurst             |  £123.79 |
    |    38 | Côte de Blaye                       |  £263.50 |
    ----------------------------------------------------------
    Output count: 5
    Return value: 0
    State change from Open to Closed.