Insert JObject list to SQL Server with dapper

Question

I need to insert the result of my JObject to an SQL Database. However it keeps saying I'm not allowed to insert arrays, lists?

What am I doing wrong?

This is what I got so far:

JArray jsonArray = JArray.Parse(sb.ToString());

var jsonObjects = jsonArray.OfType<JObject>().ToList();

     using (SqlConnection connection = new SqlConnection(connectionstring))
         {                    
             connection.Open();
             connection.Execute(@"Insert into dbo.JsonMeta values(@meta)", jsonObjects);                   
         }

Result of jsonObjects:

Count: 57000 rows

row[0] looks like this:

{{
  "ID": "asdasdasdasdIndenlandsk",
  "FLYTTEDATO": "01/02/2020",
  "FLYTTE_TIDSPUNKT": "1000",
  "R�KKEF�LGE_FLYTNING": null,
  "FRA_CHR": "asdasdasdasd",
  "TIL_CHR": "asdasdasd",
  "ANTAL": "asdasdasdasd",
  "ANTAL_CONTAINERE": null,
  "INDBERETTER_LOGON": "asdasdasd:assdaasdasd",
  "BIL_ANVENDELSE": "FORVOGN",
  "REGISTRERINGSNUMMER FORVOGN": "XXXXXX",
  "BIL_NATIONALITET FORVOGN": "XXXXX",
  "FRAFLYT_NATION": null,
  "TILFLYT_NATION": null,
  "REGISTRERINGSNUMMER H�NGER": null,
  "BIL_NATIONALITET H�NGER": null,
  "REGISTRERINGSNUMMER OML�SSER FORVOGN": null,
  "BIL_NATIONALITET OML�SSER FORVOGN": null,
  "REGISTRERINGSNUMMER OML�SSER H�NGER": null,
  "BIL_NATIONALITET OML�SSER H�NGER": null,
  "TRACES DOKNR": null,
  "SLETMARKERING": "0",
  "DATO_OPRET": "2020-06-02 08:33:54",
  "BESNR_AFSENDER": "asdasdasdasdasd",
  "BESNR_MODTAGER": "asdasdasd",
  "BRUGER_AFSENDER_CVR_NR": "sdfsdf324234",
  "BRUGER_AFSENDER_NAVN": "asdasdasdasd",
  "BRUGER_AFSENDER_ADRESSE": "asdasdasdasd",
  "BRUGER_AFSENDER_BYNAVN": "adsasdasd",
  "BRUGER_AFSENDER_POSTNR": "adsasdasd",
  "BRUGER_AFSENDER_POSTDISTRIKT": "adsasdasd",
  "BRUGER_AFSENDER_REKLAMEBESKYTTET": null,
  "BRUGER_MODTAGER_CVR_NR": "adasdasdasd",
  "BRUGER_MODTAGER_NAVN": "asdasdasdasd",
  "BRUGER_MODTAGER_ADRESSE": "adasdasd",
  "BRUGER_MODTAGER_BYNAVN": "asdasdasd",
  "BRUGER_MODTAGER_POSTNR": "adasdasd",
  "BRUGER_MODTAGER_POSTDISTRIKT": "asdasdasd",
  "BRUGER_MODTAGER_REKLAMEBESKYTTET": null,
  "BESSTR_SOER": "0",
  "BESSTR_SLAGTESVIN": "asdasdasdasdasd",
  "BESSTR_SMAAGRISE": "0",
  "ANTAL_SLAGTESVIN_DAKA": null,
  "ANTAL_SOER_DAKA": null,
  "PRAKSIS_NR": "asdasdasd",
  "PRAKSIS_NAVN": "asdasdasdasdasd",
  "PRAKSIS_ADRESSE": "asdasdasdasdasd",
  "PRAKSIS_BYNAVN": null,
  "PRAKSIS_POSTNR": "asdasdasd",
  "PRAKSIS_POSTDISTRIKT": "asdasdasdasdasd"
}}

Error:

Exception Type: System.InvalidOperationException
TargetSite: CacheInfo GetCacheInfo(Identity, System.Object, Boolean)
Message: An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context
Data: System.Collections.ListDictionaryInternal
HelpLink: NULL
Source: Dapper
HResult: -2146233079

score 2 · Accepted Answer · edited Jun 12 '20 at 22:36

2

This question probably needs more context, at the moment we don't know what your database is or looks like.

For example are you using MySQL and trying to store just the JSON as a string? Or are you trying to store the JSON object into a table the has the same number of columns?

JArray jsonArray = JArray.Parse(sb.ToString());
var sb = new StringBuild("Insert into dbo.JsonMeta values");
for(int i =0; i < jsonArray.Length; i++)
{
  sb.AppendLine($"(@meta{i}),");
}
using (SqlConnection connection = new SqlConnection(connectionstring))
{                    
  connection.Open();
  connection.Execute(sb.ToString().TrimEnd(','), jsonArray.OfType<JObject>().Select(item => item.ToStirng()).ToArray());                   
}

edited Jun 12 '20 at 22:36

Dale K

25,246
15
42
71

answered Jun 12 '20 at 20:45

ChrisBooth

66
2

Only as string - not into same amout of columns – SqlKindaGuy Jun 12 '20 at 20:47
A tag does not enlightenment make. – Robert Harvey Jun 12 '20 at 20:47
You'll either need to foreach the array and add each element, and add each item in the array to the table, please note you'll need to convert the array to string. – ChrisBooth Jun 12 '20 at 20:52
There is no way around the foreach? – SqlKindaGuy Jun 12 '20 at 21:01
I just wrote this "connection.Execute(@"Insert into dbo.JsonMeta values(@meta)", new {meta = jsonObjects.ToArray().ToString() } );" first time i didnt get an error. But it inserted this record "Newtonsoft.Json.Linq.JObjecet[] instead :D – SqlKindaGuy Jun 12 '20 at 21:03
You might be able to do this with something like the LINQ Aggregate function: https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.aggregate?view=netcore-3.1 – ChrisBooth Jun 14 '20 at 18:06

score 0 · Answer 2 · answered Jun 12 '20 at 20:47

0

Dapper.Contrib supports insertion of a list of items.

connection.Insert(jsonObjects);

Of course, you would have to provide an appropriate DTO. I'm not sure that JObject qualifies.

answered Jun 12 '20 at 20:47

Robert Harvey

178,213
47
333
501

Insert JObject list to SQL Server with dapper

2 Answers2