Create a WebService in ASP.NET that returns indefinite number of columns in a database


Normally this is the structure of a WebService that I use in a project, but now I need to bring all the columns of a table that can have N number of columns, in the example I only receive 4 columns that exist, but what happens when I have 12, 15 or 20 columns whose names are not known and I want them to receive the same treatment through the WebService (convert to json).

The table that returns N number of columns is generated with a dynamic query in SQL and changes the number of columns constantly.

        public string getInformacion()
            List<condDet> p = new List<condDet>();

            string ConnectString = ConfigurationManager.ConnectionStrings["CTS"].ToString();

            using (SqlConnection cn = new SqlConnection(ConnectString))
                string qry = "SELECT * FROM TABLAEJEMPLO";

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = qry;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cn;


                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.HasRows)
                    while (dr.Read())
                        condDet cpData = new condDet();
                        cpData.NOMBRE = dr["NOMBRE"].ToString();
                        cpData.EXC = dr["EXC"].ToString();
                        cpData.PAIS = dr["PAIS"].ToString();
                        cpData.NOMBRE_PAIS = dr["NOMBRE_PAIS"].ToString();
                string json = JsonConvert.SerializeObject(p);
                return json;

public class condDet
        public string NOMBRE { get; set; }
        public string EXC { get; set; }
        public string PAIS { get; set; }
        public string NOMBRE_PAIS { get; set; }
asked by Ricardo Soria 05.10.2018 в 03:36

1 answer


You can use a DataTable that loads directly from the query

DataTable dtTabla = new DataTable();
SqlDataReader dr = cmd.ExecuteReader();
string json = JsonConvert.SerializeObject(dtTabla);
return json;
answered by 05.10.2018 / 16:00