{"id":4214,"date":"2018-06-26T22:05:51","date_gmt":"2018-06-26T22:05:51","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4214"},"modified":"2024-02-06T11:56:28","modified_gmt":"2024-02-06T11:56:28","slug":"calling-rest-api-in-c","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/calling-rest-api-in-c\/","title":{"rendered":"Calling REST API in C# (Read JSON Data)"},"content":{"rendered":"

Introduction –\u00a0REST API using C#<\/h2>\n

In this post, We will use ZappySys ODBC Powerpack for calling REST API in C# (i.e. CSharp). We will create an ODBC connection to REST API and consume it different ways (e..g Bind API data to Data Grid, Combo Box or Write to File). ZappySys ODBC Driver allows to connect to many other REST API such as Facebook, Google, OneDrive, SharePoint and thousands\u00a0of other sources that support REST API.<\/p>\n

Requirements<\/h2>\n
    \n
  1. First of all, you will need\u00a0ZappySys ODBC PowerPack\u00a0<\/a>installed. This driver is a very powerful tool to connect with ODBC to\u00a0REST API, JSON files, XML files, WEB API, OData and more.<\/li>\n
  2. Secondly, you will need Visual Studio Installed.<\/li>\n<\/ol>\n

    Getting Started<\/h2>\n

    Let’s look at few examples to consume REST API or JSON data in C# applications (WPF, Winform, Console App or even Web Application such as ASP.net MVC or Webforms).<\/p>\n

    Calling REST API in C# to show REST API results in the console<\/h3>\n

    In order to start, we will get the values using REST API. We will use C# to\u00a0get the values. In this example, we will use the following URL to get data:<\/p>\n

    https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json<\/pre>\n
      \n
    1. First of all, we will first create a Visual Studio Windows form using C#.<\/li>\n
    2. Secondly, we will add the following code to get the values from the API:\n
      using (OdbcConnection conn = new OdbcConnection(\"Driver={ZappySys JSON Driver};\" \r\n     + \"DataPath = 'https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json';\"\r\n     ))\r\n{\r\n    conn.Open();\r\n    OdbcCommand cmd = new OdbcCommand(\"SELECT CustomerID,CompanyName FROM value\", conn);\r\n    \r\n    \/\/Increases the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios.\r\n    cmd.CommandTimeout=600; \/\/ 600-seconds\r\n    \r\n    var rdr = cmd.ExecuteReader();\r\n    while (rdr.Read())\r\n    {\r\n        Console.WriteLine(\"---- Fetching Row -------\");\r\n        for (int i = 0; i < rdr.FieldCount; i++)\r\n        {\r\n            Console.Write(\"Field {0}={1} \", i, rdr[i]);\r\n        }\r\n        Console.WriteLine(\"\");\r\n    }\r\n}\r\n<\/pre>\n<\/li>\n
    3. This code connects to ODBC using the ZappySys JSON Driver and\u00a0connection the customers URL:\u00a0Also, we open the connection and send the SQL query.\u00a0 With this\u00a0query, you can use a simple SQL Query to REST API. Here you have the lists of\u00a0sentences in SQL supported by the driver:\n
      SELECT\r\n[* | [ expression [[AS] column_name_alias] [, ...] ]\r\n[FROM table_name]\r\n[WHERE condition [, ...] ]\r\n[GROUP BY expression [, ...] ]\r\n[HAVING condition [, ...] ]\r\n[ORDER BY expression [ASC | DESC] [, ...] ]\r\n[LIMIT row_count]\r\n[WITH (option_name=option_value] [, ...]) ]<\/pre>\n<\/li>\n
    4. Finally, we show the information in the console:\n
      \"Visual<\/a>
      C# show rest API information<\/p><\/div>\n
       <\/li>\n<\/ol>\n
      Here is full C# code to read from REST API in C#. Create a new Console Application Project in Visual Studio (File > New > Project > Visual C# > Console Application<\/em> )<\/p>\n
      using System;\r\nusing System.IO;\r\nusing System.Data.Odbc;\r\n\r\npublic class Program\r\n{\r\n    public static void Main()\r\n    {\r\n        var outpath = @\"C:\\temp\\sample.txt\";\r\n        \r\n        using (var conn = new OdbcConnection(\"Driver={ZappySys JSON Driver};\"\r\n            +\"DataPath='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json';\"))\r\n        {\r\n            conn.Open();\r\n            var cmd = new OdbcCommand(@\"SELECT CustomerID,CompanyName FROM $ WITH(Filter='$.value[*]')\", conn);\r\n\r\n            \/\/Increases the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios.\r\n            cmd.CommandTimeout=600; \/\/ 600-seconds\r\n            var rdr = cmd.ExecuteReader();\r\n\r\n            using (var fs = new FileStream(outpath, FileMode.Create))\r\n            {\r\n                using (var writer = new StreamWriter(fs))\r\n                {\r\n                    \/\/write file header\r\n                    writer.WriteLine(\"CustomerID,CompanyName\");\r\n\r\n                    while (rdr.Read())\r\n                    {\r\n                        \/\/write file row\r\n                        writer.WriteLine(\"{0},{1}\", rdr[\"CustomerID\"], rdr[\"CompanyName\"]);\r\n                    }\r\n                    conn.Close(); \/\/close connection\r\n                    writer.Close();\r\n                    fs.Close();\r\n                }\r\n            }\r\n        }\r\n\r\n        \/\/Read from file and display the content\r\n        Console.Write(File.ReadAllText(outpath));\r\n\r\n        Console.WriteLine(\"\\r\\n===== Press any key to end the program =====\\r\\n\");\r\n        Console.Read();\r\n    }\r\n}<\/pre>\n
       <\/p>\n
      Calling REST API in C# to show REST API results in a combo box<\/h3>\n
      In the next example, we will show how to call REST API in C# and load the\u00a0data in a combo box. To execute this code you need to create a WinForm Project<\/strong> in Visual Studio\u00a0 (File > New > Project > Visual C# > Windows Form Application)<\/p>\n
        \n
      1. First of all, in a C# project, add the following code:\n
        try\r\n{\r\n    using (OdbcConnection conn = new OdbcConnection(\"Driver={ZappySys JSON Driver};DataPath='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json';\"))\r\n    {\r\n        conn.Open();\r\n        OdbcCommand cmd = new OdbcCommand(\"SELECT CustomerID FROM value\", conn);\r\n\r\n        \/\/Increases the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios.\r\n        cmd.CommandTimeout=600; \/\/ 600-seconds\r\n\r\n        var rdr = cmd.ExecuteReader();\r\n        while (rdr.Read())\r\n        {\r\n            Console.WriteLine(\"---- Fetching Row -------\");\r\n            for (int i = 0; i < rdr.FieldCount; i++)\r\n            {\r\n                comboBox1.Items.Add(rdr[i].ToString());\r\n            }\r\n            Console.WriteLine(\"\");\r\n            conn.Close();\r\n        }\r\n    }\r\n}\r\ncatch (Exception ex)\r\n{\r\n    MessageBox.Show(ex.ToString());\r\n}\r\n<\/pre>\n<\/li>\n
      2. Secondly, you will need to add a combo box in the project.<\/li>\n
      3. The code is similar to the previous example, but we are filling a
        \ncombo box with the following line of code:<\/p>\n
        comboBox1.Items.Add(rdr[i].ToString());<\/pre>\n<\/li>\n
      4. In addition, the query is different because we are doing a select to just
        \none column:<\/p>\n
        OdbcCommand cmd = new OdbcCommand(\"SELECT CustomerID FROM value\", conn);<\/pre>\n<\/li>\n
      5. Finally, execute the code to see the results:\n
        \"C#<\/a>
        Load combo box c# REST API<\/p><\/div><\/li>\n<\/ol>\n
        Calling REST API in C# to show REST API in a Data Grid<\/h3>\n
        The DataGridView is very popular to retrieve data. In this example, we will\u00a0show how to load REST API in the DataGridView.<\/p>\n
          \n
        1. First of all, add the following code:\n
          try\r\n{\r\n    string Conn = \"Driver={ZappySys JSON Driver};DataPath='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json';\";\r\n    string Sql = \"select CustomerID,CompanyName from value\";\r\n\r\n    using (OdbcConnection con = new OdbcConnection(Conn))\r\n    using (OdbcDataAdapter dadapter = new OdbcDataAdapter(Sql, con))\r\n    {\r\n        DataTable table = new DataTable();\r\n        dadapter.Fill(table);\r\n\r\n        this.dataGridView1.DataSource = table;\r\n    }\r\n}\r\ncatch (Exception ex)\r\n{\r\n    MessageBox.Show(ex.ToString());\r\n}<\/pre>\n<\/li>\n
        2. We first create an ODBC connection with the SQL query:\n
          string Conn = \"Driver={ZappySys JSON Driver};\r\nDataPath='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json';\";\r\nstring Sql = \"select CustomerID,CompanyName from value\";\r\nusing (OdbcConnection con = new OdbcConnection(Conn))\r\n<\/pre>\n<\/li>\n
        3. Also, we create a Data Table and fill with data:\n
          using (OdbcDataAdapter dadapter = new OdbcDataAdapter(Sql, con))\r\n{\r\n     DataTable table = new DataTable();\r\n     dadapter.Fill(table);<\/pre>\n<\/li>\n
        4. In addition, we will add the table to the dataGridView:\n
          this.dataGridView1.DataSource = table;<\/pre>\n<\/li>\n
        5. Finally, execute the code to see the results:\n
          \"Load<\/a>
          Datagridview c#<\/p><\/div><\/li>\n<\/ol>\n
          Read from JSON file in C# (Single or Multiple files)<\/h3>\n
          The ZappySys ODBC PowerPack allows connecting not only to REST API, WEB API\u00a0but also to local files. In this example, we will connect to a file named\u00a0test.json. Let’s take a look at this example to see how to do it:<\/p>\n
            \n
          1. First of all, we will have a JSON file named test.json with this\u00a0content:\n
            {\r\n  \"value\": [\r\n    {\r\n      \"CustomerID\": \"ALFKI\",\r\n      \"CompanyName\": \"Alfreds Futterkiste\",\r\n      \"ContactName\": \"Maria Anders\",\r\n      \"ContactTitle\": \"Sales Representative\",\r\n      \"Address\": \"Obere Str. 57\",\r\n      \"City\": \"Berlin\",\r\n      \"Region\": null,\r\n      \"PostalCode\": \"12209\",\r\n      \"Country\": \"Germany\",\r\n      \"Phone\": \"030-0074321\",\r\n      \"Fax\": \"030-0076545\"\r\n    },\r\n    {\r\n      \"CustomerID\": \"ANATR\",\r\n      \"CompanyName\": \"Ana Trujillo Emparedados y helados\",\r\n      \"ContactName\": \"Ana Trujillo\",\r\n      \"ContactTitle\": \"Owner\",\r\n      \"Address\": \"Avda. de la Constituci\u00f3n 2222\",\r\n      \"City\": \"M\u00e9xico D.F.\",\r\n      \"Region\": null,\r\n      \"PostalCode\": \"05021\",\r\n      \"Country\": \"Mexico\",\r\n      \"Phone\": \"(5) 555-4729\",\r\n      \"Fax\": \"(5) 555-3745\"\r\n    },\r\n    {\r\n      \"CustomerID\": \"ANTON\",\r\n      \"CompanyName\": \"Antonio Moreno Taquer\u00eda\",\r\n      \"ContactName\": \"Antonio Moreno\",\r\n      \"ContactTitle\": \"Owner\",\r\n      \"Address\": \"Mataderos 2312\",\r\n      \"City\": \"M\u00e9xico D.F.\",\r\n      \"Region\": null,\r\n      \"PostalCode\": \"05023\",\r\n      \"Country\": \"Mexico\",\r\n      \"Phone\": \"(5) 555-3932\",\r\n      \"Fax\": null\r\n    },\r\n    {\r\n      \"CustomerID\": \"AROUT\",\r\n      \"CompanyName\": \"Around the Horn\",\r\n      \"ContactName\": \"Thomas Hardy\",\r\n      \"ContactTitle\": \"Sales Representative\",\r\n      \"Address\": \"120 Hanover Sq.\",\r\n      \"City\": \"London\",\r\n      \"Region\": null,\r\n      \"PostalCode\": \"WA1 1DP\",\r\n      \"Country\": \"UK\",\r\n      \"Phone\": \"(171) 555-7788\",\r\n      \"Fax\": \"(171) 555-6750\"\r\n    },\r\n    {\r\n      \"CustomerID\": \"BOLID\",\r\n      \"CompanyName\": \"B\u00f3lido Comidas preparadas\",\r\n      \"ContactName\": \"Mart\u00edn Sommer\",\r\n      \"ContactTitle\": \"Owner\",\r\n      \"Address\": \"C\/ Araquil, 67\",\r\n      \"City\": \"Madrid\",\r\n      \"Region\": null,\r\n      \"PostalCode\": \"28023\",\r\n      \"Country\": \"Spain\",\r\n      \"Phone\": \"(91) 555 22 82\",\r\n      \"Fax\": \"(91) 555 91 99\"\r\n    },\r\n\r\n    {\r\n      \"CustomerID\": \"ERNSH\",\r\n      \"CompanyName\": \"Ernst Handel\",\r\n      \"ContactName\": \"Roland Mendel\",\r\n      \"ContactTitle\": \"Sales Manager\",\r\n      \"Address\": \"Kirchgasse 6\",\r\n      \"City\": \"Graz\",\r\n      \"Region\": null,\r\n      \"PostalCode\": \"8010\",\r\n      \"Country\": \"Austria\",\r\n      \"Phone\": \"7675-3425\",\r\n      \"Fax\": \"7675-3426\"\r\n    }\r\n  ]\r\n}<\/pre>\n<\/li>\n
          2. Secondly, we will create the following code to load the data in a\u00a0combo box:
            \nNOTE:<\/strong> To read from multiple files use wild card pattern (e.g. c:\\data\\test*.json)<\/p>\n
            using (OdbcConnection conn = new OdbcConnection(\"Driver={ZappySys JSON Driver};\" +\r\n    @\"DataPath = 'c:\\data\\test.json'; \"))\r\n{\r\n    conn.Open();\r\n    OdbcCommand cmd = new OdbcCommand(\"SELECT CustomerID FROM value\", conn);\r\n\r\n    \/\/Increases the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios.\r\n    cmd.CommandTimeout=600; \/\/ 600-seconds\r\n\r\n    var rdr = cmd.ExecuteReader();\r\n    while (rdr.Read())\r\n    {\r\n        for (int i = 0; i < rdr.FieldCount; i++)\r\n        {\r\n\r\n            comboBox1.Items.Add(rdr[i].ToString());\r\n        }\r\n\r\n    }\r\n}<\/pre>\n<\/li>\n
          3. Finally, execute the code to see the results:\n
            \"Invoke<\/a>
            Read JSON file in C#<\/p><\/div><\/li>\n<\/ol>\n
            Calling REST API in C# to get Gmail information<\/h3>\n
            The next example will show how to get Gmail information in C# using REST\u00a0API.<\/p>\n
              \n
            1. First of all, go to the ODBC Administrator in Windows:\n
            2. Secondly, in the ODBC Administrator press the add button:<\/li>\n
            3. \u00a0Also, select the ZappySys JSON driver.<\/li>\n
            4. In addition, enter the DSN and the URL. In this example, we will use the\u00a0following URL:\n
              https:\/\/www.googleapis.com\/gmail\/v1\/users\/me\/messages\/1642915a12765abe<\/pre>\n<\/li>\n
            5. Where 1642915a12765abe is the ID of the email that you can get from the email:\n
            6. Additionally, you need to select the authentication type to Oauth, select\u00a0the GET HTTP method and press the Click to configure link.<\/li>\n
            7. In the same way, we can configure the OAuth parameters. Select the\u00a0Google OAuth provider, in scopes select the following URL:\n
              https:\/\/mail.google.com<\/pre>\n
              \"Gmail<\/a>
              OAuth credential REST API Gmail<\/p><\/div><\/li>\n
            8. The code used to get data is the following:\n
                         try\r\n            {\r\n                string Conn = \"dsn=ZappySys JSON Gmail;\";\r\n                string Sql = \"select * from [_root_]\";\r\n\r\n                using (OdbcConnection con = new OdbcConnection(Conn))\r\n                using (OdbcDataAdapter dadapter = new OdbcDataAdapter(Sql, con))\r\n                {\r\n                    DataTable table = new DataTable();\r\n                    dadapter.Fill(table);\r\n\r\n                    this.dataGridView1.DataSource = table;\r\n                }\r\n            }\r\n            catch (Exception ex)\r\n            {\r\n\r\n\r\n                MessageBox.Show(ex.ToString());\r\n<\/pre>\n<\/li>\n
            9. Note that we are invoking a DSN in the code:\n
              string Conn = \"dsn=ZappySys JSON Gmail;\";<\/pre>\n<\/li>\n
            10. Also, note that the query used to get data is the following:\n
              string Sql = \"select * from [_root_]\";<\/pre>\n<\/li>\n
            11. If everything is OK, you will be able to see the mail information in the dataGridView:\n
              \"DataGrid<\/a>
              Gmail DataGrid View<\/p><\/div><\/li>\n<\/ol>\n
              ZappySys JSON \/REST API Driver Query Examples<\/h2>\n
              Reading from XML files or API can be done using the same way as previous sections except you have to use ZappySys XML Driver. Read help file here to see json query examples<\/a>.<\/p>\n
              ZappySys\u00a0XML \/ SOAP Driver Query Examples<\/h2>\n
              Reading from XML files or API can be done using the same way as previous sections except you have to use ZappySys XML Driver. Read help file here to see xml query examples<\/a>.<\/p>\n
              Calling XML SOAP Web Service in C#<\/h2>\n
              So far we have looked at examples to consume data using JSON driver. Now lets look at an example, to call XML SOAP Web Service in Qlik.<\/p>\n
              To call SOAP API you need to know Request XML Body Structure.\u00a0If you are not sure how to create SOAP Request body then no worries. Check this article<\/a> to learn how to generate SOAP Request body using the Free tool SoapUI<\/a>. Basically, you have to use SoapUI to generate Request XML and after that, you can replace parameters as needed in the generated body.\r\n
              What is SOAP Web Service?<\/h3>\r\nIf you are new to SOAP Web Service sometimes referred as XML Web Service then please read some concept\u00a0about\u00a0SOAP\u00a0Web service standard from this link<\/a>\r\n\r\nThere are two important aspects in SOAP Web service.\r\n
                \r\n \t
              1. Getting WSDL file or URL<\/li>\r\n \t
              2. Knowing exact Web Service URL<\/li>\r\n<\/ol>\r\n
                What is WSDL<\/h3>\r\nIn very simple term WSDL (often pronounced as whiz-dull) is nothing but a document which describes Service metadata (e.g. Functions you can call, Request parameters, response structure etc). Some service simply give you WSDL as xml file you can download on local machine and then\u00a0analyze or sometimes you may get direct URL (e.g. http:\/\/api.mycompany.com\/hr-soap-service\/?wsdl )\r\n
                Example SQL Query for SOAP API call using ZappySys XML Driver<\/h3>\r\nHere is an example SQL query you can write to call SOAP API. If you not sure about many details then check next few sections on how to use XML Driver User Interface to build desired SQL query to POST data to XML SOAP Web Service without any coding.\r\n
                SELECT * FROM $\r\nWITH(\r\n\t Src='http:\/\/www.holidaywebservice.com\/HolidayService_v2\/HolidayService2.asmx'\r\n\t,DataConnectionType='HTTP'\r\n\t,CredentialType='Basic' --OR SoapWss\r\n\t,SoapWssPasswordType='PasswordText'\r\n\t,UserName='myuser'\r\n\t,Password='pass$$w123'\r\n\t,Filter='$.soap:Envelope.soap:Body.GetHolidaysAvailableResponse.GetHolidaysAvailableResult.HolidayCode[*]'\r\n\t,ElementsToTreatAsArray='HolidayCode'\t\r\n\t,RequestMethod='POST'\t\r\n\t,Header='Content-Type: text\/xml;charset=UTF-8 || SOAPAction: \"http:\/\/www.holidaywebservice.com\/HolidayService_v2\/GetHolidaysAvailable\"'\r\n\t,RequestData='\r\n<soapenv:Envelope xmlns:soapenv=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\" xmlns:hol=\"http:\/\/www.holidaywebservice.com\/HolidayService_v2\/\">\r\n   <soapenv:Header\/>\r\n   <soapenv:Body>\r\n      <hol:GetHolidaysAvailable>\r\n         <!--type: Country - enumeration: [Canada,GreatBritain,IrelandNorthern,IrelandRepublicOf,Scotland,UnitedStates]-->\r\n         <hol:countryCode>UnitedStates<\/hol:countryCode>\r\n      <\/hol:GetHolidaysAvailable>\r\n   <\/soapenv:Body>\r\n<\/soapenv:Envelope>'\r\n)<\/pre>\r\nNow let's look at steps to create SQL query to call SOAP API. Later we will see how to generate code for your desired programming language (e.g. C# or SQL Server)\r\n
                Video Tutorial - Introduction to SOAP Web Service and SoapUI tool<\/h3>\r\nBefore we dive into details about calling SOAP API using ZappySys XML Driver, lets first understand what is SOAP API and how to create SOAP requests using SoapUI tool. You will learn more about this process in the\u00a0later section. The video contains some fragment about using SOAP API in SSIS but just ignore that part because we will be calling Soap API using ZappySys ODBC Driver rather than SSIS Components.\r\n\r\n \r\n\r\n