I am executing stored procedures in my project WCF C#
. From one of my SP I receive something like this:
CeldaId
CeldaIndicadorCodigo
CeldaIndicadorTipoCodigo
Then with the value of Celda Id
I call another SP that brings me:
CeldaKeyCodigo
CeldaKeyValor
From the first SP I get CeldaId
and with that value I get the results of the second SP.
My idea is from my service to deliver an array JSON
more or less with this structure
Celdas : [
IndicadorCodigo : [
{
CeldaKeyCodigo : valor,
CeldaKeyValor : valor
},
{
CeldaKeyCodigo : valor,
CeldaKeyValor : valor
}
]
]
That is, group the values of the query of my second SP by the value that IndicatorCodigo has.
The way I did it was the following... I created a class called Cell with the fields named above plus a List of a class called CellKeyVal with the fields returned by my second SP.
public class Celda
{
[DataMember]
public int celda_id { get; set; }
[DataMember]
public int celda_indicador_codigo { get; set; }
[DataMember]
public int celda_indicador_tipo_codigo { get; set; }
[DataMember]
public List<CeldaKeyVal> valores_celda { get; set; }
}
public class CeldaKeyVal
{
[DataMember]
public string celda_key_codigo { get; set; }
[DataMember]
public string celda_key_valor { get; set; }
}
After having this structure, I go through the results like this:
public ListaCeldas GetCheckIn(int gsalid)
{
SqlConnection conn = new SqlConnection(Db);
List < Celda > celdas = new List < Celda > ();
ListaCeldas listaCeldas = new ListaCeldas();
try {
SqlCommand cmd = new SqlCommand("MBL_SEL_CELDAS", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("gsalid", gsalid);
conn.Open();
cmd.ExecuteNonQuery();
DataTable dtCelda = new DataTable();
dtCelda.Load(cmd.ExecuteReader());
foreach(DataRow dr in dtCelda.Rows) {
Celda celda = new Celda();
celda.celda_id = Convert.ToInt32(dr["CeldaId"]);
celda.celda_indicador_codigo = Convert.ToString(dr["CeldaIndicadorCodigo"]);
celda.celda_indicador_tipo = Convert.ToString(dr["CeldaIndicadorTipoCodigo"]);
DataTable dtCeldasKey = new DataTable();
SqlCommand cmd_celdas_key = Helper.GetCeldaValoresKey(celda.celda_id);
dtCeldasKey.Load(cmd_celdas_key.ExecuteReader());
List < CeldaKeyVal > listaCeldaKey = new List < CeldaKeyVal > ();
foreach(DataRow drCeldasKey in dtCeldasKey.Rows) {
CeldaKeyVal celda_key_val = new CeldaKeyVal();
celda_key_val.celda_key_codigo = Convert.ToString(drCeldasKey["CeldaKeyCodigo"]);
celda_key_val.celda_key_valor = Convert.ToString(drCeldasKey["CeldaKeyValue"]);
listaCeldaKey.Add(celda_key_val);
}
celda.valores_celda = listaCeldaKey;
celdas.Add(celda);
}
List < Celda > cl = celdas.OrderBy(c => c.celda_indicador_codigo).ToList();
listaCeldas.celdas = cl;
if (celdas.Count > 0) {
listaCeldas.status = 1;
}
} catch (Exception e) {
listaCeldas.status = 0;
} finally {
conn.Close();
}
return listaCeldas;
}
This works fine for me but not the order I want, this is the JSON I get
{
"GetCheckInResult": {
"celdas": [
{
"celda_id": 1,
"celda_indicador_codigo": "I001",
"celda_indicador_tipo": "T01",
"valores_celda": [
{
"celda_key_codigo": "CODIGO",
"celda_key_valor": "CHECK1"
},
{
"celda_key_codigo": "IMAGEN",
"celda_key_valor": "L76nD.jpg"
},
{
"celda_key_codigo": "SUBTEXTO",
"celda_key_valor": "prueba subtexto 1"
},
{
"celda_key_codigo": "TEXTO",
"celda_key_valor": "Prueba dominio 1"
}
]
},
{
"celda_id": 4,
"celda_indicador_codigo": "I002",
"celda_indicador_tipo": "T03",
"valores_celda": [
{
"celda_key_codigo": "CODIGO",
"celda_key_valor": "codigo"
}
]
},
{
"celda_id": 5,
"celda_indicador_codigo": "I002",
"celda_indicador_tipo": "T03",
"valores_celda": [
{
"celda_key_codigo": "CODIGO",
"celda_key_valor": "ssddsds"
}
]
},
{
"celda_id": 3,
"celda_indicador_codigo": "I003",
"celda_indicador_tipo": "T02",
"valores_celda": [
{
"celda_key_codigo": "CODIGO",
"celda_key_valor": "sdadsa"
},
{
"celda_key_codigo": "IMAGEN",
"celda_key_valor": "L76nD.jpg"
},
{
"celda_key_codigo": "TEXTO",
"celda_key_valor": "Texto 1"
}
]
}
],
"status": 1
}
}
I know that the error is in my class structure but I can't think of how to assemble them to get the format I want to get.
WHAT I EXPECT
Get an array of cells and their values ordered byIndicadorCodigo
Expected json :
{
"GetCheckInResult": {
"celdas": [
"T01" : [
{
"celda_key_codigo": "CODIGO",
"celda_key_valor": "CHECK1"
},
{
"celda_key_codigo": "IMAGEN",
"celda_key_valor": "L76nD.jpg"
},
{
"celda_key_codigo": "SUBTEXTO",
"celda_key_valor": "prueba subtexto 1"
},
{
"celda_key_codigo": "TEXTO",
"celda_key_valor": "Prueba dominio 1"
}
],
"T02" : [
{
"celda_key_codigo": "CODIGO",
"celda_key_valor": "CHECK1"
},
{
"celda_key_codigo": "IMAGEN",
"celda_key_valor": "L76nD.jpg"
},
{
"celda_key_codigo": "SUBTEXTO",
"celda_key_valor": "prueba subtexto 1"
},
{
"celda_key_codigo": "TEXTO",
"celda_key_valor": "Prueba dominio 1"
}
]
],
"status": 1
}
}
Something like this (the names of the fields can change according to your taste):