CODEWITHSUNDEEP
c#sqlasp.net
JamesJGarner
JamesJGarner

Reputation: 185

Writing Json string from SQL query in C#

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;
   }

Upvotes: 0

Views: 6869

Answers (1)

opewix
opewix

Reputation: 5083

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;
}

Upvotes: 2

Related Questions