JSON Documents from SQL query

Question

_{Wednesday, July 22, 2020 12:30 AM}

Hi, I am working on saving JSON Documents from  a SQL query using the code below. When I try to save the file as .txt works fine but as json I get error.

C#:

                string query = "SELECT TOP 100 [BusinessEntityID],[NationalIDNumber],[OrganizationNode],[OrganizationLevel] FROM [HumanResources].[Employee] FOR JSON AUTO";
                string connectionSql = "Server=(local);Database=AdventureWorks2016CTP3;Integrated Security=true";
                StreamWriter myFile = new StreamWriter(@"c:\sqltojson\employee.json");
                using (SqlConnection connection = new SqlConnection(connectionSql))
                {
                    SqlCommand command = new SqlCommand(query, connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            myFile.WriteLine(String.Format("{0}, {1}, {2}, {3}", 
                            reader["BusinessEntityID"], reader["NationalIDNumber"], reader["OrganizationNode"], reader["OrganizationLevel"]));
                        }
                    }
                    catch (Exception ex)
                        {
                            MessageBox.Show(ex.ToString());
                            Dts.TaskResult = (int)ScriptResults.Failure;
                        }
                    finally
                        {
                            reader.Close();
                            myFile.Close();
                        }
                }

Error:

System.IndexOutOfRangeException: BusinessEntityID at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at System.Data.SqlClient.SqlDataReader.get_item(String name).

Also, I am trying to hard code the fields could you please guide me how not to do it.

Thank you.

SQLEnthusiast

All replies (4)

_{Wednesday, July 22, 2020 5:48 AM ✅Answered | 1 vote}

Hi CSharp Enthusiast,
You can iterate over each reader value and add them to a dictionary, which is added to a List. Then use the JsonConvert.SerializeObject method to serialize the list to a JSON string.
Note: You need install the Newtonsoft.Json firstly by following steps below.
Right-click your project -> Manage NuGet packages -> Search for "newtonsoft json" -> click install.
Here is a code example you can refer to.

static void Main(string[] args)
{
    string json = string.Empty;
    List<object> objects = new List<object>();
    using (SqlConnection conn = new SqlConnection("connect string"))
    {
        conn.Open();
        using (SqlCommand command = conn.CreateCommand())
        {
            command.CommandText = "select * from test";
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    IDictionary<string, object> record = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        record.Add(reader.GetName(i), reader[i]);
                    }
                    objects.Add(record);
                }
            }
        }
    }
    json = JsonConvert.SerializeObject(objects);
    using (StreamWriter sw = new StreamWriter(File.Create(@"C:\Users\Desktop\file.json")))
    {
        sw.Write(json);
    }
}

The result:

Best Regards,
Daniel Zhang

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [email protected].

_{Wednesday, July 22, 2020 1:36 AM}

Hello,

Seems this would be appropriate.

string json = String.Empty;
using (SqlConnection connection = new SqlConnection("... your connection string ...") {
    connection.Open();
    using (SqlCommand command = new SqlCommand("SELECT FROM... FOR JSON PATH", connection) {
        json = command.ExecuteScalar();
    }
}

Using ExecuteScalar example for AdventureWorks which returns the JSON below this code.

SELECT TOP 2 BusinessEntityID AS 'PersonID', 
             FirstName AS 'FirstName', 
             MiddleName AS 'MiddleName', 
             LastName AS 'LastName'
FROM Person.Person FOR JSON AUTO;

Which outputs

[{"PersonID":285,"FirstName":"Syed","MiddleName":"E","LastName":"Abbas"},{"PersonID":293,"FirstName":"Catherine","MiddleName":"R.","LastName":"Abel"}]

Which if needed can be serialized using NewtonSoft Json.DeSerializeObject.

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

_{Wednesday, July 22, 2020 9:48 AM}

Hi

using (StreamWriter myFile = new StreamWriter(@"c:\sqltojson\employee.json"))
 {
                 using (SqlConnection connection = new SqlConnection(connectionSql))
                {
                    SqlCommand command = new SqlCommand(query, connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    try
                    {
            myFile.WriteLine("[");
                        while (reader.Read())
                        {
                            myFile.WriteLine("{" + String.Format("\"BusinessEntityID\":\"{0}\", \"NationalIDNumber\":\"{1}\", \"OrganizationNode\":\"{2}\", \"OrganizationLevel\":\"{3}\"",
                            reader["BusinessEntityID"], reader["NationalIDNumber"], reader["OrganizationNode"], reader["OrganizationLevel"])  + "},");
                        }
             myFile.WriteLine("]");
                    }
                    catch (Exception ex)
                        {
                            MessageBox.Show(ex.ToString());
                            Dts.TaskResult = (int)ScriptResults.Failure;
                        }
                    finally
                        {
                            reader.Close();
                            myFile.Close();
                        }
                }
                
     }

The filestream variable should within the block .Again it was not a proper format of json .I changed to a 

proper format

Thanks and regards

_{Thursday, July 23, 2020 12:17 AM}

Thanks all for your response.

SQLEnthusiast