.NET: SqlDataAdapter: DataTable: Output Parameters: Return JSON

Question

I have an SQL Server stored procedure that returns different columns and column types based on the input parameters supplied. I am returning the output of this stored proc as a json object this way:

public async Task<IActionResult> OnGetTransactions(string p1, string p2)
{
   DataTable dataTable = new DataTable();                   
                
   await using (SqlConnection con = new SqlConnection(CS))
   {
        SqlCommand cmd = new SqlCommand("sp_GetRecords", con);
        cmd.CommandType = CommandType.StoredProcedure;                       
                    
        cmd.Parameters.AddWithValue("@param1", p1);
        cmd.Parameters.AddWithValue("@param2", p2);                        

        //the below 2 output params got added; without these 2, I was able to return the json-serialized datatable
        cmd.Parameters.Add("@oparam1", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@oparam2", SqlDbType.VarChar, 128).Direction = ParameterDirection.Output;

        await con.OpenAsync();
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
        sqlDataAdapter.Fill(dataTable);   

        //I can capture the output values as indicated below
        int returnCode = Convert.ToInt32(cmd.Parameters["@oparam1"].Value);
        string returnStr = Convert.ToString(cmd.Parameters["@oparam2"].Value);

        MyClass myClass = new MyClass();
        myClass.Transactions = dataTable.AsEnumerable(); //not sure how to convert 
        myClass.ReturnCode = returnCode;
        myClass.ReturnString = returnStr;
                                         
        jsonResult = Newtonsoft.Json.JsonConvert.SerializeObject(myClass);
        await con.CloseAsync();
    }
    return Content(jsonResult);
}


public class MyClass
{
    public IEnumerable<System.Data.DataRow> Transactions { get; set; }
    public int ReturnCode { get; set; }
    public string ReturnString { get; set; }
}

Now, two output parameters got added to the stored procedure. I'm able to capture the output values, but I can't seem to return the json correctly. How do I return the dataTable converted to a json string in this case, with output parameters? I see that there's a 'AsEnumerable()' method available for a datatable, but it returns an IEnumerable of a generic DataRow.

Any help is appreciated.

Answer 1

As I couldn't edit the stored procedure as per @Charlieface's solution, I ended up doing it this way:

public class MyClass
{
    public string Transactions { get; set; }
    public int ReturnCode { get; set; }
    public string ReturnString { get; set; }
}

In my C# code,

MyClass myClass = new MyClass();
//myClass.Transactions = dataTable.AsEnumerable(); //not sure how to convert 
myClass.Transactions = Newtonsoft.Json.JsonConvert.SerializeObject(dataTable); //this gives me a string that I further parse in my javascript code
myClass.ReturnCode = returnCode;
myClass.ReturnString = returnStr;

Javascript Code:

$.ajax({
        url: '/?handler=Transactions&p1=' + someValue1+ '&p2=' + someValue2,
        type: "GET",
        success: function (data) {
            var allData= jQuery.parseJSON(JSON.stringify(data));                 
            var justTransactions= JSON.stringify(eval("(" + allData.Transactions + ")"));                
        },
        error: function (_error) {
            console.log("Error is " + _error);
        }
    });