Search icon CANCEL
Subscription
0
Cart icon
Cart
Close icon
You have no products in your basket yet
Save more on your purchases!
Savings automatically calculated. No voucher code required
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
Apps and Services with .NET 8 - Second Edition
Apps and Services with .NET 8 - Second Edition

Apps and Services with .NET 8: Build practical projects with Blazor, .NET MAUI, gRPC, GraphQL, and other enterprise technologies, Second Edition

eBook
$39.99 $27.98
Print
$49.99
Subscription
Free Trial
Renews at $15.99p/m

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon AI Assistant (beta) to help accelerate your learning
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Table of content icon View table of contents Preview book icon Preview Book

Apps and Services with .NET 8 - Second Edition

Managing Relational Data Using SQL Server

This chapter is about managing relational data stored in SQL Server, Azure SQL Database, or Azure SQL Edge. First, you will learn how to manage the data using native Transact-SQL statements. Next, you will learn how to manage data at a low level using ADO.NET libraries (Microsoft.Data.SqlClient). Finally, you will use Dapper to make it easier to work with entity models.

This chapter will cover the following topics:

  • Understanding modern databases
  • Managing data with Transact-SQL
  • Managing SQL Server data with low-level APIs
  • Managing SQL Server data with Dapper
  • Cleaning up data resources

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, on both Intel and ARM architecture CPUs.

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.
  • The 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 name of the instance if it has more than the default 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 that will be active initially. A SQL statement could change that using the command: USE <databasename>.
  • 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 Table 2.1:

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-book

Azure SQL Edge (local Docker)

tcp:127.0.0.1,1433

Azure SQL Database

tcp:[custom server name].database.windows.net,1433

Table 2.1: Server name examples for various editions of SQL Server

Good Practice: Use a dot (.) as shorthand for the local computer name (localhost). 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 locally

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 the topic Setting up Azure SQL Database, or the online-only section Installing Azure SQL Edge in Docker found at the following link:

https://github.com/markjprice/apps-services-net8/blob/main/docs/ch02-sql-edge.md

If you prefer to install SQL Server locally on Linux, then you will find instructions at the following link: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup.

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.

Take 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, as shown in Figure 2.2:

Figure 2.2: Installing a new instance of SQL Server

  1. Select Developer as the free edition and then click Next.
  2. Accept the license terms and then click Next.
  3. Review the Microsoft Update options and then click Next.
  4. Review the install rules, fix any issues although you might want to ignore any firewall warnings since you might not want to expose those ports anyway, and then click Next.
  5. In Feature Selection, select Database Engine Services, and then click Next.
  6. In Azure Extension for SQL Server, you can turn this off.
  7. 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 apps-services-book.
  8. In Server Configuration, note the SQL Server Database Engine is configured to start automatically. If not already set by default, then set the SQL Server Browser to start automatically, and then click Next.
  9. 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.
  10. In Ready to Install, review the actions that will be taken, and then click Install.
  11. In Complete, note the successful actions taken, and then click Close.
  12. In SQL Server Installation Center, in Installation, click Install SQL Server Management Tools.
  13. In the browser window, click to download the latest version of SSMS, as shown in Figure 2.3:

    Figure 2.3: Downloading SQL Server Management Studio (SSMS)

  1. Run the SSMS installer and click Install.
  2. 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.4:

Graphical user interface, text, application

Description automatically generated

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

Creating the Northwind sample database locally

Now we can run a database script to create the Northwind sample database locally 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-net8/.
  2. In your apps-services-net8 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: /scripts/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.

    Warning! If you had to create a named instance, like apps-services-book, then enter .\apps-services-book. If you see an error about the server certificate, then click the Options >> button and select the Trust server certificate check box.

  1. Navigate to File | Open | File....
  2. Browse to select the Northwind4SQLServer.sql file and then click Open.
  3. In the toolbar, click Execute, and note the Command(s) completed successfully message.
  4. In Object Explorer, expand the Northwind database, and then expand Tables.
  5. Right-click Products, click Select Top 1000 Rows, and note the returned results, as shown in Figure 2.5:
Graphical user interface, text, application

Description automatically generated

Figure 2.5: The Products table in SQL Server Management Studio

  1. In the Object Explorer toolbar, click the Disconnect button.
  2. 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 separately 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. Next, you need to take the following steps:

  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-book and select a suitable region close to you, and then click the Review + create button, as shown in Figure 2.6:

Figure 2.6: Creating a resource group in the Azure portal

  1. Review your choices and then click the Create button.
  2. Create another resource, search for SQL Database, and click Create.
  3. 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.
  4. In the Server section, click Create New.
  5. Enter the following details for the SQL Database server, as shown in Figure 2.7:
    • Server name: apps-services-book-[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 resources. 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.7: Entering the server details for a SQL Database instance

  1. Click OK.
  2. Leave Want to use SQL elastic pool set to No.
  3. For Workload environment, select Development (instead of Production).
  4. In the Create SQL Database page, in the Compute + storage section, click Configure database.
  5. For Service tier, select Basic (For less demanding workloads). Note the maximum database size is 2 GB and the estimated cost is about $5 per month (or less than 1 cent per hour). You can delete the resources as soon as you have completed this chapter to reduce the cost further.
  6. Click Apply.
  7. In the Create SQL Database page, set Backup storage redundancy to Locally-redundant backup storage.
  8. Click the Next : Networking button.
  9. In the Network connectivity section, select Public endpoint.
  10. In the Firewall rules section, set Add current client IP address to Yes.
  11. Click the Next : Security button.
  12. Review the options but leave them as the defaults.
  13. Click the Next : Additional settings button.
  14. Review the options but leave them as the defaults.
  15. Click the Review + create button.
  16. Click the Create button.
  17. Wait for the deployment to complete, as shown in Figure 2.8:

Figure 2.8: Deployment progress for SQL Database

  1. When deployment has completed, click Go to resource.
  2. Click Overview and note the database details, as shown in Figure 2.9:

Figure 2.9: SQL Database details

  1. Click See connection strings (or click Connection strings in the left navigation).
  2. Copy the ADO.NET (SQL authentication) connection string to your clipboard.
  3. 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-book.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-book, is public and must be globally unique.

  1. Optionally, save the Notepad file for future reference.

JetBrains Rider tool window for working with SQL Server

If you use JetBrains Rider on any operating system, then you can use the following steps to connect with a SQL Server database:

  1. In JetBrains Rider, select View | Tool Windows | Database.
  2. In the Database tool window, click Connect to database....
  3. Select the Use connection string option button.
  4. Set the Database type to Microsoft SQL Server.
  5. In the String box, enter the database connection string.
  6. Change {your_password} to the password you chose.
  7. Optionally, click Test Connection and correct any errors if necessary. If you get an Inconsistent language error, then you can ignore it as we are using SQL Server as the dialect.
  8. Click Connect to Database.

Creating the Northwind sample database in the cloud

Now we can run a database script to create the Northwind sample database in the Azure SQL Database:

  1. Use your preferred database tool to connect to the SQL server in Azure:
    • In Visual Studio 2022, view Server Explorer.
    • On Windows, start SQL Server Management Studio.
    • In Visual Studio Code, view the SQL Server tool.
    • In JetBrains Rider, navigate to View | Tool Windows | Database, and then click Connect to database….
  2. Add a data connection, and fill in the dialog box with all the required connection string information, as shown in Figure 2.10:

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

    You might also be prompted to Choose Data Source. Choose Microsoft SQL Server. You can select a checkbox to always use this selection.

  1. Right-click the data connection and choose New Query.

    If you are using JetBrains Rider, then right-click the SQL Server, in the popup menu, select SQL Scripts | Run SQL Script…, and then select the Northwind4AzureSQLdatabase.sql file.

  1. 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-net8/tree/main/scripts/sql-scripts.

  1. Wait to see the Command completed successfully message. This can take a few minutes.
  2. 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 in the cloud that you can connect to from a .NET project.

Managing data with Transact-SQL

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 data stored in SQL Server tables can be treated as case-sensitive or not, depending on the configuration.

The complete reference for T-SQL is found at the following link: https://learn.microsoft.com/en-us/sql/t-sql/language-reference. From that documentation starting page, use the left side navigation to view topics like Data types, Queries, and Statements.

T-SQL data types

T-SQL has data types that are used for columns, variables, parameters, and so on, as shown in Table 2.2:

Category

Examples

Numbers

bigint, bit, decimal, float, int, money, numeric, real, smallint, smallmoney, tinyint

Date and time

date, datetime2, datetime, datetimeoffset, smalldatetime, time

Text

char, nchar, ntext, nvarchar, text, varchar

Binary

binary, image, varbinary

Other

cursor, hierarchyid, sql_variant, table, rowversion, uniqueidentifier, xml

Table 2.2: Categories of SQL Server data types

There is an xml data type but no JSON data type. Use nvarchar to store JSON values. T-SQL also has support for spatial geometry and geography types.

Documenting with comments

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

To comment out a block, use /* at 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), which 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.-,], which looks like a simplified regular expression syntax but keep in mind that it is not regular expression syntax.

If a table or column name contains spaces, then you must surround the name in square brackets, like [Order Details]. The SQL scripts to create the Northwind database include the command set quoted_identifier on, so you can also use double quotes, like "Order Details". Single quotes are used for literal text, like 'USA'.

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 Table 2.3:

Example

Description

SELECT *

FROM Employees

Get all columns of all the employees.

SELECT FirstName, LastName

FROM Employees

Get the first and last name columns of all employees.

SELECT emp.FirstName, emp.LastName

FROM Employees AS emp

Give an alias for the table name. Table name prefixes are not needed when there is only one table, but become useful to disambiguate when there are multiple tables that have columns with the same name, for example, Customers.CustomerId and Orders.CustomerId.

SELECT emp.FirstName, emp.LastName

FROM Employees emp

Give an alias for the table name without needing the AS keyword.

SELECT FirstName, LastName AS Surname

FROM Employees

Give an alias for the column name.

SELECT FirstName, LastName

FROM Employees

WHERE Country = 'USA'

Filter the results to only include employees in the USA.

SELECT DISTINCT Country

FROM Employees

Get a list of countries used as values in the Country column of the Employees table without duplicates.

SELECT UnitPrice * Quantity AS Subtotal

FROM [Order Details]

Calculate a subtotal for each order detail row.

SELECT OrderId,

SUM(UnitPrice * Quantity) AS Total

FROM [Order Details]

GROUP BY OrderId

ORDER BY Total DESC

Calculate a total for each order and sort with the largest order value at the top.

SELECT CompanyName

FROM Customers

UNION

SELECT CompanyName

FROM Suppliers

Return all the company names of all customers and suppliers.

SELECT CategoryName, ProductName

FROM Categories, Products

Match every category with every product using a Cartesian join and output their names (not what you normally want!).

616 rows (8 categories x 77 products).

SELECT CategoryName, ProductName

FROM Categories c, Products p

WHERE c.CategoryId = p.CategoryId

Match each product with its category using a WHERE clause for the CategoryId column in each table and output the category name and product name.

77 rows.

SELECT CategoryName, ProductName

FROM Categories c

INNER JOIN Products p

ON c.CategoryId = p.CategoryId

Match each product with its category using an INNER JOIN...ON clause for the CategoryId column in each table and output the category name and product name. This is a modern alternative syntax to using WHERE, and it allows outer joins, which would also include non-matches.

77 rows.

Table 2.3: Example SELECT statements with descriptions

More Information: You can read the full documentation for SELECT at the following link: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql.

Use your favorite database querying tool, like Visual Studio’s 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.11 and Figure 2:12:

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

Figure 2.12: 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 those shown in Table 2.4:

Example

Description

INSERT Employees(FirstName, LastName)

VALUES('Mark', 'Price')

Add a new row to the Employees table. The EmployeeId primary key value is automatically assigned. Use @@IDENTITY to get this value.

UPDATE Employees

SET Country = 'UK'

WHERE FirstName = 'Mark'

AND LastName = 'Price'

Update my employee row to set my Country to UK.

DELETE Employees

WHERE FirstName = 'Mark'

AND LastName = 'Price'

Delete my employee row.

DELETE Employees

Delete all rows in the Employees table and record those deletions in the transaction log.

TRUNCATE TABLE Employees

Delete all rows in the Employees table more efficiently because it does not log the individual row deletions.

Table 2.4: Example DML statements with descriptions

The above examples use the Employees table in the Northwind database. That table has referential integrity constraints that would mean that, for example, deleting all rows in the table cannot happen because every employee has related data in other tables like Orders.

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, as shown in Table 2.5:

Example

Description

CREATE TABLE dbo.Shippers (

ShipperId INT PRIMARY KEY CLUSTERED,

CompanyName NVARCHAR(40)

);

Create a table to store shippers.

ALTER TABLE Shippers

ADD Country NVARCHAR(40)

Add a column to a table.

CREATE NONCLUSTERED INDEX IX_Country

ON Shippers(Country)

Add a non-clustered index for a column in a table.

CREATE INDEX IX_FullName

ON Employees(LastName, FirstName DESC)

WITH (DROP_EXISTING = ON)

Change an aggregate index with multiple columns and control the sort order.

DROP TABLE Employees

Delete the Employees table. If it does not exist, then this throws an error.

DROP TABLE IF EXISTS Employees

Delete the Employees table if it already exists. This avoids the potential error from using the statement in the previous row.

IF OBJECT_ID(N'Employees', N'U')

IS NOT NULL

Check if a table exists. The N prefix before a text literal means Unicode. 'U' means a user table as opposed to a system table.

Table 2.5: Example DDL statements with descriptions

Managing data with low-level APIs

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

More Information: You can find the GitHub repository for ADO.NET at the following link: https://github.com/dotnet/SqlClient.

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 Table 2.6:

Type

Properties

Methods

Description

SqlConnection

ConnectionString,

State,

ServerVersion

Open, Close,

CreateCommand,

RetrieveStatistics

Manage the connection to the database.

SqlConnectionStringBuilder

InitialCatalog, DataSource, Encrypt, UserID, Password, ConnectTimeout, and so on

Clear, ContainsKey, Remove

Build a valid connection string for a SQL Server database.

After setting all the relevant individual properties, get the ConnectionString property.

SqlCommand

Connection,

CommandType,

CommandText,

Parameters,

Transaction

ExecuteReader,

ExecuteNonQuery,

ExecuteXmlReader,

CreateParameter

Configure the command to execute.

SqlParameter

ParameterName,

Value, DbType,

SqlValue,

SqlDbType,

Direction,

IsNullable

Configure a parameter for a command.

SqlDataReader

FieldCount,

HasRows,

IsClosed,

RecordsAffected

Read, Close,

GetOrdinal,

GetInt32, GetString,

GetDecimal,

GetFieldValue<T>

Process the result set from executing a query.

Table 2.6: Important types in ADO.NET SqlClient

SqlConnection has two useful events: StateChange and InfoMessage.

All the ExecuteXxx methods of SqlCommand 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 console app project, as defined in the following list:
    • Project template: Console App / console.
    • Solution file and folder: Chapter02.
    • Project file and folder: Northwind.Console.SqlClient.
    • Do not use top-level statements: Cleared.
    • Enable native AOT publish: Cleared.

    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!

  1. 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>net8.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
        <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
      </PropertyGroup>
      <ItemGroup>
        <PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.2" />
      </ItemGroup>
      <ItemGroup>
        <Using Include="System.Console" Static="true" />
      </ItemGroup>
    </Project>
    

    You can check the most recent version of the package at the following link: https://www.nuget.org/packages/Microsoft.Data.SqlClient#versions-body-tab.

  1. Build the project to restore the referenced package.
  2. Add a new class file named Program.Helpers.cs, and modify its contents to define a method to configure the console to enable special characters like the Euro currency symbol and set the current culture, and a method that will output some text to the console in a specified color, with a default color of black, as shown in the following code:
    using System.Globalization; // To use CultureInfo.
    partial class Program
    {
      private static void ConfigureConsole(string culture = "en-US",
        bool useComputerCulture = false)
      {
        // To enable Unicode characters like Euro symbol in the console.
        OutputEncoding = System.Text.Encoding.UTF8;
        if (!useComputerCulture)
        {
          CultureInfo.CurrentCulture = CultureInfo.GetCultureInfo(culture);
        }
        WriteLine($"CurrentCulture: {CultureInfo.CurrentCulture.DisplayName}");
      }
      private static void WriteLineInColor(string value, 
        ConsoleColor color = ConsoleColor.White)
      {
        ConsoleColor previousColor = ForegroundColor;
        ForegroundColor = color;
        WriteLine(value);
        ForegroundColor = previousColor;
      }
    }
    

    The default foreground color in the preceding code is white because I have assumed that most readers will have a default background color of black. On my computer, I set the default background color of the console to white so that I can take screenshots for this book. Set whatever default color is best for your computer.

  1. 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; // To use SqlInfoMessageEventArgs.
    using System.Data; // To use StateChangeEventArgs.
    partial class Program
    {
      private static void Connection_StateChange(
        object sender, StateChangeEventArgs e)
      {
        WriteLineInColor(
          $"State change from {e.OriginalState} to {e.CurrentState}.",
          ConsoleColor.DarkYellow);
      }
      private static void Connection_InfoMessage(
        object sender, SqlInfoMessageEventArgs e)
      {
        WriteLineInColor($"Info: {e.Message}.", ConsoleColor.DarkBlue);
      }
    }
    
  2. 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; // To use SqlConnection and so on.
    ConfigureConsole();
    #region Set up the connection string builder
    SqlConnectionStringBuilder builder = new()
    {
      InitialCatalog = "Northwind",
      MultipleActiveResultSets = true,
      Encrypt = true,
      TrustServerCertificate = true,
      ConnectTimeout = 10 // Default is 30 seconds.
    };
    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();
    switch (key)
    {
      case ConsoleKey.D1 or ConsoleKey.NumPad1:
        builder.DataSource = ".";
        break;
      case ConsoleKey.D2 or ConsoleKey.NumPad2:
        builder.DataSource = 
          // Use your Azure SQL Database server name.
          "tcp:apps-services-book.database.windows.net,1433";
        break;
      case ConsoleKey.D3 or ConsoleKey.NumPad3:
        builder.DataSource = "tcp:127.0.0.1,1433";
        break;
      default:
        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)
    {
      Write("Enter your SQL Server user ID: ");
      string? userId = ReadLine();
      if (string.IsNullOrWhiteSpace(userId))
      {
        WriteLine("User ID cannot be empty or null.");
        return;
      }
      builder.UserID = userId;
      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;
    }
    #endregion
    #region Create and open the connection
    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}");
    }
    catch (SqlException ex)
    {
      WriteLineInColor($"SQL exception: {ex.Message}", 
        ConsoleColor.Red);
      return;
    }
    #endregion
    connection.Close();
    

    Good Practice: In this coding task, we prompt the user to enter the password to connect to the database. In a real-world app you are more likely to store the password in an environment variable or secure storage like Azure Key Vault. You must definitely never store passwords in your source code!

  1. Run the console app, select options that work with your SQL Server setup, and note the results, including the state change event output written in dark yellow to make them easier to see, 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.2101
    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.

  1. Run the console app, select choices that require a user ID and password, for example, with Azure SQL Database, and note the result, as shown in the following output:
    Enter your SQL Server user ID: markjprice
    Enter your SQL Server password: [censored]
    Data Source=tcp:apps-services-book.database.windows.net,1433;Initial Catalog=Northwind;Persist Security Info=False;User ID=markjprice;Password=[censored];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: 12.00.5168
    State change from Open to Closed.
    
  2. Run the console app, select choices that require a user ID and password, enter a wrong password, and note the result, as shown in the following output:
    Enter your SQL Server user ID: markjprice
    Enter your SQL Server password: 123456
    Data Source=tcp:apps-services-book.database.windows.net,1433;Initial Catalog=Northwind;Persist Security Info=False;User ID=markjprice;Password=123456;Multiple Active Result Sets=True;Connect Timeout=10;Encrypt=True;Trust Server Certificate=True
    Opening connection. Please wait up to 10 seconds...
    SQL exception: Login failed for user 'markjprice'.
    
  3. In Program.cs, change the server name (the DataSource property) to something wrong.
  4. Run the console app and note the result (depending on where your database is hosted, the exception message might be slightly different), 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.)
    

When opening a SQL Server connection, the default timeout is 30 seconds for server connection problems, so be patient! We changed the timeout to 10 seconds to avoid having to wait so long.

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 that retrieve rows from a table 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; // To use CommandType.
    

    Good Practice: To save space in this book, I will use the names cmd and r to represent an SQL command and an SQL data reader. In your code, give variables proper word names like command and reader.

  1. 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();
    string horizontalLine = new string('-', 60);
    WriteLine(horizontalLine);
    WriteLine("| {0,5} | {1,-35} | {2,10} |", 
      arg0: "Id", arg1: "Name", arg2: "Price");
    WriteLine(horizontalLine);
    while (r.Read())
    {
      WriteLine("| {0,5} | {1,-35} | {2,10:C} |",
        r.GetInt32("ProductId"), 
        r.GetString("ProductName"),
        r.GetDecimal("UnitPrice"));
    }
    WriteLine(horizontalLine);
    r.Close();
    

    We format the unit price using the C format, which uses the current culture to format currency values. The call to ConfigureConsole sets the current culture to US English so the output for all readers uses $. To test alternative cultures like French that use the Euro currency symbol, modify the call at the top of the Program.cs file, as shown in the following code: ConfigureConsole("fr-FR");.

  1. 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 |
    ----------------------------------------------------------
    
  2. 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 >= @minimumPrice";
    cmd.Parameters.AddWithValue("minimumPrice", price);
    
  3. 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 |
    ----------------------------------------------------------
    

Outputting statistics

An ADO.NET connection can track useful statistics during its lifetime, including those listed in Table 2.7:

Key

Description

BuffersReceived, BuffersSent, BytesReceived, BytesSent

Data is transmitted as bytes stored in buffers.

CursorOpens

Cursors are an expensive operation because they require state on the server, and should be avoided when possible.

Prepares, PreparedExecs, UnpreparedExecs

Number of prepares (compilations), executions of prepared commands, and executions of unprepared commands.

SelectCount, SelectRows

Number of SELECT statements and rows returned by SELECT statements.

ServerRoundtrips, SumResultSets, Transactions

Number of server round trips, result sets, and transactions.

ConnectionTime, ExecutionTime, NetworkServerTime

Time in milliseconds spent connected, executing commands, or due to the network.

Table 2.7: Connection statistics that can be tracked

Let’s enable this and output some of those statistics:

  1. In Program.Helpers.cs, import the namespaces for working with ADO.NET and common collections, as shown in the following code:
    using Microsoft.Data.SqlClient; // To use SqlConnection.
    using System.Collections; // To use IDictionary.
    
  2. In Program.Helpers.cs, in the partial Program class, add a method to output statistics about the current connection, with an array of string values to control which of the dozen or more statistics we want to output, as shown in the following code:
    private static void OutputStatistics(SqlConnection connection)
    {
      // Remove all the string values to see all the statistics.
      string[] includeKeys = { 
        "BytesSent", "BytesReceived", "ConnectionTime", "SelectRows" 
      };
      IDictionary statistics = connection.RetrieveStatistics();
      foreach (object? key in statistics.Keys)
      {
        if (!includeKeys.Any() || includeKeys.Contains(key))
        {
          if (int.TryParse(statistics[key]?.ToString(), out int value))
          {
            WriteLineInColor($"{key}: {value:N0}", ConsoleColor.Cyan);
          }
        }
      }
    }
    
  3. In Program.cs, after writing the SQL Server version to the console, add a statement to enable statistics for the connection, as shown highlighted in the following code:
    WriteLine($"SQL Server version: {connection.ServerVersion}");
    connection.StatisticsEnabled = true;
    
  4. In Program.cs, before closing the connection, add a statement to output statistics for the connection, as shown highlighted in the following code:
    OutputStatistics(connection);
    connection.Close();
    
  5. Run the console app and note the statistics, as shown in the following partial output:
    BytesReceived: 3,888
    BytesSent: 336
    SelectRows: 77
    ExecutionTime: 25
    

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 5, 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 highlighted in the following code:
    await connection.OpenAsync();
    
  2. In Program.cs, change the statement to execute the command to make it asynchronous, as shown highlighted 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 highlighted 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 highlighted 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 I/O 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. Stored procedure 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 parameter direction. First, we will create the stored procedure in the database:

  1. In your preferred database tool, connect to the Northwind database.
  2. In your preferred database tool, add a new stored procedure:
    • If you are using SQL Server Management Studio, then in Object Explorer, navigate to Databases | Northwind | Programmability, right-click Stored Procedures, and select New | Stored Procedure.
    • If you are using Visual Studio 2022, then in Server Explorer, right-click Stored Procedures and select Add New Stored Procedure.
    • If you are using Visual Studio Code, then in SQL Server, right-click your connection profile and select New Query.
    • If you are using JetBrains Rider, then in the Database toolbar, click the Jump to Query Console… button, and then remove any existing statements. As well as the following SQL statements, start with a command to set the active database to Northwind: USE Northwind GO. This should prevent JetBrains Rider from creating the stored procedure in the master database!
  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
      PRINT 'Getting expensive products: ' + 
        TRIM(CAST(@price AS NVARCHAR(10)))
      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. It also calls the PRINT command, which will raise the InfoMessage event.

  1. Right-click in the SQL statements and select Execute or Execute Query.
  2. Right-click Stored Procedures and select Refresh. In JetBrains Rider, it is named routines.
  3. Expand GetExpensiveProducts and note the @price money input, @count int input/output, and return value parameters, as shown in SQL Server Management Studio in Figure 2.13:

Figure 2.13: Parameters of the GetExpensiveProducts stored procedure

  1. Close the SQL query without saving changes.
  2. 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 >= @minimumPrice";
      cmd.Parameters.AddWithValue("minimumPrice", 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.AddRange(new[] { p1, p2, p3 });
    }
    SqlDataReader r = await cmd.ExecuteReaderAsync();
    
  3. 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();
    if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
    {
      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.

  1. Run the console app and note the results if the price entered is 60, and note the InfoMessage event handler writes a message in dark blue, as shown in the following output:
    Enter a unit price: 60
    Execute command using:
      1 - Text
      2 - Stored Procedure
    Press a key: 2
    Info: Getting expensive products: 60.00.
    ----------------------------------------------------------
    |    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.
    

Outputting streams with a data reader

In a real app or service, we would likely not output to the console. More likely, as we read each row with a data reader, we might output to a stream that writes HTML tags inside a web page, or text formats like XML and JSON for returning data from a service.

Let’s add the ability to generate a JSON file:

  1. In Program.cs, import the namespace for working efficiently with JSON and to statically import the Environment and Path classes, as shown in the following code:
    using System.Text.Json; // To use Utf8JsonWriter, JsonSerializer.
    using static System.Environment;
    using static System.IO.Path;
    
  2. In Program.cs, before the while statement that processes the data reader, add statements to define a file path for a JSON file, create a file stream, and start a JSON array, then in the while block, write a JSON object that represents each product row, and finally, end the array and close the stream, as shown highlighted in the following code:
    // Define a file path to write to.
    string jsonPath = Combine(CurrentDirectory, "products.json");
    await using (FileStream jsonStream = File.Create(jsonPath))
    {
      Utf8JsonWriter jsonWriter = new(jsonStream);
      jsonWriter.WriteStartArray();
      while (await r.ReadAsync())
      {
        WriteLine("| {0,5} | {1,-35} | {2,10:C} |",
          await r.GetFieldValueAsync<int>("ProductId"),
          await r.GetFieldValueAsync<string>("ProductName"),
          await r.GetFieldValueAsync<decimal>("UnitPrice"));
        jsonWriter.WriteStartObject();
        jsonWriter.WriteNumber("productId", 
          await r.GetFieldValueAsync<int>("ProductId"));
        jsonWriter.WriteString("productName", 
          await r.GetFieldValueAsync<string>("ProductName"));
        jsonWriter.WriteNumber("unitPrice", 
          await r.GetFieldValueAsync<decimal>("UnitPrice"));
        jsonWriter.WriteEndObject();
      }
      jsonWriter.WriteEndArray();
      jsonWriter.Flush();
      jsonStream.Close();
    }
    WriteLineInColor($"Written to: {jsonPath}", ConsoleColor.DarkGreen);
    
  3. Run the console app, enter a price of 60, and note the path to the JSON file, as shown in the following output:
    Written to: C:\apps-services-net8\Chapter02\Northwind.Console.SqlClient\bin\Debug\net8.0\products.json
    
  4. Open the products.json file and note that the JSON is written with no whitespace, so it all appears on one line, as shown in the following file:
    [{"productId":9,"productName":"Mishi Kobe Niku","unitPrice":97.0000},{"productId":18,"productName":"Carnarvon Tigers","unitPrice":62.5000},{"productId":20,"productName":"Sir Rodney\u0027s Marmalade","unitPrice":81.0000},{"productId":29,"productName":"Th\u00FCringer Rostbratwurst","unitPrice":123.7900},{"productId":38,"productName":"C\u00F4te de Blaye","unitPrice":263.5000}]
    
  5. If you are using Visual Studio 2022, then you can right-click and select Format Document, and note that it is now easier to read, as shown in Figure 2.14:

Figure 2.14: The products.json file generated from a data reader

Generating objects with a data reader

For maximum flexibility, we likely want to convert the rows in a data reader into object instances stored in an array or collection. After that, we could serialize the object graph however we want. ADO.NET does not have a built-in ability to map a data reader row to an object, so we will have to do it manually.

Let’s see an example:

  1. Add a new class file named Product.cs, and modify its contents to define a class to represent just the three columns we want from each row in the Products table, as shown in the following code:
    namespace Northwind.Models;
    public class Product
    {
      public int ProductId { get; set; }
      public string? ProductName { get; set; }
      public decimal? UnitPrice { get; set; }
    }
    

    Good Practice: In this task, we will use this type only for read-only instances, so we could have used an immutable record. But later we will need to change property values after the object is created, so we have to define a class instead.

  1. At the top of Program.cs, import the Northwind.Models namespace so we can use Product.
  2. In Program.cs, before creating the file stream, instantiate a list of products with an initial storage for 77 items (but this is not a limit) because when first created the Northwind database has 77 products, as shown highlighted in the following code:
    List<Product> products = new(capacity: 77);
    await using (FileStream jsonStream = File.Create(jsonPath))
    
  3. In the while block, add statements to instantiate the Product type per row in the data reader and add it to the list, as shown highlighted in the following code:
    while (await r.ReadAsync())
    {
      Product product = new()
      {
        ProductId = await r.GetFieldValueAsync<int>("ProductId"),
        ProductName = await r.GetFieldValueAsync<string>("ProductName"),
        UnitPrice = await r.GetFieldValueAsync<decimal>("UnitPrice")
      };
      products.Add(product);
      ...
    }
    
  4. Before closing the data reader, add a statement to use the static Serialize method of the JsonSerializer class to write the list of products to the console, as shown highlighted in the following code:
    WriteLineInColor(JsonSerializer.Serialize(products),
      ConsoleColor.Magenta);
    await r.CloseAsync();
    
  5. Run the console app, enter a price of 60, and note the JSON generated from the list of products, as shown in the following output:
    Written to: C:\apps-services-net8\Chapter02\Northwind.Console.SqlClient\bin\Debug\net8.0\products.json
    [{"ProductId":9,"ProductName":"Mishi Kobe Niku","UnitPrice":97.0000},{"ProductId":18,"ProductName":"Carnarvon Tigers","UnitPrice":62.5000},{"ProductId":20,"ProductName":"Sir Rodney\u0027s Marmalade","UnitPrice":81.0000},{"ProductId":29,"ProductName":"Th\u00FCringer Rostbratwurst","UnitPrice":123.7900},{"ProductId":38,"ProductName":"C\u00F4te de Blaye","UnitPrice":263.5000}]
    

Instead of manually instantiating objects, to simplify even more, we can use a simple object-relational mapper (ORM) like Dapper.

Managing data with Dapper

Dapper uses ADO.NET underneath when working with SQL Server. Because it is a higher-level technology, it is not as efficient as using ADO.NET directly, but it can be easier. Dapper is an alternative ORM to EF Core. It is more efficient because it extends the low-level ADO.NET IDbConnection interface with very basic functionality without trying to be all things to all people.

Dapper connection extension methods

Dapper adds three extension methods to any class that implements IDbConnection (like SqlConnection). They are Query<T>, Query, and Execute. Dapper will automatically open and close the associated connection as needed.

The Query<T> extension method is the most used because it runs any specified SQL command and then returns the results as an IEnumerable<T> (a sequence of objects). It is designed to run commands that retrieve data like SELECT. It has several parameters, as shown in Table 2.8:

Parameter

Description

string sql

This is the only mandatory parameter. It is either the text of a SQL command or the name of a stored procedure.

object param = null

A complex object for passing parameters used in the query. This can be an anonymous type.

IDbTransaction transaction = null

To manage distributed transactions.

bool buffered = true

By default, it will buffer the entire reader on return. With large datasets, you can minimize memory and only load objects as needed by setting buffered to false.

int? commandTimeout = null

To change the default command timeout.

CommandType? commandType = null)

To switch to a stored procedure instead of the default of text.

Table 2.8: Dapper’s Query<T> extension method parameters

The Query extension method is a loosely-typed equivalent so it is less frequently used.

The Execute extension method runs any specified SQL command and then returns the number of rows affected as an int. It is designed to run commands like INSERT, UPDATE, and DELETE. It has the same parameters as the Query<T> extension method.

Querying using Dapper

Let’s see a simple example that queries the Suppliers table instead of the Products table:

  1. In the Northwind.Console.SqlClient project, add a package reference for Dapper, as shown highlighted in the following markup:
    <ItemGroup>
      <PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.2" />
      <PackageReference Include="Dapper" Version="2.1.21" />
    </ItemGroup>
    

    At the time of writing, the latest version of Dapper is 2.1.21, released on November 11, 2023. You can check if it has been updated since then at the following link: https://www.nuget.org/packages/Dapper.

  1. Build the project to restore packages.
  2. Add a new class file named Supplier.cs, and modify its contents to define a class to represent four columns from each row in the Suppliers table, as shown in the following code:
    namespace Northwind.Models;
    public class Supplier
    {
      public int SupplierId { get; set; }
      public string? CompanyName { get; set; }
      public string? City { get; set; }
      public string? Country { get; set; }
    }
    
  3. At the bottom of Program.cs, add statements to retrieve Supplier entities in Germany, enumerate the collection outputting basic information about each one, and then serialize the collection as JSON to the console, as shown in the following code:
    WriteLineInColor("Using Dapper", ConsoleColor.DarkGreen);
    connection.ResetStatistics(); // So we can compare using Dapper.
    IEnumerable<Supplier> suppliers = connection.Query<Supplier>(
      sql: "SELECT * FROM Suppliers WHERE Country=@Country",
      param: new { Country = "Germany" });
    foreach (Supplier s in suppliers)
    {
      WriteLine("{0}: {1}, {2}, {3}",
        s.SupplierId, s.CompanyName, s.City, s.Country);
    }
    WriteLineInColor(JsonSerializer.Serialize(suppliers),
      ConsoleColor.Green);
    OutputStatistics(connection);
    
  4. Run the console app, and in the section where we used Dapper, note the same connection was used, so its events were raised while the Dapper query was executed, the enumerated collection output, and then JSON generated from the list of suppliers, as shown in the following output:
    Using Dapper
    11: Heli Süßwaren GmbH & Co. KG, Berlin, Germany
    12: Plutzer Lebensmittelgroßmärkte AG, Frankfurt, Germany
    13: Nord-Ost-Fisch Handelsgesellschaft mbH, Cuxhaven, Germany
    [{"SupplierId":11,  "CompanyName":"Heli S\u00FC\u00DFwaren GmbH \u0026 Co. KG",
      "City":"Berlin","Country":"Germany"},
     {"SupplierId":12,
      "CompanyName":"Plutzer Lebensmittelgro\u00DFm\u00E4rkte AG",
      "City":"Frankfurt","Country":"Germany"},
     {"SupplierId":13,
      "CompanyName":"Nord-Ost-Fisch Handelsgesellschaft mbH",
      "City":"Cuxhaven","Country":"Germany"}]
    BytesReceived: 1,430
    BytesSent: 240
    SelectRows: 3
    ExecutionTime: 5
    
  5. At the bottom of Program.cs, add statements to run the GetExpensiveProducts stored procedure, passing a price parameter value of 100, enumerate the collection outputting basic information about each one, and then serialize the collection as JSON to the console, as shown in the following code:
    IEnumerable<Product> productsFromDapper = 
      connection.Query<Product>(sql: "GetExpensiveProducts",
      param: new { price = 100M, count = 0 }, 
      commandType: CommandType.StoredProcedure);
    foreach (Product p in productsFromDapper)
    {
      WriteLine("{0}: {1}, {2}",
        p.ProductId, p.ProductName, p.UnitPrice);
    }
    WriteLineInColor(JsonSerializer.Serialize(productsFromDapper),
      ConsoleColor.Green);
    

Warning! With Dapper, you must pass a param object with all parameters, even if they are only used as output parameters. For example, we must define count, or an exception will be thrown. You must also remember to explicitly set the command type to stored procedure!

Run the console app, and in the section where we used Dapper to run the stored procedure to get the products that cost more than 100, note the same connection was used so its events were raised while the Dapper query was executed, the enumerated collection output, and then JSON generated from the list of products, as shown in the following output:

Info: Getting expensive products: 100.00.
29: Thüringer Rostbratwurst, 123.7900
38: Côte de Blaye, 263.5000
[{"ProductId":29,"ProductName":"Th\u00FCringer Rostbratwurst","UnitPrice":123.7900},{"ProductId":38,"ProductName":"C\u00F4te de Blaye","UnitPrice":263.5000}]

More Information: You can learn more about Dapper at the following link: https://github.com/DapperLib/Dapper/blob/main/Readme.md.

Cleaning up data resources

When you are done with a SQL Server database, you can clean up the resources used.

The Northwind database is used in most chapters of this book so if you plan to immediately continue with more chapters after this one, do not delete Northwind yet! If you created the database on your local computer, then you can leave it forever.

Removing Azure resources

To remove the resources used by SQL Database to save costs:

Warning! If you do not remove the resources used by an Azure SQL database, then you will incur costs.

  1. In the Azure portal, find the resource group named apps-services-book.
  2. Click Delete.
  3. Enter the name of the resource group.
  4. Click Delete.

Practicing and exploring

Test your knowledge and understanding by answering some questions, getting some hands-on practice, and exploring this chapter’s topics with deeper research.

Exercise 2.1 – Test your knowledge

Answer the following questions:

  1. Which NuGet package should you reference in a .NET project to get the best performance when working with data in SQL Server?
  2. What is the safest way to define a database connection string?
  3. What must T-SQL parameters and variables be prefixed with?
  4. What must you do before reading an output parameter?
  5. What type does Dapper add its extension methods to?
  6. What are the two most commonly used extension methods provided by Dapper?

Exercise 2.2 – Explore topics

Use the links on the following page to learn more details about the topics covered in this chapter:

https://github.com/markjprice/apps-services-net8/blob/main/docs/book-links.md#chapter-2---managing-relational-data-using-sql-server

Exercise 2.3 – Alternatives for storing secrets

Secrets like passwords and other values used in database connection strings, or values like keys to access a service, are often stored in environment variables. Other places for storing these values include App Secrets. You can learn more about them in the article Safe storage of app secrets in development in ASP.NET Core, found at the following link:

https://learn.microsoft.com/en-us/aspnet/core/security/app-secrets

For related guidance about handling connection strings, you can read the following link:

https://learn.microsoft.com/en-us/ef/core/miscellaneous/connection-strings

Summary

In this chapter, you learned:

  • How to connect to an existing SQL Server database.
  • How to execute a simple query and process the results using fast and low-level ADO.NET.
  • How to execute a simple query and process the results using Dapper.

In the next chapter, you will learn how to use the more powerful and complex ORM from Microsoft named EF Core.

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Use specialized libraries to improve all aspects of your apps, including performance, security, and localization
  • Harness the full potential of .NET using cloud-native data stores like Cosmos DB, and unlock scalability, performance, and resilience in your service implementations
  • Unleash the capabilities of Blazor Full Stack and.NET MAUI to develop stunning, truly cross-platform apps for web and mobile

Description

Elevate your practical C# and .NET skills to the next level with this new edition of Apps and Services with .NET 8. With chapters that put a variety of technologies into practice, including Web API, gRPC, GraphQL, and SignalR, this book will give you a broader scope of knowledge than other books that often focus on only a handful of .NET technologies. You’ll dive into the new unified model for Blazor Full Stack and leverage .NET MAUI to develop mobile and desktop apps. This new edition introduces the latest enhancements, including the seamless implementation of web services with ADO.NET SqlClient's native Ahead-of-Time (AOT) support. Popular library coverage now includes Humanizer and Noda Time. There’s also a brand-new chapter that delves into service architecture, caching, queuing, and robust background services. By the end of this book, you’ll have a wide range of best practices and deep insights under your belt to help you build rich apps and efficient services.

What you will learn

Familiarize yourself with a variety of technologies to implement services, such as gRPC and GraphQL Store and manage data locally and cloud-natively with SQL Server and Cosmos DB Use ADO.NET SqlClient to implement web services with native AOT publish support Leverage Dapper for improved performance over EF Core Implement popular third-party libraries such as Serilog, FluentValidation, Humanizer, and Noda Time Explore the new unified hosting model of Blazor Full Stack

Product Details

Country selected

Publication date : Dec 12, 2023
Length 798 pages
Edition : 2nd Edition
Language : English
ISBN-13 : 9781837637133
Category :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon AI Assistant (beta) to help accelerate your learning
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want

Product Details


Publication date : Dec 12, 2023
Length 798 pages
Edition : 2nd Edition
Language : English
ISBN-13 : 9781837637133
Category :

Table of Contents

19 Chapters
Preface Chevron down icon Chevron up icon
1. Introducing Apps and Services with .NET Chevron down icon Chevron up icon
2. Managing Relational Data Using SQL Server Chevron down icon Chevron up icon
3. Building Entity Models for SQL Server Using EF Core Chevron down icon Chevron up icon
4. Managing NoSQL Data Using Azure Cosmos DB Chevron down icon Chevron up icon
5. Multitasking and Concurrency Chevron down icon Chevron up icon
6. Using Popular Third-Party Libraries Chevron down icon Chevron up icon
7. Handling Dates, Times, and Internationalization Chevron down icon Chevron up icon
8. Building and Securing Web Services Using Minimal APIs Chevron down icon Chevron up icon
9. Caching, Queuing, and Resilient Background Services Chevron down icon Chevron up icon
10. Building Serverless Nanoservices Using Azure Functions Chevron down icon Chevron up icon
11. Broadcasting Real-Time Communication Using SignalR Chevron down icon Chevron up icon
12. Combining Data Sources Using GraphQL Chevron down icon Chevron up icon
13. Building Efficient Microservices Using gRPC Chevron down icon Chevron up icon
14. Building Web User Interfaces Using ASP.NET Core Chevron down icon Chevron up icon
15. Building Web Components Using Blazor Chevron down icon Chevron up icon
16. Building Mobile and Desktop Apps Using .NET MAUI Chevron down icon Chevron up icon
17. Epilogue Chevron down icon Chevron up icon
18. Index Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Full star icon Full star icon 5
(6 Ratings)
5 star 100%
4 star 0%
3 star 0%
2 star 0%
1 star 0%
Filter icon Filter
Top Reviews

Filter reviews by


N/A Jan 25, 2024
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Feefo Verified review Feefo image
N/A Jan 22, 2024
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Feefo Verified review Feefo image
Jorge Rodriguez Jan 29, 2024
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Feefo Verified review Feefo image
N/A Apr 4, 2024
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Feefo Verified review Feefo image
Piotr Mokry Jun 18, 2024
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Feefo Verified review Feefo image
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.