Which is best way to store data into database.

Question

1.00/5 (1 vote)

See more:

hello,
Here is json which i want to store into database but the field "tagId" which has int datatype ,which accept multiple value from front end .so which way is best to solve this problem.

What I have tried:

{ 
"actiontaken":"safsaffsa",
"descript":"safsafaf",
"equip_type":"false",
"equipment":"afsafsa",
"history":"afasfd",
"licensor":"sss",
"netw_section":"tsadsarue",
"para_observation":"safdsaf",
"plantarea":"afssa",
"priorities":"tsdsrue",
"statuss":"Escalate",
"subject":"dsafsa",
"tagId":[{
  "tagId":4
},
{
  "tagId":4
}
]
}

Posted 2-Jun-17 4:32am

Member 12270370

Updated 2-Jun-17 9:46am

Add a Solution

Comments

F-ES Sitecore 2-Jun-17 10:50am

Have a one-to-many relationship with the main data in the json being in the "one" table and the tag ids in the "many". To preempt your next question, google "sql one to many relationship" for specific details.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2017-06-02T07:22:00

Solution 1

I would recommend PostgreSQL which has a special json field, see: PostgreSQL JSON Tutorial[^]
We have migrated from SQL Server to PostgreSQL a year ago and never regretted it.
We use C# in combination with the NpgSql driver for PostgreSQL.

If you want to try PostgreSQL, there is also a portable version available.
You can also try SQL Fiddle, they have a nice PostgreSQL example: SQL Fiddle[^]

Posted 2-Jun-17 7:22am

RickZeeland

Updated 9-Jun-17 22:05pm

v2

Comments

Richard Deeming 2-Jun-17 17:39pm

If you migrated just for the JSON support, you left marginally too soon! :D

(See solution #2, and: JSON support in SQL Server 2016[^])

RickZeeland 3-Jun-17 3:21am

No, we migrated to PostgreSQL because we had performance problems with big data and our customers did not want to fork out for extra servers etc.
Also PostgreSQL is much more compact and easier to deploy with our package.
But it is clear that Microsoft is feeling the hot breath of the PostgreSQL dragon as they are implementing these features too.

Wendelius · Answer 2 · 2017-06-02T09:46:00

Solution 2

If you're using SQL Server 2016, you can for example store the JSON in a varchar column and use the built in functions to query the data.

Have a look at Cutting Edge - Query JSON Data in SQL Server 2016[^]
and JSON Data (SQL Server) | Microsoft Docs[^]

Posted 2-Jun-17 9:46am

Wendelius