/sql-basics

Basics and intermediate topics for working with SQL-Server using C#

Primary LanguageC#

Tips on interacting with a database with C#

This article main intent is to provide useful information for interating with data using a managed data provider, adding Dapper to the mix for new developers to get the basics which in turn allows a developer to write clean reusable code.

Some developers might want to jump into Entity Framework Core which is fine but understanding the basic is going to be helpful when diving into Entity Framework Core.

Which is better, data provider or Entity Framework Core? It depends on the task and performance expected. The author uses both.

Writing your first SELECT statement

The average developer creates a database with a table or perhaps a set of tables then writes an SQL statement to select records as shown below.

✔️ Connection string is an issue we will address later

The developer saw others on the web cancatenating an SQL statement WHERE condition and heard it worked so they write the following code and pass in Brown for the WHERE condition. In this case Brown exist and the primary key is shown.

static void Playground(string lastName)
{

    string connectionString = 
        "Data Source=(localdb)\\MSSQLLocalDB;" + 
        "Initial Catalog=NorthWind2022;Integrated Security=True";

    using var cn = new SqlConnection(connectionString);
    using var cmd = new SqlCommand
    {
        Connection = cn,
        CommandText = "SELECT ContactId FROM  dbo.Contacts WHERE  (LastName = '" + lastName +"')"
    };

    cn.Open();

    var reader = cmd.ExecuteReader();
    if (reader.HasRows)
    {
        reader.Read();
        Console.WriteLine(reader.GetInt32(0));
    }
    else
    {
        Console.WriteLine("No matches");
    }
}

So what happens if Roel's is used? Upon executing the code we get

Microsoft.Data.SqlClient.SqlException: 'Incorrect syntax near 's'.
Unclosed quotation mark after the character string ')'.'

The resolution, use parameters, all data providers have parameters.

For SQL-Sever in SSMS (SQL-Server Management Studio) a parameter begin with @

DECLARE @LastName AS NCHAR(50) = N'Roel''s';

SELECT ContactId,
       FirstName,
       LastName
FROM dbo.Contacts
WHERE (LastName = @LastName);

In SSMS we must escape an apostrophe as shown above while when using parameters in C# with the command object, we create a new parameter with Parameters.Add where the first parameter in the name of the parameter (in SSMS DECLARE), the second parameter is the type, in this case for string SqlDbType.NVarChar then tag on .Value which here is lastname. Internally the code escapes the apostrophe.

New updated version of code

static void Playground(string lastName)
{

    string connectionString = 
        "Data Source=(localdb)\\MSSQLLocalDB;" + 
        "Initial Catalog=NorthWind2022;Integrated Security=True";

    using var cn = new SqlConnection(connectionString);
    using var cmd = new SqlCommand
    {
        Connection = cn,
        CommandText = "SELECT ContactId FROM  dbo.Contacts WHERE  (LastName = @LastName)"
    };

    cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = lastName;
    Console.WriteLine(cmd.CommandText);
    cn.Open();

    var reader = cmd.ExecuteReader();
    if (reader.HasRows)
    {
        reader.Read();
        Console.WriteLine(reader.GetInt32(0));
    }
    else
    {
        Console.WriteLine("No matches");
    }
}

Note the line Console.WriteLine(cmd.CommandText); which writes out.

SELECT ContactId FROM  dbo.Contacts WHERE  (LastName = @LastName)

We do not see the actual value for LastName, this is a bonus as there is no room for SQL-Injection.

So how do we know if the statement is good? We test by writing the statement in SSMS and we can also run my NuGet package DbPeekQueryLibrary.

cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = lastName;
Console.WriteLine(cmd.ActualCommandText());
cn.Open();

Which shows

SELECT ContactId FROM  dbo.Contacts WHERE  (LastName = 'Roel''s')

Note When adding parameters as shown above, the order they are added does not matter. If we were working with MS-Access the parameter order matters. If we were working with Oracle, the default is same as MS-Access but using BindByName = true on the command object parameter order does not matter, same as SQL-Server.

The following example uses a larger SELECT with several JOINS, two WHERE conditions and the parmeters are out of order which is fine as they are by name not ordinal.

static void Playground2(int countryIdentifier, int contactTypeIdentifier)
{

    string connectionString =
        "Data Source=(localdb)\\MSSQLLocalDB;" +
        "Initial Catalog=NorthWind2022;Integrated Security=True";

    using var cn = new SqlConnection(connectionString);
    using var cmd = new SqlCommand
    {
        Connection = cn,
        CommandText = """
            SELECT C.CustomerIdentifier,
                   C.CompanyName,
                   C.ContactId,
                   C.Street,
                   C.City,
                   C.PostalCode,
                   C.CountryIdentifier,
                   C.Phone,
                   C.ContactTypeIdentifier,
                   CT.ContactTitle,
                   Cont.FirstName,
                   Cont.LastName
            FROM dbo.Customers AS C
                INNER JOIN dbo.Countries AS A
                    ON C.CountryIdentifier = A.CountryIdentifier
                INNER JOIN dbo.ContactType AS CT
                    ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier
                INNER JOIN dbo.Contacts AS Cont
                    ON C.ContactId = Cont.ContactId
                       AND CT.ContactTypeIdentifier = Cont.ContactTypeIdentifier
            WHERE (C.CountryIdentifier = @CountryIdentifier)
                  AND (C.ContactTypeIdentifier = @ContactTypeIdentifier);
            """
    };

    cmd.Parameters.Add("@ContactTypeIdentifier", SqlDbType.NVarChar).Value = contactTypeIdentifier;
    cmd.Parameters.Add("@CountryIdentifier", SqlDbType.NVarChar).Value = countryIdentifier;
    Console.WriteLine(cmd.ActualCommandText());
    cn.Open();

    var reader = cmd.ExecuteReader();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            Console.WriteLine($"Id: {reader.GetInt32(0),-4} Company: {reader.GetString(1)}");
        }
        
    }
    else
    {
        Console.WriteLine("No matches");
    }
}

About connections

Since a developer could interact with database there are many who create a global connection, when needed check if the connection is open, if not open and use the connection.

Rather go into all the reason this is bad, the recommended approach is to create a new connection when needed and once finished dispose of the connection.

Each of the examples follow this approach.

About connection strings

DO NOT store connection string in each SqlConnection, for ASP.NET Core and Razor Pages we can store the connection string in a json file typically named appsettings.json.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=NorthWind2022;Integrated Security=True"
  }
}

And in Program Main have acccess via

builder.Configuration.GetConnectionString("DefaultConnection")

For other project types create a model for appsettings.json and read the settings or use my NuGet package ConfigurationLibrary.

Sample appsettings.json

{
  "ConnectionsConfiguration": {
    // do not get hung up on the next line it comes from allowing multiple environments
    "ActiveEnvironment": "Development",
    "Development": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=NorthWind2022;Integrated Security=True;Encrypt=False"
  }
}

Add the following using to the class that works with a database.

using static ConfigurationLibrary.Classes.ConfigurationHelper;

Create a new connection passing ConnectionString() which reads te Development item under ConnectionsConfiguration.

using var cn = new SqlConnection(ConnectionString());

Note Go to the package source which explains having three connection strings, development, stagging and production.

Placement of SQL statements

In all the example above the SQL statements were inline with the command object, a better idea is to create a class, in this case SqlStatements.cs and place the SQL in there.

In the last code sample the SQL took up a lot of space and can only be used in that method.

Here we have a better solution, cleans up your code and is reusable.

public class SqlStatements
{
    public static string CustomersByContactTypeAndCountry()
        => """
                SELECT C.CustomerIdentifier,
                       C.CompanyName,
                       C.ContactId,
                       C.Street,
                       C.City,
                       C.PostalCode,
                       C.CountryIdentifier,
                       C.Phone,
                       C.ContactTypeIdentifier,
                       CT.ContactTitle,
                       Cont.FirstName,
                       Cont.LastName
                FROM dbo.Customers AS C
                    INNER JOIN dbo.Countries AS A
                        ON C.CountryIdentifier = A.CountryIdentifier
                    INNER JOIN dbo.ContactType AS CT
                        ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier
                    INNER JOIN dbo.Contacts AS Cont
                        ON C.ContactId = Cont.ContactId
                           AND CT.ContactTypeIdentifier = Cont.ContactTypeIdentifier
                WHERE (C.CountryIdentifier = @CountryIdentifier)
                      AND (C.ContactTypeIdentifier = @ContactTypeIdentifier);
                """;
}

Cleaner code

using var cn = new SqlConnection(ConnectionString());
using var cmd = new SqlCommand
{
    Connection = cn,
    CommandText = SqlStatements.CustomersByContactTypeAndCountry()

};

cmd.Parameters.Add("@ContactTypeIdentifier", 
    SqlDbType.NVarChar).Value = contactTypeIdentifier;
cmd.Parameters.Add("@CountryIdentifier", 
    SqlDbType.NVarChar).Value = countryIdentifier;

cn.Open();

Reading back data

When there are more than one record we use reader.HasRows to see if there are any records then use a while to iterate data. DO NOT use this if only one record is being returned.

var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
    while (reader.Read())
    {
        Console.WriteLine($"Id: {reader.GetInt32(0),-4} Company: {reader.GetString(1)}");
    }
}

For returning a single record, see if there is data then if there is use the Read method of the reader.

var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
    reader.Read();
    Console.WriteLine(reader.GetInt32(0));
}

Storing/returning data

In each of the example above all that was done was display data to the console window.

One option is to return data into a DataTable. DataTables are okay for desktop projects but generally are too heavy for modern day web applications.

Example which the caller passing an int and we get a DataTable back.

static DataTable Playground1DataTable(int top)
{

    DataTable dt = new DataTable();

    using var cn = new SqlConnection(ConnectionString());
    using var cmd = new SqlCommand
    {
        Connection = cn,
        CommandText = SqlStatements.ContactDemo()
    };

    cmd.Parameters.Add("@Top", SqlDbType.NVarChar).Value = top;

    cn.Open();

    dt.Load(cmd.ExecuteReader());
    return dt;
}

Another option, create a model/class which has properties for each column in a SELECT statement.

The following example uses Dapper.

SELECT statement

public class SqlStatements
{
    public static string ContactDemo() =>
        """
        SELECT  ContactId,
                FirstName,
                LastName,
                ContactTypeIdentifier  
        FROM dbo.Contacts 
        WHERE ContactId > @Top
        """;

Model to match the SELECT statement

public class Contact
{
    public int ContactId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int ContactTypeIdentifier { get; set; }
    public override string ToString() => LastName;
}

Code to read

static List<Contact> Playground1Dapper(int top)
{

    using SqlConnection cn = new (ConnectionString());
    // specify our parameter for the WHERE condition
    var parameters = new { @Top = top };

    List<Contact> list = cn.Query<Contact>(
        SqlStatements.ContactDemo(), 
        parameters).ToList();


    return list;
}

Multiple Result Sets for SQL-Server

Learn how to read reference table from SQL-Server using a single method. What is shown provides an efficient way to either a connection, command objects to read data via a SqlDataReader for conventional work using methods from SqlClient and also Dapper which requires two lines of code to read data and one line of code to store data into list.

Goal

To read from three reference table in a modified version of Microsoft NorthWind database, Categories, ContactType and Countries tables.

In all code samples all records are read from each table, in some cases not all records may be needed, simply change the SQL SELECT statement with a WHERE clause. Also, the same goes for columns.

Project

See GitHubSamples (name came from another of this author's work)

Provides code samples

There is one console project uses to write all the code samples which appeared in this article. All code is in this project.

There is a Windows Form project where unlike the console project, all data opertions are in a class project.

And finally a project which uses Entity Framework which is another console project were all dta operations are in other class project.

Preparing to run code

  • Make sure to use Microsoft Visual Studio 2022
  • Run both scripts in the root of the solution

Source code

Clone the following GitHub repository.