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