Note: The library is in early stage and contributions are more than wellcome.
If you encounter a bug with the library Create an Issue. Join the DuckDB .Net Channel for DuckDB.NET related topics.
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 DuckDB native library and a managed-only library that doesn't include a native library.
| ADO.NET Provider | Includes DuckDB library | |
|---|---|---|
| Yes | DuckDB.NET.Data | DuckDB.NET.Data.Full |
| No | DuckDB.NET.Bindings | DuckDB.NET.Bindings.Full |
dotnet add package DuckDB.NET.Data.Fullusing (var duckDBConnection = new DuckDBConnection("Data Source=file.db"))
{
duckDBConnection.Open();
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();
}
}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();
}
}Starting from version 0.4.0.10, DuckDB.NET.Data supports executing parameterized queries and reading all built-in native duckdb types:
using var connection = new DuckDBConnection("DataSource=:memory:");
connection.Open();
var command = connection.CreateCommand();
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 |
| TIME | DuckDBTimeOnly |
| HUGEINT | BigInteger |
You can also use Dapper to query data:
var item = duckDBConnection.Query<FooBar>("SELECT foo, bar FROM integers");For in-memory database use Data Source=:memory: connection string. When using in-memory database no data is persisted on disk.
dotnet add package DuckDB.NET.Bindings.Fullvar result = Startup.DuckDBOpen(null, out var database);
using (database)
{
result = Startup.DuckDBConnect(database, out var connection);
using (connection)
{
result = Query.DuckDBQuery(connection, "CREATE TABLE integers(foo INTEGER, bar INTEGER);", out var queryResult);
result = Query.DuckDBQuery(connection, "INSERT INTO integers VALUES (3, 4), (5, 6), (7, 8);", out queryResult);
result = Query.DuckDBQuery(connection, "SELECT foo, bar FROM integers", out queryResult);
PrintQueryResults(queryResult);
result = Query.DuckDBPrepare(connection, "INSERT INTO integers VALUES (?, ?)", out var insertStatement);
using (insertStatement)
{
result = Query.DuckDBBindInt32(insertStatement, 1, 42); // the parameter index starts counting at 1!
result = Query.DuckDBBindInt32(insertStatement, 2, 43);
result = Query.DuckDBExecutePrepared(insertStatement, out var _);
}
result = Query.DuckDBPrepare(connection, "SELECT * FROM integers WHERE foo = ?", out var selectStatement);
using (selectStatement)
{
result = Query.DuckDBBindInt32(selectStatement, 1, 42);
result = Query.DuckDBExecutePrepared(selectStatement, out queryResult);
}
PrintQueryResults(queryResult);
// clean up
Query.DuckDBDestroyResult(out 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();
for (long row = 0; row < queryResult.RowCount; row++)
{
for (long column = 0; column < queryResult.ColumnCount; column++)
{
var val = Types.DuckDBValueInt32(queryResult, column, row);
Console.Write(val);
Console.Write(" ");
}
Console.WriteLine();
}
}