Consume Product Taxonomy API for Segments in Visual Studio
Using Bosch Product Taxonomy API developers can read list of segments by calling the Web API with WebRequest object and parsing the JSON response in SQL Server using OpenJSON() command. In this C# tutorial I want to show how I consumed this Product Taxonomy API in a sample C-Sharp project for developers developed in Visual Studio 2017 as IDE.
Before I start sharing C# codes and Visual Studio project with developers, I request all of you to have a look at new developer community portal https://developer.bosch.com/ published by Bosch where programmers can find a list of Web API that they can use to build a connected world.
What is Taxonomy?
Taxonomy is the science of classification. In general, you can apply taxonomy concept or rules on everything around you or on conceptual things as well.
It is important that the classification has been widely accepted by every one that will use this taxonomy for cases interconnecting with each other.
Product Taxonomy API
Using Product Taxonomy API published by Bosch, developers can build a complete product category tree to use in their products related software. Please have a look at this Web API details here.
Consume Product Taxonomy API using C# Code
Before we continue with C# codes, if you plan to build and run your application with Bosch Web APIs, you will require an API Key and a valid username-password pair for basic authorization to the developer portal.
If you have registered to the portal and get your API Key then we are ready to start coding.
In this tutorial, I will be creating a Windows Forms application in C# using Visual Studio 2017.
Here is the design of my Windows form application.
A simple form design including only a button and a DataGridView object to display parsed JSON response showing the list of product segments.
In our code since we will create WebRequest object, a WebProxy object if you are running your code behind a proxy server, etc, the developers should include System.Net namespace. Since in our code the developers will get the Web API response using a Stream and a StreamReader object, it is important to include System.IO namespace as well.
Again with regards to namespaces, because I prefer to parse JSON response string on SQL Server using JSON support of SQL Server 2016 and SQL Server 2017, I require to build a database connection to my local SQL Server instance and execute a stored procedure using SQLCommand object, etc. For managing database related tasks and objects, I add System.Data.SqlClient namespage into my project code, too.
Here is the additional imported namespaces:
using System.Net;
using System.IO;
using System.Data.SqlClient;
As I said before, we need a valid username and password for Bosch developer community portal authentication.
Also an API Key is required to use Product Taxonomy API.
For all such parameters I created variables to use within the application.
These parameters include web proxy server URL, too.
Here is the C-Sharp code block where I define constant parameters and their values
// WEB API URL
string sURL = "https://ews-emea.api.bosch.com/pim/product-categories/taxonomy/segments";
string APIKey = "b07b67a2-84a7-1023-c195-e5ab352a1b57"; // use your own API Key
// Basic Authentication on Remote Web Site
String uname = "kodyaz"; // use your username
String pwd = "myPassword"; // use your password
// Web Proxy
Uri proxyURL = new Uri("http://webproxy-kodyaz.com:8082"); // use your own web proxy URL
Within C# codes triggered with button click event, I create a WebRequest object using API Url.
Then I create WebProxy object using my proxy URL and current users network credentials.
In the following step, I assing the WebProxy object to WebRequest object
ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
// Create Web Request
WebRequest objWebRequest;
objWebRequest = WebRequest.Create(sURL);
WebProxy myProxy = new WebProxy(proxyURL, true);
myProxy.Credentials = CredentialCache.DefaultNetworkCredentials;
objWebRequest.Proxy = myProxy;
Programmers will immediately realize that I have a Security Protocol assignment in the first line which I did not mention before.
Actually, I had to add this code line during tests to resolve following error
System.Net.WebException: The request was aborted: Could not create SSL/TLS secure channel.
Or with its Turkish translation:
System.Net.WebException: İstek durduruldu: SSL/TLS güvenli kanalı oluşturulamadı.
So to resolve this problem, programmers can add following code line into their project code
ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
Now .NET Framework developers are ready to pass the API Key and remote web server basic authorization values.
// API Key
objWebRequest.Headers.Add("KeyId: " + APIKey);
// Basic Authentication on Remote Web Site
String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(uname + ":" + pwd));
objWebRequest.Headers.Add("Authorization: Basic " + encoded);
Now we can call Web API and consume JSON response for product segment definitions list.
// Get Response
Stream objStream;
WebResponse objResponse;
try
{
objResponse = objWebRequest.GetResponse();
objStream = objResponse.GetResponseStream();
}
catch (Exception ex)
{
// add your exception handling code here
string err = ex.ToString();
return;
}
// Display API Response
StreamReader objStreamReader = new StreamReader(objStream);
string segmentsJSON = objStreamReader.ReadToEnd();
Now we have the JSON response as string data type in a parameter value in your C# code development.
If you want you can display it on a multi line textbox object on the form or write it into a local text file before you parse it either in your C# code or just like I did parsing it using SQL Server JSON support features.
Now it is time to pass JSON response as an input parameter to SQL Server stored procedure which will parse the input string and return a the list of product segments in tabular data format.
Before implementing this code block using Microsoft.NET Framework, let's build our Transact-SQL code and SQL Server stored procedure that will parse Web API JSON response string into tabular data.
I will not explain SQL code where I used OpenJson to parse JSON string.
But I strongly recommend database developers to read Parse JSON Response of Amazon Transcribe Service using SQL OpenJSON where step-by-step parsing JSON data is explained.
If as a SQL database programmer, understand the basics for OpenJSON it will be very easy for you to parse any JSON string formmat in future.
create procedure parse_product_taxonomy_api_for_segments (
@JSON_Response as nvarchar(max)
)
as
select
ProductCategories.*
from OpenJson(@JSON_Response, '$') with (
httpStatus varchar(10),
[data] nvarchar(max) as json,
[message] varchar(max)
) as JsonData
cross apply OpenJson([data], '$') with (
[code] int,
[description] nvarchar(255),
semanticsCode nvarchar(100)
) as ProductCategories
Now we can switch back to .NET code from SQL Server database development.
Please note that I have already added the connection string in App.config file in connectionStrings section.
It is up to the developer how to handle database connection strings in his/her project.
Maybe you can have a look at SqlConnectionStringBuilder class.
SqlConnection conn = new SqlConnection();
conn.ConnectionString = Properties.Settings.Default.kodyazConnectionString;
conn.Open();
SqlCommand cmd = new SqlCommand("parse_product_taxonomy_api_for_segments", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@JSON_Response", SqlDbType.NVarChar, -1).Value = segmentsJSON;
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
dataGridView1.DataSource = dt;
conn.Close();
Now we complete our C-Sharp project code and we are ready to run our sample code after we build the application code.
To summarize, in this project as .NET programmers we used C# to call Product Taxonomy API for segments data and parsed the JSON response in SQL Server using OpenJSON command then finally we successfully displayed segments on a DataGridView table object.