Click here to Skip to main content
15,891,431 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to parse a multilayer json from url and then the parsed json rootobject in datatables for insert and update command in c#.
By far its inserting values into database but what i need is that this below code should first get convert into datatable
C#
<pre>var table = JsonConvert.DeserializeObject<RootObject>(json);


What I have tried:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebApplication3.Models
{
    public class Customer
    {
    }

    public class Meta
    {
        public string type { get; set; }
        public string date { get; set; }
        public string schemaUrl { get; set; }
    }

    public class BillingContact
    {
        public string language { get; set; }
        public string languageId { get; set; }
        public string locale { get; set; }
        public string company { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string companyRomanized { get; set; }
        public string firstnameRomanized { get; set; }
        public string lastnameRomanized { get; set; }
        public string street1 { get; set; }
        public string street2 { get; set; }
        public string postalcode { get; set; }
        public string city { get; set; }
        public string state { get; set; }
        public string stateId { get; set; }
        public string stateIsoCode { get; set; }
        public string country { get; set; }
        public string countryId { get; set; }
        public string email { get; set; }
    }

    public class DeliveryContact
    {
        public string language { get; set; }
        public string languageId { get; set; }
        public string locale { get; set; }
        public string company { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string companyRomanized { get; set; }
        public string firstnameRomanized { get; set; }
        public string lastnameRomanized { get; set; }
        public string street1 { get; set; }
        public string street2 { get; set; }
        public string postalcode { get; set; }
        public string city { get; set; }
        public string state { get; set; }
        public string stateId { get; set; }
        public string stateIsoCode { get; set; }
        public string country { get; set; }
        public string countryId { get; set; }
        public string email { get; set; }
    }

    public class LicenseeContact
    {
        public string language { get; set; }
        public string languageId { get; set; }
        public string locale { get; set; }
        public string company { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string companyRomanized { get; set; }
        public string firstnameRomanized { get; set; }
        public string lastnameRomanized { get; set; }
        public string street1 { get; set; }
        public string street2 { get; set; }
        public string postalcode { get; set; }
        public string city { get; set; }
        public string state { get; set; }
        public string stateId { get; set; }
        public string stateIsoCode { get; set; }
        public string country { get; set; }
        public string countryId { get; set; }
        public string email { get; set; }
    }

    public class PaymentInfo
    {
        public string currency { get; set; }
        public string currencyId { get; set; }
        public string paymentType { get; set; }
        public string paymentTypeId { get; set; }
        public bool isPurchaseOrder { get; set; }
    }

    public class ProfitCalculation
    {
        public double grossRevenue { get; set; }
        public double collectedVat { get; set; }
        public double netRevenue { get; set; }
        public double cbMarginPercentage { get; set; }
        public double cbMarginFix { get; set; }
        public double yourNetProfit { get; set; }
        public double yourVat { get; set; }
        public double yourGrossProfit { get; set; }
    }

    public class ProductSinglePrice
    {
        public double netPrice { get; set; }
        public double vatPrice { get; set; }
        public double grossPrice { get; set; }
        public double vatPercentage { get; set; }
    }

    public class ProductTotalPrice
    {
        public double netPrice { get; set; }
        public double vatPrice { get; set; }
        public double grossPrice { get; set; }
        public double vatPercentage { get; set; }
    }

    public class TotalSinglePrice
    {
        public double netPrice { get; set; }
        public double vatPrice { get; set; }
        public double grossPrice { get; set; }
        public double vatPercentage { get; set; }
    }

    public class TotalTotalPrice
    {
        public double netPrice { get; set; }
        public double vatPrice { get; set; }
        public double grossPrice { get; set; }
        public double vatPercentage { get; set; }
    }

    public class YourPrice
    {
        public ProductSinglePrice productSinglePrice { get; set; }
        public ProductTotalPrice productTotalPrice { get; set; }
        public TotalSinglePrice totalSinglePrice { get; set; }
        public TotalTotalPrice totalTotalPrice { get; set; }
    }

    public class ProductSinglePrice2
    {
        public double netPrice { get; set; }
        public double vatPrice { get; set; }
        public double grossPrice { get; set; }
        public double vatPercentage { get; set; }
    }

    public class ProductTotalPrice2
    {
        public double netPrice { get; set; }
        public double vatPrice { get; set; }
        public double grossPrice { get; set; }
        public double vatPercentage { get; set; }
    }

    public class TotalSinglePrice2
    {
        public double netPrice { get; set; }
        public double vatPrice { get; set; }
        public double grossPrice { get; set; }
        public double vatPercentage { get; set; }
    }

    public class TotalTotalPrice2
    {
        public double netPrice { get; set; }
        public double vatPrice { get; set; }
        public double grossPrice { get; set; }
        public double vatPercentage { get; set; }
    }

    public class CustomerPrice
    {
        public ProductSinglePrice2 productSinglePrice { get; set; }
        public ProductTotalPrice2 productTotalPrice { get; set; }
        public TotalSinglePrice2 totalSinglePrice { get; set; }
        public TotalTotalPrice2 totalTotalPrice { get; set; }
    }

    public class Service
    {
        public string deliveryType { get; set; }
        public string deliveryTypeId { get; set; }
    }

    public class Delivery
    {
        public Service service { get; set; }
    }

    public class NextBillingProfit
    {
        public double grossRevenue { get; set; }
        public double collectedVat { get; set; }
        public double netRevenue { get; set; }
        public double cbMarginPercentage { get; set; }
        public double cbMarginFix { get; set; }
        public double yourNetProfit { get; set; }
        public double yourVat { get; set; }
        public double yourGrossProfit { get; set; }
    }

    public class RecurringBilling
    {
        public string subscriptionId { get; set; }
        public string subscriptionEventtypeId { get; set; }
        public int originalPurchaseId { get; set; }
        public int originalPurchaseItemRunningNumber { get; set; }
        public int intervalNumber { get; set; }
        public int gracePeriodDays { get; set; }
        public string statusId { get; set; }
        public string status { get; set; }
        public string itemStatusId { get; set; }
        public string itemStatus { get; set; }
        public string nextBillingDate { get; set; }
        public string cancellationUrl { get; set; }
        public string changePaymentSubscriptionUrl { get; set; }
        public NextBillingProfit nextBillingProfit { get; set; }
    }

    public class Item
    {
        public int runningNumber { get; set; }
        public int productId { get; set; }
        public string productName { get; set; }
        public string yourProductId { get; set; }
        public string yourProductName { get; set; }
        public string internalCategory { get; set; }
        public int supportContactId { get; set; }
        public string supportContact { get; set; }
        public int clientId { get; set; }
        public int quantity { get; set; }
        public string yourCurrency { get; set; }
        public string yourCurrencyId { get; set; }
        public ProfitCalculation profitCalculation { get; set; }
        public YourPrice yourPrice { get; set; }
        public CustomerPrice customerPrice { get; set; }
        public List<Delivery> deliveries { get; set; }
        public RecurringBilling recurringBilling { get; set; }
    }

    public class ExtraParameters
    {
    }

    public class RootObject
    {
        public Meta meta { get; set; }
        public int purchaseId { get; set; }
        public string status { get; set; }
        public string statusId { get; set; }
        public BillingContact billingContact { get; set; }
        public DeliveryContact deliveryContact { get; set; }
        public LicenseeContact licenseeContact { get; set; }
        public PaymentInfo paymentInfo { get; set; }
        public string creationTime { get; set; }
        public string paymentArriveTime { get; set; }
        public string lastModificationTime { get; set; }
        public string subscriptionRevenueCategoryId { get; set; }
        public string remoteAddress { get; set; }
        public string remoteHost { get; set; }
        public string httpUserAgent { get; set; }
        public string httpEntryUrl { get; set; }
        public string httpReferer { get; set; }
        public string httpAcceptLanguage { get; set; }
        public string internalCustomer { get; set; }
        public string customerConfirmationPageUrl { get; set; }
        public string customerPdfDocumentUrl { get; set; }
        public string merchantOfRecord { get; set; }
        public List<Item> items { get; set; }
        public ExtraParameters extraParameters { get; set; }
    }
}



<pre>I done it like this...its insering values in database...but not through datatable...can anyone share some code or procedure?
<pre> public void customerjson()
 {
            string url = @"https://api.myjson.com/bins/yxi3x";

            using (WebClient client = new WebClient())
            {
                string json = client.DownloadString(url);
                var table = JsonConvert.DeserializeObject<RootObject>(json);
            

            string sql = "";
            SqlConnection _connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStringDb"].ToString());

            sql = sql + "insert into ContactDetails (language, languageId, locale, company, firstname, lastname, companyRomanized, firstnameRomanized, lastnameRomanized, street1, street2, postalcode, city, state, stateId, stateIsoCode, country, countryId, email) values('"
                 + table.billingContact.language.ToString() + "','"
                 + table.billingContact.languageId.ToString() + "','"
                 + table.billingContact.locale.ToString() + "','"
                 + table.billingContact.company.ToString() + "','"
                 + table.billingContact.firstname.ToString() + "','"
                 + table.billingContact.lastname.ToString() + "','"
                 + table.billingContact.companyRomanized.ToString() + "','"
                 + table.billingContact.firstnameRomanized.ToString() + "','"
                 + table.billingContact.lastnameRomanized.ToString() + "','"
                 + table.billingContact.street1.ToString() + "','"
                 + table.billingContact.street2.ToString() + "','"
                 + table.billingContact.postalcode.ToString() + "','"
                 + table.billingContact.city.ToString() + "','"
                 + table.billingContact.state.ToString() + "','"
                 + table.billingContact.stateId.ToString() + "','"
                 + table.billingContact.stateIsoCode.ToString() + "','"
                 + table.billingContact.country.ToString() + "','"
                 + table.billingContact.countryId.ToString() + "','"
                 + table.billingContact.email.ToString() + "')";

            SqlCommand cmd = new SqlCommand(sql, _connection);
            _connection.Open();
            cmd.ExecuteNonQuery();
            _connection.Close();
            }
        }
Posted
Updated 27-Jul-17 1:59am
v3
Comments
Graeme_Grant 27-Jul-17 3:44am    
What problem are you having? Please be specific.
mridulkoul123 27-Jul-17 3:50am    
i want to convert this
RootObject rt = JsonConvert.DeserializeObject<rootobject>(getTestObjects().Result);
into datable.
e.g:
DataTable table = rt.billingContact;
F-ES Sitecore 27-Jul-17 4:25am    
google "c# convert object into datatable". It depends what you want to do with this datatable though, there might be better solutions.

If you are having problems with converting the raw JSON, then pass the raw JSON data to JSON Utils: Generate C#, VB.Net, SQL Table, Java and PHP from JSON[^] and it will generate the C# class files for you.

A DataTable is a "Flat View" and your data is not flat, it has multiple layers. If you still want to use a DataTable, you will need to translate the class structure into a single class object.

This will be a great help for you: DataTable Class - Microsoft documentation[^]
 
Share this answer
 
v3
Comments
BillWoodruff 27-Jul-17 5:21am    
+5 That on-line conversion site is a great resource !
Graeme_Grant 27-Jul-17 5:25am    
:)
Karthik_Mahalingam 27-Jul-17 5:28am    
5 for the link
Graeme_Grant 27-Jul-17 5:30am    
Thx! It is a great resource for the OP...
The error message is very clear: RootObject<DataTable> causes an error because 'RootObject is not a generic Type.

You need to first get your JSON result (string), then use the Newtonsoft.Json.Converters.DataTableConverter class facilities to convert it to a DataTable [^].

It's important, if you are creating production code, that you familiarize yourself with this library's facilities, and methods. And, possible limits:
Quote:
The builtin DataTable doesn't serialize column information, it has to infer the column type from the data. I've created an improved converter here [^]
 
Share this answer
 
Comments
Graeme_Grant 27-Jul-17 5:24am    
Didn't know that one as I work primarily in WPF/Xamarin/UWP... +5 :)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900