Managing data with low-level APIs

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.