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 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 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 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); }
private string CheckVersion(short Ver, string DBname) { string SQL; DAO.Recordset RS; string Result = ""; if (Ver > cDBversion) { // higher version, program out of date Result = "Database version does not match, software out of date."; } else if (Ver == cDBversion) { Result = "true"; } else { // lower version, update // close current database try { cDB.Close(); } catch (Exception) { } // copy base file to tmp file in data folder string NewPath = mf.Tls.DataFolder + "\\NewTmp.mdb"; string BasePath = mf.Tls.SettingsFolder + "\\TempMonBase.mdb"; File.WriteAllBytes(NewPath, Properties.Resources.Base); FileInfo BaseFile = new FileInfo(BasePath); // copy current database to tmp file in data folder string TmpPath = mf.Tls.DataFolder + "\\OldTmp.mdb"; try { // delete tmp file, if it exists File.Delete(TmpPath); } catch (Exception Ex) { mf.Tls.WriteErrorLog("clsDatabase: CheckVersion: " + Ex.Message); } FileInfo OldFile = new FileInfo(DBname); OldFile.CopyTo(TmpPath); // open new tmp database and old tmp database DAO.Database DBnew = cDBE.OpenDatabase(NewPath, DAO.DriverPromptEnum.dbDriverNoPrompt, false, ""); DAO.Database DBold = cDBE.OpenDatabase(TmpPath, DAO.DriverPromptEnum.dbDriverNoPrompt, false, ""); // delete new database properties records SQL = "select * from tblProps"; RS = DBnew.OpenRecordset(SQL); while (!RS.EOF) { RS.Delete(); RS.MoveNext(); } RS.Close(); // copy matching data from old database to new database if (CopyData(DBnew, DBold)) { // update database properties SQL = "select * from tblProps"; RS = DBnew.OpenRecordset(SQL); if (RS.EOF) { RS.AddNew(); } else { RS.Edit(); } RS.Fields["dbType"].Value = DBtype; RS.Fields["dbVersion"].Value = cDBversion; RS.Update(); RS.Close(); // copy new updated database to current database name DBnew.Close(); DBold.Close(); File.Delete(DBname); // delete current file FileInfo NewFile = new FileInfo(NewPath); NewFile.CopyTo(DBname); // copy new file to current file name File.Delete(TmpPath); // delete tmp copy of current file File.Delete(NewPath); // delete new file Result = "true"; } else { // failed to copy, remove new database and tmp database File.Delete(TmpPath); // delete tmp copy of current file File.Delete(NewPath); // delete new file Result = "Failed to update database version."; } } return(Result); }
private void LoadTable() { try { DateTime StDate; DateTime EndDate; string DateFmt; DAO.Recordset RS; int ID = 0; // make tmp table string SQL1 = "SELECT recTimeStamp, senSensorNumber, recTemp INTO tmpRecs"; SQL1 += " FROM tblSensors LEFT JOIN tblRecords ON tblSensors.senID = tblRecords.recSenID"; SQL1 += " GROUP BY recTimeStamp,senSensorNumber,recTemp,senBinNumber,senCableNumber"; SQL1 += " Having senBinNumber = " + tbBin.Text; SQL1 += " And senCableNumber = " + tbCable.Text; // date StDate = dtpSingle.Value; EndDate = dtpRange.Value; if (StDate == EndDate | rbSingle.Checked) { // single date, end date is beginning of the next day EndDate = StDate.AddDays(1); } else { // date range, end date is beginning of the next day after user selected end date EndDate = EndDate.AddDays(1); } SQL1 += " And recTimeStamp >= " + mf.Dbase.ToAccessDate(StDate) + " And recTimeStamp < " + mf.Dbase.ToAccessDate(EndDate); SQL1 += " Order By senBinNumber,recTimeStamp,senCableNumber,senSensorNumber"; mf.Dbase.DeleteTable("tmpRecs"); // delete old table, if any mf.Dbase.DB.Execute(SQL1); // create tmp table // make crosstab recordset if (rbHour.Checked) { DateFmt = "" + (char)39 + "yyyy/mm/dd hh" + (char)39; } else { DateFmt = "" + (char)39 + "yyyy/mm/dd" + (char)39; } string SQL = "Transform Avg(recTemp) as AvgOfrecTemp"; SQL += " Select Format([recTimeStamp]," + DateFmt + ") as ReadDate"; SQL += " From tmpRecs"; SQL += " Group By Format([recTimeStamp]," + DateFmt + ")"; SQL += " PIVOT senSensorNumber In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)"; RS = mf.Dbase.DB.OpenRecordset(SQL); dataSet1.Clear(); try // needed to close recordset after error { while (!RS.EOF) { ID++; DataRow Rw = dataSet1.Tables[0].NewRow(); Rw[0] = ID; Rw[1] = mf.Dbase.FieldToString(RS, "ReadDate"); for (int i = 0; i < 16; i++) { Rw[i + 2] = mf.Dbase.FieldToFloat(RS, (i + 1).ToString("N0")); } dataSet1.Tables[0].Rows.Add(Rw); RS.MoveNext(); } } catch (Exception) { } RS.Close(); } catch (Exception ex) { mf.Tls.TimedMessageBox(ex.Message); mf.Tls.WriteErrorLog("frmBinReport:LoadTable " + ex.Message); } }