基于ADO.NET生成含父子关系结构JSON的技术实现咨询

Hey there! Since you're comfortable with ADO.NET and stored procedures (and want to skip EF), here's a straightforward approach to generate the JSON you need without overcomplicating things.

Step 1: Define Your Entity Classes #

First, make sure your Message and URL classes match the JSON structure you need. Note: I used corrected spellings like messageid for clarity, but if you need to strictly match the exact names from your sample (messegid, messege, messegeurl), just rename the properties accordingly:

public class URL
{
    public string url { get; set; }
}

public class Message
{
    public int messageid { get; set; }
    public string message { get; set; }
    public List<URL> messageurl { get; set; } = new List<URL>();
}

Step 2: Fetch Data with ADO.NET #

Assuming your stored procedure returns two result sets: first the main Message record, then all related URL records for that message. You can use SqlDataReader to read both results and map them to your classes seamlessly:

Message targetMessage = null;

using (SqlConnection conn = new SqlConnection("YourDatabaseConnectionString"))
{
    using (SqlCommand cmd = new SqlCommand("YourStoredProcedureName", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        // Add any required parameters, e.g.:
        // cmd.Parameters.AddWithValue("@TargetMessageId", 1);

        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            // Read first result set: Main Message data
            if (reader.Read())
            {
                targetMessage = new Message
                {
                    messageid = reader.GetInt32(reader.GetOrdinal("messageid")),
                    message = reader.GetString(reader.GetOrdinal("message"))
                };
            }

            // Move to second result set: Related URLs
            if (reader.NextResult() && targetMessage != null)
            {
                while (reader.Read())
                {
                    targetMessage.messageurl.Add(new URL
                    {
                        url = reader.GetString(reader.GetOrdinal("url"))
                    });
                }
            }
        }
    }
}

Step 3: Serialize to JSON #

Now use either System.Text.Json (built into .NET Core 3.0+, .NET 5+) or Newtonsoft.Json (for older .NET Framework apps) to turn your Message object into the desired JSON:

Option 1: System.Text.Json (Recommended for Modern .NET)

No extra packages needed—just use the built-in serializer:

using System.Text.Json;

string jsonOutput = JsonSerializer.Serialize(targetMessage, new JsonSerializerOptions
{
    WriteIndented = true // Optional: for human-readable formatting
});

Option 2: Newtonsoft.Json (For .NET Framework Compatibility)

If you're working with .NET Framework, install the Newtonsoft.Json NuGet package first, then:

using Newtonsoft.Json;

string jsonOutput = JsonConvert.SerializeObject(targetMessage, Formatting.Indented);

Why This Works #

• It plays to your existing ADO.NET/stored procedure expertise without introducing ORM overhead.
• Fetching related data via multiple result sets in one stored procedure call is efficient (avoids extra database round-trips).
• Using established serialization libraries eliminates the risk of manual JSON construction errors.

You can tweak the stored procedure to return the exact message and URLs your business logic requires—just ensure the result set columns align with how you're mapping data in the reader.

内容的提问来源于stack exchange，提问作者Jaidev Khatri