using System; using System.Data; using System.Data.SqlClient; namespace DatabaseImport { public class DbOperations { public abstract class Tags { public enum TagFields { TagId, TagText, Obsolete } } public abstract class DbVersions { public enum DbVersionFields { Version, Step, Date, Remarks, UserField } } public abstract class Settings { public enum UserFields { PropertyId, PropertyType, PropertyValue, UserId } } public abstract class Users { public enum UserFields { ID, UserName, DisplayName, Password, Role, LastModified, LastModifiedBy, LocalOnly } public enum UIItemFields { ID, Name } } public abstract class SensorDB { public enum SensorDataFields { SerialNumber = 1, UserSerialNumber, Model, Manufacturer, Status, MeasurementUnit, OffsetToleranceLow, OffsetToleranceHigh, Id, Capacity, Comment, BridgeType, BridgeLegMode, Shunt, Invert, UserValue1, UserValue2, UserValue3, FilterClass, BridgeResistance, IsoCode, CheckOffset, SupportedExcitation, InitialEU, CalInterval, CalibrationSignal, InternalShuntResistance, ExternalShuntResistance, UniPolar, RangeLow, RangeAve, RangeHigh, Created, TimesUsed, SensorCategory, BypassFilter, CouplingMode, Version, LastModified, ModifiedBy, LocalOnly, AxisNumber, NumberOfAxes, UserTags, DoNotUse, Broken, eId, DiagnosticsMode } public enum SensorModelFields { Model, Manufacturer, UserPartNumber, Capacity, OffsetToleranceLow, OffsetToleranceHigh, MeasurementUnit, Bridge, Shunt, BridgeResistance, FilterClass, UniPolar, IgnoreRange, CouplingMode, Version, RangeLow, RangeAve, RangeHigh, LastModified, ModifiedBy, LocalOnly, NumberOfAxes, CalInterval, AxisNumber, Polarity, Invert, CheckOffset, CalibrationRecord, ISOCode, SupportedExcitation } public enum SensorCalibrationFields { SerialNumber, CalibrationDate, Username, LocalOnly, NonLinear, CalibrationRecords, ModifyDate, IsProportional, RemoveOffset, ZeroMethod, CertificationDocuments, InitialOffset } } /// /// current db version in this code base /// public const int CURRENT_DB_VERSION = 61; public class DbTypeAttr : Attribute { public string DbType { get; private set; } internal DbTypeAttr(string attr) { DbType = attr; } public static string GetDbType(object o) { if (o != null) { var mi = o.GetType().GetMember(o.ToString()); if (mi != null && mi.Length > 0) { var attr = GetCustomAttribute(mi[0], typeof(DbTypeAttr)) as DbTypeAttr; if (null != attr) { return attr.DbType; } } } return null; } } public abstract class CalculatedChannels { public const string Table = "tblCalculatedChannels"; public enum Fields { [DbTypeAttr("INTEGER PRIMARY KEY NOT NULL")] Id, [DbTypeAttr("INTEGER")] Operation, [DbTypeAttr("NVARCHAR(255)")] CalculatedChannelValueCode, [DbTypeAttr("BLOB")] InputChannelIds, [DbTypeAttr("NVARCHAR(255)")] CFCForInputChannels, [DbTypeAttr("NVARCHAR(255)")] CFCForOutput, [DbTypeAttr("NVARCHAR(255)")] TestSetupName, [DbTypeAttr("NVARCHAR(255)")] CCName } } public abstract class LevelTriggers { //public const string Table = "tblLevelTriggers"; public enum Fields { [DbTypeAttr("NVARCHAR(255) NOT NULL")] TestSetupName, [DbTypeAttr("NVARCHAR(255) NOT NULL")] GroupSerialNumber, [DbTypeAttr("NVARCHAR(255) NOT NULL")] TestObjectChannelId, [DbTypeAttr("NVARCHAR(255) NOT NULL")] HardwareChannelId, [DbTypeAttr("NVARCHAR(255) NOT NULL")] SensorSerialNumber, [DbTypeAttr("BIT NOT NULL")] GreaterThanEnabled, [DbTypeAttr("FLOAT NOT NULL")] GreaterThanEU, [DbTypeAttr("BIT NOT NULL")] LessThanEnabled, [DbTypeAttr("FLOAT NOT NULL")] LessThanEU, [DbTypeAttr("FLOAT NULL")] InsideUpperEU, [DbTypeAttr("FLOAT NULL")] InsideLowerEU, [DbTypeAttr("FLOAT NULL")] OutsideUpperEU, [DbTypeAttr("FLOAT NULL")] OutsideLowerEU, [DbTypeAttr("BIT NULL")] TriggerInside, [DbTypeAttr("BIT NULL")] TriggerOutside, [DbTypeAttr("NVARCHAR(255) NOT NULL")] TestObjectName }; } public abstract class TestSetups { public enum HardwareFields { TestSetupName, HardwareId, AddOrRemove //0=remove,1=add } public enum ChannelSettingFields { TestName, TestObjectName, ChannelId, Setting, SensorSerial, Disabled } // public const string TestSetupsTable = "tblTestSetups"; public enum Fields { SetupName, SetupDescription, AutomaticTestProgression, AutomaticProgressionDelayMS, InvertTrigger, InvertStart, ViewDiagnostics, VerifyChannels, AutoVerifyChannels, VerifyChannelsDelayMS, RecordingMode, SamplesPerSecond, PreTriggerSeconds, PostTriggerSeconds, StrictDiagnostics, RequireConfirmationOnErrors, ROIDownload, ViewROIDownload, DownloadAll, ViewRealtime, RealtimePlotCount, RegionsOfInterest, ROIStart, ROIEnd, ViewDownloadAll, Export, ExportFormat, LabDetails, UseLabDetails, CustomerDetails, UseCustomerDetails, AllowMissingSensors, AllowSensorIdToBlankChannel, LocalOnly, LastModified, LastModifiedBy, TurnOffExcitation, TriggerCheckRealtime, TriggerCheckStep, PostTestDiagnostics, ExportFolder, DownloadFolder, CommonStatusLine, SameAsDownloadFolder, UploadData, UploadDataFolder, UploadExportsOnly, Settings, WarnOnBatteryFail, Dirty, Complete, ErrorMessage, TestEngineerDetails, UseTestEngineerDetails, UserTags, DoAutoArm, CheckoutMode, QuitTestWithoutWarning, SuppressMissingSensorsWarning, ISFFile, NotAllChannelsRealTime, NotAllChannelsViewer } public enum TestSetupObjectFields { TestObjectSerialNumber, TestObjectName, //TestObjectSerialNumber was changed to TestObjectName in a later version TestSetupName, TargetSampleRate, ExcitationWarmupTimeMS, LocalOnly, TestObjectType, TestObjectPosition } public enum TestObjectMetaDataFields { TestObject, ISOTestObject, SetupName, TestSetupName, PropName, PropValue, Optional, Version, } public enum GraphFields { GraphName, GraphDescription, TemplateName, Channels, UseDomainMin, DomainMin, UseDomainMax, DomainMax, UseRangeMin, RangeMin, UseRangeMax, RangeMax, Thresholds, LocalOnly, TestSetupName } } public abstract class TestObjectChannelSettings { public enum Fields { TestObjectSerial, ChannelId, Setting, SensorSerial, SerialNumber } } public abstract class DigitalOutputSettings { public enum Fields { ChannelDescription, DelayMS, DurationMS, OutputMode, LimitDuration, LastModified, LastModifiedBy, Version, LocalOnly, DurationMSFloat, UserTags, Broken, //new in 2.0 DoNotUse //new in 2.0 } } public abstract class Squib { public enum Fields { SerialNumber, SquibDescription, BypassCurrentFilter, BypassVoltageFilter, DelayMS, DurationMS, FireMode, ISOCode, MeasurementType, SquibOutputCurrent, SquibToleranceLow, SquibToleranceHigh, LimitDuration, ArticleId, LocalOnly, Version, LastModified, LastModifiedBy, UserValue1, UserValue2, UserValue3, UserTags, Broken, //new in 2.0 DoNotUse //new in 2.0 } } public class MMETables { public enum MMEDirectionsFields { s_GUID, DIRECTION, TEXT_L1, TEXT_L2, DATE, VERSION, EXPIRED, REMARKS, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY, SORTKEY } public enum MMEFilterClassesFields { s_GUID, FILTER_CLASS, TEXT_L1, TEXT_L2, VERSION, DATE, REMARKS, EXPIRED, SORTKEY, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY } public enum MMEFineLocations1Fields { s_GUID, FINE_LOC_1, TEXT_L1, TEXT_L2, VERSION, DATE, REMARKS, EXPIRED, SORTKEY, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY } public enum MMEFineLocations2Fields { s_GUID, FINE_LOC_2, TEXT_L1, TEXT_L2, VERSION, DATE, REMARKS, EXPIRED, SORTKEY, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY } public enum MMEFineLocations3Fields { s_GUID, FINE_LOC_3, TEXT_L1, TEXT_L2, VERSION, DATE, REMARKS, EXPIRED, SORTKEY, PICTURE_SHORTNAME, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY } public enum MMEPhysicalDimensionFields { s_GUID, PHYSICAL_DIMENSION, TEXT_L1, TEXT_L2, DEFAULT_UNIT, LENGTH_EXP, TIME_EXP, MASS_EXP, ELECTRIC_CURRENT_EXP, TEMPERATURE_EXP, LUMINOUS_INTENSITY_EXP, AMOUNT_OFSUBSTANCE_EXP, VERSION, DATE, REMARKS, EXPIRED, SORTKEY, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY } public enum MMEPositionsFields { s_GUID, POSITION, TEXT_L1, TEXT_L2, VERSION, DATE, REMARKS, EXPIRED, SORTKEY, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY } public enum MMETestObjectsFields { s_GUID, TEST_OBJECT, TEXT_L1, TEXT_L2, VERSION, DATE, REMARKS, EXPIRED, SORTKEY, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY } public enum MMEMainLocationsFields { s_GUID, TYPE, TRANS_MAIN_LOC, TEXT_L1, TEXT_L2, VERSION, DATE, REMARKS, EXPIRED, SORTKEY, PICTURE_SHORTNAME, LAST_CHANGE, LAST_CHANGE_TEXT, HISTORY } } public static bool _usingCentralizedDB = true; //True means using a remote, centralized server; False means using a local SqlLocalDb public static bool _usingMSSQL = true; public static bool _usingNTLMAuthentication = false; public static string _previousDir = string.Empty; public class DAS { public enum Fields { SerialNumber, Type, MaxModules, MaxMemory, MaxSampleRate, MinSampleRate, FirmwareVersion, CalDate, ProtocolVersion, LastModified, LastModifiedBy, Version, LocalOnly, LastUsed, LastUsedBy, Connection, Channels, Position, ChannelTypes, Reprogramable, Reconfigurable, IsModule, PositionOnDistributor, PositionOnChain, Port, ParentDAS, MaxAAFRate } public enum DASChannelFields { HardwareId, ChannelIdx, SupportedBridges, SupportedExcitations, DASDisplayOrder, LocalOnly, SupportedDigitalInputModes, SupportedSquibFireModes, SupportedDigitalOutputModes, ModuleSerialNumber, ModuleArrayIndex } } private static System.Data.SqlClient.SqlCommand _cmd = null; public static System.Data.SqlClient.SqlCommand cmd { get => _cmd; set => _cmd = value; } public static System.Data.SqlClient.SqlCommand GetSQLCommand() { return GetSQLCommand(false); } public static bool IsServerConnected() { using (var connection = new SqlConnection(Connection.GetLocalConnectionString())) { try { connection.Open(); return true; } catch (SqlException) { return false; } } } public static System.Data.SqlClient.SqlCommand GetSQLCommand(bool newCommand) { if (_cmd == null) { _cmd = new System.Data.SqlClient.SqlCommand(); } var currentCmd = _cmd; if (newCommand) { currentCmd = new System.Data.SqlClient.SqlCommand(); } if (currentCmd.Connection == null || currentCmd.Connection.State != ConnectionState.Open) { currentCmd.Connection = new System.Data.SqlClient.SqlConnection(Connection.GetLocalConnectionString()); currentCmd.Connection.Open(); } currentCmd.Parameters.Clear(); return currentCmd; } private string _localConnection = null; public string GetLocalConnectionString() { lock (dbLock) { if (null != _localConnection) return _localConnection; if (null == Server /*&& !_bCS3*/) { throw new Exception("db connection not initialized"); } //if (_bCS3) //{ // _localConnection = _bCS3UsingNTLMAuthentication ? string.Format("Server={0};Database={1};Trusted_Connection=TRUE;", _csHost, _cs3Name) : string.Format("Server={0};Database={1};User Id={2};Password={3}", _csHost, _cs3Name, _csUser, _cs3Password); //} //else //{ _localConnection = _usingNTLMAuthentication ? string.Format("Server={0};Database={1};Trusted_Connection=TRUE;", Server, DBName) : string.Format("Server={0};Database={1};User Id={2};Password={3};", Server, DBName, Username, Password); //} } return _localConnection; } public string Server { get; set; } = null; public string Username { get; set; } = null; public string Password { get; set; } = null; public string DBName { get; set; } = null; private static DbOperations _dbOperations = null; private static readonly object dbLock = new object(); public static DbOperations Connection { get { lock (dbLock) { if (null == _dbOperations) { _dbOperations = new DbOperations(); } } return _dbOperations; } } public DataSet QueryDataSet(System.Data.SqlClient.SqlCommand icmd) { //try { Log(icmd); } //catch (Exception ex) { APILogger.Log(ex); } if (cmd == null) return null; using (var adapter = new System.Data.SqlClient.SqlDataAdapter(icmd)) { var ds = new DataSet(); try { adapter.Fill(ds); return ds; } catch (Exception ex) { //Log(icmd, true); throw ex; } } // } } public class DigitalInputSettings { public enum Fields { SettingName, SettingMode, ScaleMultiplier, LastModified, LastModifiedBy, SensorId, UserValue1, UserValue2, UserValue3, UserTags, eId, Broken, //new in 2.0 DoNotUse //new in 2.0 } } } }