I am trying to save JSON into my Postgres database from .NET Core.
This is my table :
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:
2
Answers
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
implementsIEnumerable
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’sJson.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.You can convert it to string, which provvides valid JSON