Creating Json string in C# with column headings

Question

the code below is working perfectly but I would like to know a way to pull the column headings out too? I would like it inside my while loop if possible. Hopefully someone here can help.

Thanks, James.

namespace WorldViewNet.include
{
    public class ImageHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            object data = LoadAPI(context);
            string jsonData = JsonConvert.SerializeObject(data);
            context.Response.Write(jsonData);
        }

        public List<object[]> LoadAPI(HttpContext context)
        {
            List<object[]> list = new List<object[]>();
            SqlConnection sqlConnection1 = new SqlConnection(globalLibrary.NewConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;
            string tablename = context.Request.QueryString["tablename"];
            string maybeID = context.Request.QueryString["ID"];

            cmd.CommandText = "SELECT * FROM " + tablename;

            if (maybeID != null)
            {
                cmd.CommandText += " WHERE " + tablename + "ID =" + maybeID;
            }

            cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();

            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                object[] row = new object[23];
                reader.GetValues(row);
                list.Add(row);
            }
            sqlConnection1.Close();
            return list;
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }

    }
}

Your code is wide open to SQL injection. Use parameters and, if you really need to inject the table name, use whitelist validation! — Tim S., Dec 18 '14 at 16:32
Will Do Tim! Just want to get it displaying what I want before adding security measures plus it's a internal application — James Garner, Dec 18 '14 at 16:39

Icemanind · Accepted Answer · 2014-12-18T16:32:20.720

0

You should be able to simply do this:

reader = cmd.ExecuteReader();
var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();

columns should be a List now of all your column names.

If you need a non-Linq way to do this:

reader = cmd.ExecuteReader();
var columns = new List<string>();

for(int z=0;z<reader.FieldCount;z++)
{
   columns.Add(reader.GetName(z));
}

edited Dec 18 '14 at 16:32

answered Dec 18 '14 at 16:27

Icemanind

47,519
50
171
296

Thanks for a fast response, how would I intergrate that with the JsonConvert? Thanks – James Garner Dec 18 '14 at 16:38
@JamesGarner - Is your goal to make JSON.NET use your database field names as Json keys? – Icemanind Dec 18 '14 at 16:43
You should look at this stackoverflow question it might provide you with new insights http://stackoverflow.com/questions/4535840/deserialize-json-object-into-dynamic-object-using-json-net – TYY Dec 18 '14 at 16:48

Creating Json string in C# with column headings

1 Answers1