public long getItemID(string item_name)
{
//Check item name against current entries
db.CreateCommand("Select item_id from items where item_name=" + SQLSpecific.QVal(item_name));
db.SetConnection();
return(db.ScalarIntx());
}
public bool Delete(String Id)
{
db.SetConnection();
string s = "DELETE FROM collar_data_entry where item_name = " + SQLSpecific.QVal(Id);;
return(db.Execute(s));
}
public int ScalarIntx()
{
int iData = -1;
try
{
SQLSpecific.OpenConn(sql_con);
var wht = sql_cmd.ExecuteScalar();
if (wht == null)
{
iData = 0;
}
else
{
iData = Convert.ToInt32(wht.ToString());
}
} catch (Exception caught)
{
try
{
DBMain.AltLog(LogLevels.Warning, 70105, "ScalarIntx '" + caught.Message + "' " + sql_cmd.CommandText);
sql_con.Close();
return(-1);
} catch { }
}
sql_con.Close();
return(iData);
}
// assumes DB is there, but might need to build it before we get here
public bool Execute(string sSQL)
{
bool needDb = false;
try
{
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = sSQL;
SQLSpecific.OpenConn(sql_con);
try
{
int i = sql_cmd.ExecuteNonQuery();
}
catch (System.Data.Common.DbException dbx)
{
needDb = DBMain.DBExceptionHandler(dbx, sSQL);
}
}
catch (Exception caught)
{
try
{
DBMain.AltLog(LogLevels.Warning, 70101, "Execute '" + caught.Message + "' " + sSQL);
sql_con.Close();
return(false);
}
catch { }
}
if (sql_con != null)
{
sql_con.Close();
}
return(true);
}
public bool Update(ElementList collar, ElementList collar_det, ElementList k5)
{
bool success = false;
db.SetConnection();
ArrayList sqlList = new ArrayList();
string sSQL = "UPDATE collar_rec SET ";
sSQL += collar.ColumnsValues;
sqlList.Add(sSQL);
sqlList.Add(SQLSpecific.getLastID("collar_rec"));
success = db.Execute(sqlList) == -1?false:true;
sqlList = new ArrayList();
// The collar is made of three records. One is in collar_rec, one in collar_detector, and one in collar_k5
sSQL = "UPDATE collar_detector_rec SET ";
sSQL += collar_det.ColumnsValues;
sqlList.Add(sSQL);
sqlList.Add(SQLSpecific.getLastID("collar_detector_rec"));
success = success || db.Execute(sqlList) == -1 ? false : true;
sqlList = new ArrayList();
// The collar is made of three records. One is in collar_rec, one in collar_detector, and one in collar_k5
sSQL = "UPDATE collar_k5_rec SET";
sSQL += k5.ColumnsValues;
sqlList.Add(sSQL);
sqlList.Add(SQLSpecific.getLastID("collar_k5_rec"));
success = success || db.Execute(sqlList) == -1 ? false : true;
return(success);
}
public DataTable HVPlateauResultsForDet(string name, bool includeRuns)
{
db.SetConnection();
string sSQL = "SELECT * FROM HVResult where detector_id=" + SQLSpecific.QVal(name);
return(db.DT(sSQL));
}
//Return a DataTable based on SQL query
public DataTable DT(string sSQL)
{
DataTable DT = new DataTable();
try
{
SQLSpecific.OpenConn(sql_con);
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = sSQL;
sql_da.SelectCommand = sql_cmd;
//DS.Reset();
int i = sql_da.Fill(DT); // hangs here during transfer operations
//DT = DS.Tables[0];
}
catch (Exception caught)
{
try
{
DBMain.AltLog(LogLevels.Warning, 70103, "DT '" + caught.Message + "' " + sSQL);
}
catch { }
}
//Return DataTable
if (sql_con != null)
{
sql_con.Close();
}
return(DT);
}
public bool Delete(string Id)
{
db.SetConnection();
string s = "DELETE FROM " + table + " where " + SQLSpecific.QValCompare("name", Id, true);
return(db.Execute(s));
}
public DataTable DBProbe(string sSQL)
{
DataTable DT = new DataTable();
try
{
SQLSpecific.OpenConn(sql_con);
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = sSQL;
sql_da.SelectCommand = sql_cmd;
int i = sql_da.Fill(DT);
string s = "Database:" + sql_con.Database + ", DataSource:" + sql_con.DataSource.ToString() + ", ServerVersion:" + sql_con.ServerVersion;
DBDescStr = sql_da.GetType().FullName + "; " + s + " (" + sql_con.ConnectionString + ")";
}
catch (Exception caught)
{
DT = null;
DBErrorStr = caught.Message;
DBMain.AltLog(LogLevels.Warning, 70193, "DBProbe '" + caught.Message + "'");
}
if (sql_con != null)
{
sql_con.Close();
}
return(DT);
}
public long Update(string Id, ElementList sParams)
{
db.SetConnection();
DataTable dt = GetRows(Id); // must be unknown or at least one with same name because unique did not fire
if (dt != null)
{
string wh = " where " + "isotopics_id = " + SQLSpecific.QVal(Id);
string sSQL = "UPDATE isotopics SET ";
sSQL += sParams.ColumnEqValueList + wh;
if (db.Execute(sSQL))
{
return(PrimaryKey(Id));
}
else
{
return(-1);
}
}
else // totally new
{
ArrayList sqlList = new ArrayList();
string sSQL = "Insert into isotopics ";
sSQL += sParams.ColumnsValues;
sqlList.Add(sSQL);
sqlList.Add(SQLSpecific.getLastID("isotopics"));
return(db.ExecuteTransactionID(sqlList));
}
}
public bool Delete(string Id)
{
db.SetConnection();
string s = "DELETE FROM composite_isotopics_rec where isotopics_id = " + SQLSpecific.QVal(Id);
return(db.Execute(s));
}
public string Scalar(string sSQL)
{
string sData = "";
try
{
SQLSpecific.OpenConn(sql_con);
sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = sSQL;
var o = sql_cmd.ExecuteScalar();
if (o != null)
{
sData = o.ToString();
}
}
catch (System.Data.Common.DbException dbx)
{
DBMain.DBExceptionHandler(dbx, sSQL);
}
catch (Exception caught)
{
try
{
DBMain.AltLog(LogLevels.Warning, 70104, "Scalar '" + caught.Message + "' " + sSQL);
sql_con.Close();
return(null);
}
catch { }
}
sql_con.Close();
return(sData);
}
public bool Delete(long DetectorId, string CounterType, ElementList sParams)
{
DataRow dr = null;
string table = "CountingParams";
if (CounterType.Equals("Multiplicity") || CounterType.Equals("Coincidence"))
{
table = "LMMultiplicity";
}
if (table.Equals("LMMultiplicity"))
{
dr = HasRow(DetectorId, CounterType, table, sParams, sParams[faidx].Value); // the FA parameter
}
else
{
dr = HasRow(DetectorId, CounterType, table, sParams);
}
if (dr != null)
{
string sSQL = "DELETE FROM " + table + " where counter_type=" + SQLSpecific.QVal(CounterType) + " AND detector_id=" + DetectorId.ToString();
if (table.Equals("LMMultiplicity"))
{
sSQL += " AND " + sParams[faidx].Name + "=" + sParams[faidx].Value;
}
return(db.Execute(sSQL));
}
else
{
return(true);
}
}
public bool Update(string Name, string Description)
{
db.SetConnection();
string sSQL = "";
bool updated = false;
if (Unique(Name, Description))
{
return(false);
}
DataTable dt = GetRows(Name); // must be unknown or at least one with same name because unique did not fire
if (dt != null)
{
DataRow[] dr = dt.Select("description = " + SQLSpecific.QVal(Description));
if (dr.Length < 1) // desc changed for an existing type
{
sSQL = "UPDATE " + table + " SET "
+ "[description] = " + SQLSpecific.QVal(Description)
+ " WHERE " + SQLSpecific.QValCompare("name", Name, true);
updated = db.Execute(sSQL);
}
}
else // totally new type name
{
sSQL = "Insert into " + table + " ([name], [description]) "
+ " Values (" + SQLSpecific.QVal(Name) + "," + SQLSpecific.QVal(Description) + ")";
updated = db.Execute(sSQL);
}
return(updated);
}
public DataTable MeasurementsForInspection(string number)
{
db.SetConnection();
string sSQL = "SELECT * FROM measurements where detector_id=" + SQLSpecific.QVal(number) + " ORDER BY DateTime DESC";
return(db.DT(sSQL));
}
public bool UpdateFileName(string fileName, long Meas_ID)
{
db.SetConnection();
string wh = " where id = " + Meas_ID.ToString();
string sSQL1 = "UPDATE measurements SET FileName = " + SQLSpecific.QVal(fileName) + wh;
return(db.Execute(sSQL1));
}
public bool DefinitionExists(string DetectorName)
{
db.SetConnection();
string sSQL = "Select * FROM detector Where detector_name = " + SQLSpecific.QVal(DetectorName);
DataTable dt = db.DT(sSQL);
return(dt.Rows.Count > 0);
}
public bool UpdateEndingComment(long mid, string ec)
{
db.SetConnection();
string wh = " where mid=" + mid.ToString();
string sSQL1 = "UPDATE results_rec SET ending_comment=" + SQLSpecific.QVal(ec) + wh;
return(db.Execute(sSQL1));
}
public bool UpdateNote(string notes, long Meas_ID)
{
db.SetConnection();
string wh = " where id = " + Meas_ID.ToString();
string sSQL1 = "UPDATE measurements SET Notes = " + SQLSpecific.QVal(notes) + wh;
return(db.Execute(sSQL1));
}
// return the stratum
public DataTable Get(string stratumId)
{
db.SetConnection();
string sSQL = "Select * FROM stratum_ids where name=" + SQLSpecific.QVal(stratumId);
DataTable dt = db.DT(sSQL);
return(dt);
}
public DataTable MeasurementsForDet(string name)
{
db.SetConnection();
//Changed SQL to display measurements w/newest first hn 9.10.2015
string sSQL = "SELECT * FROM measurements where detector_id=" + SQLSpecific.QVal(name) + " ORDER BY DateTime DESC";
return(db.DT(sSQL));
}
public bool Update(long Id, string NewId)
{
db.SetConnection();
string wh = " where id = " + Id.ToString();
string sSQL1 = "UPDATE isotopics SET isotopics_id=" + SQLSpecific.QVal(NewId);
string sSQL = sSQL1 + wh;
return(db.Execute(sSQL));
}
// NEXT: figure out difference between use of meas_detector_id and detector_id
public bool Has(DateTimeOffset measDatetime, string measDetId, string item_type)
{
db.SetConnection();
string sSQL = "select * from acquire_parms_rec where meas_detector_id=" + SQLSpecific.Value(measDetId, true) +
" and item_type=" + SQLSpecific.Value(item_type, true);
DataTable dt = db.DT(sSQL);
return(dt.Rows.Count > 0);
}
public bool Update(string name, DateTime dt, ElementList sParams)
{
db.SetConnection();
string sSQL1 = "UPDATE HVResult SET ";
string wh = " where detector_id=" + SQLSpecific.QVal(name) + " AND HVPDateTime=" + SQLSpecific.getDate(dt);
string sSQL = sSQL1 + sParams.ColumnEqValueList + wh;
return(db.Execute(sSQL));
}
public bool Update(DateTimeOffset measDatetime, string measDetId, string itemId, ElementList sParams)
{
db.SetConnection();
string wh = " where item_type = " + SQLSpecific.Value(itemId, true) + " AND meas_detector_id = " + SQLSpecific.Value(measDetId, true);
string sSQL1 = "UPDATE acquire_parms_rec SET ";
string sSQL = sSQL1 + sParams.ColumnEqValueList + wh;
return(db.Execute(sSQL));
}
public bool Update(long Id, string NewId)
{
db.SetConnection();
string wh = " where id = " + Id.ToString();
string sSQL1 = "UPDATE poison_rod_type_rec SET poison_rod_type=" + SQLSpecific.QVal(NewId);
string sSQL = sSQL1 + wh;
return(db.Execute(sSQL));
}
public bool Update(long Id, string NewId)
{
db.SetConnection();
string wh = " where " + "item_id = " + Id.ToString();
string sSQL1 = "UPDATE collar_data_entry SET item_name=" + SQLSpecific.QVal(NewId);
string sSQL = sSQL1 + wh;
return(db.Execute(sSQL));
}
public bool Update(long Id, string NewId)
{
db.SetConnection();
string wh = " where glovebox_id = " + Id.ToString();
string sSQL1 = "UPDATE holdup_config_rec SET glovebox_id=" + SQLSpecific.QVal(NewId);
string sSQL = sSQL1 + wh;
return(db.Execute(sSQL));
}
private DataTable BasicSelect(string ItemName)
{
db.SetConnection();
string sSQL = "SELECT item_name "
+ " FROM collar_data_entry"
+ " Where item_name = " + SQLSpecific.QVal(ItemName);
DataTable dt = db.DT(sSQL);
return(dt);
}
private DataTable BasicSelect(string IsoName)
{
db.SetConnection();
string sSQL = "SELECT ci_isotopics_id "
+ " FROM composite_isotopics_rec"
+ " Where ci_isotopics_id = " + SQLSpecific.QVal(IsoName);
DataTable dt = db.DT(sSQL);
return(dt);
}
