Iterations
introduce
When making decisions about the correct path to complete a task, developers often choose what they feel is appropriate. Information is provided to get them out of their comfort zone and choose the right coding path to accomplish a specific task.
The task is to read a column containing one or more addresses of a person from a SQL-Server database table. Because there are multiple technologies, such as using the Microsoft SqlClient data provider, Dapper, and Microsoft EF Core, as well as several different SQL statements, the code uses these technologies and explains why.
Use a Windows Forms project first because it makes it easier to test different data paths. If written correctly, as done here, the data manipulation code will not be connected to anything related to Windows Forms, so when you make your final choice, put the code in everything from the console to the desktop to the Web. in the required project type.
Each form shown uses three different models to call code in separate data classes.
Require
- Microsoft Visual Studio 2022 version 17.12 or higher
- basic
- Understanding of C#
- Understanding T-SQL
basic knowledge
Write the business requirements for the project and then build a database schema that follows the business requirements.
After creating the database schema, load the database using simulated data. The next step is to verify that the schema can accommodate what is written in the business requirements and then build the necessary indexes.
notes
The above operation should be done in the database, not in the code, because the decision to access the data has not yet been made.
If this is the first time you create a database, please learn the following modified Microsoft Nuofeng database And practice writing SQL statements in SSMS (SQL-Studio Management Studio). The library isn’t perfect, but it’s better than the original.
Jump to JSON column
EF core code
data provider code
Suppose there is a task that stores order information for customer addresses and shipping addresses. Additionally, this task instructs you to save the information in the NVARCAR field as json.
- JSON might not be the right direction, the alternative might be an address table.
- As written, there is a company attribute that is supposed to specify the developer’s address type, rather than creating the correct structure for copying code from other sources.
Option 1 Microsoft EF Core
To represent the data, the following model is 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 DbContext. have many Allows you to model entity types that can only appear on the navigation properties of other entity types. .ToJson maps an or address to a person. See Map to JSON column.
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 back a person is no different than normal reading without the json data.
using var context = new Context();
var person = context.Person.FirstOrDefault();
Read All reads all records grouped by last name
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}");
}
}
}
}
Fix bad design
We change the name to AddressType instead of Company.
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 the development stage, we can use the following command to re-create a new database and add a new 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();
}
}
Read operations remain unchanged.
Option 2 Exquisite
For those who prefer to use Dapper, the following SQL reads a record by last name, of course we can 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;
model
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();
}
As a side note, use SqlClient (includes 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 for adding new records
The Insert statement 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 used Fake 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 operations of adding, deleting, modifying and checking
For EF Core, using the same code in Dapper or SqlClient as without json data, the address must be serialized using JsonSerializer.Serialize, as shown in the new code example above.
generalize
The main topic is first planning a database design using string columns with json data, and then how to process these data using Microsoft EF Core, Microsoft SqlClient and NuGet suite Dapper.
- Always validate your database architecture against business needs and, if possible, consider future business requirements.
- There is a toolbox available for understanding how to work with the data presented here, EF Core and Dapper, and the data providers in the provided source code.
An improvement is to use enumerations to specify address types to avoid errors such as typos. For EF Core check There is conversion
Image source
from designer in march
2024-12-22 20:01:10