DuckDB.NET.Bindings 0.10.1

This package has a SemVer 2.0.0 package version: 0.10.1+133.
dotnet add package DuckDB.NET.Bindings --version 0.10.1
NuGet\Install-Package DuckDB.NET.Bindings -Version 0.10.1
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="DuckDB.NET.Bindings" Version="0.10.1" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add DuckDB.NET.Bindings --version 0.10.1
#r "nuget: DuckDB.NET.Bindings, 0.10.1"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install DuckDB.NET.Bindings as a Cake Addin
#addin nuget:?package=DuckDB.NET.Bindings&version=0.10.1

// Install DuckDB.NET.Bindings as a Cake Tool
#tool nuget:?package=DuckDB.NET.Bindings&version=0.10.1

DuckDB.NET

DuckDB bindings for C#

GitHub Workflow Status Coveralls License Ko-Fi alternate text is missing from this package README image

alternate text is missing from this package README image alternate text is missing from this package README image

alternate text is missing from this package README image alternate text is missing from this package README image

Project Icon

Usage

Support

If you encounter a bug with the library Create an Issue. Join the DuckDB .Net Channel for DuckDB.NET related topics.

Getting Started

There are two ways to work with DuckDB from C#: You can use ADO.NET Provider or use low-level bindings library for DuckDB. The ADO.NET Provider is built on top of the low-level library and is the recommended and most straightforward approach to work with DuckDB.

In both cases, there are two NuGet packages available: The Full package that includes the DuckDB native library and a managed-only library that doesn't include a native library.

ADO.NET Provider Includes DuckDB library
DuckDB.NET.Bindings
DuckDB.NET.Bindings.Full
DuckDB.NET.Data
DuckDB.NET.Data.Full

Using ADO.NET Provider

dotnet add package DuckDB.NET.Data.Full
using (var duckDBConnection = new DuckDBConnection("Data Source=file.db"))
{
  duckDBConnection.Open();

  using var command = duckDBConnection.CreateCommand();

  command.CommandText = "CREATE TABLE integers(foo INTEGER, bar INTEGER);";
  var executeNonQuery = command.ExecuteNonQuery();

  command.CommandText = "INSERT INTO integers VALUES (3, 4), (5, 6), (7, 8);";
  executeNonQuery = command.ExecuteNonQuery();

  command.CommandText = "Select count(*) from integers";
  var executeScalar = command.ExecuteScalar();

  command.CommandText = "SELECT foo, bar FROM integers";
  var reader = command.ExecuteReader();

  PrintQueryResults(reader);
}

private static void PrintQueryResults(DbDataReader queryResult)
{
  for (var index = 0; index < queryResult.FieldCount; index++)
  {
    var column = queryResult.GetName(index);
    Console.Write($"{column} ");
  }

  Console.WriteLine();

  while (queryResult.Read())
  {
    for (int ordinal = 0; ordinal < queryResult.FieldCount; ordinal++)
    {
      var val = queryResult.GetInt32(ordinal);
      Console.Write(val);
      Console.Write(" ");
    }

    Console.WriteLine();
  }
}

Efficient data loading with Appender

Appenders are the most efficient way of loading data into DuckDB. Starting from version 0.6.1, you can use a managed Appender instead of using low-level DuckDB Api:

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

using (var duckDbCommand = connection.CreateCommand())
{
  var table = "CREATE TABLE AppenderTest(foo INTEGER, bar INTEGER);";
  duckDbCommand.CommandText = table;
  duckDbCommand.ExecuteNonQuery();
}

var rows = 10;
using (var appender = connection.CreateAppender("managedAppenderTest"))
{
  for (var i = 0; i < rows; i++)
  {
    var row = appender.CreateRow();
    row.AppendValue(i).AppendValue(i+2).EndRow();
  }
}

Parameterized queries and DuckDB native types.

Starting from version 0.4.0.10, DuckDB.NET.Data supports executing parameterized queries and reading all built-in native DuckDB types. Starting from version 0.9.0 the library supports named parameters too:

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

using var command = connection.CreateCommand();

//Named parameters
command.CommandText = "INSERT INTO ParametersTestKeyValue (KEY, VALUE) VALUES ($key, $value)";
command.Parameters.Add(new DuckDBParameter("key", 42));
command.Parameters.Add(new DuckDBParameter("value", "hello"));
var affectedRows = command.ExecuteNonQuery();

//Positional parameters
command.CommandText = "INSERT INTO ParametersTestKeyValue (KEY, VALUE) VALUES (?, ?)";
command.Parameters.Add(new DuckDBParameter(24));
command.Parameters.Add(new DuckDBParameter("world"));
affectedRows = command.ExecuteNonQuery();

command.CommandText = "SELECT * from integers where foo > ?;";
command.Parameters.Add(new new DuckDBParameter(3));

using var reader = command.ExecuteReader();

To read DuckDB specific native types use DuckDBDataReader.GetFieldValue<T> method. The following table shows the mapping between DuckDB native type and DuckDB.NET.Data .Net type:

DuckDB Type .Net Type
INTERVAL DuckDBInterval
DATE DuckDBDateOnly/DateOnly
TIME DuckDBTimeOnly/TimeOnly
HUGEINT BigInteger

List, Struct, Enum, and other composite types

DuckDB.NET 0.9.2 supports reading Enum, Struct, List, Array and Map types as well as any combination of nested types.

To read an Enum, Struct or List or nested List use DuckDBDataReader.GetFieldValue<T>. For example, to read a list of doubles: DuckDBDataReader.GetFieldValue<List<double>> If the list contains null, use DuckDBDataReader.GetFieldValue<List<double?>>, otherwise an exception will be thrown when null is encountered. If you don't know whether the list contains null or not but want to skip all null values, you can use select [x for x in mylist if x IS NOT NULL] as filtered; to remove null values from the list.

Nested List can be read in a similar way: reader.GetFieldValue<List<List<int>>>

Check Tests for more examples.

Executing multiple statements in a single go.

Starting from version 0.8, you can execute multiple statements in a single go:

 using var command = duckDBConnection.CreateCommand();

 command.CommandText = "INSTALL 'httpfs'; Load 'httpfs';";
 command.ExecuteNonQuery();

To consume multiple result sets use NextResult:

using var duckDbCommand = connection.CreateCommand();
duckDbCommand.CommandText = "Select 1; Select 2";

using var reader = duckDbCommand.ExecuteReader();

reader.Read();
var firstValue = reader.GetInt32(0);

reader.NextResult();

reader.Read();

var secondResult = reader.GetInt32(0);

Connection String Parameters

DuckDB.NET connection strings follow the standard ADO.NET syntax as a semicolon-separated list of keywords and values. The connection string must specify the database path to the database file with either DataSource or Data Source keyword and can also contain one or more configuration options supported by DuckDB.

For example, to limit DuckDB memory usage to 16 GB use Data Source=file.db;memory_limit=16GB. To set number of threads used by the system use Data Source=file.db;threads=8

To specify both options use: Data Source=file.db;memory_limit=16GB;threads=8

DuckDB.NET connection string keywords are case insensitive.

Dapper

You can also use Dapper to query data:

var item = duckDBConnection.Query<FooBar>("SELECT foo, bar FROM integers");

In-Memory database

For an in-memory database use Data Source=:memory: connection string. When using an in-memory database no data is persisted on disk. Every in-memory connection results in a new, isolated database so tables created inside one in-memory connection aren't visible to another in-memory connection. If you want to create a shared in-memory database, you can use DataSource=:memory:?cache=shared connection string. Both connection strings are exposed by the library as DuckDBConnectionStringBuilder.InMemoryDataSource and DuckDBConnectionStringBuilder.InMemorySharedDataSource respectively.

Use low-level bindings library

dotnet add package DuckDB.NET.Bindings.Full
var result = Startup.DuckDBOpen(null, out var database);

using (database)
{
  result = Startup.DuckDBConnect(database, out var connection);
  using (connection)
  {
    var queryResult = new DuckDBResult();
    result = Query.DuckDBQuery(connection, "CREATE TABLE integers(foo INTEGER, bar INTEGER);", null);
    result = Query.DuckDBQuery(connection, "INSERT INTO integers VALUES (3, 4), (5, 6), (7, 8);", null);
    result = Query.DuckDBQuery(connection, "SELECT foo, bar FROM integers", queryResult);

    PrintQueryResults(queryResult);

    result = PreparedStatements.DuckDBPrepare(connection, "INSERT INTO integers VALUES (?, ?)", out var insertStatement);

    using (insertStatement)
    {
      result = PreparedStatements.DuckDBBindInt32(insertStatement, 1, 42); // the parameter index starts counting at 1!
      result = PreparedStatements.DuckDBBindInt32(insertStatement, 2, 43);

      result = PreparedStatements.DuckDBExecutePrepared(insertStatement, null);
    }


    result = PreparedStatements.DuckDBPrepare(connection, "SELECT * FROM integers WHERE foo = ?", out var selectStatement);

    using (selectStatement)
    {
      result = PreparedStatements.DuckDBBindInt32(selectStatement, 1, 42);

      result = PreparedStatements.DuckDBExecutePrepared(selectStatement, queryResult);
    }

    PrintQueryResults(queryResult);

    // clean up
    Query.DuckDBDestroyResult(queryResult);
  }
}

private static void PrintQueryResults(DuckDBResult queryResult)
{
  var columnCount = Query.DuckDBColumnCount(queryResult);
  for (var index = 0; index < columnCount; index++)
  {
    var columnName = Query.DuckDBColumnName(queryResult, index).ToManagedString(false);
    Console.Write($"{columnName} ");
  }

  Console.WriteLine();

  var rowCount = Query.DuckDBRowCount(queryResult);
  for (long row = 0; row < rowCount; row++)
  {
    for (long column = 0; column < columnCount; column++)
    {
      var val = Types.DuckDBValueInt32(queryResult, column, row);
      Console.Write(val);
      Console.Write(" ");
    }

    Console.WriteLine();
  }
}

Sponsors

A big thanks to AWS Open Source Software Fund for sponsoring the project!

AWS

Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.
  • .NETStandard 2.0

    • No dependencies.
  • net6.0

    • No dependencies.

NuGet packages (1)

Showing the top 1 NuGet packages that depend on DuckDB.NET.Bindings:

Package Downloads
DuckDB.NET.Data

DuckDB ADO.NET Provider for C#. This package does not include a copy of the native DuckDB library.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
0.10.1 124 3/18/2024
0.9.2 89,179 11/14/2023
0.9.1 26,293 10/13/2023
0.9.0.3 5,330 10/4/2023
0.9.0 1,045 10/2/2023
0.8.1 94,348 7/8/2023
0.8.0 16,830 5/17/2023
0.7.1 541 3/4/2023
0.6.1 243 1/8/2023
0.4.0.10 1,549 9/3/2022
0.4.0.1 1,371 7/6/2022
0.4.0 816 6/27/2022
0.2.9.1 2,749 9/24/2021
0.2.9 573 9/15/2021
0.1.3 578 9/3/2021
0.1.2 747 4/12/2021
0.1.1 575 4/9/2021
0.1.0 834 11/12/2020

Update to DuckDB 0.10.1.