DuckDB.NET

To prevent silent data corruption caused by type mismatches (e.g., appending a decimal to a REAL column), use the DuckDBAppenderMap<T> pattern. This approach validates your .NET types against the actual database schema at the moment the appender is created.

1. Define the Map

Create a class inheriting from DuckDBAppenderMap<T>. You must define property mappings in the exact order they appear in the database table columns.

public class PersonMap : DuckDBAppenderMap<Person>
{
    public PersonMap()
    {
        Map(p => p.Id);            // Column 0
        Map(p => p.Name);          // Column 1
        Map(p => p.Height);        // Column 2
        Map(p => p.BirthDate);     // Column 3
    }
}

2. Use the Type-Safe Appender

Use the CreateAppender<T, TMap> method on your connection. If the types in your map do not match the database schema, an exception will be thrown immediately upon creation.

// Create table
connection.ExecuteNonQuery("CREATE TABLE person(id INTEGER, name VARCHAR, height REAL, birth_date TIMESTAMP)");

var people = new[] 
{
    new Person { Id = 1, Name = "Alice", Height = 1.65f, BirthDate = new DateTime(1990, 1, 15) }
};

// Type validation happens here at creation
using (var appender = connection.CreateAppender<Person, PersonMap>("person"))
{
    appender.AppendRecords(people);
}

DuckDB.NET provides an ADO.NET compatible interface. You can use DuckDBConnection to connect to a database file, create commands, and execute queries using standard methods like ExecuteNonQuery, ExecuteScalar, and ExecuteReader.

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();
  }
}