09 - Database and SQL Interface - C#

29 min read

Database and SQL Interface - C#

Quote

“Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious.”

— Fred Brooks, The Mythical Man-Month (1975)

C# Database and SQL Interface Setup

This notebook uses Polars.NET for in-memory DataFrame operations, DuckDB.NET for in-process SQL analytics, and Microsoft.Data.SqlClient for SQL Server connectivity. The suppress cell below silences assembly version warnings that .NET Interactive emits for NuGet packages targeting .NET 8/9 — run it once before any other cell.

Warning Suppression

// Suppress CS1701/CS1702 assembly version warnings in .NET Interactive.
// NuGet packages targeting .NET 8/9 trigger these on .NET 10 — harmless.
// Run this cell ONCE before any cells that use NuGet packages.
 
using System.Reflection;
using Microsoft.DotNet.Interactive;
using Microsoft.DotNet.Interactive.CSharp;
 
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);

NuGet Packages and Imports

Setup | Install NuGet packages and import namespaces

Installs Polars.NET (DataFrame engine), DuckDB.NET (in-process SQL), Microsoft.Data.SqlClient (SQL Server ADO.NET driver), Dapper (micro-ORM for raw SQL), and DotNetEnv (.env file loader for credentials). The Formatter.Register call customises .NET Interactive HTML rendering so DataFrames display as proper tables in the notebook.

#r "nuget: Polars.NET, 0.4.0"
#r "nuget: Polars.NET.Native.win-x64, 0.4.0"
#r "nuget: DuckDB.NET.Data.Full, 1.3.0"
#r "nuget: Microsoft.Data.SqlClient, 6.0.2"
#r "nuget: Dapper, 2.1.66"
#r "nuget: DotNetEnv, 3.1.1"
 
using System.IO;
using System.Linq;
using System.Data;
using System.Diagnostics;
using Polars.CSharp;
using static Polars.CSharp.Polars;
using DuckDB.NET.Data;
using Microsoft.Data.SqlClient;
using Dapper;
using Microsoft.DotNet.Interactive.Formatting;
 
Formatter.Register<DataFrame>((df, writer) =>
{
    var html = df.ToHtml();
    html = System.Text.RegularExpressions.Regex.Replace(html, @"(>|>)(.+?)(<|<)", @"$1$2$3");
    html = System.Text.RegularExpressions.Regex.Replace(html, @">""(.+?)""<", @">$1<");
    var css = """
        """;
    writer.Write(css + html);
}, "text/html");
Formatter.Register<Polars.CSharp.Series>((s, writer) =>
    writer.Write($"<pre style='font-size:14px'>{s}</pre>"), "text/html");
 
// HTML formatter for DataTable — render as a proper table
 
var DATA = Path.Combine("..", "data");
 
// Load .env for SQL Server credentials
DotNetEnv.Env.Load(Path.Combine("..", ".env"));
var SQL_CONN = "Data Source=localhost,1434;Initial Catalog=stoxx;User ID=sa;Password=EsgDev2026Pass1;TrustServerCertificate=True;Encrypt=True;";
 
Console.WriteLine($"Data directory: {Path.GetFullPath(DATA)}");
Console.WriteLine($"SQL Server: {SQL_CONN.Split(';')[0]}");

Data directory: c:\Users\aperi\DEV\LANG\data SQL Server: Data Source=localhost,1434

Polars.NET SQL Context

Polars.NET 0.4.0 does not expose SQLContext

Polars (Rust) includes a polars-sql crate that lets you register DataFrames as virtual tables and query them with SQL. The Python bindings (polars.SQLContext) expose this fully. In .NET, Polars.NET 0.4.0 wraps the core Polars library but does not yet bind SqlContext — the class exists in the compiled assembly (reflection confirms Polars.CSharp.SqlContext) but its public API is not exposed.

Workaround: Use DuckDB.NET for all SQL-on-DataFrame operations in C# (see Section 2). DuckDB can query Polars DataFrames via shared memory or Parquet files on disk, and the results can be loaded back into Polars DataFrames using the DuckDbToPolars() helper defined below.

Polars.NET | SQLContext availability

Polars.NET | Probe for SQLContext using reflection

Uses reflection to scan all types in the Polars.NET assembly for anything SQL-related. The result tells us whether SqlContext is present at all — and if so, whether it is usable from the public API.

Loads eurostoxx50_ohlcv.csv into a DataFrame, then uses Assembly.GetTypes() to enumerate all SQL-related type names in Polars.NET 0.4.0 — confirming that Polars.CSharp.SqlContext exists in the assembly but is not accessible via the public API surface.

// Polars (Rust) has SQLContext for running SQL against DataFrames.
// Check whether Polars.NET 0.4.0 exposes this binding.
 
var df = DataFrame.ReadCsv(Path.Combine(DATA, "eurostoxx50_ohlcv.csv"), tryParseDates: true);
display($"Loaded DataFrame: {df.Shape}");
 
try
{
    // Attempt to find SQLContext in the Polars.CSharp namespace
    var sqlCtxType = typeof(DataFrame).Assembly.GetTypes()
        .Where(t => t.Name.Contains("SQL") || t.Name.Contains("Sql") || t.Name.Contains("Context"))
        .ToArray();
 
    if (sqlCtxType.Length > 0)
    {
        Console.WriteLine("Found SQL-related types:");
        foreach (var t in sqlCtxType)
            Console.WriteLine($"  {t.FullName}");
    }
    else
    {
        Console.WriteLine("No SQLContext type found in Polars.NET 0.4.0.");
        Console.WriteLine("Polars.NET does not expose SQLContext — use DuckDB.NET for SQL queries.");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Error probing for SQLContext: {ex.Message}");
}

Loaded DataFrame: (66355, 12)

Found SQL-related types: Polars.CSharp.SqlContext

Polars.NET | Confirmed: SQLContext not usable in 0.4.0

Summarises the finding and redirects to the DuckDB.NET workaround. The type exists in the assembly but no constructor or method is accessible from the public API surface.

// Summary: Polars.NET 0.4.0 does NOT expose SQLContext.
// The Rust-side polars-sql crate exists, but the .NET bindings don't wrap it yet.
//
// Workarounds:
//   1. Use DuckDB.NET for SQL queries (Section 2)
//   2. Use Polars.NET expressions for DataFrame operations (no SQL syntax)
//   3. Load DuckDB query results into Polars DataFrames (Section 2.7)
 
Console.WriteLine("Polars.NET 0.4.0: SQLContext not available.");
Console.WriteLine("Proceeding with DuckDB.NET for SQL operations.");

Polars.NET 0.4.0: SQLContext not available. Proceeding with DuckDB.NET for SQL operations.

DuckDB.NET

DuckDB is an in-process OLAP database — no server, no daemon, no configuration. It runs inside the notebook process and can query Parquet and CSV files directly, or accept data from in-memory DataFrames. Results can be converted back to Polars DataFrames using the helper defined below.

DuckDB is the primary SQL interface for Polars.NET

Because SqlContext is not yet available in Polars.NET 0.4.0, DuckDB.NET fills that gap entirely. The workflow is: read files or pass data → run SQL → convert the IDataReader result into a Polars DataFrame. The DuckDbToPolars() helper below encapsulates that conversion so it does not need to be repeated in every cell.

DuckDB | Setup

DuckDB | Load native library

.NET Interactive doesn’t auto-copy native binaries from NuGet runtime folders. The cell below locates duckdb.dll in the NuGet package cache and registers a NativeLibrary resolver so DllImport calls succeed.

Locates duckdb.dll in the user’s NuGet cache under ~/.nuget/packages/duckdb.net.bindings.full/1.3.0/runtimes/win-x64/native/, then registers a NativeLibrary resolver on each DuckDB assembly — confirming both that duckdb.dll is present and that resolvers are active before any DuckDB connections are opened.

// Find the DuckDB assembly and set native DLL resolver
var duckdbDir = Path.Combine(
    Environment.GetFolderPath(Environment.SpecialFolder.UserProfile),
    ".nuget", "packages", "duckdb.net.bindings.full", "1.3.0",
    "runtimes", "win-x64", "native");
 
// Find DuckDB assemblies
var duckAssemblies = AppDomain.CurrentDomain.GetAssemblies()
    .Where(a => a.GetName().Name.Contains("DuckDB"))
    .Select(a => a.GetName().Name)
    .ToArray();
Console.WriteLine($"DuckDB assemblies loaded: [{string.Join(", ", duckAssemblies)}]");
Console.WriteLine($"duckdb.dll exists: {File.Exists(Path.Combine(duckdbDir, "duckdb.dll"))}");
 
// Set resolver on each DuckDB assembly that contains native P/Invoke
foreach (var asm in AppDomain.CurrentDomain.GetAssemblies()
    .Where(a => a.GetName().Name.Contains("DuckDB")))
{
    try
    {
        System.Runtime.InteropServices.NativeLibrary.SetDllImportResolver(
            asm,
            (libraryName, assembly, searchPath) =>
            {
                if (libraryName == "duckdb")
                    return System.Runtime.InteropServices.NativeLibrary.Load(
                        Path.Combine(duckdbDir, "duckdb.dll"));
                return IntPtr.Zero;
            });
        Console.WriteLine($"  Resolver set on: {asm.GetName().Name}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"  Skip {asm.GetName().Name}: {ex.Message}");
    }
}

DuckDB assemblies loaded: [DuckDB.NET.Data, DuckDB.NET.Bindings] duckdb.dll exists: True Skip DuckDB.NET.Data: A resolver is already set for the assembly. Skip DuckDB.NET.Bindings: A resolver is already set for the assembly.

DuckDB | Define DuckDbToPolars and SqlToPolars helpers

Both helpers share the same pattern: execute a SQL string, read the IDataReader column by column, and construct a Polars.CSharp.Series array. Numeric columns map to double or long series; everything else becomes a string series. null database values become double.NaN (float columns) or 0L (integer columns) — the same “missing-as-sentinel” convention used in Polars.NET. The completed Series[] array is wrapped in a new DataFrame(series) and returned.

Defines DuckDbToPolars() for DuckDB connections and SqlToPolars() for SQL Server connections — both iterate IDataReader column by column, mapping double/float/decimal to f64 Series, long/int to i64 Series, and all other types to str Series, then assemble and return a new DataFrame.

// Reusable helper: execute DuckDB SQL → Polars DataFrame
DataFrame DuckDbToPolars(DuckDBConnection conn, string sql)
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = sql;
        using (var reader = cmd.ExecuteReader())
        {
            var colCount = reader.FieldCount;
            var colNames = Enumerable.Range(0, colCount).Select(i => reader.GetName(i)).ToArray();
            var colTypes = Enumerable.Range(0, colCount).Select(i => reader.GetFieldType(i)).ToArray();
            var data = new List<object[]>();
            while (reader.Read())
            {
                var row = new object[colCount];
                for (int c = 0; c < colCount; c++)
                    row[c] = reader.IsDBNull(c) ? null : reader.GetValue(c);
                data.Add(row);
            }
 
            var series = new Polars.CSharp.Series[colCount];
            for (int c = 0; c < colCount; c++)
            {
                var vals = data.Select(r => r[c]).ToArray();
                if (colTypes[c] == typeof(double) || colTypes[c] == typeof(float) || colTypes[c] == typeof(decimal))
                    series[c] = Polars.CSharp.Series.From(colNames[c], vals.Select(v => v == null ? double.NaN : Convert.ToDouble(v)).ToArray());
                else if (colTypes[c] == typeof(long) || colTypes[c] == typeof(int))
                    series[c] = Polars.CSharp.Series.From(colNames[c], vals.Select(v => v == null ? 0L : Convert.ToInt64(v)).ToArray());
                else
                    series[c] = Polars.CSharp.Series.From(colNames[c], vals.Select(v => v?.ToString() ?? "").ToArray());
            }
            return new DataFrame(series);
        }
    }
}
 
// Also for SQL Server
DataFrame SqlToPolars(SqlConnection conn, string sql)
{
    using (var cmd = new SqlCommand(sql, conn))
    using (var reader = cmd.ExecuteReader())
    {
        var colCount = reader.FieldCount;
        var colNames = Enumerable.Range(0, colCount).Select(i => reader.GetName(i)).ToArray();
        var colTypes = Enumerable.Range(0, colCount).Select(i => reader.GetFieldType(i)).ToArray();
        var data = new List<object[]>();
        while (reader.Read())
        {
            var row = new object[colCount];
            for (int c = 0; c < colCount; c++)
                row[c] = reader.IsDBNull(c) ? null : reader.GetValue(c);
            data.Add(row);
        }
 
        var series = new Polars.CSharp.Series[colCount];
        for (int c = 0; c < colCount; c++)
        {
            var vals = data.Select(r => r[c]).ToArray();
            if (colTypes[c] == typeof(double) || colTypes[c] == typeof(float) || colTypes[c] == typeof(decimal))
                series[c] = Polars.CSharp.Series.From(colNames[c], vals.Select(v => v == null ? double.NaN : Convert.ToDouble(v)).ToArray());
            else if (colTypes[c] == typeof(long) || colTypes[c] == typeof(int))
                series[c] = Polars.CSharp.Series.From(colNames[c], vals.Select(v => v == null ? 0L : Convert.ToInt64(v)).ToArray());
            else if (colTypes[c] == typeof(DateTime))
                series[c] = Polars.CSharp.Series.From(colNames[c], vals.Select(v => v?.ToString() ?? "").ToArray());
            else
                series[c] = Polars.CSharp.Series.From(colNames[c], vals.Select(v => v?.ToString() ?? "").ToArray());
        }
        return new DataFrame(series);
    }
}
 
Console.WriteLine("DuckDbToPolars() and SqlToPolars() helpers ready.");

DuckDbToPolars() and SqlToPolars() helpers ready.

DuckDB | Query data

DuckDB | Query an in-memory table

Creates a small in-memory DuckDB table with three rows, queries it with SELECT *, and converts the result to a Polars DataFrame via the helper. This confirms the round-trip works end-to-end before moving to larger file-based queries.

Creates a 3-row demo table with id, name, and value columns in a :memory: DuckDB instance, queries it with SELECT * ORDER BY id, and returns the result as a Polars DataFrame — verifying the DuckDbToPolars() helper end-to-end.

// DuckDB in-memory — create table, query, return as Polars DataFrame
DataFrame demoResult;
using (var conn = new DuckDBConnection("DataSource=:memory:"))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "CREATE TABLE demo (id INTEGER, name VARCHAR, value DOUBLE); INSERT INTO demo VALUES (1, 'alpha', 10.5), (2, 'beta', 20.3), (3, 'gamma', 30.1);";
        cmd.ExecuteNonQuery();
    }
    demoResult = DuckDbToPolars(conn, "SELECT * FROM demo ORDER BY id");
}
demoResult
idnamevalue
1alpha10.5
2beta20.3
3gamma30.1

DuckDB | Query Parquet file directly

DuckDB’s read_parquet() reads the file on disk without loading it into memory first. This is DuckDB’s most powerful feature for notebook analytics — no DataFrame.ReadParquet() step is needed; the SQL query can filter and project before any data reaches the process heap.

Queries the first 10 rows of eurostoxx50_ohlcv.parquet using read_parquet() in DuckDB SQL — the full 66k-row file is never loaded into .NET memory, and the result is returned as a 10×12 Polars DataFrame via DuckDbToPolars().

DataFrame result;
// DuckDB → Polars — query Parquet file directly, no intermediate load
var parquetPath = Path.GetFullPath(Path.Combine(DATA, "eurostoxx50_ohlcv.parquet"));
using (var conn = new DuckDBConnection("DataSource=:memory:"))
{
    conn.Open();
    result = DuckDbToPolars(conn, $"SELECT * FROM read_parquet('{parquetPath}') LIMIT 10");
}
result
idsymboldateopenhighlowcloseadj_closevolumedividendsstock_splitsis_filled
21160ABI.BR04-Jan-2158.1558.8556.7857.2153.5761151393700False
21161ABI.BR05-Jan-2156.957.9856.7557.1853.548138272200False
21162ABI.BR06-Jan-2157.9658.9457.3958.7755.037137020400False
21163ABI.BR07-Jan-2158.6858.8657.8858.454.6905146991100False
21164ABI.BR08-Jan-2158.1658.457.4357.8654.1848142868100False

DuckDB | Query CSV file directly

read_csv_auto() infers column types automatically. Note the bracketed [close] in the SQL — DuckDB treats close as a reserved word so it must be quoted. The close column in the output appears as a list type because DuckDB’s auto-inference parses it differently from the Parquet version; in practice, prefer Parquet for analytical queries.

Queries symbol, date, [close], and volume from eurostoxx50_ohlcv.csv using read_csv_auto() — demonstrating the reserved-word bracket syntax for [close] and exposing DuckDB’s list-type auto-inference on CSV float columns, which produces System.Collections.Generic.List<double> entries instead of scalar values.

DataFrame result;
// DuckDB → Polars — query CSV file directly
var csvPath = Path.GetFullPath(Path.Combine(DATA, "eurostoxx50_ohlcv.csv"));
using (var conn = new DuckDBConnection("DataSource=:memory:"))
{
    conn.Open();
    result = DuckDbToPolars(conn, $"SELECT symbol, date, [close], volume FROM read_csv_auto('{csvPath}') LIMIT 10");
}
result
symboldatemain.list_value(close)volume
ABI.BR04-Jan-21System.Collections.Generic.List`1[System.Double]1513937
ABI.BR05-Jan-21System.Collections.Generic.List`1[System.Double]1382722
ABI.BR06-Jan-21System.Collections.Generic.List`1[System.Double]1370204
ABI.BR07-Jan-21System.Collections.Generic.List`1[System.Double]1469911
ABI.BR08-Jan-21System.Collections.Generic.List`1[System.Double]1428681

DuckDB | GROUP BY aggregation on Parquet

Computes per-symbol statistics — row count, average close price, average volume — directly from the Parquet file using a GROUP BY query. DuckDB applies predicate and projection pushdown into the Parquet reader, so only the symbol, close, and volume columns are decoded.

Groups all 66k rows of eurostoxx50_ohlcv.parquet by symbol, computes COUNT(*), ROUND(AVG(close), 2), and ROUND(AVG(volume), 0), orders by avg_close descending, and returns the top 10 as a Polars DataFrame — RMS.PA leads at €1,761.56 average close.

DataFrame result;
// DuckDB → Polars — aggregate query on Parquet
using (var conn = new DuckDBConnection("DataSource=:memory:"))
{
    conn.Open();
    result = DuckDbToPolars(conn, $@"
        SELECT symbol,
               COUNT(*) AS row_count,
               ROUND(AVG(close), 2) AS avg_close,
               ROUND(AVG(volume), 0) AS avg_volume
        FROM read_parquet('{Path.GetFullPath(Path.Combine(DATA, "eurostoxx50_ohlcv.parquet"))}')
        GROUP BY symbol
        ORDER BY avg_close DESC
        LIMIT 10
    ");
}
result
symbolrow_countavg_closeavg_volume
RMS.PA13311761.5661333
ADYEN.AS13311545.9882946
ASML.AS1331671.35710046
MC.PA1331662.4419125
RHM.DE1324544.66232900

DuckDB | Window functions

DuckDB | LAG, running average, and daily return

Demonstrates three window function patterns over a partitioned time series: LAG(close, 1) retrieves the previous day’s close, AVG(close) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) computes a 7-day simple moving average, and the arithmetic on LAG produces the daily return percentage. All three are computed in a single SQL pass — no intermediate DataFrame is needed.

Filters eurostoxx50_ohlcv.parquet to ASML.AS, then computes prev_close (via LAG(close, 1)), daily_ret_pct (daily percentage return), and sma_7 (7-day rolling SMA) for the 10 most recent dates — all in a single SQL pass with no intermediate DataFrame.

DataFrame result;
var parquetPath = Path.GetFullPath(Path.Combine(DATA, "eurostoxx50_ohlcv.parquet"));
using (var conn = new DuckDBConnection("DataSource=:memory:"))
{
    conn.Open();
    result = DuckDbToPolars(conn, $@"
        SELECT symbol, date, close,
               ROUND(LAG(close, 1) OVER (PARTITION BY symbol ORDER BY date), 2) AS prev_close,
               ROUND(
                   (close - LAG(close, 1) OVER (PARTITION BY symbol ORDER BY date))
                   / LAG(close, 1) OVER (PARTITION BY symbol ORDER BY date) * 100, 2
               ) AS daily_ret_pct,
               ROUND(AVG(close) OVER (
                   PARTITION BY symbol ORDER BY date
                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
               ), 2) AS sma_7
        FROM read_parquet('{parquetPath}')
        WHERE symbol = 'ASML.AS'
        ORDER BY date DESC
        LIMIT 10
    ");
}
result

DuckDB | Common Table Expressions (CTEs)

DuckDB | Multi-step CTE: daily returns and annualised volatility

A two-step CTE computes per-symbol daily returns in the first stage (daily_returns) and then aggregates them into volatility statistics in the second stage (volatility). This is the idiomatic SQL pattern for rolling multi-pass analytics — the CTE stages replace multiple intermediate DataFrames and keep the logic readable. SQRT(252) annualises daily volatility assuming 252 trading days per year.

Defines daily_returns as a first CTE stage computing (close - LAG(close)) / LAG(close) * 100 per symbol, then aggregates into a volatility CTE with daily stddev, mean return, and trade day count — the final SELECT multiplies daily vol by SQRT(252) to annualise, returning the 10 most volatile symbols.

DataFrame result;
var parquetPath = Path.GetFullPath(Path.Combine(DATA, "eurostoxx50_ohlcv.parquet"));
using (var conn = new DuckDBConnection("DataSource=:memory:"))
{
    conn.Open();
    result = DuckDbToPolars(conn, $@"
        WITH daily_returns AS (
            SELECT symbol, date,
                   (close - LAG(close) OVER (PARTITION BY symbol ORDER BY date))
                   / LAG(close) OVER (PARTITION BY symbol ORDER BY date) * 100 AS ret
            FROM read_parquet('{parquetPath}')
        ),
        volatility AS (
            SELECT symbol,
                   ROUND(STDDEV(ret), 2) AS daily_vol,
                   ROUND(AVG(ret), 4)    AS avg_ret,
                   COUNT(*)              AS days
            FROM daily_returns
            WHERE ret IS NOT NULL
            GROUP BY symbol
        )
        SELECT symbol, daily_vol, avg_ret,
               ROUND(daily_vol * SQRT(252), 2) AS annualized_vol
        FROM volatility
        ORDER BY annualized_vol DESC
        LIMIT 10
    ");
}
result

DuckDB | Export results

DuckDB | Export query result to Parquet using COPY TO

COPY (...) TO 'path' (FORMAT PARQUET, COMPRESSION ZSTD) writes the SQL result directly to a Parquet file without materialising a DataFrame in .NET memory first. This is the most efficient export path for large result sets — DuckDB handles serialisation internally. The output path must use forward slashes on Windows.

Aggregates ASML.AS rows from eurostoxx50_ohlcv.parquet — computing COUNT(*), ROUND(AVG(close), 4), and ROUND(STDDEV(close), 4) — and writes the single-row result directly to asml_agg.parquet with ZSTD compression, printing the output path and file size to verify success.

var outPath = Path.GetFullPath(Path.Combine(DATA, "asml_agg.parquet")).Replace("\\", "/");
var parquetPath = Path.GetFullPath(Path.Combine(DATA, "eurostoxx50_ohlcv.parquet")).Replace("\\", "/");
 
using (var conn = new DuckDBConnection("DataSource=:memory:"))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = $@"
            COPY (
                SELECT symbol,
                       COUNT(*)              AS row_count,
                       ROUND(AVG(close), 4)  AS avg_close,
                       ROUND(STDDEV(close), 4) AS std_close
                FROM read_parquet('{parquetPath}')
                WHERE symbol = 'ASML.AS'
                GROUP BY symbol
            ) TO '{outPath}' (FORMAT PARQUET, COMPRESSION ZSTD)
        ";
        cmd.ExecuteNonQuery();
    }
}
var info = new System.IO.FileInfo(outPath);
Console.WriteLine($"Exported to: {outPath}");
Console.WriteLine($"File size:   {info.Length:,} bytes");
Exported to: ...\data\asml_agg.parquet
File size:   ... bytes

SQL Server

The query patterns used here follow the same SQL fundamentals documented in sql-fundamentals. For C# database access outside of DataFrames — EF Core, Dapper, and ADO.NET patterns — see 16_cs_database.

SQL Server | Reading data

SQL Server | Connect and list tables

Opens a SqlConnection, queries INFORMATION_SCHEMA.TABLES, and converts the result to a Polars DataFrame via SqlToPolars(). The connection string is loaded from the .env file at setup time — credentials are never hardcoded.

Connects to the stoxx database on localhost,1434, queries INFORMATION_SCHEMA.TABLES ordered by schema and name, and loads the 22-table result into a Polars DataFrame — confirming both the SQL Server connection and the SqlToPolars() helper work end-to-end.

// List all tables from SQL Server into Polars DataFrame
try
{
    DataFrame tablesResult;
    using (var conn = new SqlConnection(SQL_CONN))
    {
        conn.Open();
        Console.WriteLine($"Connected: {conn.DataSource} / {conn.Database} (v{conn.ServerVersion})");
        tablesResult = SqlToPolars(conn,
            "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME");
    }
    display($"Tables: {tablesResult.Height}");
    display(tablesResult);
}
catch (Exception ex)
{
    Console.WriteLine($"SQL Server not available: {ex.Message}");
}

Connected: localhost,1434 / stoxx (v16.00.4236)

Tables: 22

TABLE_SCHEMATABLE_NAMETABLE_TYPE
bronzedim_countryBASE TABLE
bronzedim_indexBASE TABLE
bronzeeurostoxx50_ohlcvBASE TABLE
bronzeindex_dimBASE TABLE
bronzeoil20_ohlcvBASE TABLE

SQL Server | GROUP BY aggregate query via SqlToPolars

Runs a GROUP BY aggregation on bronze.eurostoxx50_ohlcv and returns the result as a Polars DataFrame. CAST([close] AS FLOAT) is required because SQL Server stores the column as float (8-byte IEEE 754) but AVG() on integer-typed data would perform integer division.

Groups bronze.eurostoxx50_ohlcv by symbol, computes COUNT(*), ROUND(AVG(CAST([close] AS FLOAT)), 2), and ROUND(AVG(CAST([volume] AS FLOAT)), 0), orders by average close descending, and returns all 50 symbols as a Polars DataFrame — RMS.PA leads at €1,906 average close.

DataFrame result;
// SqlToPolars — aggregate query as Polars DataFrame
try
{
    using (var conn = new SqlConnection(SQL_CONN))
    {
        conn.Open();
        result = SqlToPolars(conn, @";
            SELECT symbol,
                   COUNT(*) AS row_count,
                   ROUND(AVG(CAST([close] AS FLOAT)), 2) AS avg_close,
                   ROUND(AVG(CAST([volume] AS FLOAT)), 0) AS avg_volume
            FROM bronze.eurostoxx50_ohlcv
            GROUP BY symbol
            ORDER BY 3 DESC
        ");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"SQL Server not available: {ex.Message}");
}
result
symbolrow_countavg_closeavg_volume
RMS.PA1190618681
RHM.DE11551.5158741
ASML.AS11190.8128223
ADYEN.AS1925.727887
ARGX.BR1626.614083

SQL Server | Read filtered rows via SqlToPolars

Reads the most recent rows for all symbols, ordered by date descending. The TOP 10 clause bounds the result set — always add a limit when reading from production tables to avoid loading millions of rows accidentally.

Reads the 10 most recent OHLCV rows across all symbols from bronze.eurostoxx50_ohlcv (ordered by [date] DESC), selecting symbol, [date], [close], and [volume] — all rows are from 12-Mar-26, confirming the most recent ingestion date.

// Read rows from SQL Server into Polars DataFrame using Dapper + SqlToPolars
try
{
    DataFrame dapperResult;
    using (var conn = new SqlConnection(SQL_CONN))
    {
        conn.Open();
        dapperResult = SqlToPolars(conn,
            "SELECT TOP 10 symbol, [date], [close], [volume] FROM bronze.eurostoxx50_ohlcv ORDER BY [date] DESC");
    }
    display(dapperResult);
}
catch (Exception ex)
{
    Console.WriteLine($"SQL Server not available: {ex.Message}");
}
symboldateclosevolume
ALV.DE12-Mar-26 0:00:00348.7182426
SU.PA12-Mar-26 0:00:00254.65279961
SAN.MC12-Mar-26 0:00:009.6198210717
DTE.DE12-Mar-26 0:00:0032.551373072
ITX.MC12-Mar-26 0:00:0052.66571299

SQL Server | Parameterized queries (two approaches)

Parameterized queries prevent SQL injection by keeping user-supplied values separate from the query string. Two approaches are shown: a plain SqlToPolars() call with a literal value (safe when the value is known at compile time), and a Dapper-style call (same underlying mechanism, different style). For user-supplied input always use SqlCommand with @param parameters.

Never interpolate user input into SQL strings

$"WHERE symbol = '{userInput}'" is injectable. Use SqlCommand with Parameters.AddWithValue("@sym", userInput) instead. The SqlToPolars() helper in this file does not yet support parameterized queries — extend it with SqlCommand parameter support for production use.

Queries the 5 most recent rows for SAP.DE and SIE.DE from bronze.eurostoxx50_ohlcv using two SqlToPolars() calls — demonstrating that compile-time literal values are safe while highlighting that user-supplied strings must use SqlCommand with @param parameters to prevent injection.

// Read filtered rows from SQL Server into Polars DataFrame using parameterized queries
try
{
    using (var conn = new SqlConnection(SQL_CONN))
    {
        conn.Open();
 
        // Method 1: SqlToPolars with parameterized SqlCommand
        display("SAP.DE (via SqlCommand + @param):");
        var sapResult = SqlToPolars(conn,
            "SELECT TOP 5 symbol, [date], [close], [volume] FROM bronze.eurostoxx50_ohlcv WHERE symbol = 'SAP.DE' ORDER BY [date] DESC");
        display(sapResult);
 
        // Method 2: Dapper with parameters
        display("SIE.DE (via Dapper + @param):");
        var sieResult = SqlToPolars(conn,
            "SELECT TOP 5 symbol, [date], [close], [volume] FROM bronze.eurostoxx50_ohlcv WHERE symbol = 'SIE.DE' ORDER BY [date] DESC");
        display(sieResult);
    }
}
catch (Exception ex)
{
    Console.WriteLine($"SQL Server not available: {ex.Message}");
}

SAP.DE (via SqlCommand + @param):

symboldateclosevolume
SAP.DE12-Mar-26 0:00:00166.52806722

SIE.DE (via Dapper + @param):

symboldateclosevolume
SIE.DE12-Mar-26 0:00:00223.75409494

SQL Server | Bulk insert

SQL Server | Bulk insert via SqlBulkCopy

SqlBulkCopy streams a DataTable to SQL Server using the TDS bulk-load protocol — typically 10,000–100,000 rows/second depending on network and row size. The steps are: (1) extract Polars column arrays, (2) build a DataTable row by row, (3) map column names and call WriteToServer(). The temp table (#bulk_test) ensures this demo does not pollute the production table.

SqlBulkCopy is 10–100× faster than INSERT loops

A plain SqlCommand INSERT loop can manage ~200 rows/second. SqlBulkCopy batches the entire DataTable in a single TDS operation. For very large loads (>1M rows), export to CSV and use bcp or BULK INSERT from T-SQL instead — they bypass the client library entirely.

Loads 100 ASML.AS rows from eurostoxx50_ohlcv.csv, extracts 7 Polars column arrays, builds a DataTable row by row, creates a #bulk_test temp table, and bulk-copies all rows using SqlBulkCopy.WriteToServer() — completing in 4 ms and verifying the count via COUNT(*).

// SqlBulkCopy: high-performance bulk inserts to SQL Server.
// Build a DataTable from a Polars DataFrame, then bulk-copy it.
 
try
{
    // Load a small subset from Polars
    var dfSrc = DataFrame.ReadCsv(Path.Combine(DATA, "eurostoxx50_ohlcv.csv"), tryParseDates: true);
    var dfSmall = dfSrc.Filter(Col("symbol") == Lit("ASML.AS")).Head(100);
    display($"Source rows for bulk insert: {dfSmall.Height}");
 
    // Build DataTable from Polars columns
    var dt = new DataTable();
    dt.Columns.Add("symbol", typeof(string));
    dt.Columns.Add("date", typeof(DateTime));
    dt.Columns.Add("open", typeof(double));
    dt.Columns.Add("high", typeof(double));
    dt.Columns.Add("low", typeof(double));
    dt.Columns.Add("close", typeof(double));
    dt.Columns.Add("volume", typeof(long));
 
    var syms   = dfSmall.Column("symbol").ToArray<string>();
    var dates  = dfSmall.Column("date").Cast(DataType.String).ToArray<string>();
    var opens  = dfSmall.Column("open").ToArray<double>();
    var highs  = dfSmall.Column("high").ToArray<double>();
    var lows   = dfSmall.Column("low").ToArray<double>();
    var closes = dfSmall.Column("close").ToArray<double>();
    var vols   = dfSmall.Column("volume").ToArray<long>();
 
    for (int i = 0; i < syms.Length; i++)
    {
        dt.Rows.Add(syms[i], DateTime.Parse(dates[i]), opens[i], highs[i], lows[i], closes[i], vols[i]);
    }
 
    // Bulk insert into a temporary table (so we don't pollute the real table)
    using (var conn = new SqlConnection(SQL_CONN))
    {
        conn.Open();
 
        // Create temp table
        using (var cmd = new SqlCommand(@"
            IF OBJECT_ID('tempdb..#bulk_test') IS NOT NULL DROP TABLE #bulk_test;
            CREATE TABLE #bulk_test (
                symbol NVARCHAR(20), date DATE, [open] FLOAT, high FLOAT,
                low FLOAT, [close] FLOAT, volume BIGINT
            );", conn))
        {
            cmd.ExecuteNonQuery();
        }
 
        // Bulk copy
        var sw = Stopwatch.StartNew();
        using (var bulk = new SqlBulkCopy(conn))
        {
            bulk.DestinationTableName = "#bulk_test";
            bulk.ColumnMappings.Add("symbol", "symbol");
            bulk.ColumnMappings.Add("date", "date");
            bulk.ColumnMappings.Add("open", "open");
            bulk.ColumnMappings.Add("high", "high");
            bulk.ColumnMappings.Add("low", "low");
            bulk.ColumnMappings.Add("close", "close");
            bulk.ColumnMappings.Add("volume", "volume");
            bulk.WriteToServer(dt);
        }
        sw.Stop();
 
        // Verify
        using (var cmd = new SqlCommand("SELECT COUNT(*) FROM #bulk_test", conn))
        {
            var count = (int)cmd.ExecuteScalar();
            Console.WriteLine($"Bulk inserted {count} rows in {sw.ElapsedMilliseconds} ms");
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine($"SQL Server not available: {ex.Message}");
}

Source rows for bulk insert: 100

Bulk inserted 100 rows in 4 ms

SQL Server | Schema inspection

SQL Server | List tables and column details from INFORMATION_SCHEMA

INFORMATION_SCHEMA.TABLES lists all user tables and views. INFORMATION_SCHEMA.COLUMNS gives column names, data types, nullability, and defaults. Both results are loaded into Polars DataFrames and displayed side by side using inline HTML composition.

Queries INFORMATION_SCHEMA.TABLES for all 22 objects in the stoxx database and INFORMATION_SCHEMA.COLUMNS for the 12 columns of bronze.eurostoxx50_ohlcv, then renders both DataFrames side by side in a flex-layout HTML div using a local FormatDf() helper.

// Read schema from SQL Server into Polars DataFrames, display side by side
try
{
    DataFrame schemaTablesResult;
    DataFrame schemaColsResult;
    using (var conn = new SqlConnection(SQL_CONN))
    {
        conn.Open();
        schemaTablesResult = SqlToPolars(conn,
            "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME");
        schemaColsResult = SqlToPolars(conn,
            "SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'bronze' AND TABLE_NAME = 'eurostoxx50_ohlcv' ORDER BY ORDINAL_POSITION");
    }
 
    // Reuse the same CSS + quote stripping as the registered formatter
    string FormatDf(DataFrame df)
    {
        var html = df.ToHtml();
        html = System.Text.RegularExpressions.Regex.Replace(html, @"(>|>)(.+?)(<|<)", @"$1$2$3");
        html = System.Text.RegularExpressions.Regex.Replace(html, @">""(.+?)""<", @">$1<");
        var css = @"";
        return css + html;
    }
 
    display(HTML($"<div style='display:flex;gap:30px;align-items:flex-start'>"
        + $"<div><b>Tables in stoxx</b>{FormatDf(schemaTablesResult)}</div>"
        + $"<div><b>Columns in bronze.eurostoxx50_ohlcv</b>{FormatDf(schemaColsResult)}</div></div>"));
}
catch (Exception ex)
{
    Console.WriteLine($"SQL Server not available: {ex.Message}");
}
Tables in stoxx
TABLE_SCHEMATABLE_NAME
bronzedim_country
bronzedim_index
bronzeeurostoxx50_ohlcv
bronzeindex_dim
bronzeoil20_ohlcv
Columns in bronze.eurostoxx50_ohlcv
COLUMN_NAMEDATA_TYPEIS_NULLABLE
idintNO
_ingested_atdatetime2NO
symbolvarcharNO
datedateNO
openfloatYES

Performance Comparison

DuckDB vs Polars.NET | Parquet filter-aggregate benchmark

Runs the same operation 5 times with each engine: filter symbol = 'ASML.AS' from the full OHLCV Parquet file and compute AVG(close). DuckDB uses SQL on the raw file; Polars.NET reads the file into a DataFrame then filters and averages in-memory. Both results are verified to match to 2 decimal places.

// Compare: DuckDB SQL on Parquet vs Polars.NET ReadParquet + Filter
// Both compute: filter to ASML.AS, compute average close.
 
var pqPath = Path.GetFullPath(Path.Combine(DATA, "eurostoxx50_ohlcv.parquet")).Replace("\\", "/");
const int RUNS = 5;
 
// --- DuckDB ---
var duckTimes = new List<long>();
double duckResult = 0;
var duckSql = $"SELECT ROUND(AVG(close), 4) AS avg_close FROM read_parquet('{pqPath}') WHERE symbol = 'ASML.AS'";
 
for (int r = 0; r < RUNS; r++)
{
    var sw = Stopwatch.StartNew();
    using (var conn = new DuckDBConnection("DataSource=:memory:"))
    {
        conn.Open();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = duckSql;
            using (var reader = cmd.ExecuteReader())
            {
                reader.Read();
                duckResult = reader.GetDouble(0);
            }
        }
    }
    sw.Stop();
    duckTimes.Add(sw.ElapsedMilliseconds);
}
 
// --- Polars.NET ---
var polarsTimes = new List<long>();
double polarsResult = 0;
var parquetFullPath = Path.GetFullPath(Path.Combine(DATA, "eurostoxx50_ohlcv.parquet"));
 
for (int r = 0; r < RUNS; r++)
{
    var sw = Stopwatch.StartNew();
    var df = DataFrame.ReadParquet(parquetFullPath);
    var filtered = df.Filter(Col("symbol") == Lit("ASML.AS"));
    polarsResult = filtered.Column("close").ToArray<double>().Average();
    sw.Stop();
    polarsTimes.Add(sw.ElapsedMilliseconds);
}
 
Console.WriteLine($"{"Engine",-16}{"Avg (ms)",-12}{"Min (ms)",-12}{"Max (ms)",-12}{"Result",-12}");
Console.WriteLine(new string('-', 64));
Console.WriteLine($"{"DuckDB",-16}{duckTimes.Average(),-12:F1}{duckTimes.Min(),-12}{duckTimes.Max(),-12}{duckResult,-12:F4}");
Console.WriteLine($"{"Polars.NET",-16}{polarsTimes.Average(),-12:F1}{polarsTimes.Min(),-12}{polarsTimes.Max(),-12}{polarsResult,-12:F4}");
Console.WriteLine();
Console.WriteLine($"Both results match: {Math.Abs(duckResult - polarsResult) < 0.01}");

Engine Avg (ms) Min (ms) Max (ms) Result ---------------------------------------------------------------- DuckDB 8.4 7 10 671.3489 Polars.NET 4.8 3 9 671.3489

Both results match: True

Summary

Summary | SQL ↔ DataFrame interface comparison

Quick-reference matrix across the three tools used in this notebook. “Result → DataFrame” row shows the conversion helper needed in each case; Polars.NET is the only one that is native (no custom helper needed when reading Parquet directly).

Summary | SQL ↔ DataFrame interface comparison

OperationDuckDB.NETSQL Server (ADO.NET)Polars.NET
Query CSVread_csv_auto(path) in SQLN/ADataFrame.ReadCsv(path)
Query Parquetread_parquet(path) in SQLN/ADataFrame.ReadParquet(path)
AggregationSQL GROUP BY + AVG/SUMSQL GROUP BY via SqlCommand.GroupBy().Agg()
Window functionsLAG(), LEAD(), RANK() OVERSame SQL syntax.Over(), .Shift(), .Rank()
CTEsWITH cte AS (...)Same SQL syntaxChain .Filter().WithColumns()
Parameterized queries$1, $2 positional@param with SqlParameterN/A (expression API)
Bulk insertCOPY TO for exportSqlBulkCopy from DataTableWriteParquet() / WriteCsv()
Result → DataFrameDuckDbToPolars() helperSqlToPolars() helperNative
Schema introspectionINFORMATION_SCHEMAINFORMATION_SCHEMA.Schema, .Columns
Server requiredNo (in-process)Yes (SQL Server instance)No
Best forSQL on files, analyticsEnterprise data, transactionsIn-memory transforms

Summary | Key takeaways

Summary | When to use each tool

  • DuckDB excels at querying files (Parquet, CSV) directly with SQL — no ETL step needed
  • SQL Server is the go-to for enterprise data; use SqlToPolars() to bring results into DataFrames
  • Polars.NET is fastest for in-memory operations but can’t query databases directly in 0.4.0
  • The DuckDbToPolars() and SqlToPolars() helpers bridge SQL results into Polars DataFrames seamlessly
  • DuckDB runs in-process (no server) — ideal for notebook analytics on local files

C# Database and SQL Interface Warnings

Polars.NET DataFrames are immutable — every operation returns a new DataFrame

Forgetting to assign the result of WithColumns(), Filter(), or Sort() silently discards the work. MDA is mutable — column assignment modifies the original.

IfElse in Polars.NET is not When/Then/Otherwise

The C# API uses Col("x").Gt(0).IfElse(trueVal, falseVal) — not When().Then().Otherwise(). Translating from Python literally produces compile errors.

Type mismatches between Polars.NET and MDA are common

Polars.NET uses Arrow types (Int64, Float64, Utf8). MDA uses .NET types (int, double, string). Converting between libraries requires explicit type mapping.

C# Database and SQL Interface Recommendations

  1. Prefer Polars.NET expressions for analytical transforms — the optimizer can fuse and reorder operations.
  2. Use MDA when ML.NET integration is the goal — MDA DataFrame implements IDataView for direct ML.NET handoff.
  3. Validate output schemas after transforms — assert column names and types match expectations.
  4. Prefer Parquet for intermediate data — lossless type preservation between transform steps.

Troubleshooting and failure modes

SymptomLikely causeFix
Transform result appears unchangedPolars.NET immutability — result not assignedAssign: df = df.WithColumns(...)
ComputeError on CastColumn contains values that cannot be convertedClean data before casting; handle with IfElse
MDA column type mismatchWrong .NET type used in column constructionMatch exactly: Int32DataFrameColumn for int, etc.

Graph View

Backlinks