Presentation 01

The data layer
DataAccessLayer

How a single entity turns into a table in the DB, with no hand-written SQL and no migrations.

What is this layer for?

Every database access goes through here — no other layer writes SQL.
A small, focused ORM of our own: Record plus attributes, no Entity Framework.
Supports three providers from exactly the same code: SQL Server, MySQL, SQLite.
Asynchronous end to end — every call to the DB is an await, no thread is ever blocked.

Record — the heart of the layer

Every entity has an ID (the primary key) and a Description.
Mark a property with [Column] — and it is a column in the DB.
The table name = the class name; the key = Name+Id.
No manual ALTER: SetupAsync runs at startup, creates the missing tables and adds or widens columns to match the code.

public class Car : Record
{
    [Column(Size = 300)]
    public override string Description { get; set; }

    [Column] public bool IsAutomatic { get; set; }
    [Column] public double Volume { get; set; }
    [Column] public string Color { get; set; }
    [Column] public DateTime ProductionDate { get; set; }
}

That is all the code it takes for a Car table to come up in the DB and be managed on screen.

Asynchronous CRUD

Car c = new Car { Description = "Mazda 3", Volume = 2.0 };
await c.SaveAsync();                    // INSERT (a new ID) or UPDATE — automatically

Car loaded = await Record.GetAsync<Car>(c.ID);      // load by key
List<Car> all = await Record.AllAsync<Car>();       // every record
int n        = await Record.CountAsync<Car>();      // a count

c.Color = "Red";
await c.UpdatePropertiesAsync("Color");  // updates one single column — not a full Save!

await c.DeleteAsync();

House rule: a targeted update is UpdatePropertiesAsync, not SaveAsync — so that a full save does not drag its side effects along.
DateTime.MinValue means "no date" — it is stored as NULL.

Filtered queries — AndFilter

AndFilter f = new AndFilter();
f.Add("color", "Red");                           // equality, as a parameter
f.Add("volume", 1.6, EqalityOperators.GreaterThan);
f.AddBetween("productionDate", from, to);

List<Car> cars = await Record.PartialAsync<Car>(f, "productionDate desc",
                                                pageNumber: 0, pageSize: 20);
int total     = await Record.CountAsync<Car>(f);

The filter builds the WHERE clause with parameters — no string concatenation and no SQL Injection.
OrFilter covers "or" conditions; paging goes through each provider's PagingTemplate.

How does the same code run on three databases?

The provider is picked from the connection string: Server= ← MySQL, Data Source=*.db ← SQLite, anything else ← SQL Server.
The code is written in T-SQL; each provider's FixSql translates it: GETDATE(), ISNULL, [brackets], IDENTITY…
SQLite also gets YEAR/MONTH/DAY functions implemented in C#.
Develop locally on SQLite, run production on SQL Server/MySQL — without changing a single line.

More tools in the box

Encryption: [Column(IsEncrypted = true)] — the value is AES-encrypted before it is written and decrypted when it is read.
Serialization: SerializeType = Json keeps an entire object in a single text column.
Indexes: CreateIndexAsync("color") — created only if it does not exist yet, on every provider.
Import: ImportAsync identifies existing records by IsUniqueId and updates them instead of duplicating them.
Transactions: BeginTransactionAsync / CommitTransactionAsync.

In short: define an entity, mark the columns — and the layer does all the rest.