store json data in database using C#

Question

temp= {
       "Vikas": 1,
       "Pravin": 2,
       "Akshay": 5,
       "Vijay": 3,
       "Prasad": 4 
      }

This is value I am getting in temp variable I want to store this data in database with one additional column like following

name      rank   createdby
vikas      1       nitin
pravin     2       nitin
akshay     5       nitin
vijay      3       nitin
Prasad     4       nitin


Note:  name column is employee name so it is anything

I tried like getting in C#

[System.Web.Services.WebMethod]
public static string setAnswer(string jsondata)
{
    List<string[]> data = JsonConvert.DeserializeObject<List<string[]>>(jsondata);
}

Question.

1) how to get this json data in C# and add additional column

2) Is it possible to pass whole data using multiple row insert in one query instead of using loop and insert single row.

depends on the database you are using. If the db allows bulkinsert its definitly possible. to add a row to an array you have to copy the whole array. — Sebastian L, Feb 06 '17 at 11:00
it is possible or not using sql server 2008 and how to do my it first question — User, Feb 06 '17 at 11:01
yes to avoid for loop you can use table valued parameters you need to convert list to datatable check this link https://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/ let me know if you require more help — Chintan Udeshi, Feb 06 '17 at 11:02
@Chintan Udeshi please give example how to pass value for such procedure using C# — User, Feb 06 '17 at 11:08
I assume `temp = ` is your way of saying that you have a variable with that content, not that this is part of the payload/string because this part is not legal json. — Lasse V. Karlsen, Feb 06 '17 at 11:10
@Pravin check this link http://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure — Chintan Udeshi, Feb 06 '17 at 11:27

BWA · Answer 1 · 2017-02-06T11:16:44.563

3

Edited after explantions:

You can deserialize it to dictionary in this way:

var data = JsonConvert.DeserializeObject<Dictionary<string, int>>(temp);

I this point we have dictionary with data. How to save it into DB depends how you access to db.

edited Feb 06 '17 at 11:16

answered Feb 06 '17 at 11:03

BWA

5,672
7
34
45

thanks for reply but no..no... this is employee name so i cannot make it static it may be anything so this is not possible to make class – User Feb 06 '17 at 11:06
A dictionary of string to int is more appropriate, `Dictionary`. – Lasse V. Karlsen Feb 06 '17 at 11:08
@Pravin you don say it, this is very imprtant thing for solution. Edit question and add this info. – BWA Feb 06 '17 at 11:08
@LasseV.Karlsen with this info you are right – BWA Feb 06 '17 at 11:09
okk I will add in question – User Feb 06 '17 at 11:09

score 3 · Accepted Answer · answered Feb 06 '17 at 11:13

The correct way to deserialize this content:

{
    "Vikas": 1,
    "Pravin": 2,
    "Akshay": 5,
    "Vijay": 3,
    "Prasad": 4 
}

Is to deserialize it into a Dictionary<string, int>.

Here's a LINQPad example:

void Main()
{
    const string json = @"{
        ""Vikas"": 1,
        ""Pravin"": 2,
        ""Akshay"": 5,
        ""Vijay"": 3,
        ""Prasad"": 4
    }";
    Dictionary<string, int> empsAndNumbers =
        JsonConvert.DeserializeObject<Dictionary<string, int>>(json);
    empsAndNumbers.Dump();
}

which outputs:

Dronacharya · Answer 3 · 2017-02-06T11:21:25.953

2

Create an Entity : MemberRank

public class MemberRank
{
    public int Id { get; set; }
    public string MemberName { get; set; }
    public int Rank{ get; set; }
    public string CreatedBy { get; set; }
}

Then in Controller,

var membersText = File.ReadAllText(Path.Combine(envContentRootPath, @"PathOfTheFile"));
var members = JsonConvert.DeserializeObject<List<MemberRank>>(membersText);
members.ForEach(cb => cb.CreatedBy = "nitin");
_dbContext.Set<MemberRank>().AddRange(members);
_dbContext.SaveChanges();

For this to work, you need to change your json file as follows:

[
  {
     "memberName" : "Vikas",
     "rank" : 1
  },
  {
     "memberName" : "Pravin",
     "rank" : 2
  },
  {
     "memberName" : "Akshay",
     "rank" : 5
  },
  {
     "memberName" : "Vijay",
     "rank" : 3
  },
  {
     "memberName" : "Prasad",
     "rank" : 4
  }
]

edited Feb 06 '17 at 11:21

answered Feb 06 '17 at 11:12

Dronacharya

1,191
11
13

How do you get Json.net to deserialize the example json into your `MemberRank` type? – Lasse V. Karlsen Feb 06 '17 at 11:15
Where do you have in question info about EF? – BWA Feb 06 '17 at 11:19
I am assuming that he is using EF, Sir. – Dronacharya Feb 06 '17 at 11:23

store json data in database using C#

3 Answers3