Json data call error

[robdineen]

Hello there. I am a newbie and am trying to learn C#

I have been writing a script to call some data via a Json Url.

My code below will hopefully call data from the json url and insert into a sql server table.

See script below and Images attached.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization;
using System.Data.SqlClient;
using System.Configuration;
#endregion

namespace ST_210806adc57542e086c637425fdaf2d3
{
    
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{


		
		public void Main()
		{
            downloadjson();
		}

        private void downloadjson()
        {
            var w = new WebClient();

            string Conn = @"Sql Server Connection";


            string url = "json URL";

            var jsondata = string.Empty;

            jsondata = w.DownloadString(url);

            var jss = new JavaScriptSerializer();

            var dict = jss.Deserialize<club>(jsondata);

            using (SqlConnection conn = new SqlConnection(Conn))
            {


                conn.Open();

                //try
                //{

                foreach (var  club in dict.data)
                {
                    int id = (int)dict.id;
                    string name = dict.name;
                    int countyid = (int)dict.county_id;
                    string status = dict.status;
                    string lastUpdated = dict.last_updated;

                    SqlCommand sql = new SqlCommand("INSERT INTO [Staging].[Clubs] ([id],[Name], [Country_ID] ,[status] ,[Last_Updated] ) values (@id, @name, @countyid, @status, @lastUpdated)", conn);

                    sql.Parameters.AddWithValue("@id", id);
                    sql.Parameters.AddWithValue("@name", name);
                    sql.Parameters.AddWithValue("@countyid", countyid);
                    sql.Parameters.AddWithValue("@status", status);
                    sql.Parameters.AddWithValue("@lastUpdated", lastUpdated);

                    sql.ExecuteNonQuery();

                    sql.Parameters.Clear();

                }

            }
        }

            //    catch (Exception e)
            //{

            //    FailComponent(e.ToString());

            //}

    public class club
    {

            public int id { get; set; }
        public string name { get; set; }
        public int? county_id { get; set; }
        public string status { get; set; }
        public string last_updated { get; set; }


        public Dictionary<object, object> data { get; set; }

      

    }




      

    }





        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}

I have attached a screenshot of error when I debugged the code.

and I also got the below error.

---------------------------------------------------------------------------------------------------------------------------------------------------------------

DTS Script task has encountered an exception in user code

project name: bla bla bla

Exception has been thrown by the target of an invocation


at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

--------------------------------------------------------------------------------------------------------------------------------------------------------------

Kind Regards

Rob

[robdineen]

Forgot to mention, I am using .net 4.0

[Tron]

Looks like either "dict" or "dict.data" variable is null.

Test the value before running your for loop.

if ((dict != null) and (dict.data != null))

[robdineen]

Hi Tron

How would I implement this.

I have added two if statements to both Int variables.

but still getting the same error. see below

could you help with your suggestion please.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization;
using System.Data.SqlClient;
using System.Configuration;
#endregion

namespace ST_210806adc57542e086c637425fdaf2d3
{
    
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{


		
		public void Main()
		{
            downloadjson();
		}

        private void downloadjson()
        {
            var w = new WebClient();

            string Conn = @"sql connection";


            string url = "json_URL";

            var jsondata = string.Empty;

            jsondata = w.DownloadString(url);

            var jss = new JavaScriptSerializer();

            var dict = jss.Deserialize<club>(jsondata);

            using (SqlConnection conn = new SqlConnection(Conn))
            {


                conn.Open();

                //try
                //{

                foreach (var  club in dict.data)
                {
                    
                    int? id = 0;
                    if (dict.id != null)
                    {
                        id = (int)dict.id;
                    }else
                            {
                        id = 0;
                            }


                    string name = dict.name;


                     int? countyid = 0;
                    if (dict.county_id != null)
                    {
                        countyid = (int)dict.county_id;
                    }else
                    
                            {
                                countyid = 0;
                            } 

                    string status = dict.status;
                    string lastUpdated = dict.last_updated;

                    SqlCommand sql = new SqlCommand("INSERT INTO [Staging].[Clubs] ([id],[Name], [Country_ID] ,[status] ,[Last_Updated] ) values (@id, @name, @countyid, @status, @lastUpdated)", conn);

                    sql.Parameters.AddWithValue("@id", id);
                    sql.Parameters.AddWithValue("@name", name);
                    sql.Parameters.AddWithValue("@countyid", countyid);
                    sql.Parameters.AddWithValue("@status", status);
                    sql.Parameters.AddWithValue("@lastUpdated", lastUpdated);

                    sql.ExecuteNonQuery();

                    sql.Parameters.Clear();

                }

            }
        }

            //    catch (Exception e)
            //{

            //    FailComponent(e.ToString());

            //}

    public class club
    {

            public int? id { get; set; }
        public string name { get; set; }
        public int? county_id { get; set; }
        public string status { get; set; }
        public string last_updated { get; set; }


        public Dictionary<object, object> data { get; set; }

      

    }




      

    }





        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}

[Tron]

if ((dict != null) and (dict.data != null)) 
{
                foreach (var  club in dict.data)
                {
                      .....
                }
}
else
{
     MessageBox.Show("My JSON data is null");
}

[robdineen]

hi Tron

thank you. yes I have tested this and the dict.data is null.

but when I debug I can see the Json string in the jsondata variable.

any ideas?

so strange why the columns / nodes are not being picked up

[Tron]

Check out http://www.tomasvera.com/programming...-json-objects/

The issue must be in your JavaScriptSerializer.

[Arjay]

Check out http://www.tomasvera.com/programming...-json-objects/

The issue must be in your JavaScriptSerializer.

Or the json string doesn't match what is defined in the club class. Or you need to handle json into dictionaries as a special case. See http://weblogs.asp.net/hajan/javascr...eserialization

[robdineen]

Thank you all for your help.

very greatful.

I managed to sort out the problem from a lots of variations of examples.

see my working code below.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization;
using System.Data.SqlClient;
using System.Configuration;
#endregion

namespace ST_210806adc57542e086c637425fdaf2d3
{
    
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{


		
		public void Main()
		{
            downloadjson();
		}

        public void downloadjson()
        {

            string Conn = @"sql server connection";


            string url = "json url";



            using (SqlConnection conn = new SqlConnection(Conn))
            {

                conn.Open();

                ClubsImport outPutMetrics = GetWebServiceResult(url);

                foreach (var club in outPutMetrics.clubs)
                {

                    int? id = (int)club.id;




                   
                    string name = club.name;

                    int? countyid = (int)club.county_id;

                   
                    string status = club.status;
                    string lastUpdated = club.last_updated;

                    SqlCommand sql = new SqlCommand("INSERT INTO [Staging].[Clubs] ([id],[Name], [Country_ID] ,[status] ,[Last_Updated] ) values (@id, @name, @countyid, @status, @lastUpdated)", conn);

                    sql.Parameters.AddWithValue("@id", id);
                    sql.Parameters.AddWithValue("@name", name);
                    sql.Parameters.AddWithValue("@countyid", countyid);
                    sql.Parameters.AddWithValue("@status", status);
                    sql.Parameters.AddWithValue("@lastUpdated", lastUpdated);



                    sql.ExecuteNonQuery();

                    sql.Parameters.Clear();


                    //return; ////////////////////////
                }

            }

        }

             private ClubsImport GetWebServiceResult(string url)
    {

       //' var w = new WebClient();

        HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(url);

        HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();

        ClubsImport jsonResponse = null;


        Stream responseStream = httpWResp.GetResponseStream();

        string jsondata = null;

        //jsondata = w.DownloadString(url);

        using (StreamReader reader = new StreamReader(responseStream))
        {

            jsondata = reader.ReadToEnd().Replace("\\", "");

            reader.Close();

        }

var jss = new JavaScriptSerializer();
        jsonResponse = jss.Deserialize<ClubsImport>(jsondata.Trim('"'));

        



        var dict = jss.Deserialize<ClubsImport>(jsondata);

       
    return jsonResponse;

    }

   public class Club
    {
        public int id { get; set; }
        public string name { get; set; }
        public int? county_id { get; set; }
        public string status { get; set; }
        public string last_updated { get; set; }
    }

   public class ClubsImport
   {

       public ClubsImport()
       {

       }

       public System.Collections.Generic.List<Club> clubs { get; set; }

   }

    }
    }