Unsupport for Boolean and Array(T) type data in ClickHouseDB insertion.

[MohammadAliSO]

Boolean

When inserting data into the Click House database, if a Boolean parameter is defined in the database table, it gives an error. I built the code and noticed that bool is mapped to Int8 in the types dictionary.

1. It should be converted to Boolean.
2. Also, this is not true (Int8) , it is true(UInt8)

In class: ClickHouseCodeFirst.cs :

static Dictionary<string, CsToDb<DbType>> _dicCsToDb = new Dictionary<string, CsToDb<DbType>>()
        {
            { typeof(bool).FullName, CsToDb.New(DbType.SByte, "Int8", "Int8", null, false, false) },
            { typeof(bool?).FullName, CsToDb.New(DbType.SByte, "Int8", "Nullable(Int8)", null, true, null) },

            { typeof(sbyte).FullName, CsToDb.New(DbType.SByte, "Int8", "Int8", false, false, 0) },
            { typeof(sbyte?).FullName, CsToDb.New(DbType.SByte, "Int8", "Nullable(Int8)", false, true, null) },
            { typeof(short).FullName, CsToDb.New(DbType.Int16, "Int16", "Int16", false, false, 0) },
            { typeof(short?).FullName, CsToDb.New(DbType.Int16, "Int16", "Nullable(Int16)", false, true, null) },
            { typeof(int).FullName, CsToDb.New(DbType.Int32, "Int32", "Int32", false, false, 0) },
            { typeof(int?).FullName, CsToDb.New(DbType.Int32, "Int32", "Nullable(Int32)", false, true, null) },
            { typeof(long).FullName, CsToDb.New(DbType.Int64, "Int64", "Int64", false, false, 0) },
            { typeof(long?).FullName, CsToDb.New(DbType.Int64, "Int64", "Nullable(Int64)", false, true, null) },

            { typeof(byte).FullName, CsToDb.New(DbType.Byte, "UInt8", "UInt8", true, false, 0) },
            { typeof(byte?).FullName, CsToDb.New(DbType.Byte, "UInt8", "Nullable(UInt8)", true, true, null) },
            { typeof(ushort).FullName, CsToDb.New(DbType.UInt16, "UInt16", "UInt16", true, false, 0) },
            { typeof(ushort?).FullName, CsToDb.New(DbType.UInt16, "UInt16", "Nullable(UInt16)", true, true, null) },
            { typeof(uint).FullName, CsToDb.New(DbType.UInt32, "UInt32", "UInt32", true, false, 0) },
            { typeof(uint?).FullName, CsToDb.New(DbType.UInt32, "UInt32", "Nullable(UInt32)", true, true, null) },
            { typeof(ulong).FullName, CsToDb.New(DbType.UInt64, "UInt64", "UInt64", true, false, 0) },
            { typeof(ulong?).FullName, CsToDb.New(DbType.UInt64, "UInt64", "Nullable(UInt64)", true, true, null) },

            { typeof(double).FullName, CsToDb.New(DbType.Double, "Float64", "Float64", false, false, 0) },
            { typeof(double?).FullName, CsToDb.New(DbType.Double, "Float64", "Nullable(Float64)", false, true, null) },
            { typeof(float).FullName, CsToDb.New(DbType.Single, "Float32", "Float32", false, false, 0) },
            { typeof(float?).FullName, CsToDb.New(DbType.Single, "Float32", "Nullable(Float32)", false, true, null) },
            {
                typeof(decimal).FullName,
                CsToDb.New(DbType.Decimal, "Decimal(38, 19)", "Decimal(38, 19)", false, false, 0)  //Nullable(Decimal(38, 19))
            },
            {
                typeof(decimal?).FullName,
                CsToDb.New(DbType.Decimal, "Nullable(Decimal(38, 19))", "Nullable(Decimal(38, 19))", false, true, null)
            },

            {
                typeof(DateTime).FullName,
                CsToDb.New(DbType.DateTime, "DateTime('Asia/Shanghai')", "DateTime('Asia/Shanghai')", false, false,
                    new DateTime(1970, 1, 1))
            },
            {
                typeof(DateTime?).FullName,
                CsToDb.New(DbType.DateTime, "DateTime('Asia/Shanghai')", "Nullable(DateTime('Asia/Shanghai'))", false,
                    true, null)
            },

            { typeof(string).FullName, CsToDb.New(DbType.String, "String", "String", false, null, "") },
            { typeof(char).FullName, CsToDb.New(DbType.String, "String", "String", false, false, "") },
            { typeof(char?).FullName, CsToDb.New(DbType.Single, "String", "Nullable(String)", false, true, null) },
            { typeof(Guid).FullName, CsToDb.New(DbType.String, "String", "String", false, false, Guid.Empty) },
            { typeof(Guid?).FullName, CsToDb.New(DbType.String, "String", "Nullable(String)", false, true, null) },
        };

this line:

 { typeof(bool).FullName, CsToDb.New(DbType.SByte, "Int8", "Int8", null, false, false) },
 { typeof(bool?).FullName, CsToDb.New(DbType.SByte, "Int8", "Nullable(Int8)", null, true, null) },

Why does this problem occur, what is your solution?

Array(T) type

When inserting data, if the data type is an Array, strangely, you don't consider it at all in the query, and you return it to null in the code. What is the cause?
You also don't support this type at all.
In class: ClickHouseCodeFirst.cs :

 public override DbInfoResult GetDbInfo(Type type)
        {
            if (_dicCsToDb.TryGetValue(type.FullName, out var trydc))
                return new DbInfoResult((int)trydc.type, trydc.dbtype, trydc.dbtypeFull, trydc.isnullable,
                    trydc.defaultValue);
            if (type.IsArray)
                return null;
            return null;
        }

Why does this problem occur, what is your solution?

. net core？DotNet6

Tasks

Beta Give feedback

No tasks being tracked yet.

[2881099]

1. The reason for bool mapping int8 is misleading by Chinese documents. Next version fix.

https://clickhouse.com/docs/zh/sql-reference/data-types/boolean

2. The current version does not provide support for array types.

[ZUOXIANGE]

support array types +1

[2881099]

v3.2.806-preview20231130

• Support for Boolean and Array(T) type data in ClickHouseDB

c# bool、int[]、string[]

[d4ilys]

Note that the insertion of array types needs to be unparameterized

_fsql.Insert(new table { tags1 = new[] {"a", "b" }}).NoneParameter().ExecuteAffrows();
//or
_fsql.Insert(new table { tags1 = new[] {"a", "b" }}).ExecuteBulkCopy();