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

Dale K · Accepted Answer · 2020-06-12 22:36:54Z

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, 2020 at 22:36

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 12, 2020 at 20:45

ChrisBooth

662 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SqlKindaGuy Over a year ago

Only as string - not into same amout of columns

Robert Harvey Over a year ago

A tag does not enlightenment make.

ChrisBooth Over a year ago

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.

SqlKindaGuy Over a year ago

There is no way around the foreach?

SqlKindaGuy Over a year ago

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

|

Robert Harvey · Accepted Answer · 2020-06-12 20:47:04Z

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, 2020 at 20:47

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

Collectives™ on Stack Overflow

Insert JObject list to SQL Server with dapper

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related