c# SELECT with FOR JSON

user_679099 0
Viewed 6660

I'm using SQL Server 2008 R2 and I want to build a web form in ASP.NET that runs a SELECT command on my database and creates a json from it.

This is my code:

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    String query = "SELECT * FROM dbo.apartments FOR JSON";

    using (SqlCommand command = new SqlCommand(query, connection))
    {
        connection.Open();

        var jsonResult = new StringBuilder();
        var reader = command.ExecuteReader();

        if (!reader.HasRows)
        {
            jsonResult.Append("[]");
        }
        else
        {
            while (reader.Read())
            {
                jsonResult.Append(reader.GetValue(0).ToString());
            }
        }

        connection.Close();
    }
}

When I run it, I get this error:

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'JSON'.

sql-server asp.net sql .net c#
5 Answers

As Lennart Stoop Suggested a option is to read all the items to your object and then just serialize to Json with JSON.NET. 

Nuget package needed NewtonSoft.Json
using Newtonsoft.Json;

Example is

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
   String query = "SELECT * FROM dbo.apartments";

   using (SqlCommand command = new SqlCommand(query, connection))
   {
       connection.Open();
       List<MyObject> myObjectList= new List<MyObject>();
       var reader = command.ExecuteReader();
       if (reader.HasRows)
       {
          while (reader.Read())
          {    MyObject myObject = new MyObject();
               myObject.Name = reader["Name"].ToString();
               myObjectList.Add(myObject);
          }
       }
      var JsonResult = JsonConvert.SerializeObject(myObjectList);
    } 
}

JsonResult is the json object that you want

EDIT modified to work with List for beter example

user_73246310

I think you are using old version of SQL Server. JSON feature was added to SQL version 2016.

user_20368280

Format query results as JSON with FOR JSON is available starting with 2016. You can read data with SqlDataReader and then create json manually in C# code (e.g with Json.Net, see Creating JSON)

user_42425900

The exception error you are getting is due to the syntax error in your query. When using FOR JSON keywords you need to end it with either AUTO or PATH. So, your query needs to be either:

String query = "SELECT * FROM dbo.apartments FOR JSON AUTO";

or:

String query = "SELECT * FROM dbo.apartments FOR JSON PATH";
user_42591440

Note : SQL Server 2016 adds new support for the JSON format.

Change your SQL and use FOR JSON AUTO or FOR JSON PATH to get the expected output

Look at the syntax

user_62308630
Related