how to query data from SQL in C# for visualization in chartJS?

Question

User-1313071134 posted

hi all,

I am developing a front-end application using ASP.NET. The goal is to query data from multiple databases and visualize them using ChartJS.

The application has a drop-down menu which allows the user to select a specific option which is Last-minute or Last hour or Last day.

The data for each option from the dropdown menu is queried from a different database. Below is some sample from each database.

Database 1 > FROM [Fact].[ReadingsHour] (to retrieve data from the last 24 hours)

DateId    TimeId    ProfilerId    SensorId    SensorValue
538    01:05:48.000    0    0    0.00
538    01:05:48.000    0    1    306.40
538    01:05:48.000    0    2    0.00
538    01:05:48.000    0    3    0.00
538    01:05:48.000    0    4    1.04
538    01:05:48.000    0    5    0.00
538    01:05:48.000    0    6    17647.89
538    01:05:48.000    0    7    40.12
538    01:05:48.000    0    8    10.00
538    01:05:48.000    0    19    2225.00
538    01:05:48.000    0    20    31.00
538    02:00:59.000    0    0    0.00
538    02:00:59.000    0    1    306.40
538    02:00:59.000    0    2    0.00
538    02:00:59.000    0    3    0.00
538    02:00:59.000    0    4    1.04
538    02:00:59.000    0    5    0.00
538    02:00:59.000    0    6    17647.89

Database 2 > FROM [Fact].[ReadingsMinutes] (to retrieve data from the last 60 minutes)

DateId    TimeId    ProfilerId    SensorId    SensorValue
538    01:17:14.000    0    2    0.00
538    01:17:14.000    0    3    0.00
538    01:17:14.000    0    4    0.00
538    01:17:14.000    0    5    0.00
538    01:17:14.000    0    6    0.00
538    01:17:14.000    0    7    37.75
538    01:17:14.000    0    8    9.00
538    01:17:14.000    0    19    2225.00
538    01:17:14.000    0    20    31.00
538    01:18:00.000    0    0    0.00
538    01:18:00.000    0    1    0.00
538    01:18:00.000    0    2    0.00
538    01:18:00.000    0    3    0.00
538    01:18:00.000    0    4    0.00
538    01:18:00.000    0    5    0.00
538    01:18:00.000    0    6    0.00
538    01:18:00.000    0    7    38.12
538    01:18:00.000    0    8    10.00
538    01:18:00.000    0    19    2225.00
538    01:18:00.000    0    20    31.00

Database 2 > FROM [Fact].[Readings] (to retrieve data from the last 60 seconds)

DateId    TimeId    ProfilerId    SensorId    SensorValue
538    02:30:32.000    0    2    0.00
538    02:30:32.000    0    3    0.00
538    02:30:32.000    0    4    0.00
538    02:30:32.000    0    5    0.00
538    02:30:32.000    0    6    0.00
538    02:30:32.000    0    7    38.50
538    02:30:32.000    0    8    9.00
538    02:30:32.000    0    19    2226.00
538    02:30:32.000    0    20    30.00
538    02:30:38.000    0    0    0.00
538    02:30:38.000    0    1    0.00
538    02:30:38.000    0    2    0.00
538    02:30:38.000    0    3    0.00
538    02:30:38.000    0    4    0.00
538    02:30:38.000    0    5    0.00
538    02:30:38.000    0    6    0.00
538    02:30:38.000    0    7    38.50
538    02:30:38.000    0    8    9.00
538    02:30:38.000    0    19    2226.00

for all table timeId has format of Time(3).

I want the Drop down menu to have the following query as listed below:

1. If the user selected "Last Day", Then it displays the last 24 hours from Fact.ReadingsHours
2. If the user selected "Last Hour", Then it displays the last 60 minutes from Fact.ReadingsMinutes
3. If the user selected "Last Day", Then it displays the last 60 Seconds from Fact.ReadingsSeconds

Below is how I am currently doing the query in C# (this is not ideal because I am not able to implement the aforementioned condition):

 protected void PopulateData(string option)
        {
            string sql = "";

            if (option != null && option != "4")
            {
                switch (option)
                {
                    case "0":
                        sql += "SELECT * FROM [Fact].[Readings] where DateId=646";
                        break;
                    case "1":
                        sql += "SELECT * FROM [Fact].[ReadingsMinute] where DateId=646";
                        break;
                    case "2":
                        sql += "SELECT * FROM [Fact].[ReadingsHour] where DateId=646";
                        break;
                    default:
                        break;
                }
            }
            ShowData(SelectFromDatabase(sql));
        }

I was able to accomplish that, therefore, I would really appreciate any kind of help to make this happen.