Menu
I have created a stored procedure which returns JSON data:
ALTER PROCEDURE SpEmployeeSel
AS
BEGIN
SET NOCOUNT ON;
SELECT ve.*
FROM dbo.VwEmployee AS ve
FOR JSON PATH, INCLUDE_NULL_VALUES
END;
This query outputs:
[
{
"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"
}
]
You can see the procedure outputs the expected json data in a single column.
Now in Visual Studio, I tried calling the procedure through ADO.NET like this
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();
Now When I look at the output result I only get half of the json data like below:
[
{
"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
The json string terminates in middle.
I tried looking the content of dataTable and there the string was broken into two rows which caused the the code dt.Rows[0][0].ToString() to return only first row and this is the root cause of the problem.
So I don't know why the string was broken down into two rows.
I then tried calling the procedure with entity framework like this:
string result;
using (AccountingEntities db = new AccountingEntities())
{
result = db.SpEmployeeSel().FirstOrDefault();
}
But the result was same as Ado.Net with broken json string.
I would really be grateful if you guys give solution of Entity Framework rather than in ADO.NET. Since my application uses Entity Framework.
UPDATE: Workaround That I did for Entity Framework:
string jsonresult;
using (AccountingEntities db = new AccountingEntities())
{
List<string> jsonlist= db.SpTestSEl().ToList();
jsonresult = String.Join("", jsonlist.ToArray());
}
Workaround that I did for 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
Note: Entity framework sucks.
517
asked Oct 27 '25 19:10
I havent tried this whether its working this is one solution i found please try it and see.
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());
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
