public ArrayList GetClientSystems()
{
ArrayList list_ClientSystems = new ArrayList();
try
{
connection = new SQLiteConnection("Data Source=" + dbpath + ";Version=3;MultipleActiveResultSets=True; foreign keys=true;");
connection.Open();
command = new SQLiteCommand(connection);
command.CommandText = "SELECT * FROM clientsystem;";
SQLiteDataReader r = command.ExecuteReader();
while (r.Read())
{
ClientSystem c;
object test = r["networknumber"];
if (test != DBNull.Value)
{
c = new ClientSystem(r.GetInt32(0), IPAddress.Parse((string)r["clientIP"]), r.GetInt32(1));
}
else
{
c = new ClientSystem(r.GetInt32(0), IPAddress.Parse((string)r["clientIP"]), -1);
}
test = r["type"];
if (test != DBNull.Value)
{
c.Type = (string)r["type"];
c.Computername = (string)r["computername"];
c.Serial = (string)r["serial"];
}
else
{
c.Type = null;
c.Computername = null;
c.Serial = null;
}
list_ClientSystems.Add(c);
}
}
catch (Exception e)
{
MessageBox.Show("Error SQL Reading ClientSystems", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
Log.WriteLog(e.Message);
}
connection.Close();
return list_ClientSystems;
}
public ArrayList GetSystemInventories()
{
ArrayList list_systeminventories = new ArrayList();
try
{
connection = new SQLiteConnection("Data Source=" + dbpath + ";Version=3; MultipleActiveResultSets=True; foreign keys=true;");
connection.Open();
command = new SQLiteCommand(connection);
command.CommandText = "SELECT * FROM systeminventory;";
SQLiteDataReader r = command.ExecuteReader();
while (r.Read())
{
SystemInventory currentSystemInventory = new SystemInventory(r.GetInt32(1), r.GetInt32(0));
currentSystemInventory.Date = (DateTime)r.GetDateTime(2);
//Get the beloning clientsystems of the systeminventory
SQLiteCommand command2 = new SQLiteCommand(connection);
command2.CommandText = "SELECT clientSystemNumber FROM containsSystem WHERE systemInventoryNumber=@siNumber;";
command2.Parameters.AddWithValue("@siNumber", currentSystemInventory.SystemInventoryNumber);
SQLiteDataReader r_inner = command2.ExecuteReader();
while (r_inner.Read())
{
int clientsystemkNumber = r_inner.GetInt32(0);
//Get the beloning Clientsystem
SQLiteCommand command3 = new SQLiteCommand(connection);
command3.CommandText = "SELECT * FROM clientsystem WHERE clientSystemNumber=@clientSystemNumber;";
command3.Parameters.AddWithValue("@clientSystemNumber", clientsystemkNumber);
SQLiteDataReader r_inner_inner = command3.ExecuteReader();
while (r_inner_inner.Read())
{
ClientSystem c;
object test = r_inner_inner["networknumber"];
if (test != DBNull.Value)
{
c = new ClientSystem(r_inner_inner.GetInt32(0), IPAddress.Parse((string)r_inner_inner["clientIP"]), r_inner_inner.GetInt32(1));
}
else
{
c = new ClientSystem(r_inner_inner.GetInt32(0), IPAddress.Parse((string)r_inner_inner["clientIP"]), -1);
}
test = r_inner_inner["type"];
if (test != DBNull.Value)
{
c.Type = (string)r_inner_inner["type"];
c.Computername = (string)r_inner_inner["computername"];
c.Serial = (string)r_inner_inner["serial"];
}
else
{
c.Type = null;
c.Computername = null;
c.Serial = null;
}
currentSystemInventory.AddSystemToInventory(c);
}//END ClientSystem
}//END containsSystem
list_systeminventories.Add(currentSystemInventory);
}//END SI
}
catch (Exception e)
{
MessageBox.Show("Error SQL Reading SystemInventories", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
Log.WriteLog(e.Message);
}
connection.Close();
return list_systeminventories;
}
public void SaveClientSystem(ClientSystem c, SystemInventory si)
{//Mehtod is onlyused to initial save the system
try
{
connection = new SQLiteConnection("Data Source=" + dbpath + ";Version=3; foreign keys=true;");
connection.Open();
command = new SQLiteCommand(connection);
command.CommandText = "INSERT INTO clientsystem (clientSystemNumber, networknumber, computername, type, serial, clientIP) VALUES(@clientSystemNumber, @networknumber, @computername, @type, @serial, @clientIP);";
command.Parameters.AddWithValue("@clientSystemNumber", c.ClientSystemNumber);
command.Parameters.AddWithValue("@networknumber", c.Networknumber);
command.Parameters.AddWithValue("@computername", c.Computername);
command.Parameters.AddWithValue("@type", c.Type);
command.Parameters.AddWithValue("@serial", c.Serial);
command.Parameters.AddWithValue("@clientIP", c.ClientIP.ToString());
command.ExecuteNonQuery();
//Connect clientsystem to systeminventory in database
command.CommandText = "INSERT INTO containsSystem (systemInventoryNumber, clientSystemNumber) VALUES(@systemInventoryNumber, @clientSystemNumber);";
command.Parameters.AddWithValue("@clientSystemNumber", c.ClientSystemNumber);
command.Parameters.AddWithValue("@systemInventoryNumber", si.SystemInventoryNumber);
command.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show("Error SQL Write Clientsystem", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
Log.WriteLog(e.Message);
}
connection.Close();
}
public void UpdateClientSystem(ClientSystem c)
{
try
{
connection = new SQLiteConnection("Data Source=" + dbpath + ";Version=3; foreign keys=true;");
connection.Open();
command = new SQLiteCommand(connection);
command.CommandText = "UPDATE clientsystem SET computername=@computername, computername=@computername, type=@type, serial=@serial WHERE clientSystemNumber=@clientSystemNumber ;";
command.Parameters.AddWithValue("@clientSystemNumber", c.ClientSystemNumber);
command.Parameters.AddWithValue("@computername", c.Computername);
command.Parameters.AddWithValue("@type", c.Type);
command.Parameters.AddWithValue("@serial", c.Serial);
command.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show("Error SQL Update ClientSystem", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
Log.WriteLog(e.Message);
}
connection.Close();
}
public void AddClientSystem(ClientSystem c)
{
lstClients.Items.Add(c);
}
//scan Network for Clients
private void scanNetwork(ProgressBar progress)
{
//Create a new system Inventory
currentSystemInventory = CreateSystemInventroy(currentCustomer.Cnumber);
Log.WriteLog("New SystemInventory created");
//Get the latest systemnumber
int latestsystemnumber;
//only if there is no data, set it manually
if (list_systems.Count <= 0)
{
latestsystemnumber = 0;
}
else
{
currentSystem = (ClientSystem)list_systems[list_systems.Count - 1];
latestsystemnumber = currentSystem.ClientSystemNumber;
}
//Get number adresses
int i = 0;
foreach (Network n in selectedNetworks)
{
foreach (IPAddress ip in n.IpAddresses) i++;
}
if (progress != null)
{
progress.Value = 0;
progress.Maximum = i;
}
//scan Networks
Dictionary<String, IPAddress> unique = new Dictionary<string, IPAddress>();
foreach (Network n in selectedNetworks)
{
// Ping each ip address of the network with timeout of 100ms
foreach (IPAddress ip in n.IpAddresses)
{
try
{
if (progress != null) progress.PerformStep();
unique.Add(ip.ToString(), ip);
Ping pingSender = new Ping();
PingReply reply = pingSender.Send(ip, 100);
Log.WriteLog(string.Format("Ping: {0}", ip.ToString()));
if (reply.Status == IPStatus.Success)
{
currentSystem = new ClientSystem(++latestsystemnumber, ip, n.NetworkNumber);
list_systems.Add(currentSystem);
db.SaveClientSystem(currentSystem, currentSystemInventory);
currentSystemInventory.AddSystemToInventory(currentSystem);
Log.WriteLog(string.Format("System {0} added to Systeminventory",
currentSystem.ClientIP.ToString()));
}
}
//If dictionary contains key already
catch (ArgumentException e)
{
Log.WriteLog(string.Format("IP-Addresse already in list: {0}", ip.ToString()));
Log.WriteLog(e.Message);
}
}
}
}
//Updates the Clientinformations in the view
public void ClientSelected(Object c)
{
if (c != null)
{
currentSystem = (ClientSystem)c;
view.UpdateClientDetails(currentSystem.Computername, currentSystem.ClientIP.ToString(), currentSystem.Type);
}
}
//functions
public void AddSystemToInventory(ClientSystem sys)
{
list_systems.Add(sys);
}
//functions
public void AddSystemToInventory(ClientSystem sys)
{
list_systems.Add(sys);
}
