trying to use json array to insert data in sql with c# but still getting Newtonsoft.Json.Linq.JValue error?

Question

I'm passing an array of JSON data from client-side to server-side (c#). However when I try to insert this data into a SQL table I'm having this Newtonsoft.Json.Linq.JValue just at the moment of the query execution.

Here's the code I have so far:

Client (js);

var json = [];

var Firstcotizacion = {
    idCotizacion: "111111",
    idProyecto: "8047",
    nombreProyecto: "Edificio Caiquen",
    idProducto: "MLC462815278"
}


var Secondcotizacion = {
    idCotizacion: "222222",
    idProyecto: "1234",
    nombreProyecto: "Edificio malbec",
    idProducto: "MLC29870FD"
}


json.push(Firstcotizacion)
json.push(Secondcotizacion)

$.post("../Ajax/GuardaCotizacionesPI", { json: JSON.stringify(json) }, function (data) {
    console.log(data)
});

Server-side (C#):

public string GuardarCotizacion(string json)
{
    string SP_INSERTA_COTIZACIONPI = "SVI_CPI_COTIZACION_PI";
    dynamic cotizaciones = JsonConvert.DeserializeObject(json);

    foreach (var cotizacion in cotizaciones)
    {
        using (SqlConnection conexion = new SqlConnection(Inventa.PazCorp.Data.BaseDatos.StringConexionGestionContactos(System.Configuration.ConfigurationManager.AppSettings["ambiente"].ToString())))
        {
            conexion.Open();
            using (SqlCommand comm = new SqlCommand(SP_INSERTA_COTIZACIONPI, conexion))
            {
                comm.CommandType = System.Data.CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("IDCOTIZACION", cotizacion.idCotizacion);
                comm.Parameters.AddWithValue("IDPROYECTO", cotizacion.idProyecto);
                comm.Parameters.AddWithValue("NOMBRE_PROYECTO", cotizacion.nombreProyecto);
                comm.Parameters.AddWithValue("IDPRODUCTO", cotizacion.idProducto);
                comm.ExecuteNonQuery(); //Here is where the error appears
            }
        }
    }
    return json;
}

Any Idea what could be wrong?

You make a call to /Ajax/GuardaCotizacionesPI but then you show a method called GuardarCotizacion. How is the 1st connected to the 2nd? — Peter B
– Peter B, Commented Feb 21, 2020 at 14:24
Why are you deserialising to dynamic though? Use a concrete class and all this goes away. Also, every time you use dynamic, a kitten dies. — DavidG
– DavidG, Commented Feb 21, 2020 at 14:24
@DavidG : Is telling the purest truth. Why are you using dynamic ? You should use explicit type. Try implementing a List<Cotizacion> in a Cotizaciones class, and use JsonConvert.DeserializeObject<Cotizaciones>(json). — SunPaz
– SunPaz, Commented Feb 21, 2020 at 14:28

DavidG · Accepted Answer · 2020-02-21 14:34:29Z

2

Because you use the non-generic version of JsonConvert.DeserializeObject, JSON.Net will deserialise everything to it's own objects like JObject and JValue. And because you are hiding all of that by using dynamic, you are susceptible to all sorts of runtime errors.

However, if you use a concrete class though, you will get type-safe code. For example:

public class MyThing
{
    public string idCotizacion { get; set; }
    public string idProyecto { get; set; }
    public string nombreProyecto { get; set; }
    public string idProducto { get; set; }
}

Now use the generic method:

var cotizaciones = JsonConvert.DeserializeObject<List<MyThing>>(json);

And the rest of your code should fit right in, but is now much clearer and safe.

As an aside, you should not be using AddWithValue.

edited Feb 21, 2020 at 14:34

answered Feb 21, 2020 at 14:28

DavidG

120k13 gold badges232 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SunPaz · Accepted Answer · 2020-02-21 14:33:34Z

Based on the code you posted, I would implement a Cotizacion class :

public class Cotizacion {
   public string idCotizacion {get; set;}
   public string idProyecto {get; set;}
   public string nombreProyecto {get; set;}
   public string idProducto{get; set;}
}

And then do something like :

public string GuardarCotizacion(string json)
        {

            string SP_INSERTA_COTIZACIONPI = "SVI_CPI_COTIZACION_PI";
            var cotizaciones = JsonConvert.DeserializeObject<List<Cotizacion>>(json);

            foreach (var cotizacion in cotizaciones)
            {
                using (SqlConnection conexion = new SqlConnection(Inventa.PazCorp.Data.BaseDatos.StringConexionGestionContactos(System.Configuration.ConfigurationManager.AppSettings["ambiente"].ToString())))
                {
                    conexion.Open();
                    using (SqlCommand comm = new SqlCommand(SP_INSERTA_COTIZACIONPI, conexion))
                    {
                        comm.CommandType = System.Data.CommandType.StoredProcedure;
                        comm.Parameters.AddWithValue("IDCOTIZACION", cotizacion.idCotizacion);
                        comm.Parameters.AddWithValue("IDPROYECTO", cotizacion.idProyecto);
                        comm.Parameters.AddWithValue("NOMBRE_PROYECTO", cotizacion.nombreProyecto);
                        comm.Parameters.AddWithValue("IDPRODUCTO", cotizacion.idProducto);
                        comm.ExecuteNonQuery(); //Here is where the error appears
                    }
                }
            }
            return json;
        }

Collectives™ on Stack Overflow

trying to use json array to insert data in sql with c# but still getting Newtonsoft.Json.Linq.JValue error?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related