I have a json string returned from an api. Here are the classes;

namespace APItest
{
public class Rootobject
{
    public bool success { get; set; }
    public int status { get; set; }
    public string message { get; set; }
    public Result[] results { get; set; }
}

    public class Result
    {
        public Referral referral { get; set; }
        public Customer customer { get; set; }
        public Closing closing { get; set; }
    }

    public class Referral
    {
        public long id { get; set; }
        public DateTime? created_on { get; set; }
        public DateTime? accepted_on { get; set; }
    }

    public class Customer
    {
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string email { get; set; }
        public string phone { get; set; }
    }

    public class Closing
    {
        public DateTime? date { get; set; }
        public int? home_price { get; set; }
    }
}

Eventually I need to pass these values to a SQL stored procedure. The problem I'm having is not all key value pairs exist all the time. If the referral hasn't been accepted, the referral.accepted_on key and value don't exist in the string. If there is no closing, the entire closing class will be missing.

I've tried

var deserializedJson = JsonSerializer.Deserialize<Rootobject>(json);
            var referralFields = deserializedJson.results.Select(i => new 
            { 
                i.referral.id, 
                i.referral.created_on,
                i.referral.accepted_on,

                i.customer.firstname,
                i.customer.lastname,
                i.customer.email,
                i.customer.phone,

                i.closing.date,
                i.closing.home_price
            
            }).ToList();

            SqlConnection Conn = new SqlConnection(sqlConnectionString);
            SqlCommand sqlCmd = new SqlCommand("uspDwellfulReferralsInsert", Conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;

            Conn.Open();

            foreach (var field in referralFields)
            {

                sqlCmd.Parameters.Add(new SqlParameter("@ReferralId", field.id));

                if (field.created_on != null)
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@ReferralCreatedOn", field.created_on));
                }

                if (field.accepted_on != null)
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@ReferralAcceptedOn", field.accepted_on));
                }
                if (field.firstname != null)
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@CustomerFirstName", field.firstname));
                }

                if (field.lastname != null)
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@CustomerLastName", field.lastname));
                }

                if (field.email != null)
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@CustomerEmail", field.email));
                }

                if (field.phone != null)
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@CustomerPhone", field.phone));
                }

                if (field.date != null)
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@ClosingDate", field.date));
                }

                if (field.home_price != null)
                {
                    sqlCmd.Parameters.Add(new SqlParameter("@ClosingHomePrice", field.home_price));
                }

                sqlCmd.ExecuteNonQuery();
                sqlCmd.Parameters.Clear();
            }
            
            Conn.Close();
}

This works when all key value pairs are present, but fails when some are missing. I won't go through the numerous things I've tried, but I think I've managed to generate every error message known to man. Nothing I've tried works. How do you account for missing pairs, or an entire missing class?

CodePudding user response：

Is there any specific reason why you use the linq method to retrieve properties from a list to generate a new list? You can simplify your code by iterating on the main results property of the object:

        var deserializedJson = JsonSerializer.Deserialize<Rootobject>(json);

        foreach (var obj in deserializedJson.results)
        {
            if(obj.referral.created_on != null)
            {
                sqlCmd.Parameters.Add(new SqlParameter("@ReferralCreatedOn", obj.referral.created_on));
            }
            if (obj.referral.accepted_on != null)
            {
                sqlCmd.Parameters.Add(new SqlParameter("@ReferralAcceptedOn", obj.referral.accepted_on));
            }
            if (obj.customer.firstname != null)
            {
                sqlCmd.Parameters.Add(new SqlParameter("@CustomerFirstName", obj.customer.firstname));
            }
            // rest of checks
        }

CodePudding user response：

I need to pass these values to a SQL stored procedure. The problem I'm having is not all key value pairs exist all the time.

Why not, in the database, create a User Defined Table Type which can have each of the models as sub User Defined Table types. Then adjust sproc to instead take the top level table type instead.

To do this the code will need to create a user defined table type, which is like the SqlParameter processing you do now.

This works when all key value pairs are present, but fails when some are missing.

Using User Defined Table types comes into the sproc as individual tables (1-N); if there is a missing sub table, the sproc should be able to handle that indirectly or directly if need be.

The added benefit of using Table Types is that the Merge statement can handle insert/updates quite easily, since the data is already in tables instead of scalars; sweet.