Data Types

Table of Contents

Objects

The CrateDB Object Handler

This plugin provides a CrateDB object handler that allows you to read and write arbitrary CLR objects from and CrateDB columns.

To use this feature, import the Npgsql.CrateDb namespace and call the UseCrateDbObjectHandler extension method on the TypeMapper of your NpgsqlConnection, like so:

using Npgsql.CrateDb;

...

using (var con = OpenConnection())
{
    con.TypeMapper.UseCrateDbObjectHandler();

    ...
}

Reading

Reading Objects

Objects can be read with the GetObject<T>(NpgsqlDataReader, int) extension method, like so:

using (var command = new NpgsqlCommand("select my_json_column from my_table", con))
using (var reader = command.ExecuteReader())
{
    if (reader.Read())
    {
        // Returns an instance of type MyClrObject
        var obj = reader.GetObject<MyClrObject>(0);

        ...
    }
}

Note

The GetObject<T>(NpgsqlDataReader, int) extension method is in the Npgsql.CrateDb namespace.

Reading Object Arrays

Object arrays can be read with the GetObjectArray<T>(NpgsqlDataReader, int) extension method, like so:

...

using (var command = new NpgsqlCommand("select my_json_array_column from my_table", con))
using (var reader = command.ExecuteReader())
{
    if (reader.Read())
    {
        // Returns an instance of type MyClrObject[]
        var arr = reader.GetObjectArray<MyClrObject>(0);

        ...
    }
}

Note

The GetObjectArray<T>(NpgsqlDataReader, int) extension method is in the Npgsql.CrateDb namespace.

Writing Objects and Object Arrays

You can set arbitrary CLR objects and arrays as values for parameters referencing columns with a JSON or JSON array data type. For example:

...

command.Parameters.AddWithValue("@obj_field", NpgsqlTypes.NpgsqlDbType.Json,
                                new { inner = "Zoon" });
command.Parameters.AddWithValue("@obj_array", NpgsqlTypes.NpgsqlDbType.Json,
                                new object[] {
                                    new { inner = "Zoon1" },
                                    new { inner = "Zoon2" }
                                });
...

Internally, Newtonsoft.Json is used to serialize objects to JSON strings and vice versa.

Byte Arrays

A CrateDB byte array is read into .NET as an array of either int2 or char[] objects. Normally, to read those values into a byte[] object, you could do something like this:

char[] array = reader.GetFieldValue<char[]>(ordinal);
return array.Select(c => Convert.ToByte(c)).ToArray();

This plugin provides the GetBytes(NpgsqlDataReader, int) extension method, which achieves the same result.

So, you can read a byte array like this:

...

using (var command = new NpgsqlCommand("select my_byte_array_column from my_table", con))
using (var reader = command.ExecuteReader())
{
    if (reader.Read())
    {
        // Returns an array of bytes
        var arr = reader.GetBytes(0);

        ...
    }
}