none
how to call POST API service using C# windows Forms RRS feed

  • Question

  • Hi ,

    I want to call a POST API service using C#Windows Forms.

    Request Body Json File

    ==============

    {
    "Number": "12345678910",
    "DeviceEventTime": "2017-09-17T07:44:17.696Z",
    "Identifier": "3332266985",
    "TransactionId": "aaaaaaa-bbbb-1111-2223-123456789332",
    "AssemblyCollection": [
    {
    "PN": "ABC-00001",
    "AssemblyNumber": "12345678910"
    }
    ]
    }

    For this here are my requirements.

    1. I have a Excel File where it will have the Input values that will post to the above mentioned .Json file(Here this json file is the request body).

    For example below is the EXCEL format.

    Number PN
    12345678912 ABC-00001

    21345678913    ABC-00012

    2. Whenever I click on Submit Button it should , it should fetch the data from EXCEL and replace the value in JSON and call POST API service(https://abc.net/service/Number). 

    3. IF there are N number of rows in excel, it should perform step 2.

    So, please help in creating this activity.

    Thursday, November 1, 2018 5:07 PM

Answers

  • Hi,

    Sorry, I can’t access the site "https://abc.net/service/Number" you provided. So, I just implemented reading data from excel and then modifying the json string. The specific implementation code is as follows:

        private void Submit_Click(object sender, EventArgs e)
        {
            string strFileName = @"D:\Book1.xlsx";
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            if (excel == null)
            {
                MessageBox.Show("Can’t access excel");
    
            }
            else
            {
                excel.Visible = false; excel.UserControl = true;
                Microsoft.Office.Interop.Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
                    missing, missing, missing, true, missing, missing, missing, missing, missing);
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
                int rowsint = ws.UsedRange.Cells.Rows.Count;
                Microsoft.Office.Interop.Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint);
                Microsoft.Office.Interop.Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint);
                object[,] arry1 = (object[,])rng1.Value2;
                object[,] arry2 = (object[,])rng2.Value2;
                string numbers = "";
                string pn = "";
                foreach (var i in arry1)
                {
                    numbers += i.ToString() + " ";
                }
                foreach (var i in arry2)
                {
                    pn += i.ToString() + " ";
                }
                string[] nArray = numbers.Split(' ');
                string[] pArray = pn.Split(' ');
                for (int i = 0; i < rowsint - 1; i++)
                {
                    string json = "[{'Number':'12345678910','DeviceEventTime':'2017-09-17T07:44:17.696Z','Identifier':'aaaaaaa-bbbb-1111-2223-123456789332','AssemblyCollection':[{'PN': 'ABC-00001','AssemblyNumber': '12345678910'}]]";
                    Regex reg1 = new Regex(@"'Number':'(\d{11})'");
                    string modified = reg1.Replace(json, $"'Number':'{nArray[i]}'");
                    Regex reg2 = new Regex(@"'PN': '[A-Z]{3}-\w{5}'");
                    string newmodified = reg2.Replace(modified, $"'PN': '{pArray[i]}'");
                    // ...
                    // the code post data
                    // ...
                }
           }
        }

    Before using it, you need to use "System.Text.RegularExpressions" and add "Microsoft.Office.Interop.Excel" from "NuGet". The specific operation is as follows:

    Right click the Reference and select "Manage NuGet Packages...", then type "Microsoft.Office.Interop.Word" in the search bar and install it.

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pavan 565 Monday, November 5, 2018 7:30 AM
    Friday, November 2, 2018 9:53 AM
    Moderator
  • Hi Pavan,

    >>...but this is not saving the file.

    If you want to save the modified string as a new json file, you can use the following code to achieve it.

        // code omited
        FileStream fs = new FileStream($"D:/{i}.json", FileMode.OpenOrCreate, FileAccess.ReadWrite);
        StreamWriter sw = new StreamWriter(fs);
        sw.WriteLine(newmodified);
        sw.Close();
        // ...
        // the code post data
        // ...

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pavan 565 Monday, November 5, 2018 7:30 AM
    Monday, November 5, 2018 1:36 AM
    Moderator
  • Hi,

    You can do this by escape character "\", the specific code is shown as follows.

        // code omited
        string json = "{\"Number\": \"12345678910\",\"DeviceEventTime\": \"2017-09-17T07:44:17.696Z\",\"Identifier\": \"aaaaaaa-bbbb-1111-2223-123456789332\",\"AssemblyCollection\": [{\"PN\": \"ABC-00001\", \"AssemblyNumber\": \"12345678910\"}]}";
    
        Regex reg1 = new Regex("\"Number\": \"(\\d{11})\"");
        string nummodified = reg1.Replace(json, $"\"Number\":\"{nArray[i]}\"");
    
        Regex reg2 = new Regex("\"PN\": \"[A-Z]{3}-\\w{5}\"");
        string pnmodified = reg2.Replace(nummodified, $"\"PN\": \"{pArray[i]}\"");
    
        Regex reg3 = new Regex("\"AssemblyNumber\": \"(\\d{11})\"");
        string anummodified = reg3.Replace(pnmodified, $"\"AssemblyNumber\": \"{nArray[i]}\"");
    
        Regex reg4 = new Regex("\"Identifier\": \"[0-9a-zA-Z]{7}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}\"");
        string guidmodified = reg4.Replace(anummodified, $"\"Identifier\":\"{str}\"");
        // code omitted

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 6, 2018 9:22 AM
    Moderator

All replies

  • Hi,

    Sorry, I can’t access the site "https://abc.net/service/Number" you provided. So, I just implemented reading data from excel and then modifying the json string. The specific implementation code is as follows:

        private void Submit_Click(object sender, EventArgs e)
        {
            string strFileName = @"D:\Book1.xlsx";
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            if (excel == null)
            {
                MessageBox.Show("Can’t access excel");
    
            }
            else
            {
                excel.Visible = false; excel.UserControl = true;
                Microsoft.Office.Interop.Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
                    missing, missing, missing, true, missing, missing, missing, missing, missing);
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
                int rowsint = ws.UsedRange.Cells.Rows.Count;
                Microsoft.Office.Interop.Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint);
                Microsoft.Office.Interop.Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint);
                object[,] arry1 = (object[,])rng1.Value2;
                object[,] arry2 = (object[,])rng2.Value2;
                string numbers = "";
                string pn = "";
                foreach (var i in arry1)
                {
                    numbers += i.ToString() + " ";
                }
                foreach (var i in arry2)
                {
                    pn += i.ToString() + " ";
                }
                string[] nArray = numbers.Split(' ');
                string[] pArray = pn.Split(' ');
                for (int i = 0; i < rowsint - 1; i++)
                {
                    string json = "[{'Number':'12345678910','DeviceEventTime':'2017-09-17T07:44:17.696Z','Identifier':'aaaaaaa-bbbb-1111-2223-123456789332','AssemblyCollection':[{'PN': 'ABC-00001','AssemblyNumber': '12345678910'}]]";
                    Regex reg1 = new Regex(@"'Number':'(\d{11})'");
                    string modified = reg1.Replace(json, $"'Number':'{nArray[i]}'");
                    Regex reg2 = new Regex(@"'PN': '[A-Z]{3}-\w{5}'");
                    string newmodified = reg2.Replace(modified, $"'PN': '{pArray[i]}'");
                    // ...
                    // the code post data
                    // ...
                }
           }
        }

    Before using it, you need to use "System.Text.RegularExpressions" and add "Microsoft.Office.Interop.Excel" from "NuGet". The specific operation is as follows:

    Right click the Reference and select "Manage NuGet Packages...", then type "Microsoft.Office.Interop.Word" in the search bar and install it.

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pavan 565 Monday, November 5, 2018 7:30 AM
    Friday, November 2, 2018 9:53 AM
    Moderator
  • Hi Stanly,

    Thanks for sharing the code but this is not saving the file.

    Can you please help it.

    • Edited by Pavan 565 Sunday, November 4, 2018 4:39 PM
    Sunday, November 4, 2018 1:24 PM
  • Hi Pavan,

    >>...but this is not saving the file.

    If you want to save the modified string as a new json file, you can use the following code to achieve it.

        // code omited
        FileStream fs = new FileStream($"D:/{i}.json", FileMode.OpenOrCreate, FileAccess.ReadWrite);
        StreamWriter sw = new StreamWriter(fs);
        sw.WriteLine(newmodified);
        sw.Close();
        // ...
        // the code post data
        // ...

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Pavan 565 Monday, November 5, 2018 7:30 AM
    Monday, November 5, 2018 1:36 AM
    Moderator
  • Hi Stanley,

    I want to replace the Identifier with NEW GUID and AssemblyNumber with Number from EXCEL file input, from the JSON file.

    [{'Number':'12345678910','DeviceEventTime':'2017-09-17T07:44:17.696Z',

    'Identifier':'aaaaaaa-bbbb-1111-2223-123456789332',

    'AssemblyCollection':[{'PN': 'ABC-00001','AssemblyNumber': '12345678910'}]]"

    So I have tried this:

    Regex reg1 = new Regex(@"'Number':'(\d{11})'");
                        string nummodified = reg1.Replace(json, $"'Number':'{nArray[i]}'");
                        Regex reg2 = new Regex(@"'PN': '[A-Z]{3}-\w{5}'");
                        string pnmodified = reg2.Replace(nummodified, $"'PN': '{pArray[i]}'");
    //Added below piece of code but the value is not changed                    

    Regex reg3 = new Regex(@"'AssemblyNumber':'(\d{11})'");
                        string anummodified = reg3.Replace(pnmodified, $"'AssemblyNumber': '{nArray[i]}'");

    please help me on this for both Identifier and AssemblyNumber

    Monday, November 5, 2018 7:26 AM
  • Hi,

    >> … Added below piece of code but the value is not changed …

    The reason why you can’t replace AssemblyNumber is that a space is missing between ":" and "'" in regular expressions. You should modify the code as shown below.

        //Added below piece of code but the value is not changed   
        //Regex reg3 = new Regex(@"'AssemblyNumber':'(\d{11})'");
        //string anummodified = reg3.Replace(pnmodified, $"'AssemblyNumber': '{nArray[i]}'");
    
        Regex reg3 = new Regex(@"'AssemblyNumber': '(\d{11})'");
        string anummodified = reg3.Replace(pnmodified, $"'AssemblyNumber': '{nArray[i]}'");

    >> please help me on this for both Identifier and AssemblyNumber

    You can use the same method to replace the identifier with a GUID. Here I generate the GUID randomly in the test code below.

        //code omited
        for (int i = 0; i < rowsint - 1; i++)
        {
            System.Guid guid = new Guid();
            guid = Guid.NewGuid();
            string str = guid.ToString();
            string json = "[{'Number':'12345678910','DeviceEventTime':'2017-09-17T07:44:17.696Z','Identifier':'aaaaaaa-bbbb-1111-2223-123456789332','AssemblyCollection':[{'PN': 'ABC-00001','AssemblyNumber': '12345678910'}]]";
            // ...
           // code omitted
    // ...
            Regex reg4 = new Regex(@"'Identifier':'[0-9a-zA-Z]{7}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}'");
            string guidmodified = reg4.Replace(anummodified, $"'Identifier':'{str}'");
    }
        // code omitted

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 5, 2018 9:28 AM
    Moderator
  • Hi Stanley,

    How to deal with the same code if we have JSON File like below. I mean how to mention the Regular Expression here.

    {
    "Number": "12345678910","DeviceEventTime": "2017-09-17T07:44:17.696Z",
        "Identifier": "aaaaaaa-bbbb-1111-2223-123456789332",
        "AssemblyCollection": [

            {
    "PN": "ABC-00001", "AssemblyNumber": "12345678910"
    }
    ]
    }

    Please help me on this

    Monday, November 5, 2018 6:23 PM
  • Hi,

    >>I mean how to mention the Regular Expression here.

    Sorry, I don't quite understand your specific needs. What do you mean mention the regular expression? Could you please provide more details for this purpose?

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 6, 2018 7:22 AM
    Moderator
  • So how to create below code, 

    Regex reg1 = new Regex(@"'Number':'(\d{11})'");
                        string nummodified = reg1.Replace(json, $"'Number':'{nArray[i]}'");
                        Regex reg2 = new Regex(@"'PN': '[A-Z]{3}-\w{5}'");
                        string pnmodified = reg2.Replace(nummodified, $"'PN': '{pArray[i]}'");

    if we have the JSON format with double quotes: 

    {
    "Number": "12345678910","DeviceEventTime": "2017-09-17T07:44:17.696Z",
        "Identifier": "aaaaaaa-bbbb-1111-2223-123456789332",
        "AssemblyCollection": [

            {
    "PN": "ABC-00001", "AssemblyNumber": "12345678910"
    }
    ]
    }

    Tuesday, November 6, 2018 8:44 AM
  • Hi,

    You can do this by escape character "\", the specific code is shown as follows.

        // code omited
        string json = "{\"Number\": \"12345678910\",\"DeviceEventTime\": \"2017-09-17T07:44:17.696Z\",\"Identifier\": \"aaaaaaa-bbbb-1111-2223-123456789332\",\"AssemblyCollection\": [{\"PN\": \"ABC-00001\", \"AssemblyNumber\": \"12345678910\"}]}";
    
        Regex reg1 = new Regex("\"Number\": \"(\\d{11})\"");
        string nummodified = reg1.Replace(json, $"\"Number\":\"{nArray[i]}\"");
    
        Regex reg2 = new Regex("\"PN\": \"[A-Z]{3}-\\w{5}\"");
        string pnmodified = reg2.Replace(nummodified, $"\"PN\": \"{pArray[i]}\"");
    
        Regex reg3 = new Regex("\"AssemblyNumber\": \"(\\d{11})\"");
        string anummodified = reg3.Replace(pnmodified, $"\"AssemblyNumber\": \"{nArray[i]}\"");
    
        Regex reg4 = new Regex("\"Identifier\": \"[0-9a-zA-Z]{7}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}\"");
        string guidmodified = reg4.Replace(anummodified, $"\"Identifier\":\"{str}\"");
        // code omitted

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 6, 2018 9:22 AM
    Moderator