Introduction
Making decisions for the correct path to work on a task, usually a developer will fall back on what they are comfortable with. Information presented is for providing getting out of their comfort zone and picking the right code path to complete a specific task.
The task is to read from an SQL-Server database table were one column containing one or more addresses for a person. Since there are multiple technologies e.g. using the Microsoft SqlClient data provider, Dapper and Microsoft EF Core and several different SQL statements the code will use these and explain why.
To get started a Windows Forms project is used as this type of project makes it easier to test out different data paths. When written properly, as done here the data operations code is not connected to anything to do with Windows Forms so when a final selection is made, drop the code in the desired project type from console, desktop to web.
Each form shown calls code in separate data classes using three different models.
Requirements
- Microsoft Visual Studio 2022 edition 17.12 or higher
- Basic
- Understanding of C#
- Understanding of T-SQL
Fundamentals
Writing up the business requirements for the project followed by creating a database schema which follows the business requirements.
Once the database schema has been created, load the database with mocked data. Next step is to validate that the schema can accommodate what is written in the business requirements followed by creating necessary indices.
Note
The above should be done in the database, not in code as the decision has not been made for accessing the data.
If this is the first time creating a database, study the following modified Microsoft NothWind database and practice writing SQL statements in SSMS (SQL-Studio Management Studio). The database is not perfect but better than the origina.
Jumping into JSON Columns
EF Core code Data provider code
Imagine a task is given to store information for an order for a customer’s address and shipping address. Furthermore, the task indicates to store information in NVARCAR column as json.
- JSON may not be the correct direction, instead an alternate might be an address table.
- As written there is a Company property that should be address type as the assigned developer rather than create the proper structure copied code from another source.
Option 1 Microsoft EF Core
To express the data, the following models are used.
public partial class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DateOfBirth { get; set; }
public List<Address> Addresses { get; set; }
}
public class Address
{
public string Company { get; set; }
public string Street { get; set; }
public string City { get; set; }
public override string ToString() => Company;
}
Configuration in the DbContext. OwnsMany allows you to model entity types that can only ever appear on navigation properties of other entity types. And .ToJson maps one or Address to a Person. See also Mapping to JSON columns.
The following shows how to add a new customer with two addresses.
private static void AddOnePerson()
{
using var context = new Context();
Person person = new Person()
{
Addresses = new List<Address>()
{
new()
{
Company = "Company1",
City = "Wyndmoor",
Street = "123 Apple St"
},
new()
{
Company = "Company2",
City = "Portland",
Street = "999 34th St"
},
},
FirstName = "Karen",
LastName = "Payne",
DateOfBirth = new DateTime(1956, 9, 24)
};
context.Add(person);
context.SaveChanges();
}
Reading a person back requires nothing different from a normal read without json data.
using var context = new Context();
var person = context.Person.FirstOrDefault();
Read all read all records grouped by LastName
public static void Grouped()
{
using var context = new Context();
var people = context.Person.ToList();
var groupedByLastName = people
.GroupBy(person => person.LastName)
.OrderBy(group => group.Key);
foreach (var group in groupedByLastName)
{
AnsiConsole.MarkupLine($"[cyan]{group.Key}[/]");
foreach (var person in group)
{
Console.WriteLine($" - {person}");
foreach (var address in person.Addresses)
{
AnsiConsole.MarkupLine(address.AddressType == "Home"
? $" * AddressType: [yellow]{address.AddressType}[/], Street: {address.Street}, City: {address.City}"
: $" * AddressType: [magenta2]{address.AddressType}[/], Street: {address.Street}, City: {address.City}");
}
}
}
}
Fixing a bad design
Rather than Company, let's change the name to AddressType.
public class Address
{
public string AddressType { get; set; }
public string Street { get; set; }
public string City { get; set; }
public override string ToString() => AddressType;
}
Since we are still in development we can use the following which recreates the database fresh and adds a single record.
private static void AddOnePerson()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
Person person = new Person()
{
Addresses = new List<Address>()
{
new()
{
AddressType = "Company1",
City = "Wyndmoor",
Street = "123 Apple St"
},
new()
{
AddressType = "Company2",
City = "Portland",
Street = "999 34th St"
},
},
FirstName = "Karen",
LastName = "Payne",
DateOfBirth = new DateTime(1956, 9, 24)
};
context.Add(person);
context.SaveChanges();
context.Person.FirstOrDefault()!
.Addresses
.FirstOrDefault()
!.City = "Ambler";
context.SaveChanges();
}
}
The read operation remains the same.
Option 2 Dapper
For those who rather use Dapper, the following SQL reads one record by last name and of course we could use the primary key.
WITH PersonAddresses AS (
SELECT
p.Id,
p.FirstName,
p.LastName,
p.DateOfBirth,
a.Street,
a.City,
a.AddressType,
ROW_NUMBER() OVER (PARTITION BY p.Id ORDER BY a.Street) AS AddressIndex
FROM
dbo.Person p
CROSS APPLY
OPENJSON(p.Addresses)
WITH (
Street NVARCHAR(MAX),
City NVARCHAR(MAX),
AddressType NVARCHAR(MAX)
) a
WHERE
p.LastName = @LastName
)
SELECT
pa.Id,
pa.FirstName,
pa.LastName,
pa.DateOfBirth,
pa.Street,
pa.City,
pa.AddressType
FROM
PersonAddresses pa;
Models
public class PersonDapper2
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DateOfBirth { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string AddressType { get; set; }
public List<Address> Addresses { get; set; }
public override string ToString() => $"{FirstName} {LastName}";
}
public class Address(string street, string city, string addressType)
{
[Column(Order = 1)]
public string Street { get; } = street;
[Column(Order = 3)]
public string City { get; } = city;
[Column(Order = 2)]
public string AddressType { get; } = addressType;
public override string ToString() => AddressType;
}
Code to read data
public List<PersonDapper2> PersonData2(string lastName)
{
var dict = new Dictionary<int, PersonDapper2>();
_cn.Query<PersonDapper2, Address, PersonDapper2>(SqlStatements.GetPersonAddressesDapperOrSqlClient, (person, address) =>
{
if (!dict.TryGetValue(person.Id, out var existingPerson))
{
existingPerson = person;
existingPerson.Addresses = [];
dict[person.Id] = existingPerson;
}
if (address != null)
{
existingPerson.Addresses.Add(address);
}
return existingPerson;
},
new { LastName = lastName },
splitOn: "Street" // split between Person and Address
);
return dict.Values.ToList();
}
On a side note, using SqlClient (include with source code)
public List<PersonSqlClient> GetPerson(string lastName)
{
using var cn = new SqlConnection(DataConnections.Instance.MainConnection);
cn.Open();
using var cmd = new SqlCommand(SqlStatements.GetPersonAddressesDapperOrSqlClient, cn);
cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar)).Value = lastName;
var reader = cmd.ExecuteReader();
var people = new List<PersonSqlClient>();
while (reader.Read())
{
var person = people.FirstOrDefault(p => p.Id == reader.GetInt32(0));
if (person == null)
{
person = new PersonSqlClient
{
Id = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
DateOfBirth = reader.GetDateTime(3),
Addresses = []
};
people.Add(person);
}
person.Addresses.Add(
new Address(
reader.GetString(4),
reader.GetString(5),
reader.GetString(6)));
}
return people;
}
Code to add a record
Insert statement which also returns the new primary key.
INSERT INTO dbo.Person (FirstName, LastName, DateOfBirth, Addresses)
VALUES (@FirstName, @LastName, @DateOfBirth, @Addresses);
SELECT CAST(scope_identity() AS int);
Code which uses Bogus NuGet package for random data.
public void AddPersonMockup()
{
var faker = new Faker();
List<Address> addresses =
[
new(faker.Address.StreetName(), faker.Address.City(), "Home"),
new(faker.Address.StreetName(), faker.Address.City(), "Shipto")
];
PersonDapper2 person = new()
{
FirstName = faker.Person.FirstName,
LastName = faker.Person.LastName,
DateOfBirth = faker.Date.Between(
new DateTime(1978,1,1,8,0,0),
new DateTime(2010, 1, 1, 8, 0, 0)),
AddressJson = JsonSerializer.Serialize(addresses)
};
int primaryKey = (int)_cn.ExecuteScalar(SqlStatements.DapperInsert,
new
{
FirstName = person.FirstName,
LastName = person.LastName,
DateOfBirth = person.DateOfBirth,
Addresses = person.AddressJson
})!;
person.Id = primaryKey;
}
Other CRUD operations
For EF Core, use the same code as done without json data while in either Dapper or SqlClient, the address must serialize using JsonSerializer.Serialize as shown with the add code sample above.
Summary
The main topic is first planning out a design for a database that uses a string column with json data followed by how to work with this data using Microsoft EF Core, Microsoft SqlClient and NuGet package Dapper.
- Always validate a database schema against business requirements and if possible, consider future ask from business.
- Have a toolbox to work with on how to work with data as presented here, EF Core and Dapper along with a data provider in provided source code.
One improvement would be to use an enum to specify the address type to avoid mistakes such as typos. For EF Core check out HasConversion<T>
Image credits
From March designers
Top comments (0)