create JSON string from SqlDataReader
Solution 1
Got it! Here's the C#...
// ... SQL connection and command set up, only querying 1 row from the table
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
JsonWriter jsonWriter = new JsonTextWriter(sw);
try {
theSqlConnection.Open(); // open the connection
// read the row from the table
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
int fieldcount = reader.FieldCount; // count how many columns are in the row
object[] values = new object[fieldcount]; // storage for column values
reader.GetValues(values); // extract the values in each column
jsonWriter.WriteStartObject();
for (int index = 0; index < fieldcount; index++) { // iterate through all columns
jsonWriter.WritePropertyName(reader.GetName(index)); // column name
jsonWriter.WriteValue(values[index]); // value in column
}
jsonWriter.WriteEndObject();
reader.Close();
} catch (SqlException sqlException) { // exception
context.Response.ContentType = "text/plain";
context.Response.Write("Connection Exception: ");
context.Response.Write(sqlException.ToString() + "\n");
} finally {
theSqlConnection.Close(); // close the connection
}
// END of method
// the above method returns sb and another uses it to return as HTTP Response...
StringBuilder theTicket = getInfo(context, ticketID);
context.Response.ContentType = "application/json";
context.Response.Write(theTicket);
... so the StringBuilder sb
variable is the JSON object that represents the row I wanted to query. Here is the JavaScript...
$.ajax({
type: 'GET',
url: 'Preview.ashx',
data: 'ticketID=' + ticketID,
dataType: "json",
success: function (data) {
// data is the JSON object the server spits out
// do stuff with the data
}
});
Thanks to Scott for his answer which inspired me to come to my solution.
Hristo
Solution 2
My version:
This doesn't use DataSchema and also wraps the results in an array, instead of using a writer per row.
SqlDataReader rdr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
using (JsonWriter jsonWriter = new JsonTextWriter(sw))
{
jsonWriter.WriteStartArray();
while (rdr.Read())
{
jsonWriter.WriteStartObject();
int fields = rdr.FieldCount;
for (int i = 0; i < fields; i++)
{
jsonWriter.WritePropertyName(rdr.GetName(i));
jsonWriter.WriteValue(rdr[i]);
}
jsonWriter.WriteEndObject();
}
jsonWriter.WriteEndArray();
}
Solution 3
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();
Related videos on Youtube
Comments
-
Hristo almost 2 years
UPDATE
I figured it out. Check out my answer below.
I'm trying to create a JSON string representing a row from a database table to return in an HTTP response. It seems like Json.NET would be a good tool to utilize. However, I'm not sure how to do build the JSON string while I'm reading from the database.
The problem is marked by 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(); } } }
The problem is that I don't know how to read each column from the row from the
SqlReader
such that I can callWriteValue
and give it the correct information and attach it to the correct column name. So if a row looks like this...| FirstName | LastName | Email |
... how would I create a
JsonWriter
for each such row such that it contains all column names of the row and the corresponding values in each column and then use thatJsonWriter
to build a JSON string that is ready for returning through an HTTP Response?Let me know if I need to clarify anything.
-
Code Maverick about 13 yearsEdited answer to show example off of msdn. You can also view other ways to do something similar on that page.
-
Hristo about 13 years@Scott... awesome! I'll put this together. Once I get my upvoting privileges back, I'll give you a +1
-
Code Maverick about 13 yearsObviously though, from the above you can deduce how to incorporate that into your specific need.
-
Hristo about 13 years@Scott... now this leads to another question. How would I build the JsonWriter using this looping? Can I predefine the property names and then add the values as I loop through the columns?
-
Code Maverick about 13 years@Hristo - Let me edit the answer again for you. I've never used Json.NET before, but I will edit to what I would try.
-
Hristo about 13 years@Scott... I appreciate it. Take your time. I won't be working on this until Friday anyway.
-
Code Maverick about 13 years@Hristo - Updated. Not sure if that will work or if that's exactly what you want, but that's what I assume you are talking about.
-
Hristo about 13 years@Scott... ahhh cool! That looks like it should work. I'll try it out later this week when I get back to work and I'll let you know. Much appreciated!!!
-
Code Maverick about 13 years@Hristo - No problem, let me know ... I'd be interested to know as well! Good luck.
-
Hristo about 13 years@Scott... definitely. Thanks!
-
Hristo about 13 years@Scott... I implemented your code above and its not working totally right. I think the problem is the
GetSchemaTable()
. I don't want the whole table, just a row that theSqlReader
will read. What you suggested works but its just not getting the correct row and column names -
Hristo about 13 years@Scott... I figured it out. Check out my solution: stackoverflow.com/questions/5554472/…. Thanks for your help!
-
Deep in the Code almost 11 yearsVery helpful! Thank you very much!
-
Deep in the Code almost 11 yearsThis answer, combined with the one at stackoverflow.com/a/14818354/2079606 has solved my problem! Thanks very much.
-
mike over 9 yearsShould use a
StringBuilder
object or something of the like, otherwise you're rebuilding the entire String and copying it over and over -
JaKXz over 9 yearsIf you had multiple rows in your datareader, would this give you an array of objects in your JSON response? i.e.
[{ "row1-col1":"dataABC", "row1col2":"dataDEF" }, { "row2col1": "more data", "row2col2": "even more" }]
- I'm somewhat surprised Microsoft hasn't provided a built in method for this in .NET 4.5+... -
THE JOATMON over 7 yearsI really wish they would make @mentions dynamic.