UPDATE

I believed it. Check out my answer below.


I am trying to produce a JSON string representing a row from the database table to come back within an HTTP response. It appears like Json.NET will be a good tool to make use of. However, I am unsure how you can do build the JSON string while I am reading through in the database.

The issue is marked through the obnoxious comments /******** ********/

// connect to DB
theSqlConnection.Open(); // open the connection

SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows) {

    while(reader.Read()) {

        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);

        using (JsonWriter jsonWriter = new JsonTextWriter(sw)) {

            // read columns from the current row and build this JsonWriter
            jsonWriter.WriteStartObject();
            jsonWriter.WritePropertyName("FirstName");

            // I need to read the value from the database
/******** I can't just say reader[i] to get the ith column. How would I loop here to get all columns? ********/
            jsonWriter.WriteValue(... ? ...);
            jsonWriter.WritePropertyName("LastName");
            jsonWriter.WriteValue(... ? ...);
            jsonWriter.WritePropertyName("Email");
            jsonWriter.WriteValue(... ? ...);

            // etc...
            jsonWriter.WriteEndObject();
        }
    }
}

However , I'm not sure how you can read each column in the row in the SqlReader such will be able to call WriteValue and provide it the right information and fix it towards the correct column title. Therefore if a row appears like this...

| FirstName | LastName | Email |

... wouldso would I produce a JsonWriter for every such row so that it consists of all column names from the row and also the corresponding values in every column after which use that JsonWriter to construct a JSON string that's ready for coming back with an HTTP Response?

Tell me if I have to clarify anything.

EDITED FOR SPECIFIC EXAMPLE:

theSqlConnection.Open();

SqlDataReader reader = sqlCommand.ExecuteReader();
DataTable schemaTable = reader.GetSchemaTable();

foreach (DataRow row in schemaTable.Rows)
{
    StringBuilder sb = new StringBuilder();
    StringWriter sw = new StringWriter(sb);

    using (JsonWriter jsonWriter = new JsonTextWriter(sw)) 
    {    
        jsonWriter.WriteStartObject();

        foreach (DataColumn column in schemaTable.Columns)
        {
            jsonWriter.WritePropertyName(column.ColumnName);
            jsonWriter.WriteValue(row[column]);
        }

        jsonWriter.WriteEndObject();
    }
}

theSqlConnection.Close();