private void LoadData()
{
DAO.Recordset RS;
string SQL = "Select * from tblProps";
RS = mf.Dbase.DB.OpenRecordset(SQL);
tbSaveInterval.Text = Convert.ToString(RS.Fields["dbSaveInterval"].Value);
tbRecordInterval.Text = Convert.ToString(RS.Fields["dbRecordInterval"].Value);
tbDelay.Text = Convert.ToString(RS.Fields["dbControlBoxDelay"].Value);
ckAutoSave.Checked = Convert.ToBoolean(RS.Fields["dbSaveReport"].Value);
ckRecording.Checked = Convert.ToBoolean(RS.Fields["dbRecordData"].Value);
ckListen.Checked = Convert.ToBoolean(RS.Fields["dbListenOnly"].Value);
tbSaveLocation.Text = Convert.ToString(RS.Fields["dbSaveLocation"].Value);
tbMaxBoxes.Text = Convert.ToString(RS.Fields["dbMaxBoxes"].Value);
SleepInterval = mf.Dbase.FieldToInt(RS, "dbSleepInterval");
tbSleep.Text = (SleepInterval).ToString();
RS.Close();
// backup values
tbSaveInterval.Tag = tbSaveInterval.Text;
tbRecordInterval.Tag = tbRecordInterval.Text;
tbDelay.Tag = tbDelay.Text;
ckAutoSave.Tag = ckAutoSave.Checked;
ckRecording.Tag = ckRecording.Checked;
tbSaveLocation.Tag = tbSaveLocation.Text;
tbMaxBoxes.Tag = tbMaxBoxes.Text;
tbSleep.Tag = tbSleep.Text;
}
public void Save()
{
if (cNumber == 0)
{
throw new ArgumentException("Controlbox ID not set.");
}
DAO.Recordset RS;
string SQL = "Select * from tblControlBoxes where cbID = " + cID.ToString();
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (NewRecord)
{
RS.AddNew();
}
else
{
RS.Edit();
}
RS.Fields["cbNumber"].Value = cNumber;
RS.Fields["cbDescription"].Value = cDescription + " ";
RS.Fields["cbUseSleep"].Value = cUseSleep;
RS.Fields["cbUseDiagnostics"].Value = cUseDiagnostics;
RS.Fields["cbIPaddress"].Value = cIPaddress;
RS.Fields["cbMac"].Value = cMac + " ";
RS.Update();
if (NewRecord)
{
RS.set_Bookmark(RS.LastModified);
cID = (byte)(RS.Fields["cbID"].Value ?? 0);
}
RS.Close();
NewRecord = false;
}
public void Load(short recID = 0, short SenID = 0)
{
DAO.Recordset RS;
string SQL = "select * from tblRecords";
if (SenID > 0)
{
SQL += " where recSenID = " + SenID.ToString();
}
else
{
SQL += " where recID = " + recID.ToString();
}
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (!RS.EOF)
{
cID = RS.Fields["recID"].Value ?? 0;
cRecNum = RS.Fields["recRecNum"].Value ?? 0;
cSenID = RS.Fields["recSenID"].Value ?? 0;
cTemp = RS.Fields["recTemp"].Value ?? 0;
cTimeStamp = RS.Fields["recTimeStamp"].Value ?? DateTime.Parse("01/01/1900");
NewRecord = false;
}
RS.Close();
}
public bool Load(short recID = 0, short StorNum = 0)
{
string SQL;
if (recID == 0)
{
SQL = "select * from tblStorage where storNum = " + StorNum.ToString();
}
else
{
SQL = "select * from tblStorage where storID = " + recID.ToString();
}
DAO.Recordset RS;
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (RS.EOF)
{
RS.Close();
return(false);
}
else
{
cID = (short)(RS.Fields["storID"].Value ?? 0);
cRecNum = (short)(RS.Fields["storRecNum"].Value ?? 0);
cNumber = (int)(RS.Fields["storNum"].Value ?? 0);
cDescription = (string)(RS.Fields["storDescription"].Value ?? "");
NewRecord = false;
RS.Close();
return(true);
}
}
public void Save()
{
DAO.Recordset RS;
string SQL = "Select * from tblStorage where storID = " + cID;
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (NewRecord)
{
RS.AddNew();
}
else
{
RS.Edit();
}
RS.Fields["storRecNum"].Value = cRecNum;
RS.Fields["storNum"].Value = cNumber;
RS.Fields["storDescription"].Value = cDescription;
RS.Update();
if (NewRecord)
{
RS.set_Bookmark(RS.LastModified);
cID = (short)(RS.Fields["storID"].Value ?? 0);
}
RS.Close();
NewRecord = false;
}
public bool Load(short ID = -1, short ControlBoxID = -1, string SensorAddress = "", short UserData = -1, byte[] SensorByteAddress = null)
{
// returns true if record loaded, false is new record
DAO.Recordset RS;
string SQL;
if (ControlBoxID > -1)
{
SQL = "Select * from tblSensors where senControlBoxID = " + ControlBoxID;
}
else if (SensorAddress != "")
{
SQL = "Select * from tblSensors where senAddress = '" + SensorAddress + "'";
}
else if (UserData > -1)
{
SQL = "Select * from tblSensors where senUserData = " + UserData;
}
else if (SensorByteAddress != null)
{
SQL = "Select * from tblSensors where senAddress = '" + ConvertAddressBytes(SensorByteAddress) + "'";
}
else
{
SQL = "Select * from tblSensors where senID = " + ID;
}
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (RS.EOF)
{
RS.Close();
return(false);
}
else
{
cID = (byte)(mf.Dbase.FieldToInt(RS, "senID"));
cRecNum = (byte)(mf.Dbase.FieldToInt(RS, "senRecNum"));
cSensorAddress = mf.Dbase.FieldToString(RS, "senAddress");
cControlBoxID = (byte)(mf.Dbase.FieldToInt(RS, "senControlBoxID"));
cEnabled = mf.Dbase.FieldToBool(RS, "senEnabled");
cOffset = mf.Dbase.FieldToFloat(RS, "senOffSet");
cBinNum = (byte)(mf.Dbase.FieldToInt(RS, "senBinNumber"));
cCableNum = (byte)(mf.Dbase.FieldToInt(RS, "senCableNumber"));
cSensorNum = (byte)(mf.Dbase.FieldToInt(RS, "senSensorNumber"));
NewRecord = false;
RS.Close();
// build address bytes
AddressBytes = ConvertAddressString(cSensorAddress);
return(true);
}
}
public bool UniqueID(byte NewID)
{
bool Result;
DAO.Recordset RS;
string SQL = "select * from tblControlBoxes where cbNumber = " + NewID.ToString();
RS = mf.Dbase.DB.OpenRecordset(SQL);
Result = RS.EOF;
RS.Close();
return(Result);
}
public bool UniqueID(int NewID)
{
bool Result;
DAO.Recordset RS;
string SQL = "select * from tblStorage where storNum = " + NewID.ToString();
RS = mf.Dbase.DB.OpenRecordset(SQL);
Result = RS.EOF;
RS.Close();
return(Result);
}
private bool DuplicateSensor()
{
bool result = false;
if (NewRecord)
{
DAO.Recordset RS;
string SQL;
SQL = "Select * from tblSensors where senAddress = '" + cSensorAddress + "'";
RS = mf.Dbase.DB.OpenRecordset(SQL);
result = (!RS.EOF);
RS.Close();
}
return(result);
}
public byte ControlBoxCount()
{
byte Result = 0;
try
{
DAO.Recordset RS;
string SQL = "Select * from tblProps";
RS = cDB.OpenRecordset(SQL);
Result = (byte)FieldToInt(RS, "dbMaxBoxes");
RS.Close();
}
catch (Exception)
{
}
return(Result);
}
public void Load()
{
Storages.Clear();
DAO.Recordset RS;
string SQL = "select * from tblStorage order by storRecNum";
RS = mf.Dbase.DB.OpenRecordset(SQL);
while (!RS.EOF)
{
clsStorage Stor = new clsStorage(mf);
Stor.Load((short)RS.Fields["storID"].Value);
Storages.Add(Stor);
RS.MoveNext();
}
RS.Close();
}
public void Load()
{
cSensors.Clear();
DAO.Recordset RS;
string SQL = "Select * from tblSensors order by senRecNum";
RS = mf.Dbase.DB.OpenRecordset(SQL);
while (!RS.EOF)
{
clsSensor Sen = new clsSensor(mf);
Sen.Load((short)RS.Fields["senID"].Value);
cSensors.Add(Sen);
RS.MoveNext();
}
RS.Close();
}
public void Load()
{
cControlBoxes.Clear();
DAO.Recordset RS;
string SQL = "Select * from tblControlBoxes order by cbNumber";
RS = mf.Dbase.DB.OpenRecordset(SQL);
while (!RS.EOF)
{
clsControlBox Box = new clsControlBox(mf);
Box.Load((byte)RS.Fields["cbID"].Value);
cControlBoxes.Add(Box);
RS.MoveNext();
}
RS.Close();
}
public void select(String table)
{
OleDbCommand cmd;
OleDbDataReader RS;
using (OleDbConnection Connection = new OleDbConnection())
{
Connection.ConnectionString = connectionstring;
Connection.Open();
cmd = new OleDbCommand("SELECT * FROM " + table, Connection);
RS = cmd.ExecuteReader();
while (RS.Read())
{
Console.WriteLine(RS[0] + " " + RS[1]);
}
RS.Close();
}
}
private void SetRecNum()
{
DAO.Recordset RS;
string SQL = "Select * from tblRecords order by recRecNum";
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (RS.EOF)
{
cRecNum = 1;
}
else
{
RS.MoveLast();
cRecNum = (short)(RS.Fields["recRecNum"].Value ?? 0);
cRecNum += 1;
}
RS.Close();
}
private void SetRecNum()
{
DAO.Recordset RS;
string SQL = "Select * from tblSensors order by senRecNum";
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (RS.EOF)
{
cRecNum = 1;
}
else
{
RS.MoveLast();
cRecNum = (byte)(mf.Dbase.FieldToInt(RS, "senRecNum"));
cRecNum += 1;
}
RS.Close();
}
public void insertTeachers(List <Teacher> teachers)
{
foreach (Teacher teacher in teachers)
{
OleDbCommand cmd;
OleDbDataReader RS;
String sql = "INSERT INTO Викладачі (Викладач_код, Прізвище, Ініціали, Посада) VALUES " + teacher.ToString();
using (OleDbConnection Connection = new OleDbConnection())
{
Connection.ConnectionString = connectionstring;
Connection.Open();
cmd = new OleDbCommand(sql, Connection);
RS = cmd.ExecuteReader();
RS.Close();
}
}
}
public void Delete(int SensorID)
{
int IDX = ListID(SensorID);
if (IDX != -1)
{
// remove from list
cSensors.RemoveAt(IDX);
// remove from database
DAO.Recordset RS;
string SQL = "Select * from tblSensors where senID = " + SensorID.ToString();
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (!RS.EOF)
{
RS.Delete();
}
RS.Close();
}
}
public void insertSchedule(String year, String speciality, List <Entity> entities)
{
foreach (Entity entity in entities)
{
OleDbCommand cmd;
OleDbDataReader RS;
String sql = "INSERT INTO Розклад (Спеціальність, Рік_навчання, Номер_запису, День, Пара_номер, Аудиторія, Предмет, Група, Викладач) " +
"VALUES ('" + speciality + "', " + year + ", " + entity.ToString() + ")";
using (OleDbConnection Connection = new OleDbConnection())
{
Connection.ConnectionString = connectionstring;
Connection.Open();
cmd = new OleDbCommand(sql, Connection);
RS = cmd.ExecuteReader();
RS.Close();
}
}
}
private string CheckDatabase(string DBname)
{
// check database type, version
string Result = "";
try
{
cDB = cDBE.OpenDatabase(DBname, DAO.DriverPromptEnum.dbDriverNoPrompt, false, "");
DAO.Recordset RS;
string SQL = "select * from tblProps";
RS = cDB.OpenRecordset(SQL);
if (RS.EOF)
{
RS.Close();
cDB.Close();
Result = "No Data.";
}
else
{
// check database type
string ReportedType = (string)(RS.Fields["dbType"].Value ?? "");
short ReportedVersion = (short)(RS.Fields["dbVersion"].Value ?? 0);
RS.Close();
cDB.Close();
if (ReportedType == DBtype)
{
// check version
Result = CheckVersion(ReportedVersion, DBname);
}
else
{
Result = "Wrong database type.";
}
}
}
catch (Exception ex)
{
mf.Tls.WriteErrorLog("clsDatabase:CheckDatabase: " + ex.Message);
}
return(Result);
}
public string LastValue(string Table, string Field, string SortField = "", string Where = "")
{
// returns the last value of a field in a table
DAO.Recordset RS;
if (SortField == "")
{
SortField = Field;
}
string SQL = "Select * from " + Table;
if (Where != "")
{
SQL += " " + Where + " ";
}
if (SortField != "")
{
SQL += " order by " + SortField;
}
RS = cDB.OpenRecordset(SQL);
if (RS.EOF)
{
RS.Close();
return("");
}
else
{
RS.MoveLast();
if (RS.Fields[Field].Type == (short)DAO.DataTypeEnum.dbMemo |
RS.Fields[Field].Type == (short)DAO.DataTypeEnum.dbText)
{
RS.Close();
return(RS.Fields[Field].Value ?? "");
}
else
{
RS.Close();
return(RS.Fields[Field].Value ?? 0);
}
}
}
public DateTime LastTime()
{
// look up in tblRecs using LastRecID
DAO.Recordset RS;
string SQL = "Select Top 1 tblRecords.recTemp,tblRecords.recTimeStamp";
SQL += " From tblRecords";
SQL += " Where recSenID = " + cID;
SQL += " Order By recTimeStamp DESC";
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (RS.EOF)
{
RS.Close();
return(DateTime.Parse("01/01/1900"));
}
else
{
//RS.Close();
return(RS.Fields["recTimeStamp"].Value);
}
}
private void UpdateOptions()
{
try
{
DAO.Recordset RS;
string SQL = "select * from tblProps";
RS = Dbase.DB.OpenRecordset(SQL);
RecordInterval = Dbase.FieldToInt(RS, "dbRecordInterval");
SaveInterval = Dbase.FieldToInt(RS, "dbSaveInterval");
ControlBoxDelay = Dbase.FieldToInt(RS, "dbControlBoxDelay");
MaxBoxes = Dbase.FieldToInt(RS, "dbMaxBoxes");
RecordData = Convert.ToBoolean(RS.Fields["dbRecordData"].Value);
ListenOnly = Convert.ToBoolean(RS.Fields["dbListenOnly"].Value);
SleepInterval = Dbase.FieldToInt(RS, "dbSleepInterval");
RS.Close();
}
catch (Exception Ex)
{
Tls.WriteErrorLog("frmMain: UpdateOptions: " + Ex.Message);
}
}
public void Save()
{
if (ValidationErrors() != "")
{
throw new ArgumentException(ValidationErrors());
}
DAO.Recordset RS;
string SQL;
SQL = "Select * from tblSensors where senID =" + cID;
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (NewRecord)
{
RS.AddNew();
}
else
{
RS.Edit();
}
RS.Fields["senRecNum"].Value = cRecNum;
RS.Fields["senAddress"].Value = cSensorAddress;
RS.Fields["senControlBoxID"].Value = cControlBoxID;
RS.Fields["senEnabled"].Value = cEnabled;
RS.Fields["senOffset"].Value = cOffset;
RS.Fields["senLastTemp"].Value = LastTemp();
RS.Fields["senLastTime"].Value = LastTime();
RS.Fields["senBinNumber"].Value = cBinNum;
RS.Fields["senCableNumber"].Value = cCableNum;
RS.Fields["senSensorNumber"].Value = cSensorNum;
RS.Update();
if (NewRecord)
{
// update with new autoincrement ID
RS.set_Bookmark(RS.LastModified);
cID = (byte)(mf.Dbase.FieldToInt(RS, "senID"));
}
RS.Close();
NewRecord = false;
}
public void insertWeeks(List <Weeks> weeks)
{
foreach (Weeks weeksList in weeks)
{
foreach (String weekNum in weeksList.WeeksList)
{
OleDbCommand cmd;
OleDbDataReader RS;
String sql = "INSERT INTO Розклад_Тижні (Номер_запису_Розклад, Номер_Тижні) VALUES (" + weeksList.EntityId + ", " + weekNum + ")";
using (OleDbConnection Connection = new OleDbConnection())
{
Connection.ConnectionString = connectionstring;
Connection.Open();
cmd = new OleDbCommand(sql, Connection);
RS = cmd.ExecuteReader();
RS.Close();
}
}
}
}
public void TrimRecords(DAO.Database DB)
{
DAO.Recordset RS;
string SQL = "select * from tblProps";
RS = DB.OpenRecordset(SQL);
int dbMax = (int)(RS.Fields["dbMaxSize"].Value ?? 0);
int Size = (int)(new System.IO.FileInfo(cDB.Name).Length);
RS.Close();
if (Size > dbMax)
{
SQL = "Select top 20 percent * from tblRecords order by recID desc";
RS = DB.OpenRecordset(SQL);
while (!RS.EOF)
{
RS.Delete();
RS.MoveNext();
}
RS.Close();
}
}
public void Delete(byte ID)
{
int IDX = ListID(ID);
if (IDX == -1)
{
throw new IndexOutOfRangeException();
}
// remove from list
cControlBoxes.RemoveAt(IDX);
// remove from database
DAO.Recordset RS;
string SQL = "Select * from tblControlBoxes where cbID =" + ID.ToString();
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (!RS.EOF)
{
RS.Delete();
}
RS.Close();
}
public void deleteTables()
{
String[] commands =
{
"DELETE FROM Викладачі",
"DELETE FROM Розклад",
"DELETE FROM Розклад_Тижні"
};
foreach (String sql in commands)
{
OleDbCommand cmd;
OleDbDataReader RS;
using (OleDbConnection Connection = new OleDbConnection())
{
Connection.ConnectionString = connectionstring;
Connection.Open();
cmd = new OleDbCommand(sql, Connection);
RS = cmd.ExecuteReader();
RS.Close();
}
}
}
public bool Load(byte ID = 0, byte ControlboxNumber = 0)
{
string SQL;
if (ID == 0)
{
SQL = "select * from tblControlBoxes where cbNumber = " + ControlboxNumber.ToString();
}
else
{
SQL = "select * from tblControlBoxes where cbID = " + ID.ToString();
}
DAO.Recordset RS;
RS = mf.Dbase.DB.OpenRecordset(SQL);
if (RS.EOF)
{
RS.Close();
return(false);
}
else
{
cID = (byte)(RS.Fields["cbID"].Value ?? 0);
cNumber = (byte)(RS.Fields["cbNumber"].Value ?? 0);
cDescription = mf.Dbase.FieldToString(RS, "cbDescription");
cUseSleep = mf.Dbase.FieldToBool(RS, "cbUseSleep");
cUseDiagnostics = mf.Dbase.FieldToBool(RS, "cbUseDiagnostics");
cIPaddress = (byte)mf.Dbase.FieldToInt(RS, "cbIPaddress");
cMac = mf.Dbase.FieldToString(RS, "cbMac");
NewRecord = false;
RS.Close();
return(true);
}
}
public List <byte> ControlBoxIDs(bool OnlyEnabledSensors = false)
{
List <byte> IDs = new List <byte>();
DAO.Recordset RS;
string SQL = "select senControlBoxID, Max(recTimeStamp) as TS";
SQL += " From tblSensors Left Join tblRecords On tblSensors.senID = tblRecords.recSenID";
SQL += " Group By senControlBoxID";
if (OnlyEnabledSensors)
{
SQL += ", senEnabled having senEnabled = True";
}
SQL += " Order By Max(recTimeStamp) Desc";
RS = mf.Dbase.DB.OpenRecordset(SQL);
while (!RS.EOF)
{
IDs.Add((byte)(RS.Fields["senControlBoxID"].Value ?? 0));
RS.MoveNext();
}
RS.Close();
return(IDs);
}
