.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.

Unless I'm misunderstanding, it seems you are missing the class itself. Serialization is the process of representing an existing instance of a class as a string. This code `Newtonsoft.Json.JsonConvert.SerializeObject(dataTable);` is serializing a `DataTable` instance. — Shai Cohen, Nov 03 '21 at 18:25
Can you post the whole code pls? I see return but I can't see a header. What is this? — Serge, Nov 03 '21 at 18:25
@Shai, Newtonsoft.Json library allows for a datatable to be serialized to a json string. I cannot have a strongly typed class as the stored procedure returns different columns(and datatypes) based on the input parameters. I don't know what the datatable holds at tuntime. — SoftwareDveloper, Nov 03 '21 at 18:34
You can serialize a DataTable, but it will be a _DataTable_. It will not be limited to the two output parameters you are asking about. If a serialized DataTable string is sufficient, then maybe I am not understanding the problem. — Shai Cohen, Nov 03 '21 at 18:39
Apart from removing `.AsEnumerable()` your code looks correct. What exact;y are you having difficulty with? Side point: If you have control of the stored procedure you may just want to use `FOR JSON` — Charlieface, Nov 03 '21 at 21:23
Json.NET can serialize a `DataTable` directly, no need for `AsEnumerable()`; see [How to convert datatable to json string using json.net?](https://stackoverflow.com/a/2979938/3744182) for confirmation. In fact you would need a special converter to serialize just a `DataRow`, see [Convert a data row to a JSON object](https://stackoverflow.com/q/33399749/3744182) for an example. — dbc, Nov 03 '21 at 22:59
Does [How to convert datatable to json string using json.net?](https://stackoverflow.com/a/2979938/3744182) answer your question? Just replace `public IEnumerable Transactions` with `public DataTable Transactions` and you should be good to go. Or if not, can you please [edit] your question to clarify your requirements? — dbc, Nov 03 '21 at 23:16
@dbc: Thank you for your posts. I'll look into them and try their solution. — SoftwareDveloper, Nov 04 '21 at 13:50
@Charlieface: Thank you for the "FOR JSON" solution. That might solve my problem. — SoftwareDveloper, Nov 04 '21 at 13:55
@Charlieface: Could you put your comment as a solution so I can accept it as an answer? — SoftwareDveloper, Nov 04 '21 at 15:07
Not without your stored procedure code, becaus I don't know if it returns in the right format. Feel fre to do so yourself] — Charlieface, Nov 04 '21 at 15:15

score 0 · Accepted Answer · answered Nov 18 '21 at 17:33

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

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

1 Answers1