ExecuteXmlReader在来自SQL Server的特定JSON上失败

我将继续并建议您使用错误的api来读取for json查询结果。下面是一个实现sqlcommand.executejsonreader()扩展方法的小助手类。

    static class SqlJsonUtils
    {

        public static Newtonsoft.Json.JsonReader ExecuteJsonReader(this SqlCommand cmd)
        {
            var rdr = cmd.ExecuteReader();
            var jr = new Newtonsoft.Json.JsonTextReader(new SqlJSONReader(rdr));
            return jr;

        }

        class SqlJSONReader : System.IO.TextReader
        {
            SqlDataReader rdr;
            string currentLine = "";
            int currentPos = 0;
            public SqlJSONReader(SqlDataReader rdr)
            {
                this.rdr = rdr;
            }
            public override int Peek()
            {
                return GetChar(false);
            }
            public override int Read()
            {
                return GetChar(true);
            }
            public int GetChar(bool Advance)
            {
                while (currentLine.Length == currentPos)
                {
                    if (!rdr.Read())
                    {
                        return -1;
                    }
                    currentLine = rdr.GetString(0);
                    currentPos = 0;
                }
                int rv = (int)currentLine[currentPos];
                if (Advance) currentPos += 1;
                return rv;
            }

            public override void Close()
            {
                rdr.Close();
            }

        }


    }

从那以后我就把这个做成了一个可以下载的nuget包。见 SQLJSONReader 有异步更新。

我采纳了大卫的建议,并将其作为答案,但我需要整个原始的json。

所以我添加了 Newtonsoft.Json.JsonTextReader 因为它不会只返回一个字符串并通过调用 ReadAll .

注意它使用了newtonsoft的 JsonConvert.DeserializeObject 。

代码

public static SqlJSONReader ExecuteJsonReader(this SqlCommand cmd)
{
    var rdr = cmd.ExecuteReader();
    return new SqlJSONReader(rdr);
}

public class SqlJSONReader : System.IO.TextReader
{
    private SqlDataReader SqlReader   { get; set; }
    private string CurrentLine        { get; set; }
    private int CurrentPostion        { get; set; }

    public SqlJSONReader(SqlDataReader rdr)
    {
        CurrentLine = "";
        CurrentPostion = 0;
        this.SqlReader = rdr;
    }
    public override int Peek()
    {
        return GetChar(false);
    }
    public override int Read()
    {
        return GetChar(true);
    }
    public int GetChar(bool Advance)
    {
        while (CurrentLine.Length == CurrentPostion)
        {
            if (!SqlReader.Read())
            {
                return -1;
            }
            CurrentLine = SqlReader.GetString(0);
            CurrentPostion = 0;
        }
        var rv = CurrentLine[CurrentPostion];
        if (Advance) 
            CurrentPostion += 1;

        return rv;
    }

    public string ReadAll()
    {
        var sbResult = new StringBuilder();

        if (SqlReader.HasRows)
        {
            while (SqlReader.Read())
                sbResult.Append(SqlReader.GetString(0));

        }
        else
            return string.Empty;

        // Clean up any JSON escapes before returning
        return  JsonConvert.DeserializeObject(sbResult.ToString()).ToString();
    }

    public override void Close() { SqlReader.Close(); }
}

用法

using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = conn.CreateCommand())
{

    cmd.CommandText = "exec [dbo].[GetPhoto] @PhotoId=4";
    conn.Open();
    var rdr = cmd.ExecuteJsonReader();

    string jsonResult = rdr.ReadAll();

    conn.Close();

}