Click here to Skip to main content
14,929,037 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi I am trying to get the address values from a simple post request to an API so I can read them an populate a SQL table. Items such as name etc I am able to read and write to the database, but each person has 2 addresses so these are in the json as an array (without square brackets). Is there an easy way to do this?

What I have tried:

My model looks like this

public class Customer
{
    // Properties of Create Customer with Get/Set
    public int customer_number { get; set; } // mandatory
    public string prefix { get; set; } // not mandatory
    public string firstname { get; set; } // mandatory
    public string surname { get; set; } // mandatory
    public string type { get; set; } // mandatory -- address type "Billing | Shipping"
    public string line_1 { get; set; } // mandatory
    public string line_2 { get; set; } // not mandatory
    public string line_3 { get; set; } // not mandatory
    public string city { get; set; } // not mandatory
    public string county { get; set; } //not mandatory
    public string post_code { get; set; } //mandatory
    public string country { get; set; } // not mandatory
    public char email_opt_in { get; set; } // mandatory
}

public class CreateCustomer : Customer
{
}


and my controller like this.

// POST api/<controller>
 public HttpResponseMessage Post([FromBody]CreateCustomer value)
 {
     _conCust = new SqlConnection(ConfigurationManager.ConnectionStrings["CAR_Connection"].ConnectionString);
     _conCust.Open();

     var a_query = "insert into cust ( Title, FirstName, Surname, Address1, Address2, Address3, Town, County, Country, " +
         "PostCode, Tel, Mobile, ACCreateDate, CreatedBy, CreatedDate, CreatedMachine) values " +
         "(@Title, @First, @Surname, @Add1, @Add2, @Add3, @Town, @County, @Country, @PostCode, @Tel, @Mobile, CONVERT(VARCHAR(10), getdate(), 111), " +
         "'WebAPI', getdate(), 'AUTOMATED') ";
     SqlCommand a_insertcommand = new SqlCommand(a_query, _conCust);
     a_insertcommand.Parameters.AddWithValue("@Title", value.prefix);
     a_insertcommand.Parameters.AddWithValue("@First", value.firstname);
     a_insertcommand.Parameters.AddWithValue("@Surname", value.surname);
     a_insertcommand.Parameters.AddWithValue("@Add1", ""); //value.line_1);
     a_insertcommand.Parameters.AddWithValue("@Add2", ""); // value.line_2);
     a_insertcommand.Parameters.AddWithValue("@Add3", ""); // value.line_3);
     a_insertcommand.Parameters.AddWithValue("@Town", ""); // value.city);
     a_insertcommand.Parameters.AddWithValue("@County", ""); // value.county);
     a_insertcommand.Parameters.AddWithValue("@Country", ""); // value.country);
     a_insertcommand.Parameters.AddWithValue("@PostCode", value.post_code[1]);
     a_insertcommand.Parameters.AddWithValue("@Tel", ""); // value.phone_number);
     a_insertcommand.Parameters.AddWithValue("@Mobile", ""); //  value.mobile_number);


     int a_result = a_insertcommand.ExecuteNonQuery();


At the moment to test the name saved correctly I've commented out the address values, inserting just a blank string instead.

I know I must be just missing something simple!

A post request would be similar to this...

{
  "customer_number": 1,
  "prefix": "Mr",
  "firstname": "Test",
  "surname": "User",
  "billing_address": {
    "type": "Billing",
    "line_1": "Street 1",
    "line_2": "Street 2",
    "line_3": "Street 3",
    "city": "AnyTown",
    "county": "Cumbria",
    "post_code": "CA1 4TY",
    "country": "GB",
    "is_default": "1"
  },
  "shipping_address": {
    "type": "Shipping",
    "line_1": "Street 1",
    "line_2": "Street 2",
    "line_3": "Street 3",
    "city": "Town",
    "county": "Cumbria",
    "post_code": "CA1 4TY",
    "country": "GB",
    "is_default": "0"
  },
  "email_opt_in": "0|1"
}


Any guidance would be appreciated.
Posted
Updated 12-Aug-20 0:55am

not sure what you mean by
Quote:
so these are in the JSON as an array (without square brackets).
.. it's either proper JSON or not, else thump the person who owns the API and tell them to sharpen up !! (It's really hard to work with bastardised pseudo JSON)

If your JSON is formatted correctly, then using a JSON to CSharp tool, eg Convert JSON to C# Classes Online - Json2CSharp Toolkit[^] you usually end up with a schema and a 'root' object, which in your case would hold something like
public class Root 
{
  ...
  List<Customer> customers {get; set;}
  ...
}
and then you use a deserialise
Root myDeserializedClass = JsonConvert.DeserializeObject(myJsonResponse);


If your JSON is 'proper' the generated C# class should have billing address and shipping address as separate entities is customer.billing_address.type vs customer.shipping_address.type - it seems your model is incorrect, it doesnt match the json snippet you show

[Edit] from your JSON snippet I generated this model
// Root myDeserializedClass = JsonConvert.DeserializeObject<Root>(myJsonResponse); 
    public class BillingAddress    {
        public string type { get; set; } 
        public string line_1 { get; set; } 
        public string line_2 { get; set; } 
        public string line_3 { get; set; } 
        public string city { get; set; } 
        public string county { get; set; } 
        public string post_code { get; set; } 
        public string country { get; set; } 
        public string is_default { get; set; } 
    }

    public class ShippingAddress    {
        public string type { get; set; } 
        public string line_1 { get; set; } 
        public string line_2 { get; set; } 
        public string line_3 { get; set; } 
        public string city { get; set; } 
        public string county { get; set; } 
        public string post_code { get; set; } 
        public string country { get; set; } 
        public string is_default { get; set; } 
    }

    public class Root    {
        public int customer_number { get; set; } 
        public string prefix { get; set; } 
        public string firstname { get; set; } 
        public string surname { get; set; } 
        public BillingAddress billing_address { get; set; } 
        public ShippingAddress shipping_address { get; set; } 
        public string email_opt_in { get; set; } 
    }
[/Edit]
   
v2
You treat properties in json that are other objects as properties in your c# classes that are classes that match the property's values. So in your case have an Address class that has the fields for the addresses and add a shipping_address and billing_address property

C#
public class Address
{
    public string type { get; set; } // mandatory -- address type "Billing | Shipping"
    public string line_1 { get; set; } // mandatory
    public string line_2 { get; set; } // not mandatory
    public string line_3 { get; set; } // not mandatory
    public string city { get; set; } // not mandatory
    public string county { get; set; } //not mandatory
    public string post_code { get; set; } //mandatory
    public string country { get; set; } // not mandatory
}

public class Customer
{
    // Properties of Create Customer with Get/Set
    public int customer_number { get; set; } // mandatory
    public string prefix { get; set; } // not mandatory
    public string firstname { get; set; } // mandatory
    public string surname { get; set; } // mandatory
    public char email_opt_in { get; set; } // mandatory
    public Address billing_address { get; set; }
    public Address shipping_address { get; set; }
}


When you deserialise your json it will create and populate the relevant address fields with the data from your json.
   
Comments
Garth J Lancaster 12-Aug-20 7:02am
   
I do like that you simplified the two generated 'Addresses' ie BillingAddress and ShippingAddress - I keep forgetting to do this ...
Bullgill Coder 12-Aug-20 9:08am
   
Thanks both.

I've changed my class to similar to the above. I think that was my biggest sticking point, and in my controller I now have whilst added the parameters into my SQL string. I've only tested so far with the postcode but it is successfully writing the data to my SQL table.

a_insertcommand.Parameters.AddWithValue("@PostCode", value.billing_address.post_code);

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