Insert JSON record into SQL Server database

Question

I have an open SqlConnection (extended by Dapper), a table name in the connected database, and a JSON string that I trust to be of the same schema. What's the simplest way to insert the JSON object's field values without deserializing to a static type?

I realize the standard option is to create a class representing the record to deserialize into. However, there are several reasons this is less than ideal. I'm syncing a number of tables in exactly the same way. The schema already exists in two places (the source and the target), so it seems poor form to repeat the schema in the middleware as well. Also, since I'm just going straight into the database, it seems excessive to require a recompile any time someone adds an additional column or table.

Is there a more dynamic solution?

That is interesting. Unfortunately the project there stores the result in a generic JSON Hierarchy table. I'm looking to insert into a particular table with the column names matching the JSON object properties. I could probably get there from here, but it would require some pretty ugly SQL. — couchand
– couchand, Commented Nov 12, 2013 at 13:39

Community · Accepted Answer · 2017-05-23 12:12:41Z

2

probably deserializing into a dynamic is your best bet. if you want to pull out the values directly from the string, you're going to have to (at least partially) parse it anyways, and at that point you might as well just deserialize it

See this answer for an example using JSON.net: Deserialize json object into dynamic object using Json.net

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Nov 13, 2013 at 21:41

rossipedia

59.8k10 gold badges95 silver badges94 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:19:11Z

0

Deserialize into a dictionary, then construct Dapper DynamicParameters.

How to create arguments for a Dapper query dynamically

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Nov 13, 2013 at 23:56

couchand

2,6491 gold badge23 silver badges27 bronze badges

Collectives™ on Stack Overflow

Insert JSON record into SQL Server database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related