DataReaderToJsonElements

DataReaderUtils.cs

public static class DataReaderUtils {
    public static List<JsonElement> ConvertDataReaderToJsonElements(this IDataReader reader)
    {
        var result = new List<JsonElement>();
        
        // Get column names
        var columnNames = new string[reader.FieldCount];
        for (int i = 0; i < reader.FieldCount; i++)
        {
            columnNames[i] = reader.GetName(i);
        }
        
        // Process each row
        while (reader.Read())
        {
            using (var stream = new System.IO.MemoryStream())
            {
                using (var writer = new Utf8JsonWriter(stream))
                {
                    writer.WriteStartObject();
                    
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string columnName = columnNames[i];
                        
                        if (reader.IsDBNull(i))
                        {
                            writer.WriteNull(columnName);
                            continue;
                        }
                        
                        object value = reader.GetValue(i);
                        
                        // Handle complex/nested objects
                        if (IsComplexObject(value))
                        {
                            // Write property name
                            writer.WritePropertyName(columnName);
                            
                            // Try to serialize the complex object
                            if (value is string jsonString && IsValidJson(jsonString))
                            {
                                // If it's already a JSON string, parse and write it directly
                                using (JsonDocument nestedDoc = JsonDocument.Parse(jsonString))
                                {
                                    WriteJsonElement(writer, nestedDoc.RootElement);
                                }
                            }
                            else
                            {
                                // For complex objects, create a simple string representation
                                writer.WriteStringValue(value.ToString());
                            }
                        }
                        else
                        {
                            // Handle primitive types as before
                            WritePropertyValue(writer, columnName, value);
                        }
                    }
                    
                    writer.WriteEndObject();
                    writer.Flush();
                    
                    stream.Position = 0;
                    
                    using (JsonDocument doc = JsonDocument.Parse(stream))
                    {
                        result.Add(doc.RootElement.Clone());
                    }
                }
            }
        }
        
        return result;
    }

// Check if a value is a complex object
    private static bool IsComplexObject(object value)
    {
        if (value == null)
            return false;
            
        Type type = value.GetType();
        return !type.IsPrimitive && 
            type != typeof(string) && 
            type != typeof(decimal) && 
            type != typeof(DateTime) && 
            type != typeof(TimeSpan) && 
            type != typeof(Guid) &&
            !type.IsEnum;
    }

// Helper to write primitive property values
    private static void WritePropertyValue(Utf8JsonWriter writer, string propertyName, object value)
    {
        switch (value)
        {
            case string strValue:
                writer.WriteString(propertyName, strValue);
                break;
            case int intValue:
                writer.WriteNumber(propertyName, intValue);
                break;
            case long longValue:
                writer.WriteNumber(propertyName, longValue);
                break;
            case double doubleValue:
                writer.WriteNumber(propertyName, doubleValue);
                break;
            case decimal decimalValue:
                writer.WriteNumber(propertyName, decimalValue);
                break;
            case bool boolValue:
                writer.WriteBoolean(propertyName, boolValue);
                break;
            case DateTime dateTimeValue:
                writer.WriteString(propertyName, dateTimeValue);
                break;
            case Guid guidValue:
                writer.WriteString(propertyName, guidValue);
                break;
            default:
                writer.WriteString(propertyName, value.ToString());
                break;
        }
}

// Check if a string is valid JSON
    private static bool IsValidJson(string jsonString)
    {
        try
        {
            using (JsonDocument.Parse(jsonString))
            {
                return true;
            }
        }
        catch
        {
            return false;
        }
    }

    // Recursively write a JsonElement to a JsonWriter
    private static void WriteJsonElement(Utf8JsonWriter writer, JsonElement element)
    {
        switch (element.ValueKind)
        {
            case JsonValueKind.Object:
                writer.WriteStartObject();
                foreach (JsonProperty property in element.EnumerateObject())
                {
                    writer.WritePropertyName(property.Name);
                    WriteJsonElement(writer, property.Value);
                }
                writer.WriteEndObject();
                break;
                
            case JsonValueKind.Array:
                writer.WriteStartArray();
                foreach (JsonElement arrayElement in element.EnumerateArray())
                {
                    WriteJsonElement(writer, arrayElement);
                }
                writer.WriteEndArray();
                break;
                
            case JsonValueKind.String:
                writer.WriteStringValue(element.GetString());
                break;
                
            case JsonValueKind.Number:
                writer.WriteNumberValue(element.GetDecimal());
                break;
                
            case JsonValueKind.True:
                writer.WriteBooleanValue(true);
                break;
                
            case JsonValueKind.False:
                writer.WriteBooleanValue(false);
                break;
                
            case JsonValueKind.Null:
                writer.WriteNullValue();
                break;
        }
    }
}