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.
Tools used
- Microsoft Visual Studio 2022 (version 17.12.3)
SSMS (SQL-Server Management Studio)
GitHub Copilot chat in Microsoft Visual Studio 2022 (version 17.12.3)- JetBrains AI Assistant
ChatGPT free edition
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
{
public static async Task<IEnumerable<Customer>> GetCustomerDetails()
{
using IDbConnection connection = new SqlConnection(DataConnections.Instance.MainConnection);
var customerDictionary = new Dictionary<int, Customer>();
var customers = await connection.QueryAsync<Customer, Contact, Country, ContactType, Customer>(
SqlStatements.CustomerWithContacts,
(customer, contact, country, contactType) =>
{
if (!customerDictionary.TryGetValue(customer.CustomerIdentifier, out var existing))
{
existing = customer;
customerDictionary[customer.CustomerIdentifier] = existing;
}
existing.Contact = contact;
existing.Country = country;
existing.ContactType = contactType;
return existing;
},
splitOn: "ContactId,CountryIdentifier,ContactTypeIdentifier");
return customers;
}
}
Above method breakdown
Since a developer should never be dependent only on AI tools, here is a breakdown of the method above.
QueryAsync<Customer, Contact, Country, ContactType, Customer>
Indicates: Customer is the main class, and the remainder is for the joins.
(customer, contact, country, contactType) =>
Defines mappings for Dapper to use.
if (!customerDictionary.TryGetValue(customer.CustomerIdentifier, out var existing))
Is used to create a instance of a Customer where the Customer will not be duplicated in the returning IEnumerable<Customer>
splitOn: "ContactId,CountryIdentifier,ContactTypeIdentifier");
Defines the keys in the tables to join on.
Executing above code
Simply call the method with a breakpoint below the call to GetCustomerDetails and inspect in Visual Studio’s local window, as in this case, there are 89 records.
Once satisfied with the code, implement the code in a project of choice. An additional recommendation is to create test.
internal partial class Program
{
static async Task Main(string[] args)
{
var customers = (await DataOperations.GetCustomerDetails()).ToList();
}
}
Adapt to return a single record
Alter the SQL SELECT to include a WHERE condition and pass a value for the WHERE, as shown below.
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;
Test to get a single customer.
var singleContact = await DataOperations.GetContactByIdAsync(1);
Claude
I gave Claude the same prompt that produced an incorrect solution, with a second prompt indicating that the first solution was incorrect; Claude said I was right and then gave a new solution, which was kind of close yet still incorrect.
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Linq;
using Dapper;
using System.Data.SqlClient;
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; }
// Navigation properties
public Contact Contact { get; set; }
public Country Country { 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 string ContactTitle { get; set; }
}
public class Country
{
public int CountryIdentifier { get; set; }
public string Name { get; set; }
}
public class CustomerRepository
{
private readonly string _connectionString;
public CustomerRepository(string connectionString)
{
_connectionString = connectionString;
}
public async Task<IEnumerable<Customer>> GetCustomersAsync()
{
const string sql = @"
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,
CT.ContactTitle,
CO.CountryIdentifier,
CO.Name
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))
{
await connection.OpenAsync();
var customerDictionary = new Dictionary<int, Customer>();
var customers = await connection.QueryAsync<Customer, Contact, Country, Customer>(
sql,
(customer, contact, country) =>
{
if (!customerDictionary.TryGetValue(customer.CustomerIdentifier, out var customerEntry))
{
customerEntry = customer;
customerDictionary.Add(customer.CustomerIdentifier, customerEntry);
}
customerEntry.Contact = contact;
customerEntry.Country = country;
return customerEntry;
},
splitOn: "ContactId,CountryIdentifier"
);
return customerDictionary.Values;
}
}
}
Best-suited tool for the task
All three tools, GitHub Copilot, JetBrains AI assistant, and ChatGPT, had acceptable solutions with minor refactoring. GitHub Copilot is the acceptable solution if an asynchronous task is needed, while JetBrains AI assistant is if the asynchronous task is necessary.
These tools win over ChatGPT because GitHub Copilot and JetBrains AI assistants work within Visual Studio. For some developers, using ChatGPT might be the proper tool even with being external to Visual Studio if other tasks of a developer work better than the other tools.
Revaluate
Today, Copilot and JetBrains AI assistants are best suited to perform the task, while in the future, one tool might overshadow another. This means reevaluating tools rather than always believing that one tool is always the best.
Working with Dapper
For those developers new to Dapper, check out the following resources.
SOCIAL SHARE CARD GENERATOR