locked
Consume Web API: Copy Multidimensional Array Values into SQL DB RRS feed

  • Question

  • User-1133012043 posted

    Hello People,

    Continuing from my previous threads, assuming I have the following JSON response:

    "Items": [
    {
    "LocalTimestamp": "2017-07-18T17:27:36+08:00",
    "Id": 658796613,
    "Description": "Speeding",
    "Processed": false,
    "Position": [
    101.57393,
    3.22654
    ]
    }
    ],

    How do I access the multidimensional array named Position (Bolded above) and copy the two values into two separate columns named x-post and y-post in SQL DB.??Undecided
    So far I have managed to copy other values listed in the JSON response via SQLBulkCopy (Please see previous thread for reference). But I can't seem to have those two values which is in the form of [,]. Here's what I have done so far.

    I've created class as such 

        public class MainPage
        {
            public int Page { get; set; }
            public int PageSize { get; set; }
            public int TotalResults { get; set; }
            public List<Alarm> Items { get; set; }
            public bool HasMoreResults { get; set; }
        }
    
        public class Alarm
        {
            public String LocalTimeStamp { set; get; }
            public int Id { set; get; }
            public string Description { set; get; }
            public string Processed { set; get; }
            public string[] Position { set; get; }
        }

    A data table for BulkCopy process

                    DataTable ATable = new DataTable();
                    ATable.Columns.Add(new DataColumn("LocalTimeStamp"));
                    ATable.Columns.Add(new DataColumn("Id"));
    ATable.Columns.Add(new DataColumn("Description")); ATable.Columns.Add(new DataColumn("Processed")); ATable.Columns.Add(new DataColumn("Position"));

    and the main program to pull and deserialize the data 

                    foreach (var item in car.Items)
                    {
                        string buzzers = mzoneweb.DownloadString("https://us.gpscar.net/api/v2/vehicles/" + "d10n" + "/alerts/all/" + Date + "T000100/" + Date + "T235900.json?");
                        AlertPageDetails alert = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<AlertPageDetails>(buzzers);
                        foreach (var list in alert.Items)
                        {
                            //Console.WriteLine("LocalTimeStamp: {0}, Id: {1}, Description: {2}, Processed: {3}", list.LocalTimeStamp, list.Id, list.Description, list.Processed);
                            DataRow TempRow = ATable.NewRow();
                            TempRow["LocalTimeStamp"] = list.LocalTimeStamp;
                            TempRow["Id"] = list.Id;
                            TempRow["Description"] = list.Description;
                            TempRow["Processed"] = list.Processed;
                            ATable.Rows.Add(TempRow);
                            foreach (var val in list.Position)                        
                            {
                                //Console.WriteLine("Position: {0}", val.ToString());
                                TempRow["Position"] = val.ToString();
                                //ATable.Rows.Add(TempRow);
                            }
                        }
                    }

    Thanks. 

    Regards,
    Dein

    Wednesday, July 19, 2017 8:40 AM

Answers

  • User-1133012043 posted

    Thank God. Guys, I have finally managed to crack this one. Thanks to the other two guys for their support. Now, to send the values of the Position array, the Position data column has to be split into two, as such

                    ATable.Columns.Add(new DataColumn("x-post"));
                    ATable.Columns.Add(new DataColumn("y-post"));
    

    Then, in the main program, I've created an indexing method to map the elements of the array to their respective temporary array indices as following

                        foreach (var list in alert.Items)
                        {
                            Console.WriteLine("LocalTimeStamp: {0}, Id: {1}, Description: {2}, Processed: {3}", list.LocalTimeStamp, list.Id, list.Description, list.Processed);
                            DataRow TempRow = ATable.NewRow();
                            TempRow["LocalTimeStamp"] = list.LocalTimeStamp;
                            TempRow["Id"] = list.Id;
                            TempRow["Description"] = list.Description;
                            TempRow["Processed"] = list.Processed;
                            ATable.Rows.Add(TempRow);
    
                            int[] index = new int[] { 0, 1 };
                            var repost = index.Select(i => list.Position[i]).ToArray();
                            TempRow["x-post"] = repost[0];
                            TempRow["y-post"] = repost[1];
    }

    Hope this helps. 

    Regards,
    Dein

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 23, 2017 12:19 AM

All replies

  • User1068175894 posted

    instead of the foreach you can just do:

    if (list.Position.Length>0)
      TempRow["x-post"]=list.Position[0]
    
    if (list.Position.Length>1)
      TempRow["y-post"]=list.Position[1]

    Wednesday, July 19, 2017 1:15 PM
  • User-271186128 posted

    Hi Dhiyaddein,

    Dhiyaddein

    How do I access the multidimensional array named Position (Bolded above) and copy the two values into two separate columns named x-post and y-post in SQL DB.?

    According to your Json response ,I have created an api method.

    If we get data from Web API in C#, the data is a JSON string ,so you could use JsonConvert class to transfer JSON string to object.
    You could search Json.NET.Web in Nuget Package Manager to add reference for JsonConvert.
    Please refer to the following code:

    Code in WebAPI Controller:

    public class AlarmController : ApiController
        {
            public MainPage GetAlarm(MainPage mainPage)
            {
                decimal[] position1 = { (decimal)101.57393, (decimal)3.22654 };
                List<Alarm> alarmList = new List<Alarm>();
                alarmList.Add( new Alarm {
                    LocalTimeStamp = "2017-07-18T17:27:36+08:00",
                    Id = 658796613,
                    Description = "Speeding",
                    Processed = "false",
                    Position = position1
                });
                mainPage = new MainPage();
                mainPage.Items = alarmList;           
                return mainPage;
            }
    }
    

    2. Code in Console project:

    class ArrayTest
        {
            public int Id { get; set; }
            public decimal x_Post { get; set; }
            public decimal y_Post { get; set; }
    }
    
    using System.Net.Http; // for JsonConvert
    class Program
        {
            static HttpClient client = new HttpClient();
            static  SqlConnection sql = new SqlConnection();
            static void Main(string[] args)
            {
                RunAsync().Wait();  
            }
            static async Task RunAsync()
            {
                client.BaseAddress = new Uri("http://localhost:59356/"); //your web api project url
                client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
                var resp2 = await client.GetAsync("api/Alarm");
                resp2.EnsureSuccessStatusCode();
                var aaa = resp2.Content;
                var result = await aaa.ReadAsStringAsync();// read webapi result. The result type is string
                MainPage mainPage = JsonConvert.DeserializeObject<MainPage>(result);// transfer json string to object
                var AlarmItem = mainPage.Items;
                var positionArray = AlarmItem.Select(p => p.Position).ToArray();
                decimal[][] bb = positionArray;
                var x_post = bb[0][0];
                var y_post = bb[0][1];
                sql.Open();
                SqlCommand cmd = new SqlCommand("insert into ArrayTests (x_Post,y_Post) values(@x_post,@y_post)", sql);
                cmd.Parameters.AddWithValue("@x_post", x_post);            
                cmd.Parameters.AddWithValue("@y_post", y_post);
                int i = cmd.ExecuteNonQuery();
                sql.Close();
                Console.WriteLine("Opsition Array:"+x_post+" "+ y_post);// output  result
                
            }       
    }
    

    Best regards,
    Dillion

    Thursday, July 20, 2017 9:16 AM
  • User-1133012043 posted

    Hi JBetancourt,

    Thanks for the prompt reply and for being a handyman in the forum. Sorry if my response is a bit late, as I'm actually busy solving problem with connecting to the Web API server. I have no idea why this happens all of a sudden. I tested the URI using SOAP UI and it works fine. But when I execute the C# program  I gives me this error "unable to connect to the server" issue. As a result, I couldn't really able to verify the solution that you have provided me on the above. If you know the workaround to this, do let me know. 


    07-22-2017 3:19 P.M

    Okay. Updates on your scripts. I managed to execute the program (tough luck) but I can't get the values of the Y-Post. Please help me on this. Below are the results for the web API query on console and in SQL DB.

    Regards,
    Dein

    Thursday, July 20, 2017 1:50 PM
  • User-1133012043 posted

    Hey Dillion,

    Wow. Many thanks for your tremendous amount of effort. First of all, I can't really modify the JSON content of the web API as it was not designed by me and I'm just the end user.

    I tried using your solutions but had some issue with parsing the elements of the array as I can't really split the array into decimal x_post and decimal y_post. However, I did refer to your brilliant method to solve this problem as quoted below:

    var positionArray = AlarmItem.Select(p => p.Position).ToArray(); decimal[][] bb = positionArray;

    Thank you for your support.

    Regards,
    Dein

    Saturday, July 22, 2017 11:58 PM
  • User-1133012043 posted

    Thank God. Guys, I have finally managed to crack this one. Thanks to the other two guys for their support. Now, to send the values of the Position array, the Position data column has to be split into two, as such

                    ATable.Columns.Add(new DataColumn("x-post"));
                    ATable.Columns.Add(new DataColumn("y-post"));
    

    Then, in the main program, I've created an indexing method to map the elements of the array to their respective temporary array indices as following

                        foreach (var list in alert.Items)
                        {
                            Console.WriteLine("LocalTimeStamp: {0}, Id: {1}, Description: {2}, Processed: {3}", list.LocalTimeStamp, list.Id, list.Description, list.Processed);
                            DataRow TempRow = ATable.NewRow();
                            TempRow["LocalTimeStamp"] = list.LocalTimeStamp;
                            TempRow["Id"] = list.Id;
                            TempRow["Description"] = list.Description;
                            TempRow["Processed"] = list.Processed;
                            ATable.Rows.Add(TempRow);
    
                            int[] index = new int[] { 0, 1 };
                            var repost = index.Select(i => list.Position[i]).ToArray();
                            TempRow["x-post"] = repost[0];
                            TempRow["y-post"] = repost[1];
    }

    Hope this helps. 

    Regards,
    Dein

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 23, 2017 12:19 AM
  • User1068175894 posted

    instead of this unnecessarily complicated code you added:

    int[] index = new int[] { 0, 1 };
    var repost = index.Select(i => list.Position[i]).ToArray();
    TempRow["x-post"] = repost[0];
    TempRow["y-post"] = repost[1];

    you can just do as I said in my original answer, more clear and with the added benefit of not failing if both positions are not provided

    if (list.Position.Length>0)
      TempRow["x-post"]=list.Position[0]
    
    if (list.Position.Length>1)
      TempRow["y-post"]=list.Position[1]

    If our answers helped, besides the thanks, marking as answer would help, thanks

    Sunday, July 23, 2017 3:52 AM
  • User-1133012043 posted

    Hey,

    Sorry if my earlier post (7/22/2017) wasn't clear enough, maybe because the image that I attached is broken. But again, I can't get the values for the Y-post even when I put those lines even in the foreach loop. I know I'm not an expert but It seems that using length as a reference won't help map the elements inside the array to the respective variables. Anyway, here's the output that I get from your lines of codes:

    Local_Time_Stamp	          Id	       Description    Processed    Y-post       X-post
    2017-07-23T03:47:44+08:00	660673543	Speeding	False	   4.46461	NULL

    while here's what I wanted to achieve (achieved from the codes that I posted)

    Local_Time_Stamp	          Id	       Description    Processed	  Y-post	X-post
    2017-07-23T03:47:44+08:00	660673543	Speeding	False	  101.17304	4.46461

    I do appreciate your sugesstions and yeah, I would mark it as answer if it's giving me the expected answer. No worries JBetancourt.

    Sunday, July 23, 2017 10:24 AM
  • User1068175894 posted

    you are almost there, put a breakpoint in your code and check the variables content via shift-F9 and you will easily spot what is wrong,

    thanks and happy coding

    Monday, July 24, 2017 2:48 AM
  • User-1133012043 posted

    Got it. Thank you. I look forward to hearing from you again in future.

    Dein

    Friday, July 28, 2017 2:55 AM