private void GenerateDataSet()
{
string query = string.Empty;
string machineIDFilter = string.Empty;
string scanIDFilter = string.Empty;
char[] sep = { '|' };
EntityDb entityDb = new EntityDb();
foreach (TreeNode nodeMachines in lstMachines.Nodes)
{
if (lstMachines.GetChecked(nodeMachines) == TriStateTreeView.CheckState.Checked ||
lstMachines.GetChecked(nodeMachines) == TriStateTreeView.CheckState.GreyChecked)
{
foreach (TreeNode mach in nodeMachines.Nodes)
{
//if ( mach.Checked == true )
if (lstMachines.GetChecked(mach) ==
TriStateTreeView.CheckState.Checked || lstMachines.GetChecked(mach) ==
TriStateTreeView.CheckState.GreyChecked)
{
string tagtext = (string)mach.Tag;
string[] values = tagtext.Split(sep);
machineIDFilter = machineIDFilter + values[0] + ",";
scanIDFilter = scanIDFilter + values[1] + ",";
}
}
}
}
machineIDFilter = machineIDFilter.TrimEnd(',', ' ');
scanIDFilter = scanIDFilter.TrimEnd(',', ' ');
if ( scanIDFilter == "")
{
return;
}
query = @"SELECT MachineName, MAX(ScanID) AS ScanID
FROM (SELECT Discover.MachineName, Scan.ScanID
FROM Discover INNER JOIN Scan ON
Discover.MachineID = Scan.MachineID
WHERE (Discover.MachineID IN (" + machineIDFilter+ "))) AS derivedtbl_1 GROUP BY MachineName";
DataTable dtMachine = new DataTable();
entityDb.RunQueryTable(query, ref dtMachine);
if (dtMachine.Rows.Count > 0)
{
StringBuilder sb = new StringBuilder();
switch (rptType)
{
case ReportsType.OSInstall:
{
DataTable dtResult = new DataTable();
sb.AppendFormat("SELECT [Name], COUNT(*) AS Count FROM [OS] where (ScanID in ({0})) GROUP BY Name ORDER BY Count DESC", scanIDFilter);
entityDb.RunQueryTable(sb.ToString(), ref dtResult);
dtResult.TableName = "OS_Set";
dsResults.Tables.Add(dtResult);
}
break;
case ReportsType.LowDiskPercent:
{
DataTable dtResult = new DataTable();
dtMachine.TableName = "Machines";
dsResults.Tables.Add(dtMachine);
sb.AppendFormat("SELECT * FROM [LogicalDrive] WHERE (PercentageFreeSpace < {0} AND Description LIKE '%Fixed%') and (ScanID in ({1}))", diskUsagePerc, scanIDFilter);
entityDb.RunQueryTable(sb.ToString(), ref dtResult);
dtResult.TableName = "LowDisk";
dsResults.Tables.Add(dtResult);
string tmpRelationName = "FK_SCAN_LOWDISK";
DataRelation relation1 = new DataRelation(
tmpRelationName,
dtMachine.Columns["ScanID"],
dtResult.Columns["ScanID"]);
dsResults.Relations.Add(relation1);
relation1.Nested = true;
}
break;
case ReportsType.DiskUtilization:
{
DataTable dtResult1 = new DataTable();
dtMachine.TableName = "Machines";
dsResults.Tables.Add(dtMachine);
sb.AppendFormat("SELECT * FROM [LogicalDrive] WHERE (Description LIKE '%Fixed%' and ScanID in ({1}))", diskUsagePerc, scanIDFilter);
entityDb.RunQueryTable(sb.ToString(), ref dtResult1);
dtResult1.TableName = "LogicalDrive";
dsResults.Tables.Add(dtResult1);
string tmpRelationName = "FK_SCAN_LOGICALDRIVE";
DataRelation relation = new DataRelation(
tmpRelationName,
dtMachine.Columns["ScanID"],
dtResult1.Columns["ScanID"]);
dsResults.Relations.Add(relation);
relation.Nested = true;
DataTable dtResult2 = new DataTable();
if(sb.Length>0)
{
sb.Remove(0, sb.Length);
}
sb.AppendFormat("SELECT * FROM [Disk] WHERE (ScanID in ({0}))", scanIDFilter);
entityDb.RunQueryTable(sb.ToString(), ref dtResult2);
dtResult2.TableName = "Disk";
dsResults.Tables.Add(dtResult2);
tmpRelationName = "FK_SCAN_DISK";
relation = new DataRelation(
tmpRelationName,
dtMachine.Columns["ScanID"],
dtResult2.Columns["ScanID"]);
dsResults.Relations.Add(relation);
relation.Nested = true;
}
break;
case ReportsType.HardwareComponents:
{
string []components = {"Bios", "CDRom", "Disk", "Memory", "NetworkAdapter", "Monitor", "Motherboard", "Printer", "Processor"};
dtMachine.TableName = "Machines";
dsResults.Tables.Add(dtMachine);
foreach (string component in components)
{
DataTable dtResult = new DataTable();
if (sb.Length > 0)
{
sb.Remove(0, sb.Length);
}
sb.AppendFormat("SELECT * FROM [{0}] WHERE (ScanID in ({1}))", component, scanIDFilter);
entityDb.RunQueryTable(sb.ToString(), ref dtResult);
dtResult.TableName = component;
dsResults.Tables.Add(dtResult);
string tmpRelationName = "FK_SCAN_"+component;
DataRelation relation = new DataRelation(
tmpRelationName,
dtMachine.Columns["ScanID"],
dtResult.Columns["ScanID"]);
dsResults.Relations.Add(relation);
relation.Nested = true;
}
}
break;
}
}
}
private void GenerateDataSet()
{
string query = string.Empty;
string machineIDFilter = string.Empty;
string scanIDFilter = string.Empty;
char[] sep = { '|' };
EntityDb entityDb = new EntityDb();
foreach (TreeNode nodeMachines in lstMachines.Nodes)
{
if (lstMachines.GetChecked(nodeMachines) == TriStateTreeView.CheckState.Checked ||
lstMachines.GetChecked(nodeMachines) == TriStateTreeView.CheckState.GreyChecked)
{
foreach (TreeNode mach in nodeMachines.Nodes)
{
if (lstEntity.GetChecked(mach) == TriStateTreeView.CheckState.Checked)
{
string tagtext = (string)mach.Tag;
string[] values = tagtext.Split(sep);
machineIDFilter = machineIDFilter + values[0] + ",";
scanIDFilter = scanIDFilter + values[1] + ",";
}
}
}
}
machineIDFilter = machineIDFilter.TrimEnd(',', ' ');
scanIDFilter = scanIDFilter.TrimEnd(',', ' ');
if ( scanIDFilter == "")
{
return;
}
query = @"SELECT MachineName, MAX(ScanID) AS ScanID
FROM (SELECT Discover.MachineName, Scan.ScanID
FROM Discover INNER JOIN Scan ON
Discover.MachineID = Scan.MachineID
WHERE (Discover.MachineID IN (" + machineIDFilter+ "))) AS derivedtbl_1 GROUP BY MachineName";
//SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(query, _connection);
DataTable dtMachine = new DataTable();
entityDb.RunQueryTable(query, ref dtMachine);
if ( dtMachine.Rows.Count> 0)
{
dtMachine.TableName = "Machines";
dsResults.Tables.Add(dtMachine);
foreach (TreeNode node in lstEntity.Nodes)
{
if (lstEntity.GetChecked(node) ==
TriStateTreeView.CheckState.Checked || lstEntity.GetChecked(node) ==
TriStateTreeView.CheckState.GreyChecked)
{
DataTable dtResult = new DataTable();
string temp = string.Empty;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT ScanID, ");
foreach (TreeNode items in node.Nodes)
{
if (lstEntity.GetChecked(items) == TriStateTreeView.CheckState.Checked ||
lstEntity.GetChecked(items) == TriStateTreeView.CheckState.GreyChecked)
{
temp = items.Text;
temp = temp.Replace(" ", "");
sb.AppendFormat("{0}, ", temp);
}
}
sb.Remove(sb.Length - 2, 1);
sb.AppendFormat("FROM [{0}] where ScanID in ({1})", node.Text, scanIDFilter);
entityDb.RunQueryTable(sb.ToString(), ref dtResult);
dtResult.TableName = node.Text;
dsResults.Tables.Add(dtResult);
string tmpRelationName = "FK_SCAN_" + node.Text;
DataRelation relation1 = new DataRelation(
tmpRelationName,
dtMachine.Columns["ScanID"],
dtResult.Columns["ScanID"]);
dsResults.Relations.Add(relation1);
relation1.Nested = true;
}
}
}
}