How to write stored procedures for JSON Web API data

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.rnrnPeterborough City Centre can be reached by car in 5 minutes and by bus in 10 minutes.rnrnThe A1 Junction can be reached by car in 11 minutes and the surrounding parkways give access to the A47 both east and west. rnrnWe 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%5Bnumber%5D=1","first":"https://inventorymanchester.co.uk/api/property-feed/sales/search?page%5Bnumber%5D=1","last":"https://inventorymanchester.co.uk/api/property-feed/sales/search?page%5Bnumber%5D=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.