//Returns DataTable showing all tenants in a location public DataTable ListTenantsAt(string location) { //string sql = "SELECT Tenants.tenant, Tenants.Location, Tenants.Tag FROM Tenants WHERE Tenants.Location = '@Loction'", "@Location", location; string sql = @"SELECT Tenants.tenant, Tenants.Location, Tenants.Tag FROM Tenants WHERE Tenants.Location = @Location"; DataTable data = DatabaseCommunication.GetDataTable(sql, "@Location", location); return(data); }
public static void CreateMainDatabase() { //Create database SQLiteConnection.CreateFile(database); //Create tables DatabaseCommunication.ExecuteSQL("CREATE TABLE Tenants (ID INTEGER NOT NULL UNIQUE, Tenant TEXT NOT NULL, Location TEXT, Tag TEXT UNIQUE, PRIMARY KEY(ID AUTOINCREMENT))"); DatabaseCommunication.ExecuteSQL("CREATE TABLE Event (ID INTEGER NOT NULL UNIQUE, Event TEXT, PRIMARY KEY(ID AUTOINCREMENT))"); DatabaseCommunication.ExecuteSQL("CREATE TABLE Door (ID INTEGER NOT NULL UNIQUE, Door TEXT NOT NULL, Location TEXT, PRIMARY KEY(ID AUTOINCREMENT))"); DatabaseCommunication.ExecuteSQL("CREATE TABLE LogEntry (ID INTEGER NOT NULL UNIQUE, Date TEXT, Door TEXT, Tag TEXT, Event TEXT, PRIMARY KEY(ID AUTOINCREMENT))"); }
//Returns DataTable showing all entries by event in LogEntry public DataTable FindEntriesByEvent(string eventInput) { string sql = @"SELECT LogEntry.Date, LogEntry.Door, LogEntry.Tag, LogEntry.Event, Tenants.Location, Tenants.Tenant FROM LogEntry JOIN Tenants ON LogEntry.Tag=Tenants.Tag WHERE LogEntry.Event LIKE @EventInput ORDER BY LogEntry.Date DESC LIMIT " + maxEntries + ""; DataTable data = DatabaseCommunication.GetDataTable(sql, "@EventInput", "%" + eventInput + "%"); return(data); }
//Returns DataTable showing all entries by tag in LogEntry public DataTable FindEntriesByTag(string tag) { string sql = @"SELECT LogEntry.Date, LogEntry.Door, LogEntry.Tag, LogEntry.Event, Tenants.Location, Tenants.Tenant FROM LogEntry JOIN Tenants ON LogEntry.Tag=Tenants.Tag WHERE Tenants.Tag LIKE @Tag ORDER BY LogEntry.Date DESC LIMIT " + maxEntries + ""; DataTable data = DatabaseCommunication.GetDataTable(sql, "@Tag", "%" + tag + "%"); return(data); }
public static void InsertIntoTables() { //Creates a new object of the class StaticData StaticData obj = new StaticData(); //Adds tenants to table for (int i = 0; i < obj.TenantsArray.Length; i++) { DatabaseCommunication.ExecuteSQL("Insert Into Tenants (Tenant, Tag) Values (@Tenant, @Tag)", "@Tenant", obj.TenantsArray[i], "@Tag", obj.TagArray[i]); } //Updates Tenants.Location with values for (int i = 0; i < obj.LocationArray.Length; i++) { //DatabaseCommunication.ExecuteSQL($@"UPDATE Tenants SET ""Location"" = '{obj.LocationArray[i]}' WHERE Tag LIKE '{obj.LocationArray[i]}%';"); DatabaseCommunication.ExecuteSQL("UPDATE Tenants SET Location = @Location WHERE Tenants.Tag LIKE @Location2;", "@Location", obj.LocationArray[i], "@Location2", obj.LocationArray[i] + "%" ); } //Adds values to Event.Event for (int i = 0; i < obj.EventArray.Length; i++) { DatabaseCommunication.ExecuteSQL("INSERT INTO Event (Event) Values (@Event)", "@Event", obj.EventArray[i]); } //Adds values to Door.Door for (int i = 0; i < obj.DoorArray.Length; i++) { DatabaseCommunication.ExecuteSQL("INSERT INTO Door (Door) Values (@Door)", "@Door", obj.DoorArray[i]); } //Updates Door.Location with values for (int i = 0; i < obj.LocationArray.Length; i++) { //DatabaseCommunication.ExecuteSQL($@"UPDATE Door SET ""Location"" = '{obj.LocationArray[i]}' WHERE Door.Door LIKE '%{obj.LocationArray[i]}';"); DatabaseCommunication.ExecuteSQL("UPDATE Door SET Location = @Location WHERE Door.Door LIKE @Location2", "@Location", obj.LocationArray[i], "@Location2", "%" + obj.LocationArray[i] + "%" ); } }
//Methods adds logs (from test data) to database public void LogEntry(string date, string door, string eventCode, string tag) { DatabaseCommunication.ExecuteSQL("INSERT INTO LogEntry (Date, Door, Event, Tag) VALUES (@date, @door, @eventCode, @tag) ", "@date", date, "@door", door, "@eventCode", eventCode, "@tag", tag); }