I have to get a set of data from a database table that looks like this:
DataTable:
id
data
phonenumber
date
and
PhoneGroup:
id
groupid
phonenumber
The first table, DataTable contains around 4000 numbers * number of date
The second table, PhoneGroup, will get all phonenumber from a groupID.
The program is a simple one where the user selects a start date and a end date and a groupid, then the program will get all the data of all the phone number in that group which will display all data in a table.
phonenumber 1AUG 2AUG 3AUG 4AUG 5AUG
11111 100 121 131 145 156
22222 141 151 171 185 196
33333 112 121 131 145 156
The user must also have the ability to get data from the subset of the queried data.
Phone number 22222 used 34MB of data from 2AUG to 4AUG
My first approach is to use a nested dictionary:
Dictionary<phonenumber, Dictionary<datetime, data>>
The code to fill this dictionary is 2 sql loops, one for phone number, one for date with data
Tested it for 1 month of data with 4000 numbers which took a really long time.
Now my question is, is there any better way to store these data where the user still have the ability to get the total data from the subset of this data, eg, 2AUG to 4AUG for number 22222
Edit: Added query code
string sqlcommand =
@"SELECT PhoneNo
FROM phonegroup
WHERE groupid=@groupid ORDER BY PhoneNo ASC";
Dictionary<string, int> phoneList = new Dictionary<string, int>(); // not in use yet
using (SqlConnection con = new SqlConnection(strConnectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(sqlcommand, con))
{
cmd.Parameters.AddWithValue("@groupid", groupid);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string phonenumber = reader["PhoneNo"].ToString();
Literal_Results.Text += "<tr><td class='zui-sticky-col'>" + phonenumber + "</td>";
int datesize = cycleDateList.Count;
for (int i = 0; i < cycleDateList.Count; i++)
{
string sqlcommand2 =
@"SELECT data
FROM dataTable
WHERE phonenumber=@phonenumber AND date_display=@date_display";
using (SqlConnection con2 = new SqlConnection(strConnectionString2))
{
con2.Open();
using (SqlCommand cmd2 = new SqlCommand(sqlcommand2, con2))
{
cmd2.Parameters.AddWithValue("@phonenumber", phonenumber);
cmd2.Parameters.AddWithValue("@date_display", cycleDateList[i].Date);
using (SqlDataReader reader2 = cmd2.ExecuteReader())
{
if (reader2.Read())
{
Literal_Results.Text += "<td>" + float.Parse(reader2["data"].ToString()).ToString("0.##") + "</td>";
datesize--;
}
}
}
con2.Close();
}
}
for (int u = 0; u < datesize; u++)
{
Literal_Results.Text += "<td>" + "NIL" + "</td>";
}
Literal_Results.Text += "</tr>";
}
}
}
con.Close();
}
// Get Phone Number End
Literal_Results.Text += "</table>";