// return record given its primary keys public static bool GetRecord(int ParLocationIid, string StorageUnitName, int SubUnitIid, int BinNumber, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from HH_SHADOW_POCKET WHERE ParLocationIid='{0}' AND StorageUnitName='{1}' AND SubUnitIid='{2}' AND BinNumber='{3}'", (int)ParLocationIid, MainClass.FixStringForSingleQuote(StorageUnitName), (int)SubUnitIid, (int)BinNumber); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "HHShadowPocket", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// return record given its primary keys public static bool GetRecord(string ItemID, string CostCenter, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from ITEM_PRICE WHERE ItemID='{0}' AND CostCenter='{1}'", MainClass.FixStringForSingleQuote(ItemID), MainClass.FixStringForSingleQuote(CostCenter)); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ItemPrice", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// return record given its primary keys public static bool GetRecord(DateTime TxTime, int NSequenceNum, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from PEND_POCKET_ACCESS WHERE TxTime='{0}' AND NSequenceNum='{1}'", MainClass.DateTimeToTimestamp(TxTime), (int)NSequenceNum); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PendPocketAccess", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// return record given its primary key public static bool GetRecord(int PktIid, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from POCKET WHERE PktIid='{0}'", (int)PktIid); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Pocket", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// return record given its primary keys public static bool GetRecord(string ImportFileName, DateTime ImportTime, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from D_S_IMPORT_REGISTRY WHERE ImportFileName='{0}' AND ImportTime='{1}'", MainClass.FixStringForSingleQuote(ImportFileName), MainClass.DateTimeToTimestamp(ImportTime)); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DSImportRegistry", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// get par locations associated with this auto refill // return false if error or no data found public static bool GetParLocs(int autoRefillIid, List <int> ParLocList) { bool Retval = false; ParLocList.Clear(); #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from HH_AUTOREFILL_TO_PARLOC WHERE autoRefillIid=" + autoRefillIid; if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "HHAutoRefillToParloc", "GetParLocs", out _conn, out myDataReader)) { try { while (myDataReader.Read()) { ParLocList.Add(MainClass.ToInt(TableName, myDataReader["parLocationIid"])); Retval = true; } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetParLocs", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// convert binary DB field value to byte[] public static byte[] ToByteArray(string table, object inval) { byte[] Retval = { }; try { if (Type.GetTypeCode(inval.GetType()) != TypeCode.DBNull) { Retval = (byte[])inval; } } catch (Exception ex) { string err = String.Format(StringTable.GetString("DatabaseCastError"), table, ex.Message.ToString()); ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "ToByteArray", err); } return(Retval); }
// check if a batch report or custom list is using the group list or email // return true if record found public static bool ReportLinkExists(int nIid) { bool Retval = false; // data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from REPORT where emailtype=1 and emailId=" + nIid.ToString(); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Report", "ReportLinkExists", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { Retval = true; } } catch (Exception ex) { string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), "Report", ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, "Report", "ReportLinkExists", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// check for duplicate GL code, ignoring the one we are working on public static bool IsDuplicatedGLCode(string glCode, int excludedIid) { bool glCodeExists = false; DeleteOldGLCode(glCode); #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from PATIENTS WHERE ptId='" + glCode + "' AND PtIid <> " + excludedIid.ToString(); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PATIENTS", "IsDuplicatedGLCode", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { glCodeExists = true; } } catch (Exception ex) { string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "IsDuplicatedGLCode", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(glCodeExists); }
// convert Decimal DB field value to Decimal public static Decimal ToDecimal(string table, object inval) { Decimal Retval = (decimal) - 1.0; try { if (Type.GetTypeCode(inval.GetType()) != TypeCode.DBNull) { Retval = (Decimal)inval; } } catch (Exception ex) { string err = String.Format(StringTable.GetString("DatabaseCastError"), table, ex.Message.ToString()); ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "ToDecimal", err); } return(Retval); }
// find Iid for printer name public static int GetIidFromPrinterName(string printerName) { int printerIid = 0; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from PRINTER_DATA WHERE printerName=" + "'" + printerName + "'"; if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PRINTER_DATA", "GetIidFromPrinterName", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { printerIid = (int)myDataReader["printerIid"]; } } catch (Exception ex) { string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetIidFromPrinterName", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(printerIid); }
// get task from TASK_SCHEDULE table given a select statement // integer vals are returned as -1 if they're null // DateTime vals are returned as MinValue if they're null // return false if error or no record found public static bool GetData(string SqlStatement, out TableData tsd) { bool Retval = false; tsd = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "TaskSchedule", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out tsd); Retval = true; } } catch (Exception ex) { string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, "TaskSchedule", "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// check for duplicate printer descriptions, ignoring the one we are working on public static bool IsDuplicatedPrinterDescription(string printerDescription, int excludedPrinterIid) { bool descExists = false; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from PRINTER_DATA WHERE description='" + printerDescription + "' AND printerIid <> " + excludedPrinterIid.ToString(); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "PRINTER_DATA", "IsDuplicatedPrinterDescription", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { descExists = true; } } catch (Exception ex) { string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "IsDuplicatedPrinterDescription", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(descExists); }
// Get Server name public static string GetServerName() { string consoleName = ""; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from TBLPYXOPTIONS"; if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "TBLPYXOPTIONS", "GetServerName", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { consoleName = myDataReader["DbServerName"].ToString(); } } catch (Exception ex) { string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetServerName", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(consoleName); }
public static bool GetEmailAddressesInList(int listIid, List <string> emailAddresses) { bool Retval = true; SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT E.EMAILADDRESS FROM EMAIL E, EMAIL_LIST_TO_EMAIL L WHERE E.EMAILID = L.EMAILID AND L.LISTIID = '{0}' AND L.EMAILTYPE = 0 ORDER BY EMAILADDRESS", listIid); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "EmailListToEmail", "GetSortedEmailAddressesForList", out _conn, out myDataReader)) { try { while (myDataReader.Read()) { emailAddresses.Add(myDataReader["EMAILADDRESS"].ToString()); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, "EmailListToEmail", "GetSortedEmailAddressList", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } return(Retval); }
// Execute a SQL statement & return the connection // Return value: true if successful static bool ExecuteSql(bool ReturnConn, string SqlStatement, bool LogErrMsg, string table, string CallingClass, string CallingFunction, out SAConnection _conn) { bool RetVal = true; _conn = null; try { if (OpenDB(CallingClass, CallingFunction, out _conn)) { SACommand cmd = new SACommand(SqlStatement, _conn); cmd.ExecuteNonQuery(); } else { RetVal = false; } } catch (Exception ex) { RetVal = false; if (LogErrMsg) { string err = String.Format(StringTable.GetString("DatabaseError"), table, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, CallingClass, CallingFunction, err); } } finally { if (!ReturnConn && _conn != null) { _conn.Close(); } } return(RetVal); }
// return record given its primary keys public static bool GetRecord(int DeviceIid, int BtnBoardNbr, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from BOARD_COUPLING WHERE DeviceIid='{0}' AND BtnBoardNbr='{1}'", (int)DeviceIid, (int)BtnBoardNbr); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "BoardCoupling", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(Retval); }
// return record given its primary keys public static bool GetRecord(string Destination, int Message_number, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from COMM_RQ WHERE Destination='{0}' AND Message_number='{1}'", MainClass.FixStringForSingleQuote(Destination), (int)Message_number); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "CommRQ", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(Retval); }
// return record given its emailId public static bool GetRecord(int listIid, int emailId, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from EMAIL_LIST_TO_EMAIL WHERE ListIid ='{0}' AND EmailId = '(1)'", (int)listIid, (int)emailId); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "EmailListToEmail", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(Retval); }
// return record given its primary keys public static bool GetRecord(int AttnIid, int AttnCategory, DateTime Message_time, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from ATTENTION WHERE AttnIid='{0}' AND AttnCategory='{1}' AND Message_time='{2}'", (int)AttnIid, (int)AttnCategory, MainClass.DateTimeToTimestamp(Message_time)); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Attention", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(Retval); }
// return record given its primary key public static bool GetRecord(DateTime DateOfArchRecs, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from ARCHIVE_INDEX WHERE DateOfArchRecs='{0}'", MainClass.DateTimeToTimestamp(DateOfArchRecs)); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ArchiveIndex", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(Retval); }
// return record given its primary keys public static bool GetRecord(int DeviceIid, string MsgType, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from DEVICE_BLOCKED_MSGS WHERE DeviceIid='{0}' AND MsgType='{1}'", (int)DeviceIid, MainClass.FixStringForSingleQuote(MsgType)); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DeviceBlockedMsgs", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(Retval); }
// return record given its primary keys public static bool GetRecord(DateTime DayOfMonth, string DeviceName, int BtnBoardNbr, int BtnPocketNbr, string ItemId, out TableData data) { bool Retval = true; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from DAILY_SUMMARY WHERE DayOfMonth='{0}' AND DeviceName='{1}' AND BtnBoardNbr='{2}' AND BtnPocketNbr='{3}' AND ItemId='{4}'", MainClass.DateTimeToTimestamp(DayOfMonth), MainClass.FixStringForSingleQuote(DeviceName), (int)BtnBoardNbr, (int)BtnPocketNbr, MainClass.FixStringForSingleQuote(ItemId)); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "DailySummary", "GetRecord", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(Retval); }
// Get PrinterIid for a given Console name public static bool GetRecordFromConsoleName(string ConsoleName, out TableData data) { bool Retval = false; data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from CONSOLE_DATA where consoleName='" + MainClass.FixStringForSingleQuote(ConsoleName) + "' "; if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ConsoleData", "GetRecordFromConsoleName", out _conn, out myDataReader)) { try { if (myDataReader.Read()) { MakeDataRec(myDataReader, out data); Retval = true; } } catch (Exception ex) { string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), "ConsoleData", ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, "ConsoleData", "GetRecordFromConsoleName", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(Retval); }
public static int FindNotices(string message, DateTime timeframe) { int count = 0; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = string.Format("SELECT * from ATTENTION WHERE attnMsg = '{0}' AND Message_time > {1}", MainClass.FixStringForSingleQuote(message), MainClass.DateTimeToTimestamp(timeframe)); if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Attention", "FindNotices", out _conn, out myDataReader)) { try { while (myDataReader.Read()) { ++count; } } catch (Exception ex) { string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } finally { if (myDataReader != null) { myDataReader.Close(); } if (_conn != null) { _conn.Close(); } } } #endif return(count); }
// convert timestamp DB field value to DateTime public static DateTime ToDate(string table, object inval) { DateTime Retval = DateTime.MinValue; try { if (Type.GetTypeCode(inval.GetType()) != TypeCode.DBNull) { Retval = (DateTime)inval; } if (Retval.Year == 1 && Retval.Month == 1 && Retval.Day == 1) { Retval = DateTime.MinValue; // sybase translates null as time 0, but sets it to 12:00PM instead of 12:00AM } } catch (Exception ex) { string err = String.Format(StringTable.GetString("DatabaseCastError"), table, ex.Message.ToString()); ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "ToDate", err); } return(Retval); }
public static bool GetRecord(string tagId, out TableData data) { bool Retval = false; data = null; #if !NO_ASA string SqlStatement = "SELECT * FROM LOT_SERIAL where tagIDDisplay = :tagId"; try { using (SAConnection con = MainClass.GetConnection()) { con.Open(); using (SACommand cmd = new SACommand(SqlStatement, con)) { cmd.Parameters.Add(new SAParameter("tagId", SADbType.VarChar, 64)).Value = tagId; using (SADataReader sa = cmd.ExecuteReader()) { if (sa.Read()) { MakeDataRec(sa, out data); Retval = true; } } } } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + "=" + tagId + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err); } #endif return(Retval); }
// convert integer DB field value to int public static int ToInt(string table, object inval) { int Retval = -1; Type t = inval.GetType(); try { if (Type.GetTypeCode(t) == TypeCode.Int16) { Retval = (Int16)inval; } else if (Type.GetTypeCode(t) != TypeCode.DBNull) { Retval = (int)inval; } } catch (Exception ex) { string err = String.Format(StringTable.GetString("DatabaseCastError"), table, ex.Message.ToString()); ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "ToInt", err); } return(Retval); }
// return all records // -1 for an int or enum arg means that DB field was NULL // DateTime.MinTime for a DateTime arg means that DB field was NULL public static bool GetRecs(List <TableData> list) { bool Retval = true; list.Clear(); #if !NO_ASA TableData data; SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from Reports"; if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "Reports", "GetData", out _conn, out myDataReader)) { try { while (myDataReader.Read()) { data = new TableData( MainClass.ToInt(TableName, myDataReader["ReportIid"]), myDataReader["reportFile"].ToString(), myDataReader["recordFilter"].ToString(), MainClass.ToInt(TableName, myDataReader["numCopy"]), MainClass.ToBool(TableName, myDataReader["sendMessage"]), myDataReader["dateFrom"].ToString(), myDataReader["dateTo"].ToString(), myDataReader["dateRange"].ToString(), (SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["groupDirection1"]), (SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["groupDirection2"]), (SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["groupDirection3"]), myDataReader["groupHeader1"].ToString(), myDataReader["groupHeader2"].ToString(), myDataReader["groupHeader3"].ToString(), myDataReader["groupTable1"].ToString(), myDataReader["groupTable2"].ToString(), myDataReader["groupTable3"].ToString(), (SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["sortDirection1"]), (SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["sortDirection2"]), (SortDirectionEnum)MainClass.ToInt(TableName, myDataReader["sortDirection3"]), myDataReader["sortHeader1"].ToString(), myDataReader["sortHeader2"].ToString(), myDataReader["sortHeader3"].ToString(), myDataReader["sortTable1"].ToString(), myDataReader["sortTable2"].ToString(), myDataReader["sortTable3"].ToString(), myDataReader["filterText1"].ToString(), myDataReader["filterText2"].ToString(), myDataReader["filterText3"].ToString(), myDataReader["filterText4"].ToString(), myDataReader["filterText5"].ToString(), myDataReader["filterText6"].ToString(), myDataReader["filterText7"].ToString(), myDataReader["filterText8"].ToString(), myDataReader["filterText9"].ToString(), myDataReader["filterText10"].ToString(), myDataReader["filterText11"].ToString(), myDataReader["filterText12"].ToString(), myDataReader["filterText13"].ToString(), myDataReader["filterText14"].ToString(), myDataReader["filterText15"].ToString(), myDataReader["batchName"].ToString(), MainClass.ToBool(TableName, myDataReader["reportDelete"]) ); list.Add(data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, "Reports", "GetRecs", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }
// get all records with specified ReportIid // integer vals are returned as -1 if they're null // to use: // List<TableData> data = new List<TableData>(); GetRecords(iid, data); public static bool GetRecords(int ReportIid, List <TableData> list) { bool Retval = true; StringBuilder filter = new StringBuilder(); StringBuilder items = new StringBuilder(); list.Clear(); TableData data = null; #if !NO_ASA SAConnection _conn; SADataReader myDataReader; string SqlStatement = "SELECT * from REPORT_FILTER WHERE reportIid=" + ReportIid + " ORDER BY filterField, filterItems"; if (MainClass.ExecuteSelect(SqlStatement, true, TableName, "ReportFilter", "GetRecord", out _conn, out myDataReader)) { try { while (myDataReader.Read()) { if (myDataReader["FilterField"].ToString() == filter.ToString()) { // multiple records, append items items.Append("," + myDataReader["filteritems"]); continue; } else { // new filter field if (data != null) { // save previous record data.FilterItems = items.ToString(); list.Add(data); } // create new record MakeDataRec(myDataReader, out data); filter.Clear(); items.Clear(); filter.Append(myDataReader["FilterField"]); items.Append(myDataReader["FilterItems"]); } } // add last record to list if (data != null) { data.FilterItems = items.ToString(); list.Add(data); } } catch (Exception ex) { Retval = false; string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + ")"); ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecords", err); } finally { if (myDataReader != null) { myDataReader.Close(); myDataReader.Dispose(); myDataReader = null; } if (_conn != null) { _conn.Close(); _conn.Dispose(); _conn = null; } } } #endif return(Retval); }