Introduction

Learn to use multiple AI tools to solve problems and/or save time by allowing AI tools to write code for you.

The goal

To read data from a modified version of the Microsoft NorthWind database using the NuGet package Dapper.

Most Dapper code samples are simplistic, consisting of reading from one table or multiple SELECT statements. Code samples on the web are sufficient for adding, editing, and deleting records.

Here, learn to work with two and three joins in select statements. The code will start by writing a valid SQL statement in SSMS, Azure Data Studio, or your favorite editor for writing SQL statements.

The results of this task do not mean that the tool selected is always going to be the best tool for all a developer’s tasks, so keep this in mind. As in the next article in this series, another tool may be better suited.

Source code

Tools used

Tool selection

Depending on varying factors, one tool may be enough, for instance, GitHub Copilot integration in Visual Studio or other IDE or editors.

The advantages of having several AI tools in a developer’s toolbox allow for comparing solutions from a prompt. Sometimes, one tool may not provide a proper solution, which may be the tool or an inability to write an appropriate prompt.

For the goal here, two AI tools returned the same response, and another tool given the same prompt required a secondary prompt.

Author preferences

  • GitHub Copilit
  • JetBrains AI Assistant
  • ChatGPT

Prompt design strategies

Providing too little context to the AI prompt is like asking a tourist to find a secret alley without a map.

Try writing out a prompt using a word process and following the guidelines below. Save prompts for when results provided are incorrect so that it is easier to refactor a prompt. Store prompts, as most tools retain how long prompts remain in history. Another reason for storing prompts is that within Visual Studio, some tools are solution-specific.

Step 1 Creating an SQL SELECT statement

Select Customers, their contact, contact type, and country.

SELECT      CU.CustomerIdentifier,
            CU.CompanyName,
            CU.ContactId,
            CU.Street,
            CU.City,
            CU.PostalCode,
            CU.CountryIdentifier,
            CU.Phone,
            CU.Fax,
            CU.Region,
            CU.ModifiedDate,
            CU.ContactTypeIdentifier,
            C.ContactId,
            C.FirstName,
            C.LastName,
            C.ContactTypeIdentifier,
            CO.CountryIdentifier,
            CO.Name,
            CT.ContactTypeIdentifier,
            CT.ContactTitle
  FROM      dbo.Customers AS CU
 INNER JOIN dbo.Contacts AS C
    ON CU.ContactId             = C.ContactId
 INNER JOIN dbo.Countries AS CO
    ON CU.CountryIdentifier     = CO.CountryIdentifier
 INNER JOIN dbo.ContactType AS CT
    ON CU.ContactTypeIdentifier = CT.ContactTypeIdentifier
   AND C.ContactTypeIdentifier  = CT.ContactTypeIdentifier
 WHERE      CU.CustomerIdentifier = @CustomerIdentifier;

Rather than creating C# classes, the expectation is that the AI tool will provide necessary classes with a proper prompt.

Step 2 create the prompt

The following prompt was used for all AI tools.

For C# using the following SQL-Server query and NuGet package Dapper create a method to return data.
    SELECT CU.CustomerIdentifier,
           CU.CompanyName,
           CU.ContactId,
           CU.Street,
           CU.City,
           CU.PostalCode,
           CU.CountryIdentifier,
           CU.Phone,
           CU.Fax,
           CU.Region,
           CU.ModifiedDate,
           CU.ContactTypeIdentifier,
           C.ContactId,
           C.FirstName,
           C.LastName,
           C.ContactTypeIdentifier,
           CO.CountryIdentifier,
           CO.Name,
           CT.ContactTypeIdentifier,
           CT.ContactTitle
    FROM dbo.Customers AS CU
        INNER JOIN dbo.Contacts AS C
            ON CU.ContactId = C.ContactId
        INNER JOIN dbo.Countries AS CO
            ON CU.CountryIdentifier = CO.CountryIdentifier
        INNER JOIN dbo.ContactType AS CT
            ON CU.ContactTypeIdentifier = CT.ContactTypeIdentifier
               AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier;
Note
In all responses below, string variables store the SQL statements. In the provided source code, read-only strings are used. The reader can use strings or stored procedures.

Step 3 Try the prompt with various tools

ChatGPT response

To create a method in C# that uses Dapper to execute the provided SQL query and return the data, you can follow the steps below. We'll define a method named GetCustomerDetails that connects to the database, executes the query, and maps the results to a collection of strongly typed objects.

Here's the implementation:

We need classes to represent the returned data.

public class Customer
{
    public int CustomerIdentifier { get; set; }
    public string CompanyName { get; set; }
    public int ContactId { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string PostalCode { get; set; }
    public int CountryIdentifier { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }
    public string Region { get; set; }
    public DateTime ModifiedDate { get; set; }
    public int ContactTypeIdentifier { get; set; }
    public Contact Contact { get; set; }
    public Country Country { get; set; }
    public ContactType ContactType { get; set; }
}

public class Contact
{
    public int ContactId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int ContactTypeIdentifier { get; set; }
}

public class Country
{
    public int CountryIdentifier { get; set; }
    public string Name { get; set; }
}

public class ContactType
{
    public int ContactTypeIdentifier { get; set; }
    public string ContactTitle { get; set; }
}

Write the Method

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;

public class CustomerRepository
{
    private readonly string _connectionString;

    public CustomerRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IEnumerable<Customer> GetCustomerDetails()
    {
        const string query = @"
            SELECT CU.CustomerIdentifier,
                   CU.CompanyName,
                   CU.ContactId,
                   CU.Street,
                   CU.City,
                   CU.PostalCode,
                   CU.CountryIdentifier,
                   CU.Phone,
                   CU.Fax,
                   CU.Region,
                   CU.ModifiedDate,
                   CU.ContactTypeIdentifier,
                   C.ContactId,
                   C.FirstName,
                   C.LastName,
                   C.ContactTypeIdentifier,
                   CO.CountryIdentifier,
                   CO.Name,
                   CT.ContactTypeIdentifier,
                   CT.ContactTitle
            FROM dbo.Customers AS CU
                INNER JOIN dbo.Contacts AS C
                    ON CU.ContactId = C.ContactId
                INNER JOIN dbo.Countries AS CO
                    ON CU.CountryIdentifier = CO.CountryIdentifier
                INNER JOIN dbo.ContactType AS CT
                    ON CU.ContactTypeIdentifier = CT.ContactTypeIdentifier
                       AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier;";

        using (IDbConnection connection = new SqlConnection(_connectionString))
        {
            var customerDictionary = new Dictionary<int, Customer>();

            var customers = connection.Query<Customer, Contact, Country, ContactType, Customer>(
                query,
                (customer, contact, country, contactType) =>
                {
                    if (!customerDictionary.TryGetValue(customer.CustomerIdentifier, out var existingCustomer))
                    {
                        existingCustomer = customer;
                        customerDictionary[customer.CustomerIdentifier] = existingCustomer;
                    }

                    existingCustomer.Contact = contact;
                    existingCustomer.Country = country;
                    existingCustomer.ContactType = contactType;

                    return existingCustomer;
                },
                splitOn: "ContactId,CountryIdentifier,ContactTypeIdentifier");

            return customers.Distinct();
        }
    }
}

The next step is to create a C# project and try the code out, but first remove Distinct() from the return statement.

If the code works by inspecting the returned data, change the query to a stored procedure or read-only string.

GitHub response

Given the same prompt as ChatGPT, Copilot provides the same classes as ChatGPT.

The following code is provided.

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Dapper;

public class CustomerRepository
{
    private readonly string _connectionString;

    public CustomerRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IEnumerable<Customer>> GetCustomersAsync()
    {
        const string query = @"
            SELECT CU.CustomerIdentifier,
                   CU.CompanyName,
                   CU.ContactId,
                   CU.Street,
                   CU.City,
                   CU.PostalCode,
                   CU.CountryIdentifier,
                   CU.Phone,
                   CU.Fax,
                   CU.Region,
                   CU.ModifiedDate,
                   CU.ContactTypeIdentifier,
                   C.ContactId,
                   C.FirstName,
                   C.LastName,
                   C.ContactTypeIdentifier,
                   CO.CountryIdentifier,
                   CO.Name,
                   CT.ContactTypeIdentifier,
                   CT.ContactTitle
            FROM dbo.Customers AS CU
                INNER JOIN dbo.Contacts AS C
                    ON CU.ContactId = C.ContactId
                INNER JOIN dbo.Countries AS CO
                    ON CU.CountryIdentifier = CO.CountryIdentifier
                INNER JOIN dbo.ContactType AS CT
                    ON CU.ContactTypeIdentifier = CT.ContactTypeIdentifier
                       AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier;";

        using (var connection = new SqlConnection(_connectionString))
        {
            var customerDictionary = new Dictionary<int, Customer>();

            var result = await connection.QueryAsync<Customer, Contact, Country, ContactType, Customer>(
                query,
                (customer, contact, country, contactType) =>
                {
                    if (!customerDictionary.TryGetValue(customer.CustomerIdentifier, out var currentCustomer))
                    {
                        currentCustomer = customer;
                        customerDictionary.Add(currentCustomer.CustomerIdentifier, currentCustomer);
                    }

                    currentCustomer.Contact = contact;
                    currentCustomer.Country = country;
                    currentCustomer.ContactType = contactType;

                    return currentCustomer;
                },
                splitOn: "ContactId,CountryIdentifier,ContactTypeIdentifier");

            return result.Distinct().ToList();
        }
    }
}
Note
A benefit of Copilot is that the developer can insert the code into a new file.

JetBrain AI Assistant response

Given the same prompt ChatGPT and Copilot provide the same classes. Also, as with Copilot offer the option to insert code into a file.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;
public class Customer
{
    public int CustomerIdentifier { get; set; }
    public string CompanyName { get; set; }
    public int ContactId { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string PostalCode { get; set; }
    public int CountryIdentifier { get; set; }
    public string Phone { get; set; }
    public string Fax { get; set; }
    public string Region { get; set; }
    public DateTime ModifiedDate { get; set; }
    public int ContactTypeIdentifier { get; set; }
    public Contact Contact { get; set; }
    public Country Country { get; set; }
    public ContactType ContactType { get; set; }
}
public class Contact
{
    public int ContactId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int ContactTypeIdentifier { get; set; }
}
public class Country
{
    public int CountryIdentifier { get; set; }
    public string Name { get; set; }
}
public class ContactType
{
    public int ContactTypeIdentifier { get; set; }
    public string ContactTitle { get; set; }
}
public class CustomerRepository
{
    private readonly string _connectionString;
    public CustomerRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public IEnumerable<Customer> GetCustomerData()
    {
        const string query = @"
            SELECT CU.CustomerIdentifier,
                   CU.CompanyName,
                   CU.ContactId,
                   CU.Street,
                   CU.City,
                   CU.PostalCode,
                   CU.CountryIdentifier,
                   CU.Phone,
                   CU.Fax,
                   CU.Region,
                   CU.ModifiedDate,
                   CU.ContactTypeIdentifier,
                   C.ContactId,
                   C.FirstName,
                   C.LastName,
                   C.ContactTypeIdentifier,
                   CO.CountryIdentifier,
                   CO.Name AS CountryName,
                   CT.ContactTypeIdentifier,
                   CT.ContactTitle
            FROM dbo.Customers AS CU
                INNER JOIN dbo.Contacts AS C
                    ON CU.ContactId = C.ContactId
                INNER JOIN dbo.Countries AS CO
                    ON CU.CountryIdentifier = CO.CountryIdentifier
                INNER JOIN dbo.ContactType AS CT
                    ON CU.ContactTypeIdentifier = CT.ContactTypeIdentifier
                       AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier";
        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            var customerDictionary = new Dictionary<int, Customer>();
            var result = db.Query<Customer, Contact, Country, ContactType, Customer>(
                query,
                (customer, contact, country, contactType) =>
                {
                    if (!customerDictionary.TryGetValue(customer.CustomerIdentifier, out var customerEntry))
                    {
                        customerEntry = customer;
                        customerDictionary.Add(customer.CustomerIdentifier, customerEntry);
                    }
                    customerEntry.Contact = contact;
                    customerEntry.Country = country;
                    customerEntry.ContactType = contactType;
                    return customerEntry;
                },
                splitOn: "ContactId,CountryIdentifier,ContactTypeIdentifier"
            );
            return result.Distinct();
        }
    }
}

Before testing the code, remove Distinct() from the return statement.

Note
Unsure why Distinct() is used other than the LLM picked up on a developer's code with duplicates.

Complete code

_The SELECT statement has been removed from the AI-generated code and placed into a separate class for the provided source code. Some may prefer to use stored procedures.
_

internal class SqlStatements
{
    public static string CustomerWithContacts =>
        """
            SELECT CU.CustomerIdentifier,
                   CU.CompanyName,
                   CU.ContactId,
                   CU.Street,
                   CU.City,
                   CU.PostalCode,
                   CU.CountryIdentifier,
                   CU.Phone,
                   CU.Fax,
                   CU.Region,
                   CU.ModifiedDate,
                   CU.ContactTypeIdentifier,
                   C.ContactId,
                   C.FirstName,
                   C.LastName,
                   C.ContactTypeIdentifier,
                   CO.CountryIdentifier,
                   CO.Name,
                   CT.ContactTypeIdentifier,
                   CT.ContactTitle
            FROM dbo.Customers AS CU
                INNER JOIN dbo.Contacts AS C
                    ON CU.ContactId = C.ContactId
                INNER JOIN dbo.Countries AS CO
                    ON CU.CountryIdentifier = CO.CountryIdentifier
                INNER JOIN dbo.ContactType AS CT
                    ON CU.ContactTypeIdentifier = CT.ContactTypeIdentifier
                       AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier;
        """;
}

Selected code

internal class DataOperations
{

    pub

  
Author Of article : Karen Payne

Read full article