JSON Documents from SQL query

[CSharp Enthusiast]

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

Continue reading...