如何在C#中读取由SQL Server发送的Json数据？

Question

如何在C#中读取由SQL Server发送的Json数据？

c#sql-serverentity-frameworkt-sqlado.net

3

我创建了一个存储过程，返回JSON数据：

ALTER PROCEDURE SpEmployeeSel 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ve.*
    FROM dbo.VwEmployee AS ve
    FOR JSON PATH, INCLUDE_NULL_VALUES
END;

这个查询输出的结果是：

[
  {
    "PersonId": 4,
    "FirstName": "Anuj",
    "MiddleName": "",
    "LastName": "Tamrakar",
    "DateofBirth": "2018-01-04T00:00:00",
    "EmployeeId": 1,
    "EmployeeCode": "Emp1",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 5,
    "FirstName": "John",
    "MiddleName": "",
    "LastName": "Pradhan",
    "DateofBirth": "2018-01-04T00:00:00",
    "EmployeeId": 2,
    "EmployeeCode": "Emp2",
    "DesignationId": 2,
    "DesignationName": "Human Resource",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 6,
    "FirstName": "Priyanka",
    "MiddleName": "",
    "LastName": "Khadgi",
    "DateofBirth": "2018-01-03T00:00:00",
    "EmployeeId": 3,
    "EmployeeCode": "Emp3",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 10,
    "FirstName": "Sakar",
    "MiddleName": "",
    "LastName": "Thapa",
    "DateofBirth": "2018-01-09T00:00:00",
    "EmployeeId": 7,
    "EmployeeCode": "Emp4",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 11,
    "FirstName": "Aaa",
    "MiddleName": "",
    "LastName": "asdfasf",
    "DateofBirth": "2018-01-03T00:00:00",
    "EmployeeId": 8,
    "EmployeeCode": "Emp5",
    "DesignationId": 2,
    "DesignationName": "Human Resource",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 12,
    "FirstName": "Bibek",
    "MiddleName": "",
    "LastName": "Thapa",
    "DateofBirth": "2018-01-11T00:00:00",
    "EmployeeId": 9,
    "EmployeeCode": "Emp6",
    "DesignationId": 3,
    "DesignationName": "Staff",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 13,
    "FirstName": "dafgasdf",
    "MiddleName": "",
    "LastName": "asfsdf",
    "DateofBirth": "2018-01-12T00:00:00",
    "EmployeeId": 10,
    "EmployeeCode": "Emp7",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 14,
    "FirstName": "sdfsdf",
    "MiddleName": "asdfsdaf",
    "LastName": "asdfasdf",
    "DateofBirth": "2018-01-03T00:00:00",
    "EmployeeId": 11,
    "EmployeeCode": "Emp8",
    "DesignationId": 2,
    "DesignationName": "Human Resource",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 15,
    "FirstName": "Asdfasf",
    "MiddleName": "asdf",
    "LastName": "asdfasf",
    "DateofBirth": "2018-01-05T00:00:00",
    "EmployeeId": 12,
    "EmployeeCode": "Emp9",
    "DesignationId": 2,
    "DesignationName": "Human Resource",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 16,
    "FirstName": "asdfasf",
    "MiddleName": "aasdfa",
    "LastName": "asdfasf",
    "DateofBirth": "2018-01-12T00:00:00",
    "EmployeeId": 13,
    "EmployeeCode": "Emp10",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 17,
    "FirstName": "1111123123",
    "MiddleName": "asdfasd",
    "LastName": "asdfasdf",
    "DateofBirth": "2018-01-05T00:00:00",
    "EmployeeId": 14,
    "EmployeeCode": "Emp11",
    "DesignationId": 2,
    "DesignationName": "Human Resource",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  }
]

你可以看到该过程在单列中输出了预期的json数据。

现在在Visual Studio中，我尝试通过ADO.NET调用该过程，如下所示：

SqlConnection conn = new SqlConnection("//connection string path");
conn.Open();

SqlCommand command = new SqlCommand("SpEmployeeSEl", conn);
command.CommandType = CommandType.StoredProcedure;

DataTable dt = new DataTable();
SqlDataAdapter sta = new SqlDataAdapter(command);
sta.Fill(dt);

conn.Close();

string result = dt.Rows[0][0].ToString();

现在当我查看输出结果时，我只能得到一半的JSON数据，如下所示：

[
  {
    "PersonId": 4,
    "FirstName": "Anuj",
    "MiddleName": "",
    "LastName": "Tamrakar",
    "DateofBirth": "2018-01-04T00:00:00",
    "EmployeeId": 1,
    "EmployeeCode": "Emp1",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 5,
    "FirstName": "John",
    "MiddleName": "",
    "LastName": "Pradhan",
    "DateofBirth": "2018-01-04T00:00:00",
    "EmployeeId": 2,
    "EmployeeCode": "Emp2",
    "DesignationId": 2,
    "DesignationName": "Human Resource",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 6,
    "FirstName": "Priyanka",
    "MiddleName": "",
    "LastName": "Khadgi",
    "DateofBirth": "2018-01-03T00:00:00",
    "EmployeeId": 3,
    "EmployeeCode": "Emp3",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 10,
    "FirstName": "Sakar",
    "MiddleName": "",
    "LastName": "Thapa",
    "DateofBirth": "2018-01-09T00:00:00",
    "EmployeeId": 7,
    "EmployeeCode": "Emp4",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 11,
    "FirstName": "Aaa",
    "MiddleName": "",
    "LastName": "asdfasf",
    "DateofBirth": "2018-01-03T00:00:00",
    "EmployeeId": 8,
    "EmployeeCode": "Emp5",
    "DesignationId": 2,
    "DesignationName": "Human Resource",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 12,
    "FirstName": "Bibek",
    "MiddleName": "",
    "LastName": "Thapa",
    "DateofBirth": "2018-01-11T00:00:00",
    "EmployeeId": 9,
    "EmployeeCode": "Emp6",
    "DesignationId": 3,
    "DesignationName": "Staff",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 13,
    "FirstName": "dafgasdf",
    "MiddleName": "",
    "LastName": "asfsdf",
    "DateofBirth": "2018-01-12T00:00:00",
    "EmployeeId": 10,
    "EmployeeCode": "Emp7",
    "DesignationId": 1,
    "DesignationName": "Ceo",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:16:00"
  },
  {
    "PersonId": 14,
    "FirstName": "sdfsdf",
    "MiddleName": "asdfsdaf",
    "LastName": "asdfasdf",
    "DateofBirth": "2018-01-03T00:00:00",
    "EmployeeId": 11,
    "EmployeeCode": "Emp8",
    "DesignationId": 2,
    "DesignationName": "Human Resource",
    "InsertPersonId": 1,
    "InsertDate": "2018-01-20T02:17:00"
  },
  {
    "PersonId": 15,
    "FirstName": "Asdfasf",
    "MiddleName": "asdf

json字符串在中途终止。

我尝试查看dataTable的内容，发现字符串被分成了两行，这导致代码dt.Rows [0] [0] .ToString()仅返回第一行，这是问题的根本原因。

所以我不知道为什么字符串被分成了两行。

然后我尝试使用实体框架调用存储过程，如下：

string result;

using (AccountingEntities db = new AccountingEntities())
{
    result = db.SpEmployeeSel().FirstOrDefault();
}

但是结果与破损的json字符串的Ado.Net相同。

如果你们能给出Entity Framework的解决方案，我将不胜感激，因为我的应用程序使用Entity Framework。

更新：我为Entity Framework做的解决方法：

string jsonresult;
using (AccountingEntities db = new AccountingEntities())
{
   List<string> jsonlist= db.SpTestSEl().ToList();
   jsonresult = String.Join("", jsonlist.ToArray());
}

我为Ado.net做的解决方法：

https://learn.microsoft.com/en-us/sql/relational-databases/json/use-for-json-output-in-sql-server-and-in-client-apps-sql-server

注意：Entity框架很烂。

- Anuj Tamrakar

1

请阅读此内容，看看它是否适用于您的情况：Sql Server将FOR JSON查询结果分成约2KB的块。 - Brien Foss

只是随意猜测，也许返回的字符串值对于行列来说太长了？所以它可能会自动将其拆分成多行（理论上根据返回的数据可能超过2行）。您可以通过循环遍历行并将字符串连接在一起。 - Corey Thompson

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- charithsuminda · Accepted Answer

我还没有尝试过这个方法是否有效，但这是我找到的一个解决方案，请您尝试并查看效果。

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());
    }
}