Parse JSON Data For Insert Into SQL Table

Inigo Montoya 586 Reputation points
2022-05-27T00:09:19.46+00:00

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);
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
689 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,098 questions
0 comments No comments
{count} votes

Accepted answer
  1. Karen Payne MVP 35,016 Reputation points
    2022-05-27T09:22:14.67+00:00

    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));  
            }  
        }  
    }  
    
    
    
      
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Jack J Jun 24,276 Reputation points Microsoft Vendor
    2022-05-27T06:58:17.783+00:00

    @Inigo Montoya , 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.

    0 comments No comments