Is there a way to create a c# Controller that provides a dynamically structured dataset as well as parameters?

Question

I have written a highly dynamic expanding data collection and storage kind of device (using SqlServer). It's a special kind of database that builds tables views procedures and functions to offer many kinds of lists of data. The output of each Entity Type is variable by it's data definitions. Basically you call a stored procedure which then creates the tables and views and procedures and functions when you call it to create a new Entity Type and add field definitions to it through stored procedures. These database objects therefore exist but they are not known in advance while I'm writing an interface to query and deliver this data.

Because the structure of datasets is dynamically created when using the software I am befuddled with how to build a Controller to encapsulate one of these datasets since it appears that IEnumerable requires a known type.

I will admit I am not skilled in MVC having spent the last decade and a half working on frameworks. So pardon me for my confusion. I have read through previous seemingly related questions here but haven't found any that appear to cover the gist of what I'm trying to do.

Essentially I want the database to be the driver of what data is provided and what data is also being asked for in the form of stored procedure names and parameters not known fully in advance (not iteratively available until runtime).

If anyone has any ideas for me, I'm all ears.

To be completely honest though I'm considering just writing my own web service structure from the ground up, which would then give me the features I want. It's just that MVC architecture appears to be a huge time saver if I could only figure out how to make it work in this setting. Please go easy on me. I'm a senior developer but stuck in .net framework until now.

I have tried looking at examples and finding none that showed promise in the following URL:

How to achieve a dynamic controller and action method in ASP.NET MVC?

Answer 1

The OK() method in the controllers accepts an object that gets converted into the body of the response.

Why don't you just use dynamic, or object through the call stack and populate it with anonymous objects created from your database?

Answer 2

I have come up with a decent plan of attack. Essentially I am inheriting from ControllerBase and not using MVC at all. I am building my API methods such that I won't need to utilize any dynamic defined parameters (each "datapoint" adds a different parameter and field to input and output as well as stored procedure names). Then I am using Microsoft.Data.SqlClient to execute stored procedures directly without any MVC layer. It's a slight interface design change to make this happen but it actually does the job well at this point. Thanks to those who lent some advice. I'm leaving this code example of a controller and how to make it work without MVC in case someone else is interested in following this approach as well.

[ApiController]
[Route("[controller]")]
public class EntityData : ControllerBase
{
    [HttpGet(Name = "GetEntityData")]
    public string Get(string EntityType = null, string IDNumber = null)
    {
        SqlParameter[] parameters = { new SqlParameter("@EntityType", EntityType), new SqlParameter("@IDNumber", IDNumber) };
        DataSet ds = SqlHelper.ExecuteDataset(DB.OpenConnection(), CommandType.StoredProcedure, "Analytics.dbo.GetEntityData", parameters);
        if ((ds != null) && (ds.Tables.Count > 0))
        {
            return JsonConvert.SerializeObject(ds.Tables[0], Formatting.Indented);
        }
        else
        {
            return "[{\"Error\": \"No data was returned\"}]";
        }
    }

    [HttpPost(Name = "SetEntityData")]
    public string Post(string DatapointName = null, string InsertionSource = null, string IDNumber = null, string Value = null)
    {
        SqlParameter[] parameters = { 
                                        new SqlParameter("@DatapointName", DatapointName), 
                                        new SqlParameter("@InsertionSource", InsertionSource),
                                        new SqlParameter("@IDNumber", IDNumber),
                                        new SqlParameter("@Value", Value)
                                    };
        DataSet ds = SqlHelper.ExecuteDataset(DB.OpenConnection(), CommandType.StoredProcedure, "Analytics.dbo.InsertEntityTypeData", parameters);
        if ((ds != null) && (ds.Tables.Count > 0))
        {
            return JsonConvert.SerializeObject(ds.Tables[0], Formatting.Indented);
        }
        else
        {
            return "[{\"Error\": \"No data was returned\"}]";
        }
    }
}