将SqlDataReader转换为JSON

Question

public string toJSON(SqlDataReader o)
{
    StringBuilder s = new StringBuilder();
    s.Append("[");
    if (o.HasRows)
        while (o.Read())
            s.Append("{" + '"' + "Id" + '"' + ":" + o["Id"] + ", "
            + '"' + "CN" + '"' + ":" + o["CatName"] + ", "
            + '"' + "Ord" + '"' + ":" + o["Ord"] + ","
            + '"' + "Icon" + '"' + ":" + o["Icon"] + "}, ");
    s.Remove(s.Length - 2, 2);
    s.Append("]");
    o.Close();
    return s.ToString();
}

我在这里使用自己的函数进行序列化。我需要知道这是否是一个好方法，或者我应该使用其他方法。顺便说一下，我已经尝试过使用JavaScriptSerializer，但它与SqlDataReader不兼容。谢谢。

- Rawhi

---

学会使用 string.Format，让你的生活更轻松。 - Oded

那么 StringBuilder.AppendFormat 呢？使用逐字字符串文字（以 @ 开头的字符串）呢？ - Oded

这可能在某种程度上有所帮助。谢谢。 但是你认为这个函数好不好！ - Rawhi

Answer 1

如果你想要将某个东西转换为任意JSON格式，你可以通过将其序列化为Dictionary(Of string, object)来进行转换，方法如下：

public IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader)
{
    var results = new List<Dictionary<string, object>>();
    var cols = new List<string>();
    for (var i = 0; i < reader.FieldCount; i++) 
        cols.Add(reader.GetName(i));

    while (reader.Read()) 
        results.Add(SerializeRow(cols, reader));

    return results;
}
private Dictionary<string, object> SerializeRow(IEnumerable<string> cols, 
                                                SqlDataReader reader) {
    var result = new Dictionary<string, object>();
    foreach (var col in cols) 
        result.Add(col, reader[col]);
    return result;
}

然后使用NewtonSoft.Json中的JsonConvert对象来获取您的JSON：

var r = Serialize(reader);
string json = JsonConvert.SerializeObject(r, Formatting.Indented);

更新：如果您只想使用内置方法，并且恰好正在使用MVC，则可以在新序列化的对象上使用内置的Json helper方法：

JsonResult Index(int id) {
    var r = Serialize(reader);
    return Json(r, JsonRequestBehavior.AllowGet);
}

- Jonathan

---

这是替换使用SqlDataSource和AutoGenerateColumns="True"的旧WebForms代码的最佳解决方案，而这正是我所需要的！你为我节省了5分钟的编码时间。爱上了互联网。 - John Zabroski

很高兴你觉得它有用。我也是用它来做同样的事情的。 :) - Jonathan

说实话，这很简单，你会认为NewtonSoft现在已经将其内置到他们的产品中了。 - KWallace

Answer 2

这样应该能完成任务。

private String sqlDatoToJson(SqlDataReader dataReader)
{
    var dataTable = new DataTable();
    dataTable.Load(dataReader);
    string JSONString = string.Empty;
    JSONString = JsonConvert.SerializeObject(dataTable);
    return JSONString;
}

- gerzalez

---

需要通过Nuget添加Newtonsoft.Json才能使用JsonConvert。 - ΩmegaMan

dataTable.load(dataReader) 会一次性将整个结果集加载到内存中吗？ - Display name

最佳答案在这里。 - Nick Painter

是的，根据我的经验，这个答案很可能一次性将整个结果集加载到内存中。对于小型结果集来说效果非常好。 - user3613932

这很棒，但我强烈建议在调试时使用JsonConvert.SerializeObject(dataTable, Formatting.Indented)来获得格式化的结果。 - undefined

Answer 3

在我的工作中，有时数据读取器(DataReader)返回的行数可能会对内存消耗造成问题。以下代码使用了一个JsonWriter(来自于JSON.NET)在流上操作。我们当然可以讨论巨大的JSON文档的实用性，但有时我们的应用场景是由其他人决定的:-)

几点说明：

• 我的SqlDataReader可能包含多个结果集('tables')
• 我可能会将输出发送到FileStream或HttpResponse流
• 我已经'抽象化'了我的对象名称，以匹配每个结果集返回的第一列
• 由于可能存在大型结果集，我使用SqlDataReader的异步方法。
• 我让JSON.NET处理数据读取器结果中实际数据的所有序列化问题。

代码:

var stream = ... // In my case, a FileStream or HttpResponse stream
using (var writer = new JsonTextWriter(new StreamWriter(stream)))
{
    writer.WriteStartObject();  
    do
    {
        int row = 0;
        string firstColumn = null;
        while (await reader.ReadAsync())
        {
            if (row++ == 0)
            {
                firstColumn = reader.GetName(0);
                writer.WritePropertyName(string.Format("{0}Collection", firstColumn));
                writer.WriteStartArray();   
            }
            writer.WriteStartObject();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                if (!reader.IsDBNull(i)) { 
                    writer.WritePropertyName(reader.GetName(i));
                    writer.WriteValue(reader.GetValue(i));
                }
            }
            writer.WriteEndObject(); 
        }
        writer.WriteEndArray();
    } while (await reader.NextResultAsync());

    writer.WriteEndObject();
}

一个异构输出（heterogeneous output）的示例如下：

{
    "ContactCollection": {
        "ContactItem": [{
                "ContactID": "1",
                "Contact": "Testing",
            },
            {
                "ContactID": "2",
                "Contact": "Smith, John",
            },
            {
                "ContactID": "4",
                "Contact": "Smith, Jane",
            }
        ],
        "MessageItem": [{
                "MessageID": "56563",
                "Message": "Contract Review Changed",
            },
            {
                "MessageID": "56564",
                "Message": " Changed",
            },
            {
                "MessageID": "56565",
                "Message": "Contract Review - Estimated Completion Added.",
            }
        ]
    }
}

Reference:

• http://www.newtonsoft.com/json/help/html/Performance.htm

- Eric Patrick

---

在我看来，最佳答案是：其他答案在内存效率方面似乎存在问题。 - dovid

如果两个结果集的形状不同，当您调用NextResultAsync时会发生什么？ - Charles Okwuagwu

@CharlesOkwuagwu，我添加了一个输出示例。 - Eric Patrick

问题在于所有内容都被序列化为 JSON 字符串。 - Kurren

Answer 4

另一个选择是使用James Newton-King的优秀的JSON.NET库 - http://www.newtonsoft.com/json

下面是一个使用它来构建集合并将其输出为JSON序列化字符串的快速示例：

using Newtonsoft.Json;

class Program
{
    static void Main(string[] args)
    {
        ArrayList objs = new ArrayList();

        //get the data reader, etc.
        while(o.Read())
        {
            objs.Add(new
            {
                Id = o["Id"],
                CN = o["CatName"],
                Ord = o["Ord"],
                Icon = o["Icon"]
            });
        }

        //clean up datareader

        Console.WriteLine(JsonConvert.SerializeObject(objs));
        Console.ReadLine();
    }
}

你可以通过将SqlDataReader中的每一行读入匿名对象，然后使用JSON.NET将其序列化为字符串来完成相同的循环操作。

希望这能帮到你！

- David Hoerster

Answer 5

自 SQL Server 2016 起，微软在 SQL 查询中嵌入了此功能。您可以通过在查询末尾使用 FOR JSON 关键字来实现它。

select * from table_example where somecolumn = somecondition FOR JSON AUTO

如需更多详细信息和示例，请查阅此官方文档：使用AUTO模式（SQL Server）自动格式化JSON输出

这里是微软提供的C#代码示例，可用于从SQL查询中获取JSON字符串：点击此处

var queryWithForJson = "SELECT ... FOR JSON";
var conn = new SqlConnection("<connection string>");
var cmd = new SqlCommand(queryWithForJson, conn);
conn.Open();
var jsonResult = new StringBuilder();
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
    jsonResult.Append("[]");
}
else
{
    while (reader.Read())
    {
        jsonResult.Append(reader.GetValue(0).ToString());
    }
}

警告：此解决方案仅适用于SQL SERVER 2016及更高版本。

- Muhammad Saqib

Answer 6

试试这个：

o = cmd.ExecuteReader();
var dataQuery = from d in o.Cast<DbDataRecord>()
                select new
                {
                    Id = (String)d["Id"],
                    CN = (String)d["CatName"],
                    Ord = (String)d["Ord"],
                    Icon = (String)d["Icon"]
                };
var data = dataQuery.ToArray();
JavaScriptSerializer serializer = new JavaScriptSerializer();
String jsonData = serializer.Serialize(data);

- Chandu

---

我一定是错过了问题中的MVC标签。哦，等一下，我没有，因为它根本不存在... - Oded

@Oded：最近非常着迷于MVC :）。如果这让你高兴，我已经更改了帖子以删除答案中的MVC风味。 - Chandu

@Cybernate - 我只是不确定 return Json(...) 是否会为 OP 编译 ;) - Oded

@Oded：谢谢你的观察，不过也许用户并没有在MVC的上下文中使用它。 - Chandu

@Rahwi：我还没对这个函数进行基准测试，但是我认为两个版本的性能几乎相同。使用Linq的这个版本看起来更易读和易于维护。 - Chandu

Answer 7

我使用这段代码，基于Jonathan的回答：

private IEnumerable<Dictionary<string, object>> ConvertToDictionary(IDataReader reader)
{
    var columns = new List<string>();
    var rows = new List<Dictionary<string, object>>();

    for (var i = 0; i < reader.FieldCount; i++)
    {
        columns.Add(reader.GetName(i));
    }

    while (reader.Read())
    {
        rows.Add(columns.ToDictionary(column => column, column => reader[column]));
    }

    return rows;
}

接着：

var rows = this.ConvertToDictionary(reader);

return JsonConvert.SerializeObject(rows, Formatting.Indented);

- Lenin

Answer 8

使用开源库Cinchoo ETL，您可以轻松地将SqlDataReader导出为JSON格式，只需几行代码即可实现。

string connectionstring = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Northwind;Integrated Security=True";
StringBuilder sb = new StringBuilder();

using (var conn = new SqlConnection(connectionstring))
{
    conn.Open();
    var comm = new SqlCommand("SELECT top 2 * FROM Customers", conn);

    using (var parser = new ChoJSONWriter(sb))
        parser.Write(comm.ExecuteReader());
}

Console.WriteLine(sb.ToString());

输出：

[
 {
  "CustomerID": "ALFKI",
  "CompanyName": "Alfreds Futterkiste",
  "ContactName": "Maria Anders",
  "ContactTitle": "Sales Representative",
  "Address": "Obere Str. 57",
  "City": "Berlin",
  "Region": {},
  "PostalCode": "12209",
  "Country": "Germany",
  "Phone": "030-0074321",
  "Fax": "030-0076545"
 },
 {
  "CustomerID": "ANATR",
  "CompanyName": "Ana Trujillo Emparedados y helados",
  "ContactName": "Ana Trujillo",
  "ContactTitle": "Owner",
  "Address": "Avda. de la Constitución 2222",
  "City": "México D.F.",
  "Region": {},
  "PostalCode": "05021",
  "Country": "Mexico",
  "Phone": "(5) 555-4729",
  "Fax": "(5) 555-3745"
 }
]

- Cinchoo

Answer 9

这是对Chandu的Linq答案（使用查询语法from ... select ...）进行增强。如果您喜欢方法语法，以下就是您需要的答案。

drdr = cmd.ExecuteReader();
Record[] recs = drdr.Cast<DbDataRecord>().Select( data=>new Record{
            GraphID=(drdr.IsDBNull(0) ? "" : (string)data["LabelX"])
        , XAxis=(drdr.IsDBNull(1) ? "1999-09-09 00:00:00" : Convert.ToDateTime(data["XDate"]).ToString("yyyy-MM-dd HH:mm:ss"))
        , YVal=(drdr.IsDBNull(2) ? 0 : int.Parse(data["YFreq"].ToString()))
        }).ToArray();

MemoryStream mem = new MemoryStream();
DataContractJsonSerializer szr = new DataContractJsonSerializer(typeof(Record[]));
szr.WriteObject(mem, recs);
String jsonData = Encoding.UTF8.GetString(mem.ToArray(), 0, (int)mem.Length); 

希望这能帮助到某些人。

- Jenna Leaf

---

不需要转换或解析，只需进行强制类型转换即可。 - montelof

Answer 10

这不应该很难。当我想要将搜索结果作为JSON返回到网页时，我所做的就是以下步骤。

首先，需要一个类似于下面这样的类：

public class SearchResult
{
    public string model_no { get; set; }
    public string result_text { get; set; }
    public string url { get; set; }
    public string image_url { get; set; }
}

然后有以下代码。

        string sql_text = "select * from product_master where model_no like @search_string and active=1";
        SqlConnection connection = new SqlConnection(sql_constr);
        SqlCommand cmd = new SqlCommand(sql_text, connection);
        cmd.Parameters.AddWithValue("@search_string", "%" + search_string + "%");
        connection.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        List<SearchResult> searchresults = new List<SearchResult>();

        while (rdr.Read())
        {
            SearchResult sr = new SearchResult();
            sr.model_no = rdr["model_no"].ToString();
            sr.result_text = rdr["product_name"].ToString();
            sr.url = rdr["url_key"].ToString();

            searchresults.Add(sr);

        }
        connection.Close();

        //build json result
        return Json(searchresults, JsonRequestBehavior.AllowGet);

这对我非常有效，保留HTML不解释。

- BenW