创建SqlDataReader的JSON字符串 [英] create JSON string from SqlDataReader
问题描述
我想通了。 <一href=\"http://stackoverflow.com/questions/5554472/create-json-string-from-sqldatareader/5597472#5597472\">Check如下我的答案。
I figured it out. Check out my answer below.
我试图创建一个JSON字符串重新$ P $从数据库表psenting一个行中的HTTP响应返回。这似乎是 Json.NET 将利用一个很好的工具。但是,我不知道该怎么办建立JSON字符串的,而的我是从数据库中读取的
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();
}
}
}
问题是,我不知道如何从该行从 SQLReader的
阅读等每一列,我可以打电话 WriteValue
,并给它正确的信息并将其连接到正确的列名。所以,如果行看起来像这样...
The problem is that I don't know how to read each column from the row from the SqlReader
such that I can call WriteValue
and give it the correct information and attach it to the correct column name. So if a row looks like this...
| FirstName | LastName | Email |
...我将如何创建一个 JsonWriter
每个这样的行,使其包含行的所有列名,并在每列中的相应值,然后使用 JsonWriter
来构建一个JSON字符串,它是准备通过HTTP响应返回?
... 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 that JsonWriter
to build a JSON string that is ready for returning through an HTTP Response?
让我知道如果我需要澄清什么。
Let me know if I need to clarify anything.
推荐答案
明白了!下面是C#...
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);
...这样的 StringBuilder的SB
变量是JSON对象重新presents我想查询的行。这里是JavaScript ...
... 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
}
});
由于<一个href=\"http://stackoverflow.com/questions/5554472/create-json-string-from-sqldatareader/5554885#5554885\">Scott他的回答启发我去我的解决方案。
Thanks to Scott for his answer which inspired me to come to my solution.
斯托伊奇
这篇关于创建SqlDataReader的JSON字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!