How to write stored procedures for JSON Web API data-CodePudding

I haven't written a stored procedure as yet. I have just deserialised some JSON data from a Web API feed. I've got some console writelines to confirm the data is being deserialised, but need to replace them with writing stored procedures for each piece of data which can then be called from the DB in a view later on.

I've looked up writing stored procedures in VS but none look like they would work in this way. I'm assuming that within the foreach I need to execute a SqlParameter of some kind to write the data attribute from the JSON to the DB table and respective column. Just a little stuck on where to start.

My program to fetch data from the API looks like this.

private static async void UpdateStreetWebApiProperties()
{
       var client = new HttpClient();
       var request = new HttpRequestMessage
       {
           Method = HttpMethod.Get,
           RequestUri = new Uri("https://inventorymanchester.co.uk/api/property-feed/sales/search"),
           Headers =
{
    { "ContentType", "application/json" },
    { "Authorization", "Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJhdWQiOiIxIiwianRpIjoiNzU5YTJlZjZmYjY0MWE3NjRiZGE5ZjdmYzk2ZDNkMjAwN2IwNTk1MTMyZWJlYjhjMmQ0MTUyZDZlODcwZGI4ODRiOTFmMWEzMGE5NDA0ZWEiLCJpYXQiOjE2NTU3MzQzNjAuNDA5OTY1LCJuYmYiOjE2NTU3MzQzNjAuNDA5OTcxLCJleHAiOjE5NzEzNTM1NjAuMTE2MzYzLCJzdWIiOiIxMzM0MjIiLCJzY29wZXMiOlsicHVibGljLWFwaSJdfQ.kxo8GVwKFRUgfyhrRqUrh6bQvJvv5PfJgken90RulA_hTKuX4zWPs6fZ6RkljSK1ECsPYfeNKF5Z_E-xYekRWtDKOcWCXp-wKKNfHWA7rwVU1NzzeghszqcQ0ojLqcqiJ_sxFXCyouvP6LW1-jlcWmYUaHrBu2rajSSyo-K3RN_uFq85Fc5_XkCAvTzO4U59ZcNU4nhRvj3SG2b4l0kZPGlTePkJafciToZ-Q618DYDtO1Q63hUY6X6CunjIIKXiw8h1o9qG9vlmV60JBuVZ4COzc05H-e4UrA3tYkxa6O6xi3baczCZFr97bTIwXMRlifUh8E6WS29T_2uIjSl5OIcaXLuDhrxYOUrfTsFuGU2SuwKKflps0jAUir1s_T_Fi4sPiC5BqvvbrPSBHXlNQpguIN6nYCK4FXW2ixALjs5e2JKN5gMmOCFeqzEmZJAkrHR0iN7n-UbWwsX7dh8ODss3XprckzadC-f_EHiY4ZDSdKPPnJTukkA0V-ezafoO-hBPeiiPOo-IH3kqCyElUMv2sosV4yaFT8DhFlrivCAktwXz4vkKTUYhjuueWU-QxX1y2MwE0zT32SKb21Bwp4Z7nJ0Fa84_CglbWWZZVglJh6Rrgmmg17DgxpSJx3qCiqqum3UrKGTYuLTube47zjumFQeyy1cYUWmqcRG84OQ" },
},
       };

       using (var response = await client.SendAsync(request))
       {
           response.EnsureSuccessStatusCode();
           
           if (response.IsSuccessStatusCode)
           {
               var properties = await response.Content.ReadAsStringAsync();  
               string strBufJSON = properties;
               // above is web service results

               JsonModel.JSONModel.Rootobject jData;

               jData = JsonConvert.DeserializeObject<JsonModel.JSONModel.Rootobject>(strBufJSON);

               foreach (var data in jData.data)
               {
                   // now display some values
                   Console.WriteLine(data.attributes.public_url);
                   Console.WriteLine(data.attributes.inline_address);
                   Console.WriteLine(data.attributes.sale_status);
                   Console.WriteLine(data.attributes.bathrooms);
               }          
           }
           else
           {
               Console.WriteLine("Could not get properties");
           }
       }
}

I have outlined my table like this and matched my JSON data attributes to match the table columns in a hope it makes it easier. I'm running SQL Server 2014.

USE [TortoiseDB]
  GO

 /****** Object:  Table [dbo].[Tortoise_Street_Properties]    
Script Date: 07/26/2022 11:54:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Tortoise_Street_Properties](
[id] [int] IDENTITY(1,1) NOT NULL,
[Pid] [varchar](255) NULL,
[inlineaddress] [varchar](255) NULL,
[postcode] [varchar](20) NULL,
[description] [varchar](255) NULL,
[bedrooms] [varchar](255) NULL,
[price] [varchar](255) NULL,
[salestatus] [varchar](255) NULL,
[brochure] [varchar](255) NULL,
[longitude] [int] NULL,
[latitude] [int] NULL,
[virtualtour] [varchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

The JSON looks like the following also to add context:

{"data":[{"type":"property","id":"a91ab45e-5db8-4486-9bdf-f38dcb63c400","attributes":{"branch_uuid":"3e7a4a68-ab41-46c3-9a48-e3d1635cd056","inline_address":"101 London Road, Peterborough","public_address":"London Road, Peterborough, PE2","postcode":"PE2 9DD","bedrooms":5,"bathrooms":2,"receptions":2,"floor_area":null,"plot_area":null,"land_area":null,"property_type":"Detached House","property_age_bracket":null,"construction_year":null,"status":"For Sale","sale_status":"For Sale","lettings_status":null,"owner_label":"Vendor","tenure":null,"tenure_notes":null,"lease_expiry_year":null,"lease_expiry_date":null,"public_url":"https:\/\/inventorymanchester.co.uk\/platform\/properties\/a91ab45e-5db8-4486-9bdf-f38dcb63c400","created_at":"2022-06-17T15:18:53 01:00","updated_at":"2022-07-12T11:23:11 01:00","custom_meta_data":[],"property_urls":[],"viewing_booking_url":"https:\/\/inventorymanchester.co.uk\/platform\/properties\/a91ab45e-5db8-4486-9bdf-f38dcb63c400\/book-viewing"},"relationships":{"address":{"data":{"type":"address","id":"433518e4-d544-42ce-aba4-7d1137465af1"}},"details":{"data":{"type":"details","id":"1bf2b0fc-36c1-40f1-9e04-5b5cf72ffd0c"}},"salesListing":{"data":{"type":"sales_listing","id":"992114a6-3fcf-48b1-af1d-f5f3976a23da"}},"lettingsListing":{"data":null},"primaryImage":{"data":{"type":"media","id":"9ed40865-0873-4159-808b-5941faa520c9"}}}},{"type":"property","id":"4fd57964-71ea-4a77-b773-b4079a0f95dc","attributes":{"branch_uuid":"3e7a4a68-ab41-46c3-9a48-e3d1635cd056","inline_address":"4 Riverside Mead, Peterborough","public_address":"Riverside Mead, Peterborough, PE2","postcode":"PE2 8JN","bedrooms":4,"bathrooms":3,"receptions":2,"floor_area":null,"plot_area":null,"land_area":null,"property_type":"Detached House","property_age_bracket":null,"construction_year":null,"status":"Sold STC","sale_status":"Sold STC","lettings_status":null,"owner_label":"Vendor","tenure":null,"tenure_notes":null,"lease_expiry_year":null,"lease_expiry_date":null,"public_url":"https:\/\/inventorymanchester.co.uk\/platform\/properties\/4fd57964-71ea-4a77-b773-b4079a0f95dc","created_at":"2022-06-17T16:39:19 01:00","updated_at":"2022-07-19T11:39:26 01:00","custom_meta_data":[],"property_urls":[],"viewing_booking_url":"https:\/\/inventorymanchester.co.uk\/platform\/properties\/4fd57964-71ea-4a77-b773-b4079a0f95dc\/book-viewing"},"relationships":{"address":{"data":{"type":"address","id":"03d1a68a-6f4a-42ff-bf65-5b9768d6ce81"}},"details":{"data":{"type":"details","id":"f2b1a173-0611-4014-a980-894257b0bab0"}},"salesListing":{"data":{"type":"sales_listing","id":"be1cec3a-cf2f-40c4-a627-427cf3fbdfa7"}},"lettingsListing":{"data":null},"primaryImage":{"data":{"type":"media","id":"125542ce-27f1-4852-8fb6-b71daaaa70d1"}}}}],"included":[{"type":"address","id":"433518e4-d544-42ce-aba4-7d1137465af1","attributes":{"anon_address":"London Road, Peterborough, PE2","line_1":"101 London Road","line_2":"Peterborough","line_3":null,"town":"Peterborough","postcode":"PE2 9DD","inline":"101 London Road, Peterborough, PE2 9DD","longitude":-0.2465764,"latitude":52.560172}},{"type":"details","id":"1bf2b0fc-36c1-40f1-9e04-5b5cf72ffd0c","attributes":{"display_property_style":null,"work_required":null,"heating_system":null,"council_tax_band":null,"council_tax_cost":null,"local_authority":null,"service_charge":null,"service_charge_period":"month","service_charge_notes":null,"ground_rent":null,"ground_rent_period":"month","ground_rent_review_period_years":null,"ground_rent_uplift":null,"ground_rent_expiry":null,"full_description":"<p>Tortoise Property are pleased to offer this five bed detached house that is situated in the popular location of London Road, Fletton.<br><br>**Please call for either a viewing or virtual tour of this property.**<br><br>The property has a hallway, ground floor bathroom, bedroom, kitchen, dining room, lounge and converted garage on the ground floor. There are four bedrooms and the family bathroom on the first floor.<br><br>Outside the property has a front garden, a back garden and off-road parking for four cars.<br><\/p>","short_description":null,"location_summary":"London Road is a great location that is situated within walking distance of the city centre and local amenities. The Queensgate shopping centre is an 16 minute walk. The train station is a 21 minute walk or 5 minutes by car. The Kings secondary school is a 6 minute drive.\r\n\r\nPeterborough City Centre can be reached by car in 5 minutes and by bus in 10 minutes.\r\n\r\nThe A1 Junction can be reached by car in 11 minutes and the surrounding parkways give access to the A47 both east and west. \r\n\r\nWe love Fletton because of the lifestyle you can enjoy here. Great homes, close to nature and superb facilities make this one of our favourite places to live and work.","has_parking":null,"has_outdoor_space":null,"virtual_tour":null,"shared_ownership":false,"shared_ownership_notes":null,"shared_ownership_rent":null,"shared_ownership_rent_frequency":null,"shared_ownership_percentage_sold":null,"created_at":"2022-06-17T15:18:54 01:00","updated_at":"2022-06-17T15:28:08 01:00"}},{"type":"sales_listing","id":"992114a6-3fcf-48b1-af1d-f5f3976a23da","attributes":{"status":"For Sale","price":300000,"price_qualifier":"In Excess of","display_price":true,"archived":false,"is_low_profile":false,"occupancy_status":1,"new_home":false,"created_at":"2022-06-17T15:29:16 01:00","updated_at":"2022-06-17T15:29:57 01:00"}},{"type":"media","id":"9ed40865-0873-4159-808b-5941faa520c9","attributes":{"name":"136511_31517777_IMG_17_0000","order":0,"is_featured":true,"feature_index":1,"title":null,"is_image":true,"url":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844548\/136511_31517777_IMG_17_0000.jpeg","urls":{"thumbnail":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844548\/136511_31517777_IMG_17_0000.jpeg?tr=pr-true,n-property_thumb","small":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844548\/136511_31517777_IMG_17_0000.jpeg?tr=pr-true,n-property_small_fill_crop","medium":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844548\/136511_31517777_IMG_17_0000.jpeg?tr=pr-true,n-property_medium_fill_crop","large":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844548\/136511_31517777_IMG_17_0000.jpeg?tr=pr-true,n-property_large_fill_crop","hero":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844548\/136511_31517777_IMG_17_0000.jpeg?tr=pr-true,n-property_hero","full":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844548\/136511_31517777_IMG_17_0000.jpeg"}}},{"type":"address","id":"03d1a68a-6f4a-42ff-bf65-5b9768d6ce81","attributes":{"anon_address":"Riverside Mead, Peterborough, PE2","line_1":"4 Riverside Mead","line_2":"Peterborough","line_3":null,"town":"Peterborough","postcode":"PE2 8JN","inline":"4 Riverside Mead, Peterborough, PE2 8JN","longitude":-0.2305068,"latitude":52.5631968}},{"type":"details","id":"f2b1a173-0611-4014-a980-894257b0bab0","attributes":{"display_property_style":null,"work_required":null,"heating_system":null,"council_tax_band":null,"council_tax_cost":null,"local_authority":null,"service_charge":null,"service_charge_period":"month","service_charge_notes":null,"ground_rent":null,"ground_rent_period":"month","ground_rent_review_period_years":null,"ground_rent_uplift":null,"ground_rent_expiry":null,"full_description":"<p>Here at Tortoise Property, we pride ourselves on doing things differently, by offering a complete partnership and consistent approach to construct a comprehensive marketing package tailored for the single purpose of selling your property as agreed at the initial valuation.<br><br>\"Tortoise provided me with a comprehensive property management service over a four year period, offering a friendly, transparent and consistent relationship.<br><br>When I decided to sell my property I immediately engaged with Tortoise to undertake the action. They actively advertised my property, were proactive with local sale opportunities and provided timely updates on progress. The sale on my property was agreed, exchanged and completed within five weeks. I would highly recommend Tortoise Property for their professional and friendly approach.\" - Tracey Matthews - Testimonial <br><br>Valuations<br><br>We concentrate on the maximum price your house is likely to sell for then agree a sensible timeframe for which the property should be sold whilst clearly explaining how the fee you are charged, is invested in enabling us to find your buyer from across the country.<br><br>\"I recently used Tortoise to sell my house, Chris came round and went through everything there price was better than all others I had received and they seemed a lot more genuine.\" - James Richards - Testimonial <br><br>Relationship management<br><br>Our relationship managers are here to personally look after you offering complete transparency and guidance throughout the sales process, following a 12-week programme that provides regular viewings with prompt feedback.<br><br>\"They say selling your home can be very stressful, not with this team, there was never a time you could not get in touch with these guys. You will be in safe hands all the way from start to finish.\" - Maxine Ambrose - Testimonial <br><br>Facebook<br><br>The growth of our sales portfolio into the wider Peterborough area we believe is the result of our unique strategy to capture maximum exposure. Facebook provides us with the opportunity to target our property marketing and expand our reach beyond the property portals.<br><br>Facebook live<br><br>The potential reach of a digital tour is limitless. Our live feed property tours on facebook are great for potential buyers to not only view the property but to ask relevant questions and get instant replies from wherever they are based.<br><br>Online and traditional auctions<br><br>Our property auction service gives you the ability to sell your property at auction either online or at a live auction. The buyer pays a commission so your house is sold at no cost to you. The buyer must complete within 28 or 56 days meaning your property is sold fast.<br><br>Performance-related fees<br><br>Here at Tortoise we do offer traditional fee structures based on a standard percentage of the purchase price or a fixed fee. However, we are so good at what we do that we are confident enough to offer you performance related fees we believe we should win together.<br><br>24\/7 services<br><br>Property sales can be daunting, especially if it is your first time. Here at Tortoise, we have real people available to talk to 24 hours a day 7 days a week as well as a live web chat so that you can chat to someone at your convenience.<br><br><br><br><br><br>Negotiator awards<br><br>In 2017 Tortoise Property was shortlisted for website of the year in the negotiator awards competing with large national estate agency chains illustrating the quality and presentation of our brand and level of service.<br><br>Our micro-site offers plenty of information so please choose from one of the tabs on the left that is applicable to your requirements and we look forward to seeing you in the near future or to find out more about us and our services visit www.tortoise property.co.uk<br><\/p>","short_description":null,"location_summary":null,"has_parking":null,"has_outdoor_space":null,"virtual_tour":null,"shared_ownership":false,"shared_ownership_notes":null,"shared_ownership_rent":null,"shared_ownership_rent_frequency":null,"shared_ownership_percentage_sold":null,"created_at":"2022-06-17T16:39:20 01:00","updated_at":"2022-06-17T16:45:16 01:00"}},{"type":"sales_listing","id":"be1cec3a-cf2f-40c4-a627-427cf3fbdfa7","attributes":{"status":"Sold STC","price":350000,"price_qualifier":"Fixed Price","display_price":true,"archived":false,"is_low_profile":false,"occupancy_status":1,"new_home":false,"created_at":"2022-07-19T11:38:08 01:00","updated_at":"2022-07-19T11:39:26 01:00"}},{"type":"media","id":"125542ce-27f1-4852-8fb6-b71daaaa70d1","attributes":{"name":"136511_31519016_IMG_00_0000","order":0,"is_featured":true,"feature_index":1,"title":null,"is_image":true,"url":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844609\/136511_31519016_IMG_00_0000.jpeg","urls":{"thumbnail":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844609\/136511_31519016_IMG_00_0000.jpeg?tr=pr-true,n-property_thumb","small":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844609\/136511_31519016_IMG_00_0000.jpeg?tr=pr-true,n-property_small_fill_crop","medium":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844609\/136511_31519016_IMG_00_0000.jpeg?tr=pr-true,n-property_medium_fill_crop","large":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844609\/136511_31519016_IMG_00_0000.jpeg?tr=pr-true,n-property_large_fill_crop","hero":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844609\/136511_31519016_IMG_00_0000.jpeg?tr=pr-true,n-property_hero","full":"https:\/\/ik.imagekit.io\/street\/street-mobile\/properties\/general\/844609\/136511_31519016_IMG_00_0000.jpeg"}}}],"meta":{"pagination":{"total":2,"count":2,"per_page":250,"current_page":1,"total_pages":1}},"links":{"self":"https:\/\/inventorymanchester.co.uk\/api\/property-feed\/sales\/search?page[number]=1","first":"https:\/\/inventorymanchester.co.uk\/api\/property-feed\/sales\/search?page[number]=1","last":"https:\/\/inventorymanchester.co.uk\/api\/property-feed\/sales\/search?page[number]=1"}}

So the idea is we have "id" going into Pid, "inline_address" going into "inlineaddress" and so on. I don't need to store everything that's a part of the JSON string. Just parts that match the db columns if that makes sense.

CodePudding user response：

Well, since you ALREADY have a nice parsing system of the data setup, and you ALREADY have a nice class object that represents the json which is now a great easy to use class object?

Then all we need and are asking here?

How can I insert a row of data into a table?

The above is YOUR ONLY question here.

So your code (warning - air code follows), will be somewhat like this:

string strSQL
  = @"INSERT into [Tortoise_Street_Properties]
    (Pid, InLineAddress) VALUES(@Pid, @Address)";

 // replace TEST4 and properites with your conneciton string.
 using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
 {
    using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
    {
     cmdSQL.Parameters.Add("@Pid", SqlDbType.NVarChar).Value = data.attributes.Pid;
     cmdSQL.Parameters.Add("@Address", SqlDbType.NVarChar).Value = data.attributes.InLineAddress;

     conn.Open();
     cmdSQL.ExecuteNonQuery();
    }
 }

So, you simple outline the columns, setup the values from the object, and then insert the values.

We really don't care if you have 3 vars A, B, C, are reading a text file, or you have that complex json object.

Your goal, your question, your quest?

How can I insert some data into a database. That is the skill set and goal of yours.

edit: The only issue is that some of the data is "repeating", and thus above simple insert code shows the field mapping and a simple insert of data into a table? You might have a outer loop - a for each on the particular data array. But, remember, you have a class type EVEN for those repeating data, and you want to use that class defined for the array.

You have STRONG typed data with that existing class, and you are now free to pull out data from the class with great ease. Simple find and get the data you want from that class, and use a simple SQL insert statement as per above.

Also, for creating say half a dozen little inserts? VERY little need to create a store procedure for each routine - just code out the simple sql and insert as per above. I would only write a gazillion little insert store procedures if you need them in more then one place in your code. Writing a store procedure for what amounts to a simple one line sql insert is a formula for world poverties - it just not required.

CodePudding user response：

The easiest method to bulk insert this might be to send the whole JSON string to SQL Server and parse it there using OPENJSON.

This is a somewhat complex JSON, as it has references in its properties to other properties.

Therefore it's probably best to first parse out the included array values into a table variable, then join it back to the main array.

It's unclear where you get virtualtour from, so I've left it out.
Note the data types, your current types leave what to be desired. latitude should not be an int, it should be a decimal, and price should not be varchar. The IDs appear to all be uniqueidentifier.
You don't need to specify a path if the property name is an exact match.
Nested arrays with more than one object must be parsed using a separate OUTER APPLY OPENJSON. In this instance you don't have any apart from included which we are parsing separately into a table variable.

CREATE OR ALTER PROC dbo.InsertJSON
  @json nvarchar(max)
AS

SET NOCOUNT, XACT_ABORT ON;

DECLARE @included TABLE (id uniqueidentifier PRIMARY KEY, attributes nvarchar(max));

INSERT @included (id, attributes)
SELECT j.id, j.attributes
FROM OPENJSON(@json, '$.included')
  WITH (
    id uniqueidentifier,
    attributes nvarchar(max) AS JSON  -- for whole JSON object
  ) j;


INSERT dbo.Tortoise_Street_Properties
  (Pid, inlineaddress, postcode, description, bedrooms, price, salestatus, brochure, longitude, latitude)
SELECT
  main.PId,
  main.inline_address,
  main.postcode,
  details.full_description,
  main.bedrooms,
  listing.price,
  main.sale_status,
  media.url,
  addr.longitude,
  addr.latitude
FROM OPENJSON(@json)
  WITH (
    Pid uniqueidentifier '$.id',
    inline_address varchar(255) '$.attributes.inline_address',
    postcode varchar(20) '$.attributes.postcode',
    bedrooms int '$.attributes.bedrooms',
    sale_status varchar(255) '$.attributes.sale_status',
    address uniqueidentifier '$.relationships.address.data.id',
    details uniqueidentifier '$.relationships.details.data.id',
    salesListing uniqueidentifier '$.relationships.salesListing.data.id',
    primaryImage uniqueidentifier '$.relationships.primaryImage.data.id'
  ) main

LEFT JOIN @included Iaddr ON Iaddr.id = main.address
OUTER APPLY OPENJSON(Iaddr.attributes)
  WITH (
    longitude decimal(9,6),
    latitude  decimal(9,6)
  ) addr

LEFT JOIN @included Idetails ON Idetails.id = main.details
OUTER APPLY OPENJSON(Idetails.attributes)
  WITH (
    full_description nvarchar(max)
  ) details

LEFT JOIN @included Ilisting ON Ilisting.id = main.salesListing
OUTER APPLY OPENJSON(Ilisting.attributes)
  WITH (
    price int
  ) listing

LEFT JOIN @included Imedia ON Imedia.id = main.primaryImage
OUTER APPLY OPENJSON(Imedia.attributes)
  WITH (
    url varchar(255)
  ) media
;

Your C# code should look like this

Use await wherever possible.
Pass parameters with the exact SqlDbType and precision/length.

...
           if (response.IsSuccessStatusCode)
           {
               var properties = await response.Content.ReadAsStringAsync();
               await InsertJSON(properties);
           }
....

private static async Task InsertJSON(string json)
{
    using (var conn = new SqlConnection(YourConnString))
    using (var comm = new SqlCommand("dbo.InsertJSON", conn))
    {
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Add("@json", SqlDbType.NVarChar, -1).Value = json;  // -1 means max
        await conn.OpenAsync();
        await comm.ExecuteNonQueryAsync();
    }
}