Take output from SQL Server stored proc and convert output of multiple line resultset into 1 JSON packet in C#
I have a stored procedure that produces output that looks (in SSMS) like this:
Device Loc CaptureTime
0000000001D0 9 22:18:05
000000000402 3 22:18:05
00000000016C 7 22:18:05
00000000013D 7 22:18:05
There could be several hundred rows like this.
I need to create a single JSON package of all of the records in the SQL resultset in C#.
I know how to connect to the database and execute the stored procedure.
Using the following I get all of the contents of just first column (I think):
while(rdr.Read())
{
String strResultSet = rdr.GetString(0);
Console.WriteLine("{0}", strResultSet);
}
I think I need to get DataReader to return all of the columns of each record. I don't know how.
Then, is there a way within DataReader for all records to be appended to strResultSet or will I need to concatenate each new record explicitly?
Please consider me as someone who has not done anything in C# until very recently.
Thank you.
Why fewer and fatter packets? Something to do with how Azure charges for IoTHub messages.
Yes, I'm very new to this.
Device Loc CaptureTime
0000000001D0 9 22:18:05
000000000402 3 22:18:05
00000000016C 7 22:18:05
00000000013D 7 22:18:05
There could be several hundred rows like this.
I need to create a single JSON package of all of the records in the SQL resultset in C#.
I know how to connect to the database and execute the stored procedure.
Using the following I get all of the contents of just first column (I think):
while(rdr.Read())
{
String strResultSet = rdr.GetString(0);
Console.WriteLine("{0}", strResultSet);
}
I think I need to get DataReader to return all of the columns of each record. I don't know how.
Then, is there a way within DataReader for all records to be appended to strResultSet or will I need to concatenate each new record explicitly?
Please consider me as someone who has not done anything in C# until very recently.
Thank you.
Why fewer and fatter packets? Something to do with how Azure charges for IoTHub messages.
Yes, I'm very new to this.
Then, is there a way within DataReader for all records to be appended to strResultSet or will I need to concatenate each new record explicitly?It can be done but probably not efficient enough.
to make things more efficiently, you could probably use a DataTable and serialize it
you could refer below the article (3rd method):
public string DataTableToJsonWithJsonNet(DataTable table)
{
string jsonString=string.Empty;
jsonString = JsonConvert.SerializeObject(table);
return jsonString;
}Convert Datatable to JSON in Asp.net C# [3 ways]
https://codepedia.info/convert-datatable-to-json-in-asp-net-c-sharpASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I love it here.
I get the solution but I also get explanations that help train me.
Thanks, all.
I get the solution but I also get explanations that help train me.
Thanks, all.
Even though you already accepted one approach as the solution:
Depending on your SQL Server version, I would probably generate the JSON directly in the database.
You have a procedure that generates the result set. Just create another that calls the original procedure and returns the JSON.
Let the database do the work. No need for .Net code to do this.
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-ver15
Depending on your SQL Server version, I would probably generate the JSON directly in the database.
You have a procedure that generates the result set. Just create another that calls the original procedure and returns the JSON.
Let the database do the work. No need for .Net code to do this.
https://docs.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-ver15
ASKER