Convert datatable to JSON and publish to PubSub row by row

Question

My flow is to take data from a SQL Server database (I'm using datatable) and then publish it to the PubSub (different message for each row). May someone help and suggest me the way in doing it?

I am using Newtonsoft.Json to convert to JSON.

This is my connection to the database:

    public DataTable RequestDataDB()
    {            
        SqlConnection database = new SqlConnection(DatabaseConn.DBConnectionString);
        database.Open();

        SqlCommand databaseCmd = new SqlCommand("Request_PubData", database);
        SqlDataAdapter da = new SqlDataAdapter(databaseCmd);
        da.Fill(dt);

        database.Close();
        return dt;
    }

This how I call it back and convert to JSON & publish it.

Console.WriteLine("Data to be Published Total : " + totalRows);
string jsonOutput = JsonConvert.SerializeObject(RequestDataDB, Formatting.Indented);
string messageId = await publisher.PublishAsync(jsonOutput);
Console.WriteLine(jsonOutput);
await publisher.ShutdownAsync(TimeSpan.FromSeconds(15));

Thank you for your help.

Your code is converting datatable to json and publishing. You can think of looping thru rows of datatable and publishing it individually — Chetan, Nov 13 '19 at 03:56
Beside question: why are you publishing row by row? There isn't more efficient thing to do? — guillaume blaquiere, Nov 13 '19 at 08:04
Given your `DataTable` what do you want the JSON output to look like? [Convert a data row to a JSON object](https://stackoverflow.com/q/33399749/3744182) might work or might not, we can't answer without knowing your specific desired output format. — dbc, Nov 13 '19 at 16:56

score 1 · Accepted Answer · answered Nov 14 '19 at 02:11

Thank you very much for all the tips & answer given. I'm more than grateful. Actually, the requirement is to publish object by object {} {} instead of in sequence.[ {}, {} ]. So here is the solution. Thank you so much guys.

 foreach (DataRow DataDBRow in RequestDataDB.Rows)
            {
                string json = new JObject(
                                         RequestDataDB.Columns.Cast<DataColumn>()
                                        .Select(DataDBColumn => new JProperty(DataDBColumn.ColumnName, JToken.FromObject(DataDBRow[DataDBColumn])))
                                        ).ToString(Formatting.None);
                Console.WriteLine(json);
                Console.WriteLine();
                string messageId = await publisher.PublishAsync(json);

            }
            await publisher.ShutdownAsync(TimeSpan.FromSeconds(15));
            //databaseConn.Close();
            Console.ReadLine();

Previous code:

string jsonOutput = JsonConvert.SerializeObject(RequestDataDB, Formatting.Indented);

Convert datatable to JSON and publish to PubSub row by row

1 Answers1