private void FillIpListFromRangeSort(string colBytes, string colDgrams) { treeView1.Nodes.Clear(); string selMAX = @"SELECT MAX(xcolx) from dbo.ForeignIP_Stats where IP_Address = 'xipx'"; SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = SqlManagerSystem.BuildConnection("LCTEST").ConnectionString; SqlCommand sqlComm = new SqlCommand(selMAX, sqlConn); try { VseDbDataSet.ForeignIP_StatsDataTable distinctIPs = new VseDbDataSet.ForeignIP_StatsDataTable(); distinctIPs = sqlMan.Fill_DistinctIpStatsByRange(startDT, endDT); sqlConn.Open(); foreach (VseDbDataSet.ForeignIP_StatsRow fIpRow in distinctIPs.Rows) { string ipText = (string)fIpRow["IP_Address"]; string sel0 = selMAX.Replace("xipx", ipText); //base SELECT string sel1 = sel0.Replace("xcolx", colBytes); sqlComm.CommandText = sel1; //SELECT maximum byte count in timespan double maxBytes = (double)sqlComm.ExecuteScalar(); string sel2 = sel0.Replace("xcolx", colDgrams); sqlComm.CommandText = sel2;//SELECT maximum datagram count in timespan double maxDgrams = (double)sqlComm.ExecuteScalar(); fIpRow[colBytes] = maxBytes; fIpRow[colDgrams] = maxDgrams; } string selOver0 = colBytes + " > '0'"; //Filter out 0 values string sortDESC = colBytes + " DESC"; //Order descending DataRow[] maxRows = distinctIPs.Select(selOver0, sortDESC); foreach (DataRow row in maxRows) { string ipText = (string)row["IP_Address"]; double kbytes = (double)row[colBytes] / 1024; double dgrams = (double)row[colDgrams]; string bText = kbytes.ToString("N0") + " KB"; //remove decimals string dText = dgrams.ToString("N0") + " dgrams"; //remove decimals TreeNode tNode = new TreeNode(ipText); tNode.Nodes.Add(bText + " / " + dText); treeView1.Nodes.Add(tNode); } } catch (Exception exc) { Console.WriteLine(exc.Message); } finally { if (sqlConn.State != ConnectionState.Closed) { sqlConn.Close(); } } }
private void FillIpListFromRange(DateTime start, DateTime end) { treeView1.Nodes.Clear(); VseDbDataSet.ForeignIP_StatsDataTable distinctIPs = new VseDbDataSet.ForeignIP_StatsDataTable(); distinctIPs = sqlMan.Fill_DistinctIpStatsByRange(start, end); foreach (VseDbDataSet.ForeignIP_StatsRow fIpRow in distinctIPs.Rows) { string ipText = (string)fIpRow["IP_Address"]; TreeNode tNode = new TreeNode(ipText); treeView1.Nodes.Add(tNode); } }