16 - Database - C#

79 min read

16. Database - C#

Quote

“Future users of large data banks must be protected from having to know how the data is organized in the machine.”

— Edgar F. Codd, A Relational Model of Data for Large Shared Data Banks (1970)

C# Database Setup

NuGet packages and imports

Run this cell once before

Run this cell once before any cells that use NuGet packages — suppresses harmless CS1701/CS1702 assembly version warnings.

#r "nuget: Microsoft.Data.SqlClient"
#r "nuget: Microsoft.Data.Sqlite"
#r "nuget: System.Data.Odbc"
#r "nuget: Dapper"
#r "nuget: Microsoft.EntityFrameworkCore"
#r "nuget: Microsoft.EntityFrameworkCore.InMemory"
#r "nuget: DuckDB.NET.Data.Full, 1.3.0"
#r "nuget: Plotly.NET, 5.1.0"
#r "nuget: Plotly.NET.Interactive, 5.0.0"
#r "nuget: Plotly.NET.CSharp, 0.13.0"
#r "nuget: Polars.NET"
#r "nuget: Polars.NET.Native.win-x64"
using DuckDB.NET.Data;
using System.Reflection;
using Microsoft.DotNet.Interactive;
using Microsoft.DotNet.Interactive.CSharp;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Data.Sqlite;
using System.Data.Odbc;
using System.Data;
using Microsoft.DotNet.Interactive.Formatting;
using Microsoft.EntityFrameworkCore;
using Plotly.NET;
using Plotly.NET.CSharp;
using Plotly.NET.LayoutObjects;
using Polars.CSharp;
using static Polars.CSharp.Polars;
 
 
var csharpKernel = (CSharpKernel)Kernel.Root.FindKernelByName("csharp");
var optionsField = typeof(CSharpKernel).GetField("_scriptOptions",
    BindingFlags.NonPublic | BindingFlags.Instance);
 
var scriptOptions = optionsField.GetValue(csharpKernel);
var withWarningLevel = scriptOptions.GetType().GetMethod("WithWarningLevel");
var newOptions = withWarningLevel.Invoke(scriptOptions, new object[] { 0 });
optionsField.SetValue(csharpKernel, newOptions);
 
 
// extension method to .Head() datatables
static DataTable Head(this DataTable dt, int n = 5) 
    => dt.AsEnumerable().Take(n).CopyToDataTable();
 
 
// Register Polars DataFrame/Series HTML formatters (transparent for dark theme)
Formatter.Register<DataFrame>((df, writer) =>
{
    var html = df.ToHtml();
    html = System.Text.RegularExpressions.Regex.Replace(html, @"(>|>)&quot;(.+?)&quot;(<|<)", @"$1$2$3");
    html = System.Text.RegularExpressions.Regex.Replace(html, @">""(.+?)""<", @">$1<");
    var css = @"<style>.pl-dataframe,.pl-dataframe *{background:transparent!important;background-color:transparent!important;color:var(--vscode-editor-foreground,inherit)!important}.pl-dataframe{font-size:14px!important;border-collapse:collapse;width:auto}.pl-dataframe td,.pl-dataframe th{padding:6px 12px!important;text-align:left;border:1px solid var(--vscode-panel-border,#555)!important}.pl-dataframe th{font-weight:bold}.pl-dataframe .pl-dtype{font-size:11px;opacity:0.5}</style>";
    writer.Write(css + html);
}, "text/html");
Formatter.Register<Polars.CSharp.Series>((s, writer) =>
{
    var sdf = DataFrame.FromSeries(s);
    var shtml = sdf.ToHtml();
    shtml = System.Text.RegularExpressions.Regex.Replace(shtml, @"(>|>)&quot;(.+?)&quot;(<|<)", @"$1$2$3");
    shtml = System.Text.RegularExpressions.Regex.Replace(shtml, @">""(.+?)""<", @">$1<");
    var scss = @"<style>.pl-dataframe,.pl-dataframe *{background:transparent!important;color:var(--vscode-editor-foreground,inherit)!important}.pl-dataframe{font-size:14px!important;border-collapse:collapse}.pl-dataframe td,.pl-dataframe th{padding:6px 12px!important;text-align:left;border:1px solid var(--vscode-panel-border,#555)!important}.pl-dataframe th{font-weight:bold}.pl-dataframe .pl-dtype{font-size:11px;opacity:0.5}</style>";
    writer.Write(scss + shtml);
}, "text/html");

Shared helper functions

DataTable helper for query display

QueryToTable is a convenience wrapper used throughout this notebook. It executes a raw SQL string against either a SqlConnection or SqliteConnection and loads the result into a DataTable, which the notebook kernel renders as an HTML table. This avoids repeating the SqlCommand → ExecuteReader → dt.Load boilerplate for every display query.

// Helper: execute SQL query and return a DataTable for styled display
DataTable QueryToTable(SqlConnection c, string sql)
{
    var dt = new DataTable();
    var cmd = new SqlCommand(sql, c);
    using var reader = cmd.ExecuteReader();
    dt.Load(reader);
    return dt;
}
 
DataTable QueryToTable(SqliteConnection c, string sql)
{
    var dt = new DataTable();
    var cmd = c.CreateCommand();
    cmd.CommandText = sql;
    using var reader = cmd.ExecuteReader();
    dt.Load(reader);
    return dt;
}

SQLite — Lightweight Embedded Database

Connection, Schema, and CRUD

SQLite — create in-memory database and trades table

SQLite uses the ADO.NET pattern: SqliteConnection, SqliteCommand, SqliteDataReader. DataSource=:memory: creates an in-memory database. Parameterised queries use @param named placeholders. Python equivalent: sqlite3.connect(":memory:").

ADO.NET pattern (SQLite)

  • SqliteConnection / SqliteCommand / SqliteDataReader — same API as SqlClient
  • @param named placeholders prevent SQL injection
  • Always using connections to avoid leaks
  • SQLite is single-writer — use SQL Server for concurrent access
var conn = new SqliteConnection("DataSource=:memory:");
conn.Open();
 
var cmd = conn.CreateCommand();
cmd.CommandText = @"
    CREATE TABLE trades (
        trade_id   TEXT PRIMARY KEY,
        ticker     TEXT NOT NULL,
        side       TEXT NOT NULL CHECK(side IN ('BUY', 'SELL')),
        quantity   INTEGER NOT NULL CHECK(quantity > 0),
        price      REAL NOT NULL CHECK(price > 0),
        trade_date TEXT NOT NULL DEFAULT (date('now'))
    )";
cmd.ExecuteNonQuery();
trades

SQLite — INSERT with parameterised queries using @param placeholders

Always use @param named placeholders instead of string concatenation. AddWithValue binds the parameter by name, so SQLite escapes it safely before execution. String concatenation ($"INSERT ... '{ticker}'") allows SQL injection — a value like '; DROP TABLE trades;-- would execute as a second statement.

Never concatenate user input into SQL strings

cmd.CommandText = $"INSERT INTO trades VALUES ('{id}', '{ticker}', ...)" — a ticker value of '; DROP TABLE trades;-- truncates your query and executes arbitrary SQL.

Always use parameterised queries

cmd.Parameters.AddWithValue("@ticker", ticker) — SQLite treats the value as data, not code, regardless of what it contains.

var trades = new (string id, string ticker, string side, int qty, double price, string date)[]
{
    ("TRD_001", "ASML.AS", "BUY",  100, 685.40, "2026-03-15"),
    ("TRD_002", "MC.PA",   "BUY",   50, 890.20, "2026-03-15"),
    ("TRD_003", "SAP.DE",  "SELL",  75, 245.80, "2026-03-15"),
    ("TRD_004", "ASML.AS", "SELL",  30, 690.00, "2026-03-16"),
    ("TRD_005", "RMS.PA",  "BUY",   20, 2850.0, "2026-03-16"),
    ("TRD_006", "SIE.DE",  "BUY",  200, 198.50, "2026-03-17"),
};
 
foreach (var t in trades)
{
    cmd = conn.CreateCommand();
    cmd.CommandText = "INSERT INTO trades VALUES (@id, @ticker, @side, @qty, @price, @date)";
    cmd.Parameters.AddWithValue("@id", t.id);
    cmd.Parameters.AddWithValue("@ticker", t.ticker);
    cmd.Parameters.AddWithValue("@side", t.side);
    cmd.Parameters.AddWithValue("@qty", t.qty);
    cmd.Parameters.AddWithValue("@price", t.price);
    cmd.Parameters.AddWithValue("@date", t.date);
    cmd.ExecuteNonQuery();
}
Console.WriteLine(trades.Length);  // inserted
6

SQLite — SELECT with computed columns and WHERE filter

SqliteDataReader streams rows from the database without loading the full result set into memory. For display in notebooks, QueryToTable (the helper defined above) wraps the reader and loads into a DataTable, which the kernel renders as HTML. SQLite supports all standard SQL expressions in the SELECT list — ROUND(quantity * price, 2) AS notional is computed in the database, not in C#.

QueryToTable(conn, "SELECT *, ROUND(quantity * price, 2) AS notional FROM trades ORDER BY trade_date, trade_id")
trade_idtickersidequantitypricetrade_datenotional
TRD_001ASML.ASBUY100685.42026-03-1568540
TRD_002MC.PABUY50890.22026-03-1544510
TRD_003SAP.DESELL75245.82026-03-1518435
TRD_004ASML.ASSELL306902026-03-1620700
TRD_005RMS.PABUY2028502026-03-1657000
TRD_006SIE.DEBUY200198.52026-03-1739700

SQLite — aggregate queries with GROUP BY for portfolio summary

GROUP BY collapses rows with the same key column into a single row per group. SUM(CASE WHEN side='BUY' THEN quantity ELSE -quantity END) is the standard SQL pattern for computing net position — buys add, sells subtract. All aggregation (SUM, AVG, COUNT, MIN, MAX) happens inside the database engine, so only the summary rows are returned to C#.

QueryToTable(conn, @"
    SELECT ticker AS Ticker,
           SUM(CASE WHEN side='BUY' THEN quantity ELSE -quantity END) AS [Net Shares],
           ROUND(SUM(CASE WHEN side='BUY' THEN quantity*price ELSE -quantity*price END), 2) AS [Net Notional],
           COUNT(*) AS [Trade Count]
    FROM trades GROUP BY ticker ORDER BY [Net Notional] DESC")
TickerNet SharesNet NotionalTrade Count
RMS.PA20570001
ASML.AS70478402
MC.PA50445101
SIE.DE200397001
SAP.DE-75-184351

SQLite — UPDATE and DELETE rows

UPDATE and DELETE follow the same parameterised pattern as INSERT. ExecuteNonQuery() returns the number of affected rows — check this value to confirm the operation matched your intended rows (0 means the WHERE clause matched nothing). Both run under the connection’s implicit autocommit by default; wrap in a transaction if you need atomicity.

cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE trades SET price = @price WHERE trade_id = @id";
cmd.Parameters.AddWithValue("@price", 700.00);
cmd.Parameters.AddWithValue("@id", "TRD_004");
Console.WriteLine(cmd.ExecuteNonQuery());  // Updated TRD_004 price -> $700.00
 
// DELETE
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM trades WHERE trade_id = @id";
cmd.Parameters.AddWithValue("@id", "TRD_006");
Console.WriteLine(cmd.ExecuteNonQuery());  // Deleted TRD_006
1  // Updated TRD_004 price -> $700.00 (1 row affected)
1  // Deleted TRD_006 (1 row affected)

Transactions

SQLite — transaction with atomic multi-row insert and rollback

using (var tx = conn.BeginTransaction())
{
    try
    {
        var c1 = conn.CreateCommand();
        c1.Transaction = tx;
        c1.CommandText = "INSERT INTO trades VALUES (@id, @t, @s, @q, @p, date('now'))";
        c1.Parameters.AddWithValue("@id", "TRD_007");
        c1.Parameters.AddWithValue("@t", "TTE.PA");
        c1.Parameters.AddWithValue("@s", "BUY");
        c1.Parameters.AddWithValue("@q", 150);
        c1.Parameters.AddWithValue("@p", 58.30);
        c1.ExecuteNonQuery();
 
        var c2 = conn.CreateCommand();
        c2.Transaction = tx;
        c2.CommandText = "INSERT INTO trades VALUES (@id, @t, @s, @q, @p, date('now'))";
        c2.Parameters.AddWithValue("@id", "TRD_008");
        c2.Parameters.AddWithValue("@t", "BNP.PA");
        c2.Parameters.AddWithValue("@s", "BUY");
        c2.Parameters.AddWithValue("@q", 80);
        c2.Parameters.AddWithValue("@p", 72.10);
        c2.ExecuteNonQuery();
 
        tx.Commit();
    }
    catch (Exception ex)
    {
        tx.Rollback();
    }
}
 
// Final count
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM trades";
Console.WriteLine(cmd.ExecuteScalar());  // total trades
 
conn.Close();
Transaction committed (2 trades inserted)
7

PRAGMA Configuration

SQLite — PRAGMA overview and connection setup

PRAGMAs configure SQLite behavior per connection. Set them right after Open(). WAL mode enables concurrent readers. Cache and mmap control memory usage.

Key PRAGMAs

  • Set right after Open() — configure per connection

  • journal_mode=WAL — enables concurrent reads

  • busy_timeout — retries instead of failing on lock

Never use synchronous=OFF in production — data loss on crash.

Safe SQLite PRAGMA defaults

Use synchronous=NORMAL (or leave at the default FULL) in production. Enable WAL mode (journal_mode=WAL) for concurrent read access. Set busy_timeout=5000 so connections retry on lock contention rather than failing immediately.

var pragmaConn = new SqliteConnection("DataSource=:memory:");
pragmaConn.Open();
var cmd = pragmaConn.CreateCommand();

SQLite — journal_mode=WAL for concurrent reads

WAL (Write-Ahead Log) separates writes into a separate file and merges later, allowing readers to continue while a write is in progress. The default DELETE journal mode holds an exclusive lock during every write, blocking all readers. For SQLite files accessed by multiple threads or processes, WAL mode is almost always the right choice.

Use WAL mode for any SQLite file with concurrent readers

PRAGMA journal_mode=WAL returns "memory" for in-memory databases (WAL has no effect there). For file-backed databases it returns "wal". Set it once right after opening the connection.

cmd.CommandText = "PRAGMA journal_mode=WAL";
Console.WriteLine(cmd.ExecuteScalar());  // journal_mode
memory

SQLite — synchronous for durability vs speed

Controls when SQLite calls fsync() to flush writes to physical storage. FULL syncs after every commit (safest, slowest). NORMAL syncs only at critical moments — safe with WAL mode. OFF skips syncs entirely (fastest, but risks database corruption if the OS crashes mid-write). For production use, NORMAL with WAL mode provides the best balance.

cmd.CommandText = "PRAGMA synchronous=NORMAL";
cmd.ExecuteNonQuery();
cmd.CommandText = "PRAGMA synchronous";
Console.WriteLine(cmd.ExecuteScalar());  // synchronous (0=OFF, 1=NORMAL, 2=FULL)
1  // 0=OFF, 1=NORMAL, 2=FULL

SQLite — cache_size for in-memory page cache

A negative value sets the cache in kilobytes; a positive value sets it in pages. -20000 means 20MB. page_size must be set before creating any tables — it has no effect on an existing database.

cmd.CommandText = "PRAGMA cache_size=-20000";
cmd.ExecuteNonQuery();
cmd.CommandText = "PRAGMA cache_size";
Console.WriteLine(cmd.ExecuteScalar());  // cache_size (negative = KB)
-20000  // cache_size (negative = KB)

page_size must be set before any tables are created on a new database; it is a no-op on an existing database.

cmd.CommandText = "PRAGMA page_size";
Console.WriteLine(cmd.ExecuteScalar());  // page_size (bytes)
4096    // page_size (bytes)

SQLite — busy_timeout for lock contention retry

Waits up to N milliseconds for a lock instead of failing immediately. Without this, concurrent access gets an SQLITE_BUSY error instantly.

cmd.CommandText = "PRAGMA busy_timeout=5000";
cmd.ExecuteNonQuery();
cmd.CommandText = "PRAGMA busy_timeout";
Console.WriteLine(cmd.ExecuteScalar());  // busy_timeout (ms)
5000  // ms

SQLite — mmap_size for memory-mapped I/O

Maps up to N bytes of the database file into memory. 0 disables it; 268435456 = 256MB. Faster reads on large files — the OS pages data on demand rather than copying through the kernel buffer.

cmd.CommandText = "PRAGMA mmap_size=268435456";
cmd.ExecuteNonQuery();
cmd.CommandText = "PRAGMA mmap_size";
Console.WriteLine(Convert.ToInt64(cmd.ExecuteScalar()) / 1024 / 1024);  // mmap_size (MB)
0  // MB (returns 0 for in-memory databases — mmap applies to file-backed DBs)

SQLite — temp_store and foreign_keys

temp_store controls where temporary tables and indexes are stored: 0=DEFAULT, 1=FILE, 2=MEMORY. Foreign key enforcement is disabled by default in SQLite and must be enabled explicitly on every connection.

cmd.CommandText = "PRAGMA temp_store=MEMORY";
cmd.ExecuteNonQuery();
cmd.CommandText = "PRAGMA temp_store";
Console.WriteLine(cmd.ExecuteScalar());  // temp_store (0=DEFAULT, 1=FILE, 2=MEMORY)
 
cmd.CommandText = "PRAGMA foreign_keys=ON";
cmd.ExecuteNonQuery();
cmd.CommandText = "PRAGMA foreign_keys";
Console.WriteLine(cmd.ExecuteScalar());  // foreign_keys (0=OFF, 1=ON)
2  // temp_store: 0=DEFAULT, 1=FILE, 2=MEMORY
1  // foreign_keys: 0=OFF, 1=ON

Indexes and Query Performance

SQLite — CREATE TABLE for index demos

Creates the ohlcv table used throughout the index performance demos below.

cmd = pragmaConn.CreateCommand();
cmd.CommandText = @"
    CREATE TABLE IF NOT EXISTS ohlcv (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        symbol     TEXT NOT NULL,
        date       TEXT NOT NULL,
        open       REAL,
        high       REAL,
        low        REAL,
        close      REAL,
        volume     INTEGER
    )";
cmd.ExecuteNonQuery();
ohlcv

SQLite — INSERT 5000 sample OHLCV rows

Inserts 5000 rows of synthetic OHLCV data using a seeded random number generator.

var rng = new Random(42);
var symbols = new[] { "ASML.AS", "SAP.DE", "MC.PA", "SIE.DE", "TTE.PA" };
var baseDate = new DateTime(2024, 1, 1);
for (int i = 0; i < 5000; i++)
{
    var sym = symbols[i % symbols.Length];
    var dt = baseDate.AddDays(i / symbols.Length).ToString("yyyy-MM-dd");
    var price = 100 + rng.NextDouble() * 200;
    cmd.CommandText = $"INSERT INTO ohlcv (symbol, date, open, high, low, close, volume) "
        + $"VALUES ('{sym}', '{dt}', {price:F2}, {price * 1.02:F2}, {price * 0.98:F2}, {price * 1.01:F2}, {rng.Next(100000, 5000000)})";
    cmd.ExecuteNonQuery();
}
Inserted 5000 OHLCV rows

SQLite — EXPLAIN QUERY PLAN without index (full table scan)

EXPLAIN QUERY PLAN shows whether SQLite uses an index or a full table scan. Drop any existing indexes first to show the “before” state. Interpreting the output:

  • SCAN ohlcv — full table scan, reads every row; slow with no index
  • SEARCH ohlcv — index lookup, reads only matching rows; fast
  • USING INDEX idx — which index is being used
  • USING COVERING INDEX — index has all needed columns, no table access at all

The goal is to turn SCAN into SEARCH by creating the right index. A SCAN on 5000 rows is fine; on 50M rows it is a disaster.

Drop any existing indexes first to show the “before” state.

try { cmd.CommandText = "DROP INDEX IF EXISTS idx_ohlcv_symbol"; cmd.ExecuteNonQuery(); } catch {}
try { cmd.CommandText = "DROP INDEX IF EXISTS idx_ohlcv_symbol_date"; cmd.ExecuteNonQuery(); } catch {}
 
 
QueryToTable(pragmaConn, "EXPLAIN QUERY PLAN SELECT * FROM ohlcv WHERE symbol = 'ASML.AS' AND date > '2024-06-01'")
idparentnotuseddetail
20216SCAN ohlcv

SQLite — CREATE INDEX (single, composite, unique)

CREATE INDEX speeds up WHERE, JOIN, and ORDER BY. A composite index on (symbol, date) covers queries that filter on both columns. A unique index additionally enforces that no duplicate (symbol, date) pairs exist — the commented-out example would fail here because the sample data has duplicates.

A single-column index speeds up WHERE symbol = ?. A composite index on (symbol, date) covers queries filtering both columns. The unique index is commented out because the sample data contains duplicate (symbol, date) pairs.

cmd.CommandText = "CREATE INDEX idx_ohlcv_symbol ON ohlcv(symbol)";
cmd.ExecuteNonQuery();
 
cmd.CommandText = "CREATE INDEX idx_ohlcv_symbol_date ON ohlcv(symbol, date)";
cmd.ExecuteNonQuery();
 
// Unique index — enforces no duplicate (symbol, date) pairs
// cmd.CommandText = "CREATE UNIQUE INDEX idx_ohlcv_unique ON ohlcv(symbol, date)";
// Would fail here because our sample data has duplicates
idx_ohlcv_symbol created (single column)
idx_ohlcv_symbol_date created (composite)

SQLite — EXPLAIN QUERY PLAN with index (index scan)

The same query now uses the composite index instead of a full table scan. The output changes from SCAN ohlcv (reads all 5000 rows) to SEARCH ohlcv USING INDEX idx_ohlcv_symbol_date (symbol=? AND date>?) (jumps directly to matching rows). The composite index covers both WHERE conditions: symbol=? is an exact match on the first column, date>? is a range scan on the second. On 50M rows this is the difference between 50ms and 50 seconds.

QueryToTable(pragmaConn, "EXPLAIN QUERY PLAN SELECT * FROM ohlcv WHERE symbol = 'ASML.AS' AND date > '2024-06-01'")
idparentnotuseddetail
3051SEARCH ohlcv USING INDEX idx_ohlcv_symbol_date (symbol=? AND date>?)

SQLite — ANALYZE to update query planner statistics

ANALYZE collects statistics about index selectivity. The query planner uses these to choose the best index for each query. Run after bulk inserts or significant data changes.

cmd.CommandText = "ANALYZE";
cmd.ExecuteNonQuery();
query planner statistics updated

SQLite — list all indexes and tables with row counts

sqlite_master is the system catalog. Filtering by type = 'index' lists all indexes in the database.

QueryToTable(pragmaConn, "SELECT name AS [Index Name], tbl_name AS [Table] FROM sqlite_master WHERE type = 'index' ORDER BY tbl_name, name")
Index NameTable
idx_ohlcv_symbolohlcv
idx_ohlcv_symbol_dateohlcv

SQLite — list tables with row counts

Reads all table names from sqlite_master, then issues a COUNT(*) per table and assembles the results into a DataTable.

var tableNames = new List<string>();
var countCmd = pragmaConn.CreateCommand();
countCmd.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name";
using (var r = countCmd.ExecuteReader())
    while (r.Read()) tableNames.Add(r.GetString(0));
 
var dt = new DataTable();
dt.Columns.Add("Table");
dt.Columns.Add("Rows", typeof(long));
foreach (var table in tableNames)
{
    countCmd.CommandText = $"SELECT COUNT(*) FROM [{table}]";
    dt.Rows.Add(table, Convert.ToInt64(countCmd.ExecuteScalar()));
}
dt
TableRows
ohlcv5000
sqlite_sequence1
sqlite_stat12

SQLite — database size

Database size is page_count * page_size. Both values are read via PRAGMA.

countCmd.CommandText = "PRAGMA page_count";
var pageCount = Convert.ToInt64(countCmd.ExecuteScalar());
countCmd.CommandText = "PRAGMA page_size";
var pageSize = Convert.ToInt64(countCmd.ExecuteScalar());
Console.WriteLine($"{pageCount} pages x {pageSize} bytes = {pageCount * pageSize / 1024.0:F1} KB");
135 pages x 4096 bytes = 540.0 KB

SQLite — VACUUM, REINDEX, and integrity check

VACUUM — reclaim unused pages

VACUUM rebuilds and compacts the database file after DELETEs. It reclaims unused pages. On an in-memory database this is a no-op.

cmd.CommandText = "VACUUM";
cmd.ExecuteNonQuery();
database file compacted

REINDEX — rebuild all indexes from scratch

Use REINDEX after bulk updates that may have fragmented indexes.

cmd.CommandText = "REINDEX";
cmd.ExecuteNonQuery();
all indexes rebuilt

integrity_check — verify database consistency

Returns "ok" if everything is fine, or a list of problems if corruption is detected.

cmd.CommandText = "PRAGMA integrity_check";
Console.WriteLine(cmd.ExecuteScalar());  // integrity_check
 
pragmaConn.Close();
ok

SQLite — PRAGMA reference

Quick reference of all important SQLite PRAGMAs — set these right after Open().

SQL Server

Connection and CRUD

Connection strings in source code

The examples below embed credentials directly in the connection string for notebook clarity. In production, load credentials from environment variables (Environment.GetEnvironmentVariable) or a secrets manager (Azure Key Vault, AWS Secrets Manager). Never commit passwords to source control.

SQL Server — connect and list schemas/tables

Connect to the live stoxx database (localhost,1434). Query sys.tables and sys.schemas to discover the medallion architecture: bronze (raw), silver (cleaned), gold (computed scores). sys.partitions gives approximate row counts. The T-SQL patterns used throughout this section (parameterised queries, CTEs, window functions) follow sql-fundamentals.

ADO.NET pattern (SQL Server)

  • SqlConnection + SqlCommand + SqlDataReader — same pattern as SQLite
  • Always use @param for safety
  • sys.* catalog views give full metadata
  • Don’t use SELECT * in production — list columns explicitly
var connStr = "Server=localhost,1434;Database=stoxx;"
    + "User Id=sa;Password=EsgDev2026Pass1;"
    + "Encrypt=True;TrustServerCertificate=True;";
 
var conn = new SqlConnection(connStr);
conn.Open();
 
QueryToTable(conn, @"
    SELECT s.name AS [Schema], t.name AS [Table],
           FORMAT(p.rows, 'N0') AS Rows
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0, 1)
    ORDER BY s.name, t.name")
stoxx database
SchemaTableRows
bronzedim_country212
bronzedim_index4
bronzeeurostoxx50_ohlcv50
bronzeindex_dim169
bronzeoil20_ohlcv19
bronzepulse40
bronzepulse_tickers40
bronzesignals_daily169
bronzesignals_quarterly169
bronzestoxxasia50_ohlcv50
bronzestoxxusa50_ohlcv50
bronzetrading_calendar29,335
goldindex_performance5,281
goldscores_daily466
goldscores_quarterly170
silvereurostoxx50_ohlcv66,355
silverindex_dim169
silveroil20_ohlcv24,738
silversignals_daily466
silversignals_quarterly177
silverstoxxasia50_ohlcv64,045
silverstoxxusa50_ohlcv65,100

SQL Server — SELECT with parameterised queries

Uses @param named placeholders — same pattern as SQLite. AddWithValue infers the SQL type from the C# type and prevents SQL injection. FORMAT() formats numeric and date values for display.

QueryToTable(conn, @"
    SELECT TOP 10 symbol AS Symbol, CONVERT(VARCHAR, date, 23) AS Date,
           FORMAT([open], 'N2') AS [Open], FORMAT(high, 'N2') AS High,
           FORMAT(low, 'N2') AS Low, FORMAT([close], 'N2') AS [Close],
           FORMAT(volume, 'N0') AS Volume
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = 'SAP.DE'
    ORDER BY date DESC")
SymbolDateOpenHighLowCloseVolume
SAP.DE2026-03-12163.00166.74162.80166.52806,722
SAP.DE2026-03-11167.10168.96163.02165.442,953,782
SAP.DE2026-03-10171.60172.88166.46169.603,187,246
SAP.DE2026-03-09173.72173.86168.52171.881,990,823
SAP.DE2026-03-06173.66175.10170.24172.743,347,221
SAP.DE2026-03-05167.50172.80166.48170.982,961,032
SAP.DE2026-03-04169.22169.22165.94167.382,443,582
SAP.DE2026-03-03165.60166.16161.28165.483,971,985
SAP.DE2026-03-02166.62169.10164.86167.102,776,438
SAP.DE2026-02-27172.00173.34168.28170.962,673,448

SQL Server — aggregate queries and GROUP BY

QueryToTable(conn, @"
    SELECT TOP 10 symbol AS Symbol,
           COUNT(*) AS [Trading Days],
           FORMAT(AVG(CAST([close] AS FLOAT)), 'N2') AS [Avg Close],
           FORMAT(SUM(CAST(volume AS BIGINT)), 'N0') AS [Total Volume]
    FROM silver.eurostoxx50_ohlcv
    GROUP BY symbol
    ORDER BY SUM(CAST(volume AS BIGINT)) DESC")
SymbolTrading DaysAvg CloseTotal Volume
ISP.MI13213.15115,704,541,969
SAN.MC13294.4355,513,641,918
ENEL.MI13216.8232,600,561,934
BBVA.MC13298.6522,133,773,194
UCG.MI132128.4618,366,801,099
ENI.MI132113.4017,141,570,967
INGA.AS133114.0417,041,577,555
IBE.MC132912.2615,994,295,949
DTE.DE132422.4310,029,411,390
NDA-FI.HE130610.857,020,342,991

SQL Server — CREATE TABLE for demo

var sqlCmd = new SqlCommand(@"
    IF OBJECT_ID('dbo.trades_demo', 'U') IS NOT NULL DROP TABLE dbo.trades_demo;
    CREATE TABLE dbo.trades_demo (
        trade_id   NVARCHAR(20) PRIMARY KEY,
        ticker     NVARCHAR(10) NOT NULL,
        side       NVARCHAR(4) NOT NULL,
        quantity   INT NOT NULL,
        price      DECIMAL(10,2) NOT NULL,
        trade_date DATE NOT NULL DEFAULT GETDATE()
    )", conn);
sqlCmd.ExecuteNonQuery();
Created dbo.trades_demo

SQL Server — INSERT with parameterised values

SQL Server uses @named parameters (same as SQLite). AddWithValue infers the SQL type from the C# type — string → NVARCHAR, int → INT, decimal → DECIMAL. For production use, prefer Add(name, SqlDbType.NVarChar, 20) to specify types explicitly and avoid implicit conversion overhead.

var sqlCmd = new SqlCommand("INSERT INTO dbo.trades_demo VALUES (@id, @t, @s, @q, @p, @d)", conn);
sqlCmd.Parameters.AddWithValue("@id", "TRD_001");
sqlCmd.Parameters.AddWithValue("@t", "ASML.AS");
sqlCmd.Parameters.AddWithValue("@s", "BUY");
sqlCmd.Parameters.AddWithValue("@q", 100);
sqlCmd.Parameters.AddWithValue("@p", 685.40);
sqlCmd.Parameters.AddWithValue("@d", "2026-03-15");
Console.WriteLine(sqlCmd.ExecuteNonQuery());  // INSERT
1

SQL Server — UPDATE with parameterised WHERE

UPDATE changes column values in rows that match the WHERE predicate. Without a WHERE clause it updates every row in the table — always include a predicate. Returns the number of affected rows from ExecuteNonQuery().

var sqlCmd = new SqlCommand("UPDATE dbo.trades_demo SET price = @p WHERE trade_id = @id", conn);
sqlCmd.Parameters.AddWithValue("@p", 700.00);
sqlCmd.Parameters.AddWithValue("@id", "TRD_001");
Console.WriteLine(sqlCmd.ExecuteNonQuery());  // UPDATE
1

SQL Server — DELETE with parameterised WHERE

DELETE removes rows matching the WHERE predicate. Without a WHERE clause it removes all rows (equivalent to TRUNCATE but slower, as it logs each deletion). Use TRUNCATE TABLE to empty a table in one operation — but TRUNCATE cannot be rolled back in most configurations and does not fire row-level triggers.

var sqlCmd = new SqlCommand("DELETE FROM dbo.trades_demo WHERE trade_id = @id", conn);
sqlCmd.Parameters.AddWithValue("@id", "TRD_001");
Console.WriteLine(sqlCmd.ExecuteNonQuery());  // DELETE
1

SQL Server — DROP TABLE cleanup

var sqlCmd = new SqlCommand("DROP TABLE dbo.trades_demo", conn);
sqlCmd.ExecuteNonQuery();
Dropped dbo.trades_demo

SQL Server — transactions with BEGIN/COMMIT/ROLLBACK

All statements in the transaction succeed or all roll back — partial commits are not possible within a BeginTransaction block.

var sqlCmd = new SqlCommand(@"
    IF OBJECT_ID('dbo.tx_demo', 'U') IS NOT NULL DROP TABLE dbo.tx_demo;
    CREATE TABLE dbo.tx_demo (id INT PRIMARY KEY, val NVARCHAR(50))", conn);
sqlCmd.ExecuteNonQuery();
 
var tx = conn.BeginTransaction();
try
{
    new SqlCommand("INSERT INTO dbo.tx_demo VALUES (1, 'first')", conn, tx).ExecuteNonQuery();
    new SqlCommand("INSERT INTO dbo.tx_demo VALUES (2, 'second')", conn, tx).ExecuteNonQuery();
    tx.Commit();
}
catch (Exception ex)
{
    tx.Rollback();
}
 
sqlCmd = new SqlCommand("SELECT COUNT(*) FROM dbo.tx_demo", conn);
Console.WriteLine(sqlCmd.ExecuteScalar());  // rows in tx_demo
sqlCmd = new SqlCommand("DROP TABLE dbo.tx_demo", conn);
sqlCmd.ExecuteNonQuery();
Transaction committed (2 rows inserted)
Rows in tx_demo: 2

Indexes and Query Performance

SQL Server — list all indexes on a table

Joins sys.indexes, sys.index_columns, and sys.columns to show index name, type, uniqueness, and column list.

QueryToTable(conn, @"
    SELECT i.name AS [Index Name],
           i.type_desc AS [Type],
           CASE WHEN i.is_unique = 1 THEN 'Yes' ELSE 'No' END AS [Unique],
           STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS Columns
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE i.object_id = OBJECT_ID('silver.eurostoxx50_ohlcv')
    GROUP BY i.name, i.type_desc, i.is_unique
    ORDER BY i.name")
Index NameTypeUniqueColumns
IX_silver_eurostoxx50_ohlcv_symbol_dateNONCLUSTEREDYessymbol, date
PK__eurostox__3213E83FDF67D274CLUSTEREDYesid

SQL Server — benchmark full table scan with Stopwatch

This reads every 8KB page in the table. Elapsed time scales with table size. If the result is 0ms, data is fully cached in the SQL Server buffer pool.

var sw = new System.Diagnostics.Stopwatch();
 
sw.Restart();
var sqlCmd = new SqlCommand(@"
    SELECT symbol,
           COUNT(*) AS trading_days,
           ROUND(AVG(CAST([close] AS FLOAT)), 2) AS avg_close,
           ROUND(STDEV(CAST([close] AS FLOAT)), 2) AS close_stdev,
           SUM(CAST(volume AS BIGINT)) AS total_volume
    FROM silver.eurostoxx50_ohlcv
    GROUP BY symbol
    ORDER BY total_volume DESC", conn);
int rows1 = 0;
using (var reader = sqlCmd.ExecuteReader())
    while (reader.Read()) rows1++;
sw.Stop();
Console.WriteLine($"Full table scan: {rows1} symbols | {sw.ElapsedMilliseconds} ms");
50 symbols | 17 ms

SQL Server — benchmark indexed single-symbol lookup

With an index on symbol, SQL Server jumps directly to matching rows and should be significantly faster than the full scan above. If the elapsed time is similar, the table is small enough to fit entirely in the buffer pool cache.

sw.Restart();
var sqlCmd = new SqlCommand(@"
    SELECT TOP 100 symbol, date, [close], volume
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = @Symbol
    ORDER BY date DESC", conn);
sqlCmd.Parameters.AddWithValue("@Symbol", "ASML.AS");
int rows2 = 0;
using (var reader = sqlCmd.ExecuteReader())
    while (reader.Read()) rows2++;
sw.Stop();
Console.WriteLine($"Index seek: {rows2} rows | {sw.ElapsedMilliseconds} ms");
100 rows | 6 ms

SQL Server — benchmark cross-table JOIN

Combines silver.eurostoxx50_ohlcv with bronze.dim_index via a CROSS JOIN filtered to a single index key.

sw.Restart();
var joinResult = QueryToTable(conn, @"
    SELECT d.display_name AS [Index], COUNT(*) AS [OHLCV Rows],
           CONVERT(VARCHAR, MIN(o.date), 23) AS [First Date],
           CONVERT(VARCHAR, MAX(o.date), 23) AS [Last Date]
    FROM silver.eurostoxx50_ohlcv o
    CROSS JOIN bronze.dim_index d
    WHERE d.index_key = 'euro_stoxx_50'
    GROUP BY d.display_name");
sw.Stop();
Console.WriteLine($"JOIN completed in {sw.ElapsedMilliseconds} ms");
joinResult
JOIN completed in 9 ms
IndexOHLCV RowsFirst DateLast Date
Euro Stoxx 50663552021-01-042026-03-12

SQL Server — table sizes and page counts for I/O context

A full table scan reads total_pages * 8KB of data. An index seek reads only the pages containing matching rows. If the entire table fits in the buffer pool, physical reads = 0.

QueryToTable(conn, @"
    SELECT s.name + '.' + t.name AS [Table],
           FORMAT(SUM(p.rows), 'N0') AS Rows,
           CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Size MB],
           FORMAT(SUM(a.total_pages), 'N0') AS [Pages (8KB)]
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.indexes i ON t.object_id = i.object_id
    JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
    WHERE s.name IN ('bronze', 'silver', 'gold')
    GROUP BY s.name, t.name
    ORDER BY SUM(a.total_pages) DESC")
TableRowsSize MBPages (8KB)
silver.eurostoxx50_ohlcv132,7107.951,018
silver.stoxxasia50_ohlcv128,0907.70986
silver.stoxxusa50_ohlcv130,2007.39946
silver.oil20_ohlcv49,4762.83362
bronze.eurostoxx50_ohlcv1001.95250
bronze.stoxxasia50_ohlcv1001.95250
bronze.oil20_ohlcv381.89242
bronze.trading_calendar58,6701.77226
bronze.stoxxusa50_ohlcv1001.52194
bronze.index_dim6761.02130
gold.index_performance10,5620.89114
silver.index_dim6760.7798
gold.scores_daily9320.4558
gold.scores_quarterly3400.2734
bronze.signals_daily3380.2026
silver.signals_daily9320.2026
bronze.signals_quarterly3380.2026
silver.signals_quarterly3540.1418
bronze.pulse800.1418
bronze.pulse_tickers800.1418
bronze.dim_country2120.079
bronze.dim_index40.079

SQL Server — database size

QueryToTable(conn, @"
    SELECT DB_NAME() AS [Database],
           CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Size MB]
    FROM sys.database_files")
DatabaseSize MB
stoxx272.00

SQL Server — table sizes (Top 10)

QueryToTable(conn, @"
    SELECT TOP 10
           s.name + '.' + t.name AS [Table],
           FORMAT(SUM(p.rows), 'N0') AS Rows,
           CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS [Size MB]
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.indexes i ON t.object_id = i.object_id
    JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
    GROUP BY s.name, t.name
    ORDER BY SUM(a.total_pages) DESC")
TableRowsSize MB
silver.eurostoxx50_ohlcv132,7107.95
silver.stoxxasia50_ohlcv128,0907.70
silver.stoxxusa50_ohlcv130,2007.39
silver.oil20_ohlcv49,4762.83
bronze.eurostoxx50_ohlcv1001.95
bronze.stoxxasia50_ohlcv1001.95
bronze.oil20_ohlcv381.89
bronze.trading_calendar58,6701.77
bronze.stoxxusa50_ohlcv1001.52
bronze.index_dim6761.02

SQL Server — index fragmentation and maintenance

SQL Server stores index data in 8KB B-tree pages. INSERT/UPDATE/DELETE cause page splits → physical order diverges from logical order (fragmentation). A 50% fragmented index can be 2–5x slower for range scans.

FragmentationAction
< 10%Do nothing
10–30%ALTER INDEX idx REORGANIZE (online, lightweight)
> 30%ALTER INDEX idx REBUILD (recreates index, resets to 0%)
< 1000 pagesDon’t bother (too small)

Always UPDATE STATISTICS after REBUILD

Always UPDATE STATISTICS after REBUILD — stale stats produce bad query plans. Schedule via SQL Agent job. For data warehouses, rebuild after each ETL load.

Interpreting the results:

  • 95–98% on bronze tables (trading_calendar, ohlcv): bulk-loaded without sorting — random inserts cause massive page splits. A CLUSTERED INDEX at 98% means the physical row order is almost completely random relative to the index key; every range scan jumps across the entire file.
  • 47–49% on silver tables: built from bronze via INSERT...SELECT. Partial ordering from the source means partial fragmentation. Still above 30% — REBUILD is recommended.
  • Small page counts (20–51 pages): tiny tables — fragmentation matters less because the entire table fits in the buffer pool. Rebuilding is instant.
  • Larger tables (213–252 pages ≈ 2MB): fragmentation has measurable impact. Sequential scans read 2× more pages than necessary at 47%. REBUILD will cut scan time significantly.
QueryToTable(conn, @"
    SELECT TOP 10
           OBJECT_NAME(ips.object_id) AS [Table],
           i.name AS [Index],
           ips.index_type_desc AS [Type],
           CAST(ROUND(ips.avg_fragmentation_in_percent, 1) AS DECIMAL(5,1)) AS [Frag %],
           ips.page_count AS Pages,
           CASE
               WHEN ips.avg_fragmentation_in_percent < 10 THEN 'OK'
               WHEN ips.avg_fragmentation_in_percent < 30 THEN 'REORGANIZE'
               ELSE 'REBUILD'
           END AS Action
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.page_count > 10
    ORDER BY ips.avg_fragmentation_in_percent DESC")
TableIndexTypeFrag %PagesAction
trading_calendarPK_trading_calendarCLUSTERED INDEX98.7149REBUILD
eurostoxx50_ohlcvIX_bronze_eurostoxx50_ohlcv_symbol_dateNONCLUSTERED INDEX98.051REBUILD
stoxxusa50_ohlcvIX_bronze_stoxxusa50_ohlcv_symbol_dateNONCLUSTERED INDEX98.051REBUILD
oil20_ohlcvPK__oil20_oh__3213E83F22CF352ACLUSTERED INDEX95.020REBUILD
oil20_ohlcvIX_bronze_oil20_ohlcv_symbol_dateNONCLUSTERED INDEX95.020REBUILD
stoxxasia50_ohlcvIX_bronze_stoxxasia50_ohlcv_symbol_dateNONCLUSTERED INDEX94.151REBUILD
stoxxasia50_ohlcvIX_silver_stoxxasia50_ohlcv_symbol_dateNONCLUSTERED INDEX49.4247REBUILD
stoxxusa50_ohlcvIX_silver_stoxxusa50_ohlcv_symbol_dateNONCLUSTERED INDEX47.4213REBUILD
eurostoxx50_ohlcvIX_silver_eurostoxx50_ohlcv_symbol_dateNONCLUSTERED INDEX47.2252REBUILD
oil20_ohlcvIX_silver_oil20_ohlcv_symbol_dateNONCLUSTERED INDEX46.278REBUILD

SQL Server — find all indexes needing REBUILD (>30% fragmented)

Queries all indexes above the 30% fragmentation threshold and collects them into a list for the rebuild loop below.

var sqlCmd = new SqlCommand(@"
    SELECT s.name AS schema_name, OBJECT_NAME(ips.object_id) AS table_name,
           i.name AS index_name,
           ROUND(ips.avg_fragmentation_in_percent, 1) AS frag_pct
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    JOIN sys.tables t ON ips.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE ips.avg_fragmentation_in_percent > 30
      AND ips.page_count > 10
      AND i.name IS NOT NULL
    ORDER BY ips.avg_fragmentation_in_percent DESC", conn);
 
var indexesToRebuild = new List<(string schema, string table, string index, double frag)>();
using (var reader = sqlCmd.ExecuteReader())
    while (reader.Read())
        indexesToRebuild.Add((
            reader["schema_name"].ToString()!,
            reader["table_name"].ToString()!,
            reader["index_name"].ToString()!,
            Convert.ToDouble(reader["frag_pct"])));
 
Console.WriteLine(indexesToRebuild.Count);  // indexes to rebuild (>30% fragmentation)
12  // indexes to rebuild (>30% fragmentation)

SQL Server — ALTER INDEX REBUILD on each fragmented index

REBUILD recreates the index from scratch — fragmentation resets to 0%.

var sw = System.Diagnostics.Stopwatch.StartNew();
foreach (var (schema, table, index, frag) in indexesToRebuild)
{
    var rebuildSql = $"ALTER INDEX [{index}] ON [{schema}].[{table}] REBUILD";
    new SqlCommand(rebuildSql, conn).ExecuteNonQuery();
}
sw.Stop();
Console.WriteLine($"All {indexesToRebuild.Count} indexes rebuilt in {sw.ElapsedMilliseconds} ms");
[bronze].[trading_calendar].[PK_trading_calendar] (was 98.7%)
[bronze].[eurostoxx50_ohlcv].[IX_bronze_eurostoxx50_ohlcv_symbol_date] (was 98%)
[bronze].[stoxxusa50_ohlcv].[IX_bronze_stoxxusa50_ohlcv_symbol_date] (was 98%)
[bronze].[oil20_ohlcv].[PK__oil20_oh__3213E83F22CF352A] (was 95%)
[bronze].[oil20_ohlcv].[IX_bronze_oil20_ohlcv_symbol_date] (was 95%)
[bronze].[stoxxasia50_ohlcv].[IX_bronze_stoxxasia50_ohlcv_symbol_date] (was 94.1%)
[silver].[stoxxasia50_ohlcv].[IX_silver_stoxxasia50_ohlcv_symbol_date] (was 49.4%)
[silver].[stoxxusa50_ohlcv].[IX_silver_stoxxusa50_ohlcv_symbol_date] (was 47.4%)
[silver].[eurostoxx50_ohlcv].[IX_silver_eurostoxx50_ohlcv_symbol_date] (was 47.2%)
[silver].[oil20_ohlcv].[IX_silver_oil20_ohlcv_symbol_date] (was 46.2%)
[gold].[index_performance].[UX_gold_index_performance] (was 31.8%)
[gold].[scores_daily].[PK__scores_d__3213E83F41C788A9] (was 30.4%)
All 12 indexes rebuilt in 167 ms

SQL Server — UPDATE STATISTICS after rebuild

Stale statistics produce bad query plans. Always update after REBUILD.

foreach (var (schema, table, _, _) in indexesToRebuild.DistinctBy(x => x.schema + "." + x.table))
{
    new SqlCommand($"UPDATE STATISTICS [{schema}].[{table}]", conn).ExecuteNonQuery();
}
Statistics updated:
[bronze].[trading_calendar]
[bronze].[eurostoxx50_ohlcv]
[bronze].[stoxxusa50_ohlcv]
[bronze].[oil20_ohlcv]
[bronze].[stoxxasia50_ohlcv]
[silver].[stoxxasia50_ohlcv]
[silver].[stoxxusa50_ohlcv]
[silver].[eurostoxx50_ohlcv]
[silver].[oil20_ohlcv]
[gold].[index_performance]
[gold].[scores_daily]

SQL Server — verify fragmentation after rebuild

Re-runs the fragmentation query to confirm all rebuilt indexes are now at or near 0%.

QueryToTable(conn, @"
    SELECT TOP 10
           s.name + '.' + OBJECT_NAME(ips.object_id) AS [Table],
           i.name AS [Index],
           ips.index_type_desc AS [Type],
           CAST(ROUND(ips.avg_fragmentation_in_percent, 1) AS DECIMAL(5,1)) AS [Frag %],
           ips.page_count AS Pages
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    JOIN sys.tables t ON ips.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE ips.page_count > 10
    ORDER BY ips.avg_fragmentation_in_percent DESC")
TableIndexTypeFrag %Pages
bronze.index_dimPK__index_di__3213E83FDB4E5BA9CLUSTERED INDEX13.688
gold.index_performanceUX_gold_index_performanceNONCLUSTERED INDEX5.319
gold.index_performancePK__index_pe__3213E83FBBB2393ECLUSTERED INDEX4.981
silver.stoxxusa50_ohlcvPK__stoxxusa__3213E83FC84E3F24CLUSTERED INDEX1.4724
silver.index_dimPK__index_di__3213E83F590AA69ECLUSTERED INDEX1.285
silver.stoxxusa50_ohlcvIX_silver_stoxxusa50_ohlcv_symbol_dateNONCLUSTERED INDEX0.6163
silver.stoxxasia50_ohlcvIX_silver_stoxxasia50_ohlcv_symbol_dateNONCLUSTERED INDEX0.5183
silver.stoxxasia50_ohlcvPK__stoxxasi__3213E83F66A8DE5ECLUSTERED INDEX0.4729
silver.eurostoxx50_ohlcvPK__eurostox__3213E83FDF67D274CLUSTERED INDEX0.4757
silver.oil20_ohlcvPK__oil20_oh__3213E83F544EB286CLUSTERED INDEX0.4275

SQL Server — index usage statistics

sys.dm_db_index_usage_stats shows which indexes are actually used. Unused indexes waste disk space and slow down writes.

QueryToTable(conn, @"
    SELECT TOP 10
           OBJECT_NAME(s.object_id) AS [Table],
           i.name AS [Index],
           s.user_seeks AS Seeks, s.user_scans AS Scans,
           s.user_lookups AS Lookups, s.user_updates AS Updates
    FROM sys.dm_db_index_usage_stats s
    JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
    WHERE s.database_id = DB_ID()
    ORDER BY s.user_seeks + s.user_scans DESC")
TableIndexSeeksScansLookupsUpdates
eurostoxx50_ohlcvPK__eurostox__3213E83FDF67D27401630
dim_indexPK__dim_inde__D02D09ED2DACF7AF4200
dim_countryPK__dim_coun__F7018895254145131001
pulse_tickersPK__pulse_ti__3213E83FF5E3765E0000
stoxxusa50_ohlcvPK__stoxxusa__3213E83FC84E3F240000
stoxxusa50_ohlcvIX_silver_stoxxusa50_ohlcv_symbol_date0000
scores_dailyPK__scores_d__3213E83F41C788A90000
scores_dailyUX_gold_scores_daily0000
signals_dailyPK__signals___3213E83F7466104F0000
signals_quarterlyPK__signals___3213E83FD922C3080000

SQL Server — provoke missing index recommendations

Missing index DMVs only populate when SQL Server sees queries that WOULD have benefited from an index that doesn’t exist. After an index REBUILD, the DMV stats reset. Run queries on unindexed columns — SQL Server tracks these in sys.dm_db_missing_index_details.

Query 1 — filter on close (no index on close)

new SqlCommand(@"
    SELECT symbol, date, [close], volume
    FROM silver.eurostoxx50_ohlcv
    WHERE [close] > 500
    ORDER BY [close] DESC", conn).ExecuteReader().Close();

Query 2 — filter on volume (no index on volume)

new SqlCommand(@"
    SELECT symbol, date, volume
    FROM silver.eurostoxx50_ohlcv
    WHERE volume > 5000000
    ORDER BY volume DESC", conn).ExecuteReader().Close();

Query 3 — filter on date range without covering index

new SqlCommand(@"
    SELECT symbol, date, [close], high - low AS daily_range
    FROM silver.eurostoxx50_ohlcv
    WHERE date BETWEEN ‘2025-01-01’ AND ‘2025-06-30’
    ORDER BY date", conn).ExecuteReader().Close();

Repeat queries to increase impact score

Run each query a few times to increase the impact score tracked by the DMV.

for (int i = 0; i < 5; i++)
{
    new SqlCommand("SELECT * FROM silver.eurostoxx50_ohlcv WHERE [close] > 500", conn).ExecuteReader().Close();
    new SqlCommand("SELECT * FROM silver.eurostoxx50_ohlcv WHERE volume > 5000000", conn).ExecuteReader().Close();
}
Ran 13 queries on unindexed columns to provoke recommendations

SQL Server — missing index recommendations from the query optimizer

Missing index advisor

sys.dm_db_missing_index_details — SQL Server records missing indexes every time it compiles a plan.

  • equality_columns — WHERE = | inequality_columns — WHERE >
  • included_columns — SELECT → INCLUDE
  • avg_user_impact — estimated % improvement
  • Empty after restart (in-memory only)
QueryToTable(conn, @"
    SELECT TOP 5
           OBJECT_NAME(d.object_id) AS [Table],
           ISNULL(d.equality_columns, '-') AS [Equality Columns],
           ISNULL(d.inequality_columns, '-') AS [Inequality Columns],
           ISNULL(d.included_columns, '-') AS [Include Columns],
           CAST(ROUND(s.avg_user_impact, 1) AS DECIMAL(5,1)) AS [Impact %],
           s.user_seeks + s.user_scans AS [Queries]
    FROM sys.dm_db_missing_index_details d
    JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
    JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
    WHERE d.database_id = DB_ID()
    ORDER BY s.avg_user_impact * (s.user_seeks + s.user_scans) DESC")
TableEquality ColumnsInequality ColumnsInclude ColumnsImpact %Queries
eurostoxx50_ohlcv-[close][symbol], [date], [open], [high], [low], [adj_close], [volume], [dividends], [stock_splits], [is_filled]65.75
eurostoxx50_ohlcv-[volume][symbol], [date], [open], [high], [low], [close], [adj_close], [dividends], [stock_splits], [is_filled]65.75
eurostoxx50_ohlcv-[close][symbol], [date], [volume]66.71
eurostoxx50_ohlcv-[date][symbol], [high], [low], [close]65.11
eurostoxx50_ohlcv-[volume][symbol], [date]37.01

Server Configuration and Administration

SQL Server — server configuration and version

Reads server metadata from @@VERSION and SERVERPROPERTY() built-in functions.

QueryToTable(conn, @"
    SELECT
        SUBSTRING(@@VERSION, 1, CHARINDEX(' (', @@VERSION) - 1) AS [Version],
        CAST(SERVERPROPERTY('Edition') AS NVARCHAR(100)) AS Edition,
        CAST(SERVERPROPERTY('Collation') AS NVARCHAR(100)) AS Collation,
        CAST(SERVERPROPERTY('ProductLevel') AS NVARCHAR(20)) AS [Level]")
VersionEditionCollationLevel
Microsoft SQL Server 2022Developer Edition (64-bit)SQL_Latin1_General_CP1_CI_ASRTM

SQL Server — key configuration settings

Reads the three most important performance-related settings from sys.configurations.

QueryToTable(conn, @"
    SELECT name AS [Setting],
           CAST(value_in_use AS NVARCHAR(30)) AS [Value],
           CASE name
               WHEN 'max server memory (MB)' THEN 'Max RAM for buffer pool (2147483647 = unlimited)'
               WHEN 'max degree of parallelism' THEN 'Max CPU cores per query (0 = all cores)'
               WHEN 'cost threshold for parallelism' THEN 'Query cost before parallel plan (5 = default)'
               ELSE ''
           END AS [Meaning]
    FROM sys.configurations
    WHERE name IN ('max server memory (MB)', 'max degree of parallelism',
                   'cost threshold for parallelism')
    ORDER BY name")
SettingValueMeaning
cost threshold for parallelism5Query cost before parallel plan (5 = default)
max degree of parallelism0Max CPU cores per query (0 = all cores)
max server memory (MB)2147483647Max RAM for buffer pool (2147483647 = unlimited)

SQL Server — active sessions and blocking

Shows all user processes from sys.dm_exec_sessions — who is connected and what they are doing.

QueryToTable(conn, @"
    SELECT s.session_id AS SID,
           s.login_name AS [Login],
           ISNULL(s.host_name, '') AS Host,
           LEFT(ISNULL(s.program_name, ''), 30) AS Program,
           s.status AS Status,
           DB_NAME(s.database_id) AS [Database]
    FROM sys.dm_exec_sessions s
    WHERE s.is_user_process = 1
    ORDER BY s.session_id")
SIDLoginHostProgramStatusDatabase
53NT AUTHORITY\SYSTEM8482aae8ad0aSQLServerCEIPsleepingmaster
55saELYSIUMCore Microsoft SqlClient Data sleepingstoxx
56saELYSIUMCore Microsoft SqlClient Data runningstoxx
57saELYSIUMCore Microsoft SqlClient Data sleepingstoxx

SQL Server — administration reference

Quick reference of essential SQL Server DMVs and commands for monitoring, tuning, and troubleshooting.

ODBC Provider

SQL Server — ODBC Provider with positional parameters

Uses the same ODBC Driver 18 as Python pyodbc. Parameters use ? positional placeholders instead of @named.

var odbcConnStr = "Driver={ODBC Driver 18 for SQL Server};"
    + "Server=localhost,1434;Database=stoxx;"
    + "UID=sa;PWD=EsgDev2026Pass1;"
    + "Encrypt=yes;TrustServerCertificate=yes;";
 
var odbcResult = new DataTable();
using (var odbcConn = new OdbcConnection(odbcConnStr))
{
    odbcConn.Open();
    var odbcCmd = new OdbcCommand(
        "SELECT TOP 5 symbol, short_name, composite_score "
        + "FROM gold.scores_daily WHERE _index = ? "
        + "ORDER BY composite_rank", odbcConn);
    odbcCmd.Parameters.AddWithValue("@p1", "euro_stoxx_50");
    odbcResult.Load(odbcCmd.ExecuteReader());
}
odbcResult
symbolshort_namecomposite_score
BNP.PABNP PARIBAS ACT.A0.6839467847784353
BNP.PABNP PARIBAS ACT.A0.6639711356464837
BNP.PABNP PARIBAS ACT.A0.6795985859619491
DTE.DEDEUTSCHE TELEKOM AG0.5150053634526331
DTE.DEDEUTSCHE TELEKOM AG0.5214424751678984

SQL Server — SqlClient vs ODBC comparison

var cmp = new DataTable();
cmp.Columns.Add("Feature");
cmp.Columns.Add("SqlClient");
cmp.Columns.Add("ODBC");
cmp.Rows.Add("NuGet needed", "Yes (Microsoft.Data.SqlClient)", "No (System.Data.Odbc built-in)");
cmp.Rows.Add("Parameters", "@named", "? positional (like Python)");
cmp.Rows.Add("SQL Server features", "Full (bulk copy, Always Encrypted)", "Standard ODBC only");
cmp.Rows.Add("Python equivalent", "—", "pyodbc");
cmp.Rows.Add("Connection string", "Server=host;Database=db;...", "Driver={ODBC Driver 18};Server=...");
cmp.Rows.Add("Best for", "SQL Server-specific apps", "Cross-database portability");
cmp
FeatureSqlClientODBC
NuGet neededYes (Microsoft.Data.SqlClient)No (System.Data.Odbc built-in)
Parameters@named? positional (like Python)
SQL Server featuresFull (bulk copy, Always Encrypted)Standard ODBC only
Python equivalent—pyodbc
Connection stringServer=host;Database=db;...Driver={ODBC Driver 18};Server=...
Best forSQL Server-specific appsCross-database portability

Dapper — Micro-ORM

Dapper sits between raw ADO.NET and full Entity Framework. You write SQL (full control), Dapper maps results to typed C# objects (no manual reader.GetString(0)).

The problem with ADO.NET

// 10 lines of boilerplate per query
var cmd = new SqlCommand(sql, conn);
using var reader = cmd.ExecuteReader();
while (reader.Read()) {
    var row = new OhlcvRow(
        reader.GetString(0),      // which column is 0? hope you remember
        reader.GetDateTime(1),    // wrong index = runtime crash
        reader.GetDouble(2),      // manual cast for every column
    );
}

What Dapper gives you

// 1 line — same performance, typed result
var rows = conn.Query<OhlcvRow>(sql, new { Symbol = "ASML.AS" });
Console.WriteLine(rows.First().Close);   // double, not object — IntelliSense, refactoring, compile-time safety

Key difference: ADO.NET returns untyped rows (object values, index-based access). Dapper returns typed objects (real C# instances with properties). Same difference as dict vs dataclass in Python, or pd.read_sql() returning a DataFrame vs raw cursor.fetchall().

ADO.NETDapper
Access a fieldreader["close"] → object, must castrow.Close → double directly
Typo in columnRuntime crashCompile error
LINQ on resultsNot on DataReaderFull LINQ: .Where(), .OrderBy()
Parameterscmd.Parameters.AddWithValue per paramnew { Symbol = "ASML" } one-liner
PerformanceFastest~Same (IL emission, not reflection)
Python equivalentpyodbc cursor.fetchall()pd.read_sql() returning DataFrame

Dapper — NuGet setup and record DTOs

Define a record whose property names match SQL column aliases — Dapper matches by name (case-insensitive), no configuration. Auto-maps results, supports parameterised queries via anonymous objects, ~same performance as raw ADO.NET. Use EF Core for complex CRUD with relationships.

record OhlcvRow(string Symbol, DateTime Date, double Open, double High, double Low, double Close, long Volume);
record IndexInfo(string IndexKey, string DisplayName, string Currency);
record ScoreRow(string Symbol, double CompositeScore, int CompositeRank);
record TradeRow(string TradeId, string Ticker, string Side, int Quantity, decimal Price);
var dapperConn = new SqlConnection(
    "Server=localhost,1434;Database=stoxx;"
    + "User Id=sa;Password=EsgDev2026Pass1;"
    + "Encrypt=True;TrustServerCertificate=True;");
dapperConn.Open();

Dapper — Query<T> returns typed list from SQL

Query<T> returns IEnumerable<T>, mapping each row to a record. Column aliases must match property names (case-insensitive).

var indices = dapperConn.Query<IndexInfo>(
    "SELECT index_key AS IndexKey, display_name AS DisplayName, currency AS Currency FROM bronze.dim_index ORDER BY display_name");
 
var dt = new DataTable();
dt.Columns.Add("Index Key"); dt.Columns.Add("Display Name"); dt.Columns.Add("Currency");
foreach (var idx in indices)
    dt.Rows.Add(idx.IndexKey, idx.DisplayName, idx.Currency);
dt
Index KeyDisplay NameCurrency
euro_stoxx_50Euro Stoxx 50€
oil_20Oil & Gas 20$
stoxx_asia_50STOXX Asia/Pacific 50
stoxx_usa_50STOXX USA 50$

Dapper — parameterised queries with anonymous objects

Parameters are passed as an anonymous object — each property maps to a @param in the SQL. No cmd.Parameters.AddWithValue boilerplate.

var prices = dapperConn.Query<OhlcvRow>(@"
    SELECT TOP 5 symbol AS Symbol, date AS Date,
           [open] AS [Open], high AS High, low AS Low, [close] AS [Close], volume AS Volume
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = @Symbol
    ORDER BY date DESC",
    new { Symbol = "ASML.AS" });    // anonymous object maps to @Symbol
 
var dt = new DataTable();
dt.Columns.Add("Symbol"); dt.Columns.Add("Date"); dt.Columns.Add("Open");
dt.Columns.Add("High"); dt.Columns.Add("Low"); dt.Columns.Add("Close"); dt.Columns.Add("Volume");
foreach (var p in prices)
    dt.Rows.Add(p.Symbol, $"{p.Date:yyyy-MM-dd}", $"{p.Open:F2}", $"{p.High:F2}", $"{p.Low:F2}", $"{p.Close:F2}", $"{p.Volume:N0}");
dt
SymbolDateOpenHighLowCloseVolume
ASML.AS2026-03-121194.801202.201187.801190.80128'223
ASML.AS2026-03-111188.401210.801174.001198.80562'904
ASML.AS2026-03-101188.401208.401172.201200.00800'815
ASML.AS2026-03-091072.001147.601060.201147.60689'086
ASML.AS2026-03-061186.001192.601112.801147.00857'271

Dapper — multiple parameters and WHERE IN

Each property in the anonymous object becomes a @param. For WHERE IN, pass a list — Dapper expands it to (val1, val2, val3) automatically.

var filtered = dapperConn.Query<OhlcvRow>(@"
    SELECT TOP 10 symbol AS Symbol, date AS Date,
           [open] AS [Open], high AS High, low AS Low, [close] AS [Close], volume AS Volume
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = @Symbol AND volume > @MinVolume
    ORDER BY volume DESC",
    new { Symbol = "SAP.DE", MinVolume = 3_000_000 });
 
var multiSymbol = dapperConn.Query<OhlcvRow>(@"
    SELECT TOP 10 symbol AS Symbol, date AS Date,
           [open] AS [Open], high AS High, low AS Low, [close] AS [Close], volume AS Volume
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol IN @Symbols
    ORDER BY date DESC",
    new { Symbols = new[] { "ASML.AS", "SAP.DE", "MC.PA" } });
 
Console.WriteLine(filtered.Count());   // filtered (SAP + vol>3M)
Console.WriteLine(multiSymbol.Count());  // multi-symbol IN
Filtered (SAP + vol>3M): 10 rows
10 rows

Dapper — QueryFirst, QuerySingle, ExecuteScalar

  • QueryFirst<T> — returns the first row (throws if empty)
  • QueryFirstOrDefault<T> — returns the first row or null/default
  • QuerySingle<T> — returns exactly one row (throws if 0 or more than 1)
  • ExecuteScalar<T> — returns a single value (COUNT, SUM, MAX)
var latest = dapperConn.QueryFirst<OhlcvRow>(@"
    SELECT TOP 1 symbol AS Symbol, date AS Date,
           [open] AS [Open], high AS High, low AS Low, [close] AS [Close], volume AS Volume
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = @Symbol ORDER BY date DESC",
    new { Symbol = "SAP.DE" });
 
var rowCount = dapperConn.ExecuteScalar<long>("SELECT COUNT(*) FROM silver.eurostoxx50_ohlcv");
var stockCount = dapperConn.ExecuteScalar<long>("SELECT COUNT(DISTINCT symbol) FROM silver.eurostoxx50_ohlcv");
var maxVol = dapperConn.ExecuteScalar<long>("SELECT MAX(volume) FROM silver.eurostoxx50_ohlcv");
 
var dt = new DataTable();
dt.Columns.Add("Metric"); dt.Columns.Add("Value");
dt.Rows.Add("Latest SAP.DE", $"{latest.Date:yyyy-MM-dd} | Close: {latest.Close:F2} | Vol: {latest.Volume:N0}");
dt.Rows.Add("Total OHLCV rows", $"{rowCount:N0}");
dt.Rows.Add("Distinct stocks", $"{stockCount}");
dt.Rows.Add("Max volume", $"{maxVol:N0}");
dt
MetricValue
Latest SAP.DE2026-03-12 | Close: 166.52 | Vol: 806'722
Total OHLCV rows66'355
Distinct stocks50
Max volume376'391'539

Dapper — Execute for INSERT, UPDATE, DELETE

Execute returns the number of affected rows. Use it for all write operations.

Setup — create demo table

dapperConn.Execute(@"
    IF OBJECT_ID('dbo.dapper_trades', 'U') IS NOT NULL DROP TABLE dbo.dapper_trades;
    CREATE TABLE dbo.dapper_trades (
        trade_id NVARCHAR(20) PRIMARY KEY,
        ticker   NVARCHAR(10),
        side     NVARCHAR(4),
        quantity INT,
        price    DECIMAL(10,2))");

INSERT — single row

var inserted = dapperConn.Execute(
    "INSERT INTO dbo.dapper_trades VALUES (@TradeId, @Ticker, @Side, @Quantity, @Price)",
    new { TradeId = "TRD_001", Ticker = "ASML.AS", Side = "BUY", Quantity = 100, Price = 685.40m });
Console.WriteLine(inserted);  // INSERT
1 row

INSERT — batch (pass a list, Dapper executes once per item)

var batch = new[] {
    new { TradeId = "TRD_002", Ticker = "SAP.DE",  Side = "SELL", Quantity = 75,  Price = 245.80m },
    new { TradeId = "TRD_003", Ticker = "MC.PA",   Side = "BUY",  Quantity = 50,  Price = 890.20m },
    new { TradeId = "TRD_004", Ticker = "RMS.PA",  Side = "BUY",  Quantity = 20,  Price = 2850.0m },
};
var batchInserted = dapperConn.Execute(
    "INSERT INTO dbo.dapper_trades VALUES (@TradeId, @Ticker, @Side, @Quantity, @Price)", batch);
Console.WriteLine(batchInserted);  // BATCH INSERT
3 rows

UPDATE — modify a row

var updated = dapperConn.Execute(
    "UPDATE dbo.dapper_trades SET price = @Price WHERE trade_id = @TradeId",
    new { Price = 700.00m, TradeId = "TRD_001" });
Console.WriteLine(updated);  // UPDATE
1 row

DELETE — remove a row

var deleted = dapperConn.Execute(
    "DELETE FROM dbo.dapper_trades WHERE trade_id = @TradeId",
    new { TradeId = "TRD_004" });
Console.WriteLine(deleted);  // DELETE
1 row

Dapper — verify trades table after INSERT/UPDATE/DELETE

Queries the modified table to confirm TRD_001 was updated to 700.00 and TRD_004 was deleted.

var dt = new DataTable();
dt.Columns.Add("Trade ID"); dt.Columns.Add("Ticker"); dt.Columns.Add("Side");
dt.Columns.Add("Qty"); dt.Columns.Add("Price");
foreach (var t in dapperConn.Query<TradeRow>(
    "SELECT trade_id AS TradeId, ticker AS Ticker, side AS Side, quantity AS Quantity, price AS Price FROM dbo.dapper_trades ORDER BY trade_id"))
    dt.Rows.Add(t.TradeId, t.Ticker, t.Side, t.Quantity, $"{t.Price:F2}");
 
// Cleanup
dapperConn.Execute("DROP TABLE dbo.dapper_trades");
dt
Trade IDTickerSideQtyPrice
TRD_001ASML.ASBUY100700.00
TRD_002SAP.DESELL75245.80
TRD_003MC.PABUY50890.20

Dapper — aggregate queries with LINQ on results

Dapper returns IEnumerable<T>, so full LINQ works on the results. This is not possible with a raw ADO.NET DataReader.

var allPrices = dapperConn.Query<OhlcvRow>(@"
    SELECT symbol AS Symbol, date AS Date,
           [open] AS [Open], high AS High, low AS Low, [close] AS [Close], volume AS Volume
    FROM silver.eurostoxx50_ohlcv
    WHERE date >= '2025-01-01'").ToList();
 
var summary = allPrices
    .GroupBy(r => r.Symbol)
    .Select(g => new {
        Symbol = g.Key,
        Days = g.Count(),
        AvgClose = g.Average(r => r.Close),
        MaxVolume = g.Max(r => r.Volume),
    })
    .OrderByDescending(s => s.AvgClose)
    .Take(5);
 
var dt = new DataTable();
dt.Columns.Add("Symbol"); dt.Columns.Add("Days"); dt.Columns.Add("Avg Close"); dt.Columns.Add("Max Volume");
foreach (var s in summary)
    dt.Rows.Add(s.Symbol, s.Days, $"{s.AvgClose:F2}", $"{s.MaxVolume:N0}");
dt
SymbolDaysAvg CloseMax Volume
RMS.PA3052274.84200'686
RHM.DE3031539.541'665'353
ADYEN.AS3051441.14758'895
ASML.AS305797.702'619'138
ARGX.BR305618.031'507'897

Dapper — dynamic queries (no DTO needed)

var dynamic = dapperConn.Query(
    "SELECT TOP 3 symbol, date, [close], volume FROM silver.eurostoxx50_ohlcv ORDER BY volume DESC");
 
var dt = new DataTable();
dt.Columns.Add("Symbol"); dt.Columns.Add("Date"); dt.Columns.Add("Close"); dt.Columns.Add("Volume");
foreach (var row in dynamic)
    dt.Rows.Add(row.symbol, $"{row.date:yyyy-MM-dd}", $"{row.close:F2}", $"{row.volume:N0}");
dt

dynamic provides no IntelliSense and no compile-time safety. Use only for throwaway queries — prefer typed records for production.

SymbolDateCloseVolume
ISP.MI2023-08-082.34376'391'539
SAN.MC2021-10-203.36367'211'467
ISP.MI2023-05-312.16317'362'978

ADO.NET vs Dapper vs Entity Framework — comparison

Decision guide for choosing between the three approaches.

var dt = new DataTable();
dt.Columns.Add("Feature"); dt.Columns.Add("ADO.NET (raw)"); dt.Columns.Add("Dapper"); dt.Columns.Add("EF Core");
dt.Rows.Add("SQL control", "Full — you write SQL", "Full — you write SQL", "LINQ — auto-generated SQL");
dt.Rows.Add("Result type", "DataReader (untyped)", "IEnumerable<T> (typed)", "IQueryable<T> (tracked)");
dt.Rows.Add("Mapping", "Manual reader.GetXxx()", "Auto by column name", "Auto + navigation props");
dt.Rows.Add("Parameters", "cmd.Parameters.Add()", "new { Param = val }", "LINQ variables");
dt.Rows.Add("LINQ on results", "No", "Yes (in-memory)", "Yes (translated to SQL)");
dt.Rows.Add("Performance", "Fastest", "~Same as ADO.NET", "Slower (change tracking)");
dt.Rows.Add("Batch insert", "SqlBulkCopy", "Execute(sql, list)", "AddRange + SaveChanges");
dt.Rows.Add("Boilerplate", "Lots", "Minimal", "Minimal");
dt.Rows.Add("Best for", "Notebooks, scripts", "Services, APIs, pipelines", "Large apps, CRUD");
dt.Rows.Add("Python equiv", "pyodbc cursor", "pd.read_sql() → DataFrame", "SQLAlchemy ORM");
dt
FeatureADO.NET (raw)DapperEF Core
SQL controlFull — you write SQLFull — you write SQLLINQ — auto-generated SQL
Result typeDataReader (untyped)IEnumerable (typed)IQueryable (tracked)
MappingManual reader.GetXxx()Auto by column nameAuto + navigation props
Parameterscmd.Parameters.Add()new { Param = val }LINQ variables
LINQ on resultsNoYes (in-memory)Yes (translated to SQL)
PerformanceFastest~Same as ADO.NETSlower (change tracking)
Batch insertSqlBulkCopyExecute(sql, list)AddRange + SaveChanges
BoilerplateLotsMinimalMinimal
Best forNotebooks, scriptsServices, APIs, pipelinesLarge apps, CRUD
Python equivpyodbc cursorpd.read_sql() → DataFrameSQLAlchemy ORM

Entity Framework Core — Full ORM

EF Core is the dominant ORM in .NET — used by ~60-70% of .NET applications. Unlike Dapper (you write SQL, it maps results), EF Core generates SQL from LINQ and manages the full object lifecycle: change tracking, migrations, relationships.

Overview

How it works

  1. Define entity classes (C# classes = database tables)
  2. Define a DbContext (connection + table mappings + configuration)
  3. Write LINQ queries — EF Core translates to SQL automatically
  4. Change tracking — modify objects in memory, call SaveChanges(), EF generates INSERT/UPDATE/DELETE
  5. Migrations — dotnet ef migrations add generates SQL schema changes from code
DapperEF Core
You writeSQLLINQ
SQL generated byYouEF Core
Navigation propertiesNoorder.Customer.Address.City
Change trackingNoAutomatic
MigrationsManual SQL scriptsdotnet ef migrations add
PerformanceFastestSlower (tracking overhead)
Best forData pipelines, complex SQLCRUD apps, business logic

NOTEBOOK LIMITATION: EF Core requires dotnet ef CLI tools and a real project structure for migrations. In notebooks, we demonstrate the API patterns with an in-memory database. In production, use SQL Server/PostgreSQL with migrations.

Model and DbContext

EF Core — NuGet packages and entity classes

Entity classes = tables, properties = columns. DbContext maps entities via DbSet<T>. LINQ queries are compile-time checked. Change tracking generates SQL on SaveChanges(). In-memory provider for notebooks; SQL Server for production.

EF Core pitfalls

  • Lazy loading without understanding N+1 queries
  • Not using AsNoTracking() for read-only queries — adds tracking overhead
  • Loading entire tables — use IQueryable, not ToList()
  • For complex analytics or bulk operations — use Dapper or SqlBulkCopy

EF Core performance checklist

Add .AsNoTracking() on every read-only query. Use .Include() explicitly instead of lazy loading to control join depth. Filter with .Where() before .ToList() so EF pushes the predicate to SQL. Switch to Dapper for bulk inserts, aggregations, or CTEs where LINQ becomes unwieldy.

public class Stock
{
    public int Id { get; set; }
    public string Symbol { get; set; } = "";
    public string Name { get; set; } = "";
    public string Sector { get; set; } = "";
    public List<Price> Prices { get; set; } = new();  // navigation: one stock has many prices
}
 
public class Price
{
    public int Id { get; set; }
    public int StockId { get; set; }                   // foreign key
    public Stock Stock { get; set; } = null!;           // navigation: each price belongs to one stock
    public DateTime Date { get; set; }
    public double Close { get; set; }
    public long Volume { get; set; }
}
 
public class Trade
{
    public int Id { get; set; }
    public string TradeId { get; set; } = "";
    public int StockId { get; set; }
    public Stock Stock { get; set; } = null!;
    public string Side { get; set; } = "";
    public int Quantity { get; set; }
    public decimal Price { get; set; }
    public DateTime TradeDate { get; set; }
}

EF Core — DbContext definition

DbContext setup

  • DbContext maps DbSet<T> properties to tables
  • OnConfiguring — chooses the provider (UseInMemoryDatabase for tests, UseSqlServer for production)
  • OnModelCreating — configures relationships (HasOne/WithMany) and constraints (HasIndex)
  • Conventions: Id = primary key, DbSet<Stock> = “Stocks” table, navigation + FK auto-detected
public class TradingContext : DbContext
{
    // Each DbSet = one table. LINQ queries on these generate SQL.
    public DbSet<Stock> Stocks { get; set; }
    public DbSet<Price> Prices { get; set; }
    public DbSet<Trade> Trades { get; set; }
 
    // Database provider — swap this line for production
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseInMemoryDatabase("TradingDemo");
        // Production: options.UseSqlServer("Server=localhost,1434;Database=stoxx;...");
 
    protected override void OnModelCreating(ModelBuilder model)
    {
        // One-to-many: one Stock has many Prices
        // Price.StockId is the FK column, Stock.Prices is the navigation collection
        model.Entity<Price>()
            .HasOne(p => p.Stock)          // each Price belongs to one Stock
            .WithMany(s => s.Prices)       // each Stock has many Prices
            .HasForeignKey(p => p.StockId);// Price.StockId is the FK
 
        // One-to-many: each Trade references one Stock
        // No inverse navigation on Stock (WithMany() with no arg)
        model.Entity<Trade>()
            .HasOne(t => t.Stock)
            .WithMany()                    // Stock doesn't have a Trades collection
            .HasForeignKey(t => t.StockId);
 
        // Unique index on Symbol — prevents duplicate tickers
        model.Entity<Stock>()
            .HasIndex(s => s.Symbol)
            .IsUnique();
    }
}

CRUD and Queries

EF Core — seed data with Add, AddRange, SaveChanges

  • Add(entity) — marks a single object for insertion. EF Core starts tracking it in the Added state. No SQL is executed yet.
  • AddRange(entities) — same as Add but for multiple objects at once. More efficient than calling Add in a loop.
  • SaveChanges() — flushes ALL pending changes to the database in one transaction. Generates the actual INSERT/UPDATE/DELETE SQL. Returns the number of affected rows.

The pattern is always: modify objects in memory → call SaveChanges() once → EF generates SQL and executes in a transaction. If any statement fails, the entire transaction rolls back.

var db = new TradingContext();
db.Database.EnsureDeleted();   // clean slate for re-runs
db.Database.EnsureCreated();
 
// Add stocks
var asml = new Stock { Symbol = "ASML.AS", Name = "ASML Holding", Sector = "Technology" };
var sap  = new Stock { Symbol = "SAP.DE",  Name = "SAP SE",        Sector = "Technology" };
var mc   = new Stock { Symbol = "MC.PA",   Name = "LVMH",          Sector = "Consumer" };
var tte  = new Stock { Symbol = "TTE.PA",  Name = "TotalEnergies",  Sector = "Energy" };
db.Stocks.AddRange(asml, sap, mc, tte);
db.SaveChanges();
 
// Add prices
var rng = new Random(42);
foreach (var stock in new[] { asml, sap, mc, tte })
{
    var basePrice = stock.Symbol switch { "ASML.AS" => 700, "SAP.DE" => 240, "MC.PA" => 850, _ => 60 };
    for (int d = 0; d < 30; d++)
    {
        var close = basePrice + (rng.NextDouble() - 0.5) * 20;
        db.Prices.Add(new Price
        {
            StockId = stock.Id,
            Date = new DateTime(2025, 3, 1).AddDays(d),
            Close = Math.Round(close, 2),
            Volume = rng.Next(500_000, 5_000_000),
        });
    }
}
db.SaveChanges();
 
Console.WriteLine($"{db.Stocks.Count()} stocks, {db.Prices.Count()} prices");  // seeded
4 stocks, 120 prices

EF Core — LINQ queries (no SQL strings)

EF Core translates LINQ to SQL automatically. You never write SQL — the compiler checks your queries at build time. Wrong property name = compile error, not runtime crash.

var techStocks = db.Stocks
    .Where(s => s.Sector == "Technology")
    .OrderBy(s => s.Symbol)
    .ToList();
 
var dt = new DataTable();
dt.Columns.Add("Symbol"); dt.Columns.Add("Name"); dt.Columns.Add("Sector");
foreach (var s in techStocks)
    dt.Rows.Add(s.Symbol, s.Name, s.Sector);
dt
SymbolNameSector
ASML.ASASML HoldingTechnology
SAP.DESAP SETechnology

EF Core — navigation properties (joins without SQL)

stock.Prices navigates the one-to-many relationship automatically — no JOIN needed in the query.

var stocksWithPrices = db.Stocks
    .Include(s => s.Prices)    // eager load related prices (generates LEFT JOIN)
    .OrderBy(s => s.Symbol)
    .ToList();
 
var dt = new DataTable();
dt.Columns.Add("Symbol"); dt.Columns.Add("Name"); dt.Columns.Add("Price Count");
dt.Columns.Add("Latest Close"); dt.Columns.Add("Avg Close");
foreach (var s in stocksWithPrices)
{
    var latest = s.Prices.OrderByDescending(p => p.Date).FirstOrDefault();
    var avg = s.Prices.Any() ? s.Prices.Average(p => p.Close) : 0;
    dt.Rows.Add(s.Symbol, s.Name, s.Prices.Count, $"{latest?.Close:F2}", $"{avg:F2}");
}
dt
SymbolNamePrice CountLatest CloseAvg Close
ASML.ASASML Holding30704.13697.98
MC.PALVMH30858.81849.20
SAP.DESAP SE30243.02238.71
TTE.PATotalEnergies3055.8360.31

EF Core — aggregate queries with GroupBy

var sectorSummary = db.Stocks
    .Include(s => s.Prices)
    .ToList()   // materialize first for in-memory grouping
    .GroupBy(s => s.Sector)
    .Select(g => new
    {
        Sector = g.Key,
        Stocks = g.Count(),
        TotalPriceRows = g.Sum(s => s.Prices.Count),
        AvgClose = g.SelectMany(s => s.Prices).Average(p => p.Close),
    })
    .OrderByDescending(x => x.AvgClose);
 
var dt = new DataTable();
dt.Columns.Add("Sector"); dt.Columns.Add("Stocks"); dt.Columns.Add("Price Rows"); dt.Columns.Add("Avg Close");
foreach (var s in sectorSummary)
    dt.Rows.Add(s.Sector, s.Stocks, s.TotalPriceRows, $"{s.AvgClose:F2}");
dt
SectorStocksPrice RowsAvg Close
Consumer130849.20
Technology260468.34
Energy13060.31

Change Tracking and Performance

EF Core — change tracking and SaveChanges()

Modify objects in memory — EF Core tracks all changes and generates the correct INSERT/UPDATE/DELETE SQL when you call SaveChanges().

INSERT — Add + SaveChanges generates INSERT

db.Trades.Add(new Trade
{
    TradeId = "TRD_001", StockId = asml.Id,
    Side = "BUY", Quantity = 100, Price = 685.40m,
    TradeDate = DateTime.Today
});
db.SaveChanges();
TRD_001 added

UPDATE — modify a tracked entity + SaveChanges generates UPDATE

var trade = db.Trades.First(t => t.TradeId == "TRD_001");
trade.Price = 700.00m;
db.SaveChanges();
TRD_001 price -> 700.00

DELETE — Remove + SaveChanges generates DELETE

db.Trades.Remove(trade);
db.SaveChanges();
 
Console.WriteLine(db.Trades.Count());  // trades remaining
TRD_001 removed
0

EF Core — AsNoTracking() for read-only performance

Skips change tracking for read-only queries. 2–3x faster for large result sets because EF Core does not snapshot each entity.

var readOnly = db.Prices
    .AsNoTracking()
    .Where(p => p.Close > 700)
    .OrderByDescending(p => p.Close)
    .Take(5)
    .ToList();
 
var dt = new DataTable();
dt.Columns.Add("Stock ID"); dt.Columns.Add("Date"); dt.Columns.Add("Close"); dt.Columns.Add("Volume");
foreach (var p in readOnly)
    dt.Rows.Add(p.StockId, $"{p.Date:yyyy-MM-dd}", $"{p.Close:F2}", $"{p.Volume:N0}");
dt

Always use AsNoTracking() for queries that only read data. Only omit it when you need to modify the entities and call SaveChanges().

Stock IDDateCloseVolume
32025-03-06859.871'046'270
32025-03-18859.754'017'367
32025-03-27858.954'146'515
32025-03-30858.813'740'448
32025-03-12858.524'303'796

EF Core — raw SQL escape hatch with FromSqlRaw

FromSqlRaw — raw SQL inside EF Core. Use {0}, {1} placeholders (auto-parameterised). Can chain LINQ after. For complex analytics (CTEs, PIVOT, window functions) or stored procedures. Alternative: use Dapper alongside EF Core. The InMemory provider does not support FromSqlRaw, so the demo below uses LINQ instead.

Never use string interpolation $"...{var}..."

Never use string interpolation $"...{var}..." with FromSqlRaw — injection risk. Use FromSqlInterpolated instead (auto-parameterises {var} into @p0).

Safe raw SQL in EF Core

Use FromSqlInterpolated($"SELECT * FROM Stocks WHERE Sector = {sector}") — EF Core converts the interpolated expression into a parameterised query automatically. For Dapper, pass an anonymous object: conn.Query<T>(sql, new { sector }). Neither approach ever concatenates user input into SQL text.

var techStocks = db.Stocks
    .Where(s => s.Sector == "Technology")    // LINQ → generates WHERE clause
    .AsNoTracking()
    .ToList();
 
foreach (var s in techStocks)
    Console.WriteLine($"  {s.Symbol,-10} {s.Name,-20} {s.Sector}");
  ASML.AS    ASML Holding         Technology
  SAP.DE     SAP SE               Technology

Migrations and Reference

EF Core — migrations workflow (reference)

Migrations are the killer feature of EF Core — schema changes are version-controlled C# code, not ad-hoc SQL scripts. Not executable in notebooks (requires project + CLI), but this is the production workflow.

Migrations and Reference Setup

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install dotnet-ef

Migrations and Reference Workflow

  1. Modify entity classes (add property, change type, add table)
  2. dotnet ef migrations add AddVolumeColumn → generates C# migration with Up() and Down()
  3. dotnet ef database update → applies pending migrations
  4. dotnet ef migrations script → generates SQL script for DBA review

Example migration (auto-generated)

public partial class AddVolumeColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
        => migrationBuilder.AddColumn<long>("Volume", "Prices");
 
    protected override void Down(MigrationBuilder migrationBuilder)
        => migrationBuilder.DropColumn("Volume", "Prices");
}

Migration best practices

  • One migration per logical change (not one per deployment)
  • Always test Down() — rollbacks must work
  • Generate SQL scripts for production (dotnet ef migrations script)
  • Never edit a migration after it has been applied
  • Use HasData() for seed data that should be in every environment

Safe migration workflow

Run dotnet ef migrations add <Name> on a feature branch, review the generated Up() and Down() methods, then run dotnet ef migrations script --idempotent to produce a SQL file for DBA review before applying to production. Keep migrations small and reversible so any deployment can be rolled back with dotnet ef database update <PreviousMigration>.

EF Core — when to use EF Core vs Dapper

var dt = new DataTable();
dt.Columns.Add("Scenario"); dt.Columns.Add("Use"); dt.Columns.Add("Why");
dt.Rows.Add("CRUD app with 50 tables", "EF Core", "Navigation properties, migrations, change tracking");
dt.Rows.Add("Complex analytics query", "Dapper", "Window functions, CTEs, hand-tuned SQL");
dt.Rows.Add("Bulk insert 100K rows", "Dapper + SqlBulkCopy", "EF SaveChanges is row-by-row");
dt.Rows.Add("Microservice API", "Either", "Dapper for perf, EF Core for productivity");
dt.Rows.Add("Schema migrations", "EF Core", "dotnet ef migrations — version-controlled schema");
dt.Rows.Add("Notebook / script", "Dapper or ADO.NET", "No project structure needed");
dt.Rows.Add("Read-only dashboard", "Dapper", "AsNoTracking helps but Dapper is still faster");
dt.Rows.Add("Multi-table transaction", "EF Core", "SaveChanges wraps all changes in one transaction");
dt.Rows.Add("Cross-database query", "Dapper", "EF Core is one DbContext per database");
dt.Rows.Add("Both in same project", "Yes — common", "EF for CRUD, Dapper for reporting queries");
dt
ScenarioUseWhy
CRUD app with 50 tablesEF CoreNavigation properties, migrations, change tracking
Complex analytics queryDapperWindow functions, CTEs, hand-tuned SQL
Bulk insert 100K rowsDapper + SqlBulkCopyEF SaveChanges is row-by-row
Microservice APIEitherDapper for perf, EF Core for productivity
Schema migrationsEF Coredotnet ef migrations — version-controlled schema
Notebook / scriptDapper or ADO.NETNo project structure needed
Read-only dashboardDapperAsNoTracking helps but Dapper is still faster
Multi-table transactionEF CoreSaveChanges wraps all changes in one transaction
Cross-database queryDapperEF Core is one DbContext per database
Both in same projectYes — commonEF for CRUD, Dapper for reporting queries

DuckDB — Embedded Analytical SQL Database

DuckDB is an embedded columnar database — no server, runs in-process. Full SQL (window functions, CTEs, QUALIFY, PIVOT) and direct file queries.

This section demonstrates all the ways to interact with DuckDB from C#:

  • ADO.NET (DuckDBConnection, DuckDBCommand, DuckDBDataReader)
  • DuckDB Appender (fastest bulk loader)
  • Dapper (typed query mapping)
  • Performance comparison vs SQL Server

DuckDB with ADO.NET

DuckDB ADO.NET — open in-memory connection and CREATE TABLE with typed schema

DuckDBConnection/DuckDBCommand/DuckDBDataReader — same ADO.NET pattern as SQLite. Columnar engine is 10–100x faster for analytical queries. Full SQL:2003 (window functions, CTEs, QUALIFY, PIVOT). Single-writer — don’t use for OLTP.

var duck = new DuckDBConnection("Data Source=:memory:");
duck.Open();
 
var dkCmd = duck.CreateCommand();
dkCmd.CommandText = @"
    CREATE OR REPLACE TABLE ohlcv (
        symbol VARCHAR NOT NULL, date DATE NOT NULL,
        open DOUBLE, high DOUBLE, low DOUBLE, close DOUBLE,
        volume BIGINT
    )";
dkCmd.ExecuteNonQuery();
DuckDB connected + table created

DuckDB ADO.NET — INSERT rows from SQL Server using parameterised DuckDBCommand

var conn = new SqlConnection("Server=localhost,1434;Database=stoxx;User Id=sa;Password=EsgDev2026Pass1;Encrypt=True;TrustServerCertificate=True;");
conn.Open();
 
var sqlCmd = new SqlCommand(
    "SELECT symbol, date, [open], high, low, [close], volume FROM silver.eurostoxx50_ohlcv", conn);
 
int rowCount = 0;
using (var reader = sqlCmd.ExecuteReader())
{
    var ins = duck.CreateCommand();
    while (reader.Read())
    {
        ins.CommandText = "INSERT INTO ohlcv VALUES ($1, $2, $3, $4, $5, $6, $7)";
        ins.Parameters.Clear();
        ins.Parameters.Add(new DuckDBParameter { Value = reader.GetString(0) });
        ins.Parameters.Add(new DuckDBParameter { Value = reader.GetDateTime(1) });
        ins.Parameters.Add(new DuckDBParameter { Value = reader.GetDouble(2) });
        ins.Parameters.Add(new DuckDBParameter { Value = reader.GetDouble(3) });
        ins.Parameters.Add(new DuckDBParameter { Value = reader.GetDouble(4) });
        ins.Parameters.Add(new DuckDBParameter { Value = reader.GetDouble(5) });
        ins.Parameters.Add(new DuckDBParameter { Value = reader.GetInt64(6) });
        ins.ExecuteNonQuery();
        rowCount++;
    }
}
 
dkCmd.CommandText = "SELECT COUNT(*) FROM ohlcv";
Console.WriteLine(dkCmd.ExecuteScalar());  // rows loaded from SQL Server
Loaded 66355 rows from SQL Server

DuckDB ADO.NET — SELECT rows into DataTable with ExecuteReader

var dt = new DataTable();
dkCmd.CommandText = "SELECT symbol, date, close, volume FROM ohlcv WHERE symbol = 'SAP.DE' ORDER BY date DESC LIMIT 5";
dt.Load(dkCmd.ExecuteReader());
dt
symboldateclosevolume
SAP.DE12-Mar-26166.52806722
SAP.DE11-Mar-26165.442953782
SAP.DE10-Mar-26169.63187246
SAP.DE09-Mar-26171.881990823
SAP.DE06-Mar-26172.743347221

DuckDB ADO.NET — get single values with ExecuteScalar (COUNT, MAX)

dkCmd.CommandText = "SELECT COUNT(*) FROM ohlcv";
Console.WriteLine(dkCmd.ExecuteScalar());  // row count
 
dkCmd.CommandText = "SELECT COUNT(DISTINCT symbol) FROM ohlcv";
Console.WriteLine(dkCmd.ExecuteScalar());  // distinct stocks
 
dkCmd.CommandText = "SELECT MAX(close) FROM ohlcv";
Console.WriteLine(dkCmd.ExecuteScalar());  // max close
66355
50
2839

DuckDB ADO.NET — filter rows with 2 positional parameters

// $1, $2 positional parameters — safe from injection
 
dkCmd.CommandText = "SELECT symbol, date, close FROM ohlcv WHERE symbol = $1 AND close > $2 ORDER BY close DESC LIMIT 5";
dkCmd.Parameters.Clear();
dkCmd.Parameters.Add(new DuckDBParameter { Value = "ASML.AS" });
dkCmd.Parameters.Add(new DuckDBParameter { Value = 700.0 });
 
var dt = new DataTable();
dt.Load(dkCmd.ExecuteReader());
dt
symboldateclose
ASML.AS25-Feb-261288.4
ASML.AS24-Feb-261263.4
ASML.AS20-Feb-261255.6
ASML.AS23-Feb-261249.2
ASML.AS18-Feb-261244.8

DuckDB ADO.NET — UPDATE a row with ExecuteNonQuery

dkCmd.CommandText = "UPDATE ohlcv SET close = 999.99 WHERE symbol = 'SAP.DE' AND date = (SELECT MAX(date) FROM ohlcv WHERE symbol = 'SAP.DE')";
dkCmd.Parameters.Clear();
Console.WriteLine(dkCmd.ExecuteNonQuery());  // UPDATE
 
// Verify
var dt = new DataTable();
dkCmd.CommandText = "SELECT symbol, date, close FROM ohlcv WHERE symbol = 'SAP.DE' ORDER BY date DESC LIMIT 3";
dt.Load(dkCmd.ExecuteReader());
dt
1 row
symboldateclose
SAP.DE12-Mar-26999.99
SAP.DE11-Mar-26165.44
SAP.DE10-Mar-26169.6

DuckDB ADO.NET — DELETE rows with ExecuteNonQuery

dkCmd.CommandText = "SELECT COUNT(*) FROM ohlcv WHERE symbol = 'SAP.DE'";
Console.WriteLine(dkCmd.ExecuteScalar());  // before DELETE: SAP.DE rows
 
dkCmd.CommandText = "DELETE FROM ohlcv WHERE symbol = 'SAP.DE' AND date < '2023-01-01'";
Console.WriteLine(dkCmd.ExecuteNonQuery());  // DELETE
 
dkCmd.CommandText = "SELECT COUNT(*) FROM ohlcv WHERE symbol = 'SAP.DE'";
Console.WriteLine(dkCmd.ExecuteScalar());  // after DELETE: SAP.DE rows
1324 SAP.DE rows
512 rows
812 SAP.DE rows

DuckDB ADO.NET — inspect schema with DESCRIBE and information_schema

DESCRIBE uses the same syntax as PostgreSQL. Returns column names, types, nullability, key, and default values.

var dt = new DataTable();
dkCmd.CommandText = "DESCRIBE ohlcv";
dkCmd.Parameters.Clear();
dt.Load(dkCmd.ExecuteReader());
dt
column_namecolumn_typenullkeydefaultextra
symbolVARCHARNO
dateDATENO
openDOUBLEYES
highDOUBLEYES
lowDOUBLEYES
closeDOUBLEYES
volumeBIGINTYES

DuckDB ADO.NET — show query plan with EXPLAIN ANALYZE

EXPLAIN ANALYZE returns the query plan with actual execution timing.

dkCmd.CommandText = "EXPLAIN ANALYZE SELECT symbol, AVG(close) FROM ohlcv GROUP BY symbol";
using (var reader = dkCmd.ExecuteReader())
    while (reader.Read())
        Console.WriteLine(reader.GetString(1));
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT symbol, AVG(close) FROM ohlcv GROUP BY symbol
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.0022s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│__internal_decompress_strin│
│           g(#0)           │
│             #1            │
│                           │
│          50 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│    ────────────────────   │
│         Groups: #0        │
│    Aggregates: avg(#1)    │
│                           │
│          50 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           symbol          │
│           close           │
│                           │
│         65843 Rows        │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│__internal_compress_string_│
│        hugeint(#0)        │
│             #1            │
│                           │
│         65843 Rows        │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│        Table: ohlcv       │
│   Type: Sequential Scan   │
│                           │
│        Projections:       │
│           symbol          │
│           close           │
│                           │
│         65843 Rows        │
│          (0.00s)          │
└───────────────────────────┘

DuckDB Appender — fastest bulk loader

DuckDB Appender — INSERT rows without SQL using CreateRow and AppendValue

CreateAppender("table") bypasses SQL parsing — CreateRow().AppendValue().EndRow() per row, Close() flushes. 10–100x faster than parameterised INSERT. Fastest way to bulk load into DuckDB.

// Create a fresh table for the appender demo
dkCmd.CommandText = "CREATE OR REPLACE TABLE appender_demo (symbol VARCHAR, date DATE, close DOUBLE, volume BIGINT)";
dkCmd.Parameters.Clear();
dkCmd.ExecuteNonQuery();
 
using (var appender = duck.CreateAppender("appender_demo"))
{
    var row = appender.CreateRow();
    row.AppendValue("ASML.AS").AppendValue(new DateOnly(2025, 3, 15)).AppendValue(685.40).AppendValue(2500000L);
    row.EndRow();
 
    row = appender.CreateRow();
    row.AppendValue("SAP.DE").AppendValue(new DateOnly(2025, 3, 15)).AppendValue(245.80).AppendValue(1800000L);
    row.EndRow();
 
    row = appender.CreateRow();
    row.AppendValue("MC.PA").AppendValue(new DateOnly(2025, 3, 15)).AppendValue(890.20).AppendValue(900000L);
    row.EndRow();
}
 
var dt = new DataTable();
dkCmd.CommandText = "SELECT * FROM appender_demo";
dt.Load(dkCmd.ExecuteReader());
dt
symboldateclosevolume
ASML.AS15-Mar-25685.42500000
SAP.DE15-Mar-25245.81800000
MC.PA15-Mar-25890.2900000

DuckDB Appender — stream SqlDataReader into DuckDB with bulk CreateRow loop

Streams rows from SQL Server via SqlDataReader directly into DuckDB using the Appender — no intermediate buffer.

dkCmd.CommandText = "CREATE OR REPLACE TABLE ohlcv_fast AS SELECT * FROM ohlcv LIMIT 0";
dkCmd.ExecuteNonQuery();
 
var sw = System.Diagnostics.Stopwatch.StartNew();
var bulkCmd = new SqlCommand(
    "SELECT symbol, date, [open], high, low, [close], volume FROM silver.eurostoxx50_ohlcv", conn);
 
int bulkCount = 0;
using (var reader = bulkCmd.ExecuteReader())
using (var appender = duck.CreateAppender("ohlcv_fast"))
{
    while (reader.Read())
    {
        var row = appender.CreateRow();
        row.AppendValue(reader.GetString(0));
        row.AppendValue(DateOnly.FromDateTime(reader.GetDateTime(1)));
        row.AppendValue(reader.GetDouble(2));
        row.AppendValue(reader.GetDouble(3));
        row.AppendValue(reader.GetDouble(4));
        row.AppendValue(reader.GetDouble(5));
        row.AppendValue(reader.GetInt64(6));
        row.EndRow();
        bulkCount++;
    }
}
sw.Stop();
 
Console.WriteLine($"Appender: loaded {bulkCount} rows in {sw.ElapsedMilliseconds} ms");
loaded 66355 rows in 62 ms

DuckDB with Dapper

DuckDB Dapper — define record DTO for typed mapping

Record DTO for Dapper typed queries against DuckDB.

record DuckOhlcv(string Symbol, string Date, double Close, long Volume);

DuckDB Dapper — SELECT into typed records with Query

DuckDBConnection implements DbConnection, so Dapper recognises it the same way as SQL Server or SQLite.

var top5 = duck.Query<DuckOhlcv>(
    "SELECT symbol AS Symbol, CAST(date AS VARCHAR) AS Date, close AS Close, volume AS Volume FROM ohlcv ORDER BY volume DESC LIMIT 5");
 
var dt = new DataTable();
dt.Columns.Add("Symbol"); dt.Columns.Add("Date"); dt.Columns.Add("Close"); dt.Columns.Add("Volume");
foreach (var r in top5)
    dt.Rows.Add(r.Symbol, r.Date, $"{r.Close:F2}", $"{r.Volume:N0}");
dt
SymbolDateCloseVolume
ISP.MI2023-08-082.34376'391'539
SAN.MC2021-10-203.36367'211'467
ISP.MI2023-05-312.16317'362'978
ISP.MI2023-03-132.33311'886'033
SAN.MC2021-11-033.31306'973'344

DuckDB Dapper — filter with anonymous object parameters (@Symbol, @MinClose)

DuckDB uses $1 $2 positional parameters, not @named. Dapper sends @name which DuckDB interprets as a type cast operator. Workaround: use string interpolation (safe when values are not user input), or use ADO.NET with $1 positional params for user-facing queries.

var symbol = "ASML.AS";
var minClose = 700.0;
var filtered = duck.Query<DuckOhlcv>(
    $"SELECT symbol AS Symbol, CAST(date AS VARCHAR) AS Date, close AS Close, volume AS Volume "
    + $"FROM ohlcv WHERE symbol = '{symbol}' AND close > {minClose} ORDER BY close DESC LIMIT 5");
 
var dt = new DataTable();
dt.Columns.Add("Symbol"); dt.Columns.Add("Date"); dt.Columns.Add("Close"); dt.Columns.Add("Volume");
foreach (var r in filtered)
    dt.Rows.Add(r.Symbol, r.Date, $"{r.Close:F2}", $"{r.Volume:N0}");
dt
SymbolDateCloseVolume
ASML.AS2026-02-251288.40514'749
ASML.AS2026-02-241263.40690'480
ASML.AS2026-02-201255.60565'504
ASML.AS2026-02-231249.20479'494
ASML.AS2026-02-181244.80523'222

DuckDB Dapper — INSERT, UPDATE, DELETE with Execute and anonymous objects

DuckDB does not support @named parameters — use literal values in the SQL string.

// INSERT
var inserted = duck.Execute(
    "INSERT INTO ohlcv VALUES ('TEST.XX', '2025-01-01', 100.0, 105.0, 95.0, 102.0, 1000000)");
Console.WriteLine(inserted);  // Dapper INSERT
 
// UPDATE
var updated = duck.Execute(
    "UPDATE ohlcv SET close = 110.0 WHERE symbol = 'TEST.XX'");
Console.WriteLine(updated);  // Dapper UPDATE
 
// DELETE
var deleted = duck.Execute(
    "DELETE FROM ohlcv WHERE symbol = 'TEST.XX'");
Console.WriteLine(deleted);  // Dapper DELETE
1 row
1 row
1 row

DuckDB Dapper — SELECT single row with QueryFirst and single value with ExecuteScalar

Uses literal values in SQL because DuckDB does not support Dapper @named parameters.

var latest = duck.QueryFirst<DuckOhlcv>(
    "SELECT symbol AS Symbol, CAST(date AS VARCHAR) AS Date, close AS Close, volume AS Volume "
    + "FROM ohlcv WHERE symbol = 'ASML.AS' ORDER BY date DESC LIMIT 1");
Console.WriteLine($"{latest.Symbol} | {latest.Date} | {latest.Close:F2}");  // QueryFirst
 
// ExecuteScalar
var count = duck.ExecuteScalar<long>("SELECT COUNT(*) FROM ohlcv");
Console.WriteLine(count);  // ExecuteScalar (rows)
ASML.AS | 2026-03-12 | 1190.80
65843 rows

DuckDB-Specific SQL Features

DuckDB SQL — export query results to Parquet and CSV with COPY TO

COPY TO exports any query result directly to a file — Parquet or CSV.

dkCmd.CommandText = @"
    COPY (SELECT symbol, COUNT(*) AS days, ROUND(AVG(close), 2) AS avg_close
          FROM ohlcv GROUP BY symbol ORDER BY avg_close DESC)
    TO 'C:/Users/aperi/DEV/LANG/data/duckdb_export.parquet' (FORMAT PARQUET)";
dkCmd.Parameters.Clear();
dkCmd.ExecuteNonQuery();
 
dkCmd.CommandText = @"
    COPY (SELECT symbol, COUNT(*) AS days, ROUND(AVG(close), 2) AS avg_close
          FROM ohlcv GROUP BY symbol ORDER BY avg_close DESC)
    TO 'C:/Users/aperi/DEV/LANG/data/duckdb_export.csv' (FORMAT CSV, HEADER)";
dkCmd.ExecuteNonQuery();
Exported to duckdb_export.parquet
Exported to duckdb_export.csv

DuckDB SQL — load data from Parquet file with INSERT INTO … SELECT FROM

CREATE TABLE AS SELECT auto-detects the schema from the Parquet file — no column definitions needed.

dkCmd.CommandText = "CREATE OR REPLACE TABLE from_parquet AS SELECT * FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet'";
dkCmd.Parameters.Clear();
dkCmd.ExecuteNonQuery();
 
dkCmd.CommandText = "SELECT COUNT(*) FROM from_parquet";
Console.WriteLine(dkCmd.ExecuteScalar());  // loaded from Parquet
 
var dt = new DataTable();
dkCmd.CommandText = "DESCRIBE from_parquet";
dt.Load(dkCmd.ExecuteReader());
dt
66355 rows
column_namecolumn_typenullkeydefaultextra
idBIGINTYES
symbolVARCHARYES
dateDATEYES
openDOUBLEYES
highDOUBLEYES
lowDOUBLEYES
closeDOUBLEYES
adj_closeDOUBLEYES
volumeBIGINTYES
dividendsDOUBLEYES
stock_splitsDOUBLEYES
is_filledBOOLEANYES

DuckDB SQL — create table directly from CSV file with CREATE TABLE AS SELECT

One-liner — DuckDB auto-detects column types from the CSV content.

dkCmd.CommandText = "CREATE OR REPLACE TABLE ohlcv_from_csv AS SELECT * FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.csv'";
dkCmd.ExecuteNonQuery();
 
dkCmd.CommandText = "SELECT COUNT(*) FROM ohlcv_from_csv";
Console.WriteLine(dkCmd.ExecuteScalar());  // created from CSV
 
dkCmd.CommandText = "DESCRIBE ohlcv_from_csv";
var dt = new DataTable();
dt.Load(dkCmd.ExecuteReader());
dt
66355 rows
column_namecolumn_typenullkeydefaultextra
idBIGINTYES
symbolVARCHARYES
dateDATEYES
openDOUBLEYES
highDOUBLEYES
lowDOUBLEYES
closeDOUBLEYES
adj_closeDOUBLEYES
volumeBIGINTYES
dividendsDOUBLEYES
stock_splitsDOUBLEYES
is_filledBOOLEANYES

DuckDB SQL — profile all columns with SUMMARIZE (min, max, avg, nulls)

SUMMARIZE returns min, max, avg, nulls, and distinct count per column in one call — equivalent to df.describe() in pandas.

var dt = new DataTable();
dkCmd.CommandText = "SUMMARIZE ohlcv";
dt.Load(dkCmd.ExecuteReader());
dt
column_namecolumn_typeminmaxapprox_uniqueavgstdq25q50q75countnull_percentage
symbolVARCHARABI.BRWKL.AS51658430
dateDATE2021-01-042026-03-1215162023-08-09 13:12:43.7622832022-04-252023-08-102024-11-21658430
openDOUBLE1.6012926.025981197.74364532144514364.469950069394629.51621306889492870.31475017274542188.63608762992362658430
highDOUBLE1.66282957.030967200.07710156888447369.210330937075729.89963961359053771.04163364730812190.78985690530178658430
lowDOUBLE1.58422813.034449195.27775719058997359.312705187878229.29164067730794669.50168287298162186.0791768916689658430
closeDOUBLE1.60662839.031506197.74997440122425364.384683049766829.4734714978737469.66141927075263188.31922598476882658430
volumeBIGINT0376391539756685971114.7962881416215225.8708286150563114039814116754658430

DuckDB SQL — reference of DuckDB-specific features (QUALIFY, PIVOT, EXCLUDE, SAMPLE)

DuckDB-specific SQL features not available in SQL Server:

FeatureSyntax
QUALIFYSELECT *, ROW_NUMBER() OVER (...) AS rn FROM t QUALIFY rn <= 3
PIVOT / UNPIVOTPIVOT t ON category USING SUM(amount)
EXCLUDESELECT * EXCLUDE (volume) FROM ohlcv
REPLACESELECT * REPLACE (ROUND(close, 2) AS close) FROM ohlcv
SAMPLESELECT * FROM ohlcv USING SAMPLE 10%
LIST aggregationSELECT symbol, LIST(close ORDER BY date) FROM ohlcv GROUP BY symbol
Direct file querySELECT * FROM 'file.parquet'

Also: CREATE OR REPLACE (idempotent DDL), DESCRIBE/SUMMARIZE (schema + profiling), COPY FROM/TO (bulk import/export).

DuckDB Indexes and Tuning

DuckDB — CREATE INDEX (ART index for point lookups)

Indexes are optional — the columnar engine is already fast for scans. DuckDB uses ART (Adaptive Radix Tree, not B-tree) — speeds up equality filters but NOT range scans. Zone maps (min/max per row group) provide free predicate pushdown automatically.

dkCmd.CommandText = "CREATE INDEX idx_ohlcv_symbol ON ohlcv(symbol)";
dkCmd.Parameters.Clear();
dkCmd.ExecuteNonQuery();
 
// Unique index
dkCmd.CommandText = "CREATE UNIQUE INDEX idx_ohlcv_sym_date ON ohlcv(symbol, date)";
dkCmd.ExecuteNonQuery();
idx_ohlcv_symbol (ART index)
idx_ohlcv_sym_date (UNIQUE, composite)

DuckDB — list all indexes

duckdb_indexes() returns all indexes in the database.

var dt = new DataTable();
dkCmd.CommandText = @"
    SELECT index_name, table_name, is_unique
    FROM duckdb_indexes()
    ORDER BY table_name, index_name";
dt.Load(dkCmd.ExecuteReader());
dt
index_nametable_nameis_unique
idx_ohlcv_sym_dateohlcvTrue
idx_ohlcv_symbolohlcvFalse

DuckDB — DROP INDEX

Drops the single-column index and verifies only the composite index remains.

dkCmd.CommandText = "DROP INDEX IF EXISTS idx_ohlcv_symbol";
dkCmd.ExecuteNonQuery();
 
dkCmd.CommandText = "SELECT index_name FROM duckdb_indexes()";
using (var r = dkCmd.ExecuteReader())
    while (r.Read())
        Console.WriteLine(r.GetString(0));  // remaining index
idx_ohlcv_symbol
  idx_ohlcv_sym_date

DuckDB — PRAGMA database_size and memory usage

Reports database size, block usage, WAL size, and current memory consumption.

var dt = new DataTable();
dkCmd.CommandText = "CALL pragma_database_size()";
dt.Load(dkCmd.ExecuteReader());
dt
database_namedatabase_sizeblock_sizetotal_blocksused_blocksfree_blockswal_sizememory_usagememory_limit
memory0 bytes00000 bytes31.2 MiB50.0 GiB

DuckDB — pragma_storage_info — compression and row groups per column

Shows row groups, compression type, and size per column. The stats column is extracted separately below.

var storageInfo = new DataTable();
dkCmd.CommandText = "CALL pragma_storage_info('ohlcv')";
storageInfo.Load(dkCmd.ExecuteReader());
 
var clean = storageInfo.Copy();
var dropCols = new List<string>();
foreach (DataColumn col in clean.Columns)
{
    if (col.ColumnName == "stats") { dropCols.Add(col.ColumnName); continue; }
    if (clean.AsEnumerable().All(r => r[col] == DBNull.Value || r[col]?.ToString() == ""))
        dropCols.Add(col.ColumnName);
}
foreach (var col in dropCols) clean.Columns.Remove(col);
clean.AsEnumerable().Take(10).CopyToDataTable()
row_group_idcolumn_namecolumn_idcolumn_pathsegment_idsegment_typestartcountcompressionhas_updatespersistent
0symbol0[0]0VARCHAR03276UncompressedFalseFalse
0symbol0[0]1VARCHAR327626538UncompressedFalseFalse
0symbol0[0]2VARCHAR2981425764UncompressedFalseFalse
0symbol0[0]3VARCHAR5557810778UncompressedFalseFalse
0symbol0[0, 0]0VALIDITY016384UncompressedFalseFalse
0symbol0[0, 0]1VALIDITY1638449972UncompressedFalseFalse
0date1[1]0DATE02048UncompressedFalseFalse
0date1[1]1DATE204864308UncompressedFalseFalse
0date1[1, 0]0VALIDITY016384UncompressedFalseFalse
0date1[1, 0]1VALIDITY1638449972UncompressedFalseFalse

DuckDB — zone map stats per row group and segment

Row group: DuckDB splits tables into chunks of ~122K rows called row groups. Each row group is independent — columns are stored and compressed separately within it. If your table has < 122K rows, everything is in row_group_id = 0.

Segment: Within a row group, each column is further split into segments — compression blocks that DuckDB manages independently. Each segment has its own zone map (min/max stats) and compression type. DuckDB uses these zone maps for predicate pushdown: WHERE close > 500 skips segments where max(close) < 500 without reading any data.

Hierarchy: Table → Row Group (122K rows) → Column → Segment (compression block + zone map)

Storage stats show zone map min/max per column per segment. When querying WHERE symbol = 'SAP.DE', DuckDB checks each segment’s zone map and skips segments where the value can’t exist — no data read needed.

var statsOnly = new DataTable();
statsOnly.Columns.Add("row_group_id");
statsOnly.Columns.Add("column_name");
statsOnly.Columns.Add("segment_id");
statsOnly.Columns.Add("segment_type");
statsOnly.Columns.Add("stats");
foreach (DataRow row in storageInfo.Rows)
    statsOnly.Rows.Add(row["row_group_id"], row["column_name"], row["segment_id"], row["segment_type"], row["stats"]);
statsOnly.AsEnumerable().OrderBy(r => r["segment_id"].ToString()).Take(20).CopyToDataTable()
row_group_idcolumn_namesegment_idsegment_typestats
0symbol0VARCHAR[Min: ASML.AS, Max: RMS.PA, Has Unicode: false, Max String Length: 7][Has Null: false, Has No Null: true]
0symbol0VALIDITY[Has Null: false, Has No Null: true]
0date0DATE[Min: 2021-01-04, Max: 2026-03-04][Has Null: false, Has No Null: true]
0date0VALIDITY[Has Null: false, Has No Null: true]
0open0DOUBLE[Min: 394.7, Max: 1300.0][Has Null: false, Has No Null: true]
0open0VALIDITY[Has Null: false, Has No Null: true]
0high0DOUBLE[Min: 407.2, Max: 1312.8][Has Null: false, Has No Null: true]
0high0VALIDITY[Has Null: false, Has No Null: true]
0low0DOUBLE[Min: 375.75, Max: 1264.2][Has Null: false, Has No Null: true]
0low0VALIDITY[Has Null: false, Has No Null: true]
0close0DOUBLE[Min: 397.45, Max: 1288.4][Has Null: false, Has No Null: true]
0close0VALIDITY[Has Null: false, Has No Null: true]
0volume0BIGINT[Min: 48392, Max: 2713321][Has Null: false, Has No Null: true]
0volume0VALIDITY[Has Null: false, Has No Null: true]
0symbol1VARCHAR[Min: ABI.BR, Max: UCG.MI, Has Unicode: false, Max String Length: 7][Has Null: false, Has No Null: true]
0symbol1VALIDITY[Has Null: false, Has No Null: true]
0date1DATE[Min: 2021-01-04, Max: 2026-03-12][Has Null: false, Has No Null: true]
0date1VALIDITY[Has Null: false, Has No Null: true]
0open1DOUBLE[Min: 1.601, Max: 2926.0][Has Null: false, Has No Null: true]
0open1VALIDITY[Has Null: false, Has No Null: true]

DuckDB — memory_limit and threads configuration

Reads the key performance-related settings from duckdb_settings().

var dt = new DataTable();
dkCmd.CommandText = @"
    SELECT name, value, description
    FROM duckdb_settings()
    WHERE name IN ('memory_limit', 'threads', 'default_order',
                   'enable_object_cache', 'max_memory',
                   'worker_threads', 'enable_progress_bar')
    ORDER BY name";
dt.Load(dkCmd.ExecuteReader());
dt
namevaluedescription
default_orderascThe order type used when none is specified (ASC or DESC)
enable_object_cacheNULL[PLACEHOLDER] Legacy setting - does nothing
enable_progress_barfalseEnables the progress bar, printing progress to the terminal for long queries
max_memory50.0 GiBThe maximum memory of the system (e.g. 1GB)
memory_limit50.0 GiBThe maximum memory of the system (e.g. 1GB)
threads16The number of total threads used by the system.
worker_threads16The number of total threads used by the system.

DuckDB — SET memory_limit and threads

SettingDefaultRule of thumb
memory_limit80% RAM50% if alongside SQL Server, 80% if solo
threadsCPU coresN-2 if shared machine, all cores if solo
enable_object_cachefalseEnable for notebooks (re-running queries), disable for ETL

How to know if tuning

How to know if tuning is needed

  1. Query slow → EXPLAIN ANALYZE → check for disk spills
  2. “Out of Memory” → increase memory_limit
  3. CPU at 100% → reduce threads if other services need CPU
  4. Second run 2x faster → object cache is helping
dkCmd.CommandText = "SET memory_limit = '4GB'";
dkCmd.Parameters.Clear();
dkCmd.ExecuteNonQuery();
 
dkCmd.CommandText = "SET threads = 4";
dkCmd.ExecuteNonQuery();
 
dkCmd.CommandText = "SET enable_object_cache = true";  // recommended for interactive notebook use
dkCmd.ExecuteNonQuery();
 
dkCmd.CommandText = "SELECT name, value FROM duckdb_settings() WHERE name IN ('memory_limit', 'threads', 'enable_object_cache') ORDER BY name";
var dt = new DataTable();
dt.Load(dkCmd.ExecuteReader());
dt
Set memory_limit = 4GB
Set threads = 4
Set enable_object_cache = true
namevalue
enable_object_cacheNULL
memory_limit3.7 GiB
threads4

DuckDB — VACUUM ANALYZE (reclaim space and update stats)

VACUUM reclaims space from deleted rows. VACUUM ANALYZE also updates statistics for the query optimizer. CHECKPOINT forces a WAL flush to main storage — only needed for file-backed databases, not in-memory.

dkCmd.CommandText = "VACUUM";
dkCmd.ExecuteNonQuery();
 
dkCmd.CommandText = "VACUUM ANALYZE";
dkCmd.ExecuteNonQuery();
 
// CHECKPOINT — force write of WAL to main storage (file-based DBs only)
// dkCmd.CommandText = "CHECKPOINT";
// dkCmd.ExecuteNonQuery();
space reclaimed
stats updated + space reclaimed

DuckDB — tuning reference

Quick reference of all DuckDB tuning and maintenance commands.

DuckDB vs SQL Server Performance

DuckDB vs SQL Server — benchmark GROUP BY, LAG window, full scan side by side

Times the same three queries on both engines and presents the results side by side.

var sw = new System.Diagnostics.Stopwatch();
var perf = new DataTable();
perf.Columns.Add("Engine"); perf.Columns.Add("Query"); perf.Columns.Add("Time (ms)");
 
sw.Restart();
dkCmd.CommandText = "SELECT symbol, COUNT(*), ROUND(AVG(close), 2) FROM ohlcv GROUP BY symbol";
dkCmd.Parameters.Clear();
using (var r = dkCmd.ExecuteReader()) while (r.Read()) { }
sw.Stop();
perf.Rows.Add("DuckDB", "GROUP BY + AVG", sw.ElapsedMilliseconds);
 
sw.Restart();
var sqlBench = new SqlCommand("SELECT symbol, COUNT(*), ROUND(AVG(CAST([close] AS FLOAT)), 2) FROM silver.eurostoxx50_ohlcv GROUP BY symbol", conn);
using (var r = sqlBench.ExecuteReader()) while (r.Read()) { }
sw.Stop();
perf.Rows.Add("SQL Server", "GROUP BY + AVG", sw.ElapsedMilliseconds);
 
sw.Restart();
dkCmd.CommandText = "SELECT symbol, date, close, LAG(close) OVER (PARTITION BY symbol ORDER BY date) FROM ohlcv";
using (var r = dkCmd.ExecuteReader()) while (r.Read()) { }
sw.Stop();
perf.Rows.Add("DuckDB", "LAG() window", sw.ElapsedMilliseconds);
 
sw.Restart();
sqlBench = new SqlCommand("SELECT symbol, date, [close], LAG([close]) OVER (PARTITION BY symbol ORDER BY date) FROM silver.eurostoxx50_ohlcv", conn);
using (var r = sqlBench.ExecuteReader()) while (r.Read()) { }
sw.Stop();
perf.Rows.Add("SQL Server", "LAG() window", sw.ElapsedMilliseconds);
 
sw.Restart();
dkCmd.CommandText = "SELECT * FROM ohlcv";
using (var r = dkCmd.ExecuteReader()) while (r.Read()) { }
sw.Stop();
perf.Rows.Add("DuckDB", "Full table scan", sw.ElapsedMilliseconds);
 
sw.Restart();
sqlBench = new SqlCommand("SELECT * FROM silver.eurostoxx50_ohlcv", conn);
using (var r = sqlBench.ExecuteReader()) while (r.Read()) { }
sw.Stop();
perf.Rows.Add("SQL Server", "Full table scan", sw.ElapsedMilliseconds);
 
perf
EngineQueryTime (ms)
DuckDBGROUP BY + AVG4
SQL ServerGROUP BY + AVG16
DuckDBLAG() window8
SQL ServerLAG() window106
DuckDBFull table scan2
SQL ServerFull table scan37

DuckDB vs SQL Server — Plotly grouped bar chart of benchmark results

Renders the benchmark results as a grouped bar chart comparing DuckDB and SQL Server side by side.

var duckRows = perf.AsEnumerable().Where(r => r["Engine"].ToString() == "DuckDB").ToList();
var sqlRows = perf.AsEnumerable().Where(r => r["Engine"].ToString() == "SQL Server").ToList();
 
var duckBar = Plotly.NET.CSharp.Chart.Column<double, string, string>(
    values: duckRows.Select(r => Convert.ToDouble(r["Time (ms)"])).ToArray(),
    Keys: duckRows.Select(r => r["Query"].ToString()).ToArray(),
    Name: "DuckDB", MarkerColor: Color.fromHex("#4285F4"));
 
var sqlBar = Plotly.NET.CSharp.Chart.Column<double, string, string>(
    values: sqlRows.Select(r => Convert.ToDouble(r["Time (ms)"])).ToArray(),
    Keys: sqlRows.Select(r => r["Query"].ToString()).ToArray(),
    Name: "SQL Server", MarkerColor: Color.fromHex("#EA4335"));
 
Plotly.NET.CSharp.Chart.Combine(new[] { duckBar, sqlBar })
    .WithTitle("DuckDB vs SQL Server — Query Performance (ms)")
    .WithYAxisStyle(Title.init("Time (ms)"))
    .WithSize(800, 450)
    .WithLayout(Layout.init<string>(
        PaperBGColor: Color.fromString("transparent"),
        PlotBGColor: Color.fromString("transparent"),
        Font: Font.init(Color: Color.fromHex("#cccccc"))))

DuckDB — when to use ADO.NET vs Appender vs Dapper for each operation

Decision guide for choosing between the three DuckDB interfaces.

var cmp = new DataTable();
cmp.Columns.Add("Operation"); cmp.Columns.Add("ADO.NET"); cmp.Columns.Add("Appender"); cmp.Columns.Add("Dapper");
cmp.Rows.Add("SELECT rows", "ExecuteReader + DataTable", "—", "Query<T> (typed)");
cmp.Rows.Add("Single value", "ExecuteScalar", "—", "ExecuteScalar<T>");
cmp.Rows.Add("Single row", "ExecuteReader + Read()", "—", "QueryFirst<T>");
cmp.Rows.Add("INSERT 1 row", "ExecuteNonQuery", "CreateRow (overkill)", "Execute + anon obj");
cmp.Rows.Add("Bulk INSERT", "Loop + ExecuteNonQuery", "CreateRow loop (fastest)", "Execute + list");
cmp.Rows.Add("UPDATE", "ExecuteNonQuery", "—", "Execute + anon obj");
cmp.Rows.Add("DELETE", "ExecuteNonQuery", "—", "Execute + anon obj");
cmp.Rows.Add("Parameters", "$1, $2 positional", "— (no SQL)", "@Named (anon obj)");
cmp.Rows.Add("Best for", "DDL, schema ops", "Bulk loading", "Typed queries, CRUD");
cmp
OperationADO.NETAppenderDapper
SELECT rowsExecuteReader + DataTable—Query (typed)
Single valueExecuteScalar—ExecuteScalar
Single rowExecuteReader + Read()—QueryFirst
INSERT 1 rowExecuteNonQueryCreateRow (overkill)Execute + anon obj
Bulk INSERTLoop + ExecuteNonQueryCreateRow loop (fastest)Execute + list
UPDATEExecuteNonQuery—Execute + anon obj
DELETEExecuteNonQuery—Execute + anon obj
Parameters$1, $2 positional— (no SQL)@Named (anon obj)
Best forDDL, schema opsBulk loadingTyped queries, CRUD

Querying Files — DuckDB SQL vs Polars.NET DataFrame

Both DuckDB and Polars can query Parquet, CSV, and JSON files directly. This section pairs each operation side by side: DuckDB (SQL) then Polars (DataFrame API).

Read Parquet

DuckDB — read Parquet file with SELECT

DuckDB reads Parquet directly — no import step, with automatic predicate pushdown.

var dt = new DataTable();
dkCmd.CommandText = "SELECT symbol, date, close, volume FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet' LIMIT 5";
dkCmd.Parameters.Clear();
dt.Load(dkCmd.ExecuteReader());
dt
symboldateclosevolume
ABI.BR04-Jan-2157.211513937
ABI.BR05-Jan-2157.181382722
ABI.BR06-Jan-2158.771370204
ABI.BR07-Jan-2158.41469911
ABI.BR08-Jan-2157.861428681

Polars — read Parquet file with ReadParquet and Select

Polars equivalent: read Parquet, select the same 4 columns, show 5 rows.

var df = DataFrame.ReadParquet("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet");
df.Select("symbol", "date", "close", "volume").Head(5)
(66355, 12)
Polars DataFrame: (5 rows, 12 columns)
idint64symbolutf8viewdatedate32opendoublehighdoublelowdoubleclosedoubleadj_closedoublevolumeint64dividendsdoublestock_splitsdoubleis_filledbool
21160ABI.BR2021-01-0458.1558.8556.7857.2153.5761151393700false
21161ABI.BR2021-01-0556.957.9856.7557.1853.548138272200false
21162ABI.BR2021-01-0657.9658.9457.3958.7755.037137020400false
21163ABI.BR2021-01-0758.6858.8657.8858.454.6905146991100false
21164ABI.BR2021-01-0858.1658.457.4357.8654.1848142868100false

Read CSV

DuckDB — read CSV file with SELECT

DuckDB reads CSV directly — auto-detects schema and headers.

var dt = new DataTable();
dkCmd.CommandText = "SELECT symbol, date, close, volume FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.csv' LIMIT 5";
dt.Load(dkCmd.ExecuteReader());
dt
symboldateclosevolume
ABI.BR04-Jan-2157.211513937
ABI.BR05-Jan-2157.181382722
ABI.BR06-Jan-2158.771370204
ABI.BR07-Jan-2158.41469911
ABI.BR08-Jan-2157.861428681

Polars — read CSV file with ReadCsv and Select

Polars equivalent: read CSV, select the same 4 columns, show 5 rows.

var csvDf = DataFrame.ReadCsv("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.csv");
csvDf.Select("symbol", "date", "close", "volume").Head(5)
Polars DataFrame: (5 rows, 4 columns)
symbolutf8viewdatedate32closedoublevolumeint64
ABI.BR2021-01-0457.211513937
ABI.BR2021-01-0557.181382722
ABI.BR2021-01-0658.771370204
ABI.BR2021-01-0758.41469911
ABI.BR2021-01-0857.861428681

Read JSON

DuckDB — read JSON file with SELECT

DuckDB reads JSON and JSONL directly — auto-detects structure.

var dt = new DataTable();
dkCmd.CommandText = "SELECT symbol, date, close, volume FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.json' LIMIT 5";
dt.Load(dkCmd.ExecuteReader());
dt
symboldateclosevolume
ABI.BR2021-01-04T00:00:00.00057.211513937
ABI.BR2021-01-05T00:00:00.00057.181382722
ABI.BR2021-01-06T00:00:00.00058.771370204
ABI.BR2021-01-07T00:00:00.00058.41469911
ABI.BR2021-01-08T00:00:00.00057.861428681

Polars — read JSON file with ReadJson and Select

Polars equivalent: read JSON, select the same 4 columns, show 5 rows.

var jsonDf = DataFrame.ReadJson("C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.json");
jsonDf.Select("symbol", "date", "close", "volume").Head(5)
Polars DataFrame: (5 rows, 4 columns)
symbolutf8viewdateutf8viewclosedoublevolumeint64
ABI.BR2021-01-04T00:00:00.00057.211513937
ABI.BR2021-01-05T00:00:00.00057.181382722
ABI.BR2021-01-06T00:00:00.00058.771370204
ABI.BR2021-01-07T00:00:00.00058.41469911
ABI.BR2021-01-08T00:00:00.00057.861428681

Filter rows

DuckDB — filter rows with WHERE and ORDER BY

Filter on symbol = 'SAP.DE', ordered by date descending, top 5 rows.

var dt = new DataTable();
dkCmd.CommandText = "SELECT symbol, date, close, volume FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet' WHERE symbol = 'SAP.DE' ORDER BY date DESC LIMIT 5";
dt.Load(dkCmd.ExecuteReader());
dt
symboldateclosevolume
SAP.DE12-Mar-26166.52806722
SAP.DE11-Mar-26165.442953782
SAP.DE10-Mar-26169.63187246
SAP.DE09-Mar-26171.881990823
SAP.DE06-Mar-26172.743347221

Polars — filter with Filter() and Select()

Polars equivalent: filter symbol = SAP.DE, select the same columns, sort by date descending, limit 5.

df.Filter(Col("symbol") == Lit("SAP.DE"))
    .Select("symbol", "date", "close", "volume")
    .Sort("date", descending: true)
    .Head(5)
Polars DataFrame: (5 rows, 4 columns)
symbolutf8viewdatedate32closedoublevolumeint64
SAP.DE2026-03-12166.52806722
SAP.DE2026-03-11165.442953782
SAP.DE2026-03-10169.63187246
SAP.DE2026-03-09171.881990823
SAP.DE2026-03-06172.743347221

Group and aggregate

DuckDB — aggregate with GROUP BY, COUNT, AVG, SUM

Group by symbol, compute row count, average close, and total volume, ordered by total volume descending.

var dt = new DataTable();
dkCmd.CommandText = @"
    SELECT symbol, COUNT(*) AS days, ROUND(AVG(close), 2) AS avg_close,
           SUM(volume) AS total_volume
    FROM 'C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet'
    GROUP BY symbol ORDER BY total_volume DESC LIMIT 10";
dt.Load(dkCmd.ExecuteReader());
dt
symboldaysavg_closetotal_volume
ISP.MI13213.15115704541969
SAN.MC13294.4355513641918
ENEL.MI13216.8232600561934
BBVA.MC13298.6522133773194
UCG.MI132128.4618366801099
ENI.MI132113.417141570967
INGA.AS133114.0417041577555
IBE.MC132912.2615994295949
DTE.DE132422.4310029411390
NDA-FI.HE130610.857020342991

Polars — aggregate with GroupBy and Agg

Polars equivalent: group by symbol, count trading days, average close, sum volume, top 10 by volume.

df.GroupBy("symbol")
    .Agg(
        Col("close").Count().Alias("days"),
        Col("close").Mean().Alias("avg_close"),
        Col("volume").Sum().Alias("total_volume")
    )
    .Sort("total_volume", descending: true)
    .Head(10)
Polars DataFrame: (10 rows, 4 columns)
symbolutf8viewdaysuint32avg_closedoubletotal_volumeint64
ISP.MI13213.147987207115704541969
SAN.MC13294.4258476355513641918
ENEL.MI13216.82043830432600561934
BBVA.MC13298.65195410122133773194
UCG.MI132128.4571044718366801099
ENI.MI132113.3976245317141570967
INGA.AS133114.0381878317041577555
IBE.MC132912.2553115115994295949
DTE.DE132422.4300974310029411390
NDA-FI.HE130610.848078877020342991

Performance — DuckDB query across CSV vs Parquet vs JSON

DuckDB — benchmark same aggregate on CSV vs Parquet vs JSON

Runs the same GROUP BY aggregate across CSV, Parquet, and JSON to compare query speed across file formats.

var sw = new System.Diagnostics.Stopwatch();
var results = new DataTable();
results.Columns.Add("Format"); results.Columns.Add("Rows"); results.Columns.Add("Time (ms)");
 
foreach (var (format, path) in new[] {
    ("CSV",     "C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.csv"),
    ("Parquet", "C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.parquet"),
    ("JSON",    "C:/Users/aperi/DEV/LANG/data/eurostoxx50_ohlcv.json"),
})
{
    sw.Restart();
    dkCmd.CommandText = $"SELECT symbol, COUNT(*), ROUND(AVG(close), 2) FROM '{path}' GROUP BY symbol";
    int rows = 0;
    using (var reader = dkCmd.ExecuteReader())
        while (reader.Read()) rows++;
    sw.Stop();
    results.Rows.Add(format, rows, sw.ElapsedMilliseconds);
}
results

Parquet is fastest — columnar format with predicate pushdown. CSV requires full text parse; JSON requires parse + type inference.

FormatRowsTime (ms)
CSV5055
Parquet502
JSON5076

DuckDB vs Polars.NET — reference

OperationDuckDB (SQL)Polars.NET (DataFrame API)
Read ParquetSELECT * FROM 'file.parquet'DataFrame.ReadParquet(path)
Read CSVSELECT * FROM 'file.csv'DataFrame.ReadCsv(path)
FilterWHERE col = 'val'df.Filter(Col("col") == Lit("val"))
SelectSELECT col1, col2df.Select("col1", "col2")
SortORDER BY col DESCdf.Sort("col", descending: true)
Group + AggGROUP BY ... AVG(col)df.GroupBy("col").Agg(Col("col").Mean())
Add columnSELECT *, a-b AS cdf.WithColumns((Col("a")-Col("b")).Alias("c"))
Window functionLAG() OVER (PARTITION BY ...)Not available — use DuckDB
CTEWITH cte AS (...)Not available — use DuckDB
ExportCOPY (...) TO 'file.parquet'df.WriteParquet(path)

DuckDB vs Polars — decision guide

ScenarioUseWhy
Complex SQL (joins, CTEs, windows)DuckDBFull SQL:2003 support
Ad-hoc file explorationDuckDBSQL on files, no code needed
DataFrame transformsPolars.NETMethod chaining, lazy eval
ML pipeline preprocessingPolars.NETDataFrame API integrates with ML
Notebook data explorationDuckDBWrite SQL directly, instant results
Production ETL validationDuckDBSQL assertions on file data
Both in same projectYesDuckDB for SQL, Polars for transforms

Summary

C# Database Warnings

SQL injection via string interpolation

$"SELECT * FROM Users WHERE Name = '{name}'" injects raw input into the query. A value like ' OR '1'='1 bypasses all filters.

Correct pattern

Always use named parameters: cmd.Parameters.AddWithValue("@name", name). In Dapper: conn.Query<User>("SELECT * FROM Users WHERE Name = @name", new { name }). In EF Core: LINQ predicates are parameterised automatically.

Not disposing SqlConnection / DbContext

ADO.NET connections hold an underlying socket and a slot in the connection pool. An undisposed connection is not returned to the pool, causing pool exhaustion under load.

Correct pattern

Wrap every SqlConnection, SqlCommand, SqlDataReader, and DbContext in a using block or await using for async. For EF Core, register DbContext as scoped in DI and let the framework dispose it.

Calling SaveChanges() inside a loop

Each SaveChanges() call is a database round-trip. Calling it per entity in a bulk load sends N individual INSERT statements.

Correct pattern

Add all entities first, then call SaveChanges() once outside the loop. For very large batches, use ExecuteBulkOperationsAsync (EF Core extensions) or SqlBulkCopy directly.

Using Database.EnsureCreated() alongside EF Core migrations

EnsureCreated() creates the schema directly from the model and does not register a migrations history table. Subsequent dotnet ef database update commands will fail with schema conflicts.

Correct pattern

Choose one strategy: use EnsureCreated() for tests and throw-away databases; use Database.Migrate() (or dotnet ef database update) for all persistent environments.

Sharing a DbContext across threads

DbContext is not thread-safe. Using a single instance from multiple threads causes race conditions on the change tracker and connection.

Correct pattern

Register DbContext as AddDbContext<T> (scoped) in ASP.NET Core DI. For background workers, create a scope: using var scope = sp.CreateScope(); var db = scope.ServiceProvider.GetRequiredService<MyDb>();.

C# Database Recommendations

  • Always use using or await using for SqlConnection, SqlCommand, SqlDataReader, and DbContext — guarantees disposal and connection pool return.
  • Parameterise every query — use @param placeholders in ADO.NET/Dapper; rely on EF Core LINQ for automatic parameterisation.
  • Store connection strings in configuration, not source — IConfiguration["ConnectionStrings:Default"] or environment variables; use Azure Key Vault in production.
  • Use QueryFirstOrDefault<T> in Dapper for single-row results — returns null when not found instead of throwing, and avoids loading a full result set.
  • Filter before ToList() in EF Core — add .Where(), .Select(), and .Take() before .ToList() so EF Core generates a server-side filtered SQL query.
  • Use AsNoTracking() for read-only EF Core queries — disables the change tracker, reducing memory and CPU overhead for queries that don’t need update detection.
  • Use SqlBulkCopy for high-volume inserts — orders of magnitude faster than row-by-row ExecuteNonQuery for bulk loads into SQL Server.
  • Use DuckDB for in-process analytics — scanning Parquet/CSV directly with DuckDB SQL avoids loading data into a DataFrame first; preferred for ad-hoc analytical queries.

C# Database Troubleshooting

ProblemCauseFix
InvalidOperationException: Connection is already openReusing a connection that was not closedWrap each operation in using var conn = new SqlConnection(...) — opens a fresh pooled connection
SqlException: Login failed for userWrong credentials or user lacks database permissionsVerify connection string; check SQL Server login; for Windows auth, ensure Integrated Security=True and app runs as the correct identity
SqlException: Cannot open database requestedDatabase name wrong, or user lacks CONNECT permissionCheck Database= in connection string; run USE [db]; GRANT CONNECT TO [user]
EF Core LINQ query loads entire table.ToList() called before .Where()Move filter before materialisation: db.Users.Where(u => u.Active).ToList()
InvalidOperationException: A second operation was started on this contextConcurrent async calls on the same DbContextUse separate scopes per concurrent operation; don’t await multiple EF calls on the same context simultaneously
Dapper Query<T> returns empty list but SQL returns rowsColumn names in SQL don’t match property names in POCOAlign property names with column aliases, or use Dapper’s ColumnAttribute / custom type map
SaveChanges() throws DbUpdateConcurrencyExceptionRow was modified or deleted by another process since it was loadedImplement optimistic concurrency with [ConcurrencyCheck] or RowVersion; catch exception and re-fetch
DuckDB IOException: Could not load native DuckDB libraryUsing DuckDB.NET.Data (stub) instead of DuckDB.NET.Data.FullReplace NuGet reference with DuckDB.NET.Data.Full which bundles the native binary
Migration dotnet ef database update fails with already existsEnsureCreated() was used previously — schema exists but no migrations historyDelete the database and re-apply, or manually insert the baseline migration row into __EFMigrationsHistory
SqlDataReader throws InvalidCastException on nullable columnCalling reader.GetString(i) on a DBNull valueCheck reader.IsDBNull(i) before reading, or use reader.GetValue(i) as string

Medallion architecture: Bronze (raw ingested data) → Silver (cleaned, deduplicated, SCD-2) → Gold (composite scores, index performance)

Graph View

Backlinks