Writing Json string from SQL query in C#

Question

I want to create a json string inside "reader.Read()" how can I do that? This is for a API I'm creating so you can request the page for example api.ashx?tablename=CurrencySymbol&id=5 Hope someone can help

I would like to create json from column values from the database

** Lets not worry about the security of this, it's just for a internal application that only I will use **

    public void ProcessRequest (HttpContext context) 
    { 
        context.Response.Clear();

            string tablename = context.Request.QueryString["tablename"];
            int ID = Int32.Parse(context.Request.QueryString["ID"]);
            context.Response.ContentType = "text/html";

            SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldViewDatabase"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.CommandText = "SELECT * FROM " + tablename "WHERE ID = " + ID;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();

            reader = cmd.ExecuteReader();
            // Data is accessible through the DataReader object here.
            while (reader.Read())
            {
                //context.Response.Write(reader);
            }
            sqlConnection1.Close();

            context.Response.Write(ID);
            context.Response.Write(tablename);
            return;
   }

is the json stored in the database? or are you trying to create json from column values from the database? (also: if you're returning json, claiming "text/html" seems odd, and you should have some using statements (not directives) in there) — Marc Gravell
– Marc Gravell, Commented Dec 18, 2014 at 11:36
Side note: Use parametrized queries to prevent from SQL injections — Sybren
– Sybren, Commented Dec 18, 2014 at 11:38
I would like to create json from column values from the database — JamesJGarner
– JamesJGarner, Commented Dec 18, 2014 at 11:39
Your title and your question do not match, very confusing. Where is the JSON? — DrKoch
– DrKoch, Commented Dec 18, 2014 at 11:40

opewix · Accepted Answer · 2014-12-18 11:55:17Z

2

You cannot get json result from reader.Read() method. And you even do not need such behavior.

Wrap you database rows in concrete class and then just serialize your object to json using JSON.NET library

http://james.newtonking.com/json

Look at it's documentation: http://james.newtonking.com/json/help/index.html

EDIT

public class T1 {}
public class T2 {}

public void ProcessRequest (HttpContext context) 
{
    object data = null;

    string tablename = context.Request.QueryString["tablename"];
    if(tablename == "T1")
    {
        data = LoadTable1Data();
    }
    else if(tablename == "T2")
    {
        data = LoadTable2Data();
    }
    else
    {
        throw new Exception("Unknown tablename: " + tablename);
    }

    string jsonData = JsonConvert.SerializeObject(data);
    context.Response.Write(jsonData);
}

public List<T1> LoadTable1Data()
{
    List<T1> list = new List<T1>();

    ...
    SqlDataReader reader = cmd.ExecuteReader();
    while(reader.Read())
    {
        list.Add(new T1()); // fill T1 object with row data
    }

    return list;
}

edited Dec 18, 2014 at 11:55

answered Dec 18, 2014 at 11:40

opewix

5,1031 gold badge22 silver badges43 bronze badges

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

6 Comments

JamesJGarner Over a year ago

The problem is my DB columns change per table

opewix Over a year ago

You may create a wrapper class for each table. And return a complex object in json format

JamesJGarner Over a year ago

How do I wrap my database rows in concrete class?

JamesJGarner Over a year ago

I get this Error 40 The type or namespace name 'List' could not be found (are you missing a using directive or an assembly

opewix Over a year ago

@JamesJGarner VisualStudio should suggest you to add namespace System.Collections.Generic

|

Collectives™ on Stack Overflow

Writing Json string from SQL query in C#

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related