skip to Main Content

I am trying to save JSON into my Postgres database from .NET Core.

This is my table :

enter image description here

This is my query:

string strCommandInsert = @"INSERT INTO  public.""City"" (""id"", ""externalId"", ""tags"", ""members"", ""geometry"") values (@id, @externalId, @tags, @members, @geometry);";

await using (var command = new NpgsqlCommand(strCommandInsert, conn))
{
    foreach (var customer in cities.elements)
    {
        var tags = JsonConvert.SerializeObject(customer.tags);
        command.Parameters.AddWithValue("@id", customer.id);
        command.Parameters.AddWithValue("@externalId", Guid.NewGuid());
        command.Parameters.AddWithValue("@tags", JObject.Parse(tags));

        int nRows = command.ExecuteNonQuery();

        await Console.Out.WriteLineAsync(String.Format("Number of rows inserted={0}", nRows));
    }
}

But I get this error:

Opening connection

Unhandled exception. System.NotSupportedException: IEnumerable parameters are not supported, pass an array or List instead

at Npgsql.Internal.TypeMapping.TypeMapper.g__ResolveLong|30_0(Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.ResolveByClrType(Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.g__ResolveLong|30_0(Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.ResolveByClrType(Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.g__ResolveLong|29_0(Object value, Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.ResolveByValue(Object value)
at Npgsql.NpgsqlParameter.g__Resolve|96_0(TypeMapper typeMapper)
at Npgsql.NpgsqlParameter.ResolveHandler(TypeMapper typeMapper)
at Npgsql.NpgsqlParameter.Bind(TypeMapper typeMapper)
at Npgsql.NpgsqlParameterCollection.ProcessParameters(TypeMapper typeMapper, Boolean validateValues, CommandType commandType)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at EazyCityCA.Startup.Main(String[] args) in /Users/alt/Projects/MyProjects/eazy_city/EazyCityCore/EazyCityCA/Program.cs:line 104
at EazyCityCA.Startup.Main(String[] args) in /Users/alt/Projects/MyProjects/eazy_city/EazyCityCore/EazyCityCA/Program.cs:line 107
at EazyCityCA.Startup.Main(String[] args) in /Users/alt/Projects/MyProjects/eazy_city/EazyCityCore/EazyCityCA/Program.cs:line 109
at EazyCityCA.Startup.(String[] args)

This tags json:

enter image description here

2

Answers


  1. If you want to pass a value through npgsql to a JSON column then you pass that value as a string, see https://www.npgsql.org/doc/types/basic.html?q=json

    The error message is somewhat confusing as it just happens that JObject implements IEnumerable and most often the coding error is that someone wanted to pass multiple values, so an array or list type should have been passed, but in your case you want to use the string instead.

    Npgsql has built-in support for System.Text.Json but requires a plugin for support for Newtonsoft’s Json.net https://www.npgsql.org/doc/types/jsonnet.html?tabs=datasource which would allow you to use the JObject directly, but since you already have the string and are parsing it that seems a bit redundant.

    Login or Signup to reply.
  2. You can convert it to string, which provvides valid JSON

    command.Parameters.AddWithValue("@tags", JObject.Parse(tags).ToString());
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search