Table of Contents

DuckDB.NET Basic Usage

SQL Execution

To execute SQL statements in DuckDB.NET you need to create a DuckDBConnection object that acts as an entry point for executing SQL statements. After you create a connection, create a command associated with it and use one of the following methods to execute the command:

  • ExecuteNonQuery - Execute SQL that does not return results such as DDL or changes the data by executing UPDATE, INSERT, or DELETE statements
  • ExecuteScalar - Execute SQL that returns a single, scalar value.
  • ExecuteReader - Executes SQL and returns DuckDBDataReader that can be used to consume the result set.
using var duckDBConnection = new DuckDBConnection("Data Source=file.db");
duckDBConnection.Open();

using var command = duckDBConnection.CreateCommand();
command.CommandText = "CREATE TABLE weather (city VARCHAR, temp_lo INTEGER, temp_hi INTEGER, prcp REAL, date DATE);";
var executeNonQuery = command.ExecuteNonQuery();

command.CommandText = "INSERT INTO weather VALUES ('Tbilisi', 41, 55, 0.1, '2020-04-02');";
executeNonQuery = command.ExecuteNonQuery();

command.CommandText = "INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');";
executeNonQuery = command.ExecuteNonQuery();

using var duckDBConnection = new DuckDBConnection("Data Source=file.db");
duckDBConnection.Open();

using var command = duckDBConnection.CreateCommand();
command.CommandText = "Select count(*) from weather";
var count = command.ExecuteScalar();

using var duckDBConnection = new DuckDBConnection("Data Source=file.db");
duckDBConnection.Open();

using var command = duckDBConnection.CreateCommand();
command.CommandText = "SELECT city, (temp_hi + temp_lo) / 2 AS temp_avg, date FROM weather;";

using var reader = command.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine("City: {0} Average Temperature: {1}, Date: {2}", 
                      reader.GetString(0), reader.GetDouble(1), reader.GetDateTime(2));
}

Parameterized statements

When building SQL commands, always use parameterized query instead of string concatenation. This helps to protect against SQL injection, improves type safety, and can result in improved performance.

DuckDB supports three syntaxes for denoting parameters in prepared statements: auto-incremented (?), positional ($1), and named ($param). All three syntaxes are supported in DuckDB.NET:

using var connection = new DuckDBConnection("DataSource=:memory:");
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM person WHERE starts_with(name, $name_start_letter) AND age >= $minimum_age;";

command.Parameters.Add(new DuckDBParameter("minimum_age", 40));
command.Parameters.Add(new DuckDBParameter("name_start_letter", "B"));

using var reader = command.ExecuteReader();

Batching

You can execute multiple statements in a single go by concatenating the statements and delimiting them with a semicolon:

using var connection = new DuckDBConnection("DataSource=:memory:");
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "INSTALL httpfs;LOAD httpfs;";
command.ExecuteNonQuery();

If the statements return data, you can consume the multiple result sets using the NextResult method:

using var connection = new DuckDBConnection("DataSource=:memory:");
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM weather ORDER BY city, temp_lo;SELECT DISTINCT city FROM weather;";
using var reader = command.ExecuteNonQuery();

do
{
  //consume data from the reader
} while (reader.NextResult());

Materialized and Streaming mode

When executing a Select statement with a DuckDBCommand, DuckDB.NET will by default use materialized execution mode. In this mode, DuckDB will fetch the whole result set in memory which might result in high memory usage. An alternative is to use streaming execution mode, in which case DuckDB will fetch the result set chunk by chunk and use a lower amount of memory but this may result in slower query execution. To use streaming mode set the UseStreamingMode property to true.

Edit this page