title: REST API (JSON) that updates SQL Table using Windows Console Application and Scheduled Tasks
body: <p>I am a newbie at JSON programming.  Most of my experience is in C# and some in XML and Javascript.  So I am a bit lost.  I will attempt to be as specific as possible.</p>
  
  <p>I have written a windows console application that runs via the task scheduler.  Basically the windows application is supposed to take the API from a site that is managed by an outside company but the information is owned by my company and put the information within a SQL table.  The API is pretty standard and written in JSON.  </p>
  
  <p>I am successful in parsing the JSON language and (for example) displaying it in a command prompt but I need to be able to parse the language and place it into an SQL table.  I have read up on SQL injection attacks and I feel fairly confident that we have covered our bases here.  So the problem lies in the fact that it does not update the table when the application is run via the scheduler or without the scheduler.</p>
  
  <p>I have included a little bit of the JSON language below along with the language for my console application.</p>
  
  <pre><code>{"date":"2015-09-24","data":[{"cid":"17","rank":1},{"cid":"26","rank":1},{"cid":"80","rank":1},{"cid":"30","rank":1},{"cid":"90","rank":1},{"cid":"62","rank":1},{"cid":"147","rank":1},{"cid":"28","rank":1}"s":1,"e":null}
  
  using System;
  using System.Collections;
  using System.IO;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Data.SqlClient;
  using System.Diagnostics;
  using System.Linq;
  using System.Text;
  using System.Net.Http;
  using System.Net.Http.Headers;
  using System.Net.Http.Formatting;
  using System.Threading.Tasks;
  using System.Net;
  using Newtonsoft.Json;
  using Newtonsoft.Json.Linq;
  
  namespace JsonApiClient
  {
      class Program
      {
          static void Main(string[] args)
          {
  
                  ExecuteRiskSearch();
                  Console.ReadLine();
          }   
  
  
      static void ExecuteRiskSearch()
          {
              string url = "https://localhost/api/getWatchList/";
              string json = new WebClient().DownloadString(url);
              JObject results = JObject.Parse(json);
  
              foreach (var result in results)
              {
                  string cid = (string)results["CID"];
                  JToken rank = results["rank"];
  
                  string risk = "";
                  if (rank is JValue)
                  {
                      risk = (string)rank;
                  }
                  else if (rank is JArray)
                  {
                      risk = (string)((JArray)rank).First;
                  }
                  else
                  {
                      SqlConnection connection = null;
                      SqlCommand command = null;
  
                      try
                      {
                          connection = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=apiData;Data Source=serverName;");
                          command = new SqlCommand("UPDATE apiData.dbo.API SET [Category] WHERE CID=CID", connection);
                          connection.Open();
                          int numrows = command.ExecuteNonQuery();
                        }
  
                      catch (Exception ex)
                      {
                          System.Diagnostics.Debug.WriteLine(ex.Message);
                      }
  
                      finally
                      {
                          command.Dispose();
                          connection.Dispose();
                      }
                      }
                  }
              }
          }
      }
  </code></pre>
  
  <p>What am I missing to make the JSON data update my SQL table?  I have scoured Google search results and I haven't found much information.  Any help would be so greatly appreciated.  </p>