public static void InitializeDatabase(JNetcheckerWindow j)
{
//todo: 07/05/21 initilize the tickets table too, add activeticket bool for hosts
List <host> DBhosts = new List <host>();
using (var connectionC = connection()) //uses one subroutine for the connection string builder
{
connectionC.Open();
//Create a table (drop if already exists first):
var delTableCmd = connectionC.CreateCommand();
delTableCmd.CommandText = "DROP TABLE IF EXISTS hosts";
delTableCmd.ExecuteNonQuery();
var createTableCmd = connectionC.CreateCommand();
createTableCmd.CommandText = "CREATE TABLE hosts(name VARCHAR(20) PRIMARY KEY, lastLiveTime DATETIME DEFAULT CURRENT_TIMESTAMP, MAC VARCHAR(17) DEFAULT '', lastIP VARCHAR(39) DEFAULT '', " +
"timesSeen int DEFAULT 0, purpose VARCHAR (100) DEFAULT '',serial VARCHAR (100) DEFAULT '', OS varchar(50) DEFAULT '', Manufacturer VARCHAR(50) DEFAULT '', Model VARCHAR (50) DEFAULT '',warranty VARCHAR(50) DEFAULT '', online int DEFAULT 0, response int DEFAULT 0)"; // I believe Windows max length 15.
createTableCmd.ExecuteNonQuery();
//Seed some data:
using (var transaction = connectionC.BeginTransaction())
{
var insertCmd = connectionC.CreateCommand();
insertCmd.CommandText = "INSERT INTO hosts (name, purpose) VALUES('raspberrypi','Mainframe cooling')";
insertCmd.ExecuteNonQuery();
transaction.Commit();
}
}
}
public static List <host> readHostsNamesOnlyFromDatabase(JNetcheckerWindow j)
{
List <host> DBhosts = new List <host>();
var connectionStringBuilder = new SqliteConnectionStringBuilder();
using (var connectionC = connection()) //uses one subroutine for the connection string builder
{
connectionC.Open();
var selectCmd = connectionC.CreateCommand();
selectCmd.CommandText = "SELECT name FROM hosts";
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
var message = reader.GetString(0);
string hostName = (string)reader["name"];
DBhosts.Add(new host()
{
hostname = hostName
});
}
}
connectionC.Close();
}
return(DBhosts);
}
public static List <host> readHostsFromDatabase(JNetcheckerWindow j)
{
List <host> DBhosts = new List <host>();
using (var connectionC = connection()) //uses one subroutine for the connection string builder
{
connectionC.Open();
var selectCmd = connectionC.CreateCommand();
selectCmd.CommandText = "SELECT * FROM hosts ORDER BY name";
using (var reader = selectCmd.ExecuteReader())
{
while (reader.Read())
{
var message = reader.GetString(0);
string hostName = (string)reader["name"];
string purpose = (string)reader["purpose"];
string serial = (string)reader["serial"];
string MAC = (string)reader["MAC"];
string lastIP = (string)reader["lastIP"];
string OS = (string)reader["OS"];
bool online = Convert.ToBoolean((long)reader["online"]);
bool ticketactive = Convert.ToBoolean((long)reader["activeticket"]);
string warranty = (string)reader["warranty"]; //todo: 09/11/20 build functions to do with dates
int timesSeen = Convert.ToInt32((long)reader["timesSeen"]);
int response = Convert.ToInt32((long)reader["response"]);
// string time = (string)(reader["lastLiveTime"]);
string timeStamp = (string)(reader["lastLiveTime"]);
//string manufacturer = "unknown";
string model = (string)(reader["Model"]);
MAC = BlankValueCheck(MAC); //runs the subroutine to check value is not blank, if so displays as Unknown value
string manufacturer = BlankValueCheck((string)reader["Manufacturer"]);
model = BlankValueCheck(model);
serial = BlankValueCheck(serial);
OS = BlankValueCheck(OS);
purpose = BlankValueCheck(purpose);
warranty = BlankValueCheck(warranty);
DBhosts.Add(new host()
{
hostname = hostName, lastIP = lastIP, timesSeen = timesSeen, OS = OS, MAC = MAC, purpose = purpose, online = online, responseMS = response, lastLiveTime = timeStamp, serial = serial, manufacturer = manufacturer, model = model, warranty = warranty, ticketactive = ticketactive
});
}
}
connectionC.Close();
}
return(DBhosts);
}