Creating a JSON Array from SQL Rows in C# Azure Function

In this article, we will explore how to create a JSON array from SQL rows in C# Azure Function. We will look at two approaches: one using ADO.NET and another using Entity Framework. We will use a "customer" table in our examples. This article is a comprehensive guide for anyone looking to convert SQL data into a JSON array using C# in Azure Function.

Here is the code to serialize SQL data into JSON array using C# Azure Function with an HttpTrigger:

With ADO.NET

using System;
using System.Data.SqlClient;
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;

public static class Function1
{
    [FunctionName("GetCustomersADO")]
    public static IActionResult Run(
        [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
        ILogger log)
    {
        log.LogInformation("C# HTTP trigger function processed a request.");

        string connectionString = "YOUR_CONNECTION_STRING";
        string sql = "SELECT * FROM CUSTOMER";
        var customers = new List<Customer>();

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var customer = new Customer
                        {
                            Id = Convert.ToInt32(reader["Id"]),
                            Name = reader["Name"].ToString(),
                            Email = reader["Email"].ToString()
                        };
                        customers.Add(customer);
                    }
                }
            }
        }

        var json = JsonConvert.SerializeObject(customers);
        return new OkObjectResult(json);
    }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

With Entity Framework

using System;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;

public static class Function2
{
    [FunctionName("GetCustomersEF")]
    public static IActionResult Run(
        [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
        ILogger log)
    {
        log.LogInformation("C# HTTP trigger function processed a request.");

        var customers = new List<Customer>();
        using (var context = new CustomerContext())
        {
            customers = context.Customers.ToList();
        }

        var json = JsonConvert.SerializeObject(customers);
        return new OkObjectResult(json);
    }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public class CustomerContext : DbContext
{
    public CustomerContext() : base("YOUR_CONNECTION_STRING") { }
    public DbSet<Customer> Customers { get; set; }
}

Conclusion

In this article, we demonstrated how to create a JSON array from SQL rows in C# Azure Function using both ADO.NET and Entity Framework. Both approaches showed how to connect to a SQL database, retrieve data from the CUSTOMER table, and return the result as a JSON array. Whether you choose to use ADO.NET or Entity Framework, both methods provide a simple way to retrieve data from a SQL database and return it as a JSON object in an Azure Function.