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();

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();
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 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.