question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked karenpayneoregon answered

Parse JSON Data For Insert Into SQL Table

I know I can create classes from the JSON data, and parse out the elements. Butttt how do I then insert into MS SQL Server tables?

Sample JSON Data, and parsing - let's say I have a MS SQL Server table called dbo.Account that has the same columns as the C# Class. How would I "map" the data from the JSON to the SQL Table?

 public class Account
 {
     public string Email { get; set; }
     public bool Active { get; set; }
     public DateTime CreatedDate { get; set; }
     public IList<string> Roles { get; set; }
 }
    
    
 string json = @"{
   'Email': 'james@example.com',
   'Active': true,
   'CreatedDate': '2013-01-20T00:00:00Z',
   'Roles': [
     'User',
     'Admin'
   ]
 }";
    
 Account account = JsonConvert.DeserializeObject<Account>(json);
    
 Console.WriteLine(account.Email);
dotnet-csharpdotnet-entity-framework-core
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

This is simple with EF Core.

Model

 public partial class Account
 {
     public int Id { get; set; }
     public string Email { get; set; }
     public bool? Active { get; set; }
     public DateTime? CreatedDate { get; set; }
     public string[] Roles { get; set; }
 }

In OnModelCreating we use HasConversion to handle Roles array

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
     modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");
     modelBuilder.Entity<Account>()
         .Property(e => e.Roles)
         .HasConversion(
             v => string.Join(',', v),
             v => v.Split(',', 
                 StringSplitOptions.RemoveEmptyEntries));
     OnModelCreatingPartial(modelBuilder);
 }


Example code to add an account

 using System;
 using AccountsEntityFramework.Models;
 using Newtonsoft.Json;
    
 namespace AccountsEntityFramework
 {
     partial class Program
     {
         static void Main(string[] args)
         {
    
             using var context = new Context.Context();
             var account = IncomingAccount();
             context.Add(account);
             context.SaveChanges();
             Console.WriteLine($"Id for new account {account.Id}");
             Console.ReadLine();
         }
    
         static Account IncomingAccount()
         {
             Account account = new ()
             {
                 Active = true,
                 CreatedDate = new DateTime(2013,1,20),
                 Email = "james@example.com",
                 Roles = new []{"User", "Admin"}
             };
    
    
             return JsonConvert.DeserializeObject<Account>(
                 JsonConvert.SerializeObject(account, Formatting.Indented));
         }
     }
 }



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JackJJun-MSFT avatar image
0 Votes"
JackJJun-MSFT answered JackJJun-MSFT edited

@InigoMontoya-1790, Welcome to Microsoft Q&A, you could try the following code to parse json data to insert data to sql table.

Code example:

 string json = @"{
    'Email': 'james@example.com',
    'Active': true,
    'CreatedDate': '2013-01-20T00:00:00Z',
    'Roles': [
      'User',
      'Admin'
    ]
  }";
    
             Account account = JsonConvert.DeserializeObject<Account>(json);
             string connstr = @"connstr";
             SqlConnection connection = new SqlConnection(connstr);
             connection.Open();
             string listvalue = string.Join(",", account.Roles);
             string sql = "insert into Account(Email,Active,CreatedDate,Roles) values(@Email,@Active,@CreatedDate,@Roles)";
             SqlCommand cmd = new SqlCommand(sql, connection);
             cmd.Parameters.AddWithValue("@Email",account.Email);
             cmd.Parameters.AddWithValue("@Active", account.Active);
             cmd.Parameters.AddWithValue("@CreatedDate", account.CreatedDate);
             cmd.Parameters.AddWithValue("@Roles",listvalue);
             cmd.ExecuteNonQuery();
             connection.Close();

Result in database:

206078-image.png

Hope my code could help you.


Best Regards,
Jack


If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


image.png (5.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.