public MainDBClass()
{
sqliteProvider = new DB_Class_SQLite.DBClassSQLite();
string conString = string.Empty;
int bUseSQlServer = 0;
DBClassSQLite db1 = new DBClassSQLite();
db1.OpenDB();
if (db1.isTableExist("tb_SqlServer_Info"))
{
db1.getSqlServerInfo(out conString, out bUseSQlServer);
}
db1.CloseDB();
if ((bUseSQlServer == 1) && (!string.IsNullOrEmpty(conString)))
{
useDb = dbUsedType.db_SqlServer;
sqlServerProvider = new DBClassSqlServer.DBSqlServer(DBClass_SQLServer.UtilSqlServer.ConvertConnectionString(conString));
try
{
OpenDB();
CloseDB();
}
catch
{
useDb = dbUsedType.db_SqlLite;
ErrorMessage.ExceptionMessageBox.Show("Unable to connect to SQL Server Database - Application start with local database to check configuration", "Information");
}
}
}
public QuartzTriggerThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary<ConnectionConfig, Object> activConnections, bool StartedAsService)
{
this.StartedAsService = StartedAsService;
this.dbInterface = dbInterface;
this.datasetConfig = datasetConfig;
this.activConnections = activConnections;
}
public QuartzTriggerThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary <ConnectionConfig, Object> activConnections, bool StartedAsService)
{
this.StartedAsService = StartedAsService;
this.dbInterface = dbInterface;
this.datasetConfig = datasetConfig;
this.activConnections = activConnections;
}
public IEnumerable <ProtocolRow> GetProtocolData(string storageName, int startRow, int maxRows, string filter, DateTime?FromDate, DateTime?ToDate)
{
var datasetConfig = ProtokollerConfiguration.ActualConfigInstance.Datasets.FirstOrDefault(s => s.Name == storageName);
IDBInterface dbInterface = StorageHelper.GetStorage(datasetConfig, null);
dbInterface.Connect_To_Database(datasetConfig.Storage);
IDBViewable dbViewable = dbInterface as IDBViewable;
List <ProtocolRow> list = new List <ProtocolRow>();
DataTable table = dbViewable.ReadData(datasetConfig, filter, startRow, maxRows, FromDate, ToDate);
foreach (DataRow row in table.Rows)
{
ProtocolRow storageLine = new ProtocolRow();
var date = row["datetime"];
if (date is DateTime)
{
storageLine.Timestamp = (DateTime)date;
}
else
{
storageLine.Timestamp = DateTime.ParseExact(row["datetime"].ToString(), "yyyy.MM.dd - HH:mm:ss.fff", null);
}
storageLine.Telegram = Convert.ToString(row["data"]);
list.Add(storageLine);
}
return(list);
}
public void Load()
{
if (ID != -1)
{
IDBInterface db = DBInterface.CreatePointer();
db.StoredProcedure("mile_card_status_select_by_id");
db.AddParameter("@inIdMileCardStatus", MySql.Data.MySqlClient.MySqlDbType.Int32, ID);
db.AddOutParameter("@outStatus", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outMinMiles", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outMaxMiles", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIdAviaCompanyUnion", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIdAviaCompany", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outNote", MySql.Data.MySqlClient.MySqlDbType.String);
db.Execute();
Name = db.GetOutParameterStr("@outStatus");
MinVal = db.GetOutParameterInt("@outMinMiles");
MaxVal = db.GetOutParameterInt("@outMaxMiles");
Note = db.GetOutParameterStr("@outNote");
AviaCompanyID = db.GetOutParameterInt("@outIdAviaCompany");
AviaCompanyUnionID = db.GetOutParameterInt("@outIdAviaCompanyUnion");
Changed = false;
}
}
private void cmbStorage_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
lblError.Content = null;
if (dbIf != null)
dbIf.Dispose();
dbIf = null;
dbView = null;
lblDataCount.Content = null;
grdDatasetFields.ItemsSource = null;
datasetConfig = cmbStorage.SelectedItem as DatasetConfig;
dbIf = StorageHelper.GetStorage(datasetConfig, null);
dbIf.Connect_To_Database(datasetConfig.Storage);
dbView = dbIf as IDBViewable;
dbViewSQL = dbIf as IDBViewableSQL;
txtSQL.IsEnabled = false;
cmdSQL.IsEnabled = false;
txtSearch.IsEnabled = false;
cmdSearch.IsEnabled = false;
try
{
if (dbView != null)
{
txtSQL.Text = "";
CurrentNumber = 0;
DataTable tbl = dbView.ReadData(datasetConfig, null, CurrentNumber, 1000, null, null);
if (tbl != null)
grdDatasetFields.ItemsSource = tbl.DefaultView;
lblDataCount.Content = dbView.ReadCount(datasetConfig);
}
if (!string.IsNullOrEmpty(datasetConfig.DateTimeDatabaseField))
{
/*dbFieldNames = datasetConfig.DateTimeDatabaseField;
foreach (var datasetConfigRow in datasetConfig.DatasetConfigRows)
{
if (datasetConfigRow.DatabaseField.ToLower().Trim() != datasetConfig.DateTimeDatabaseField.ToLower().Trim())
dbFieldNames += ", " + datasetConfigRow.DatabaseField;
}*/
dbFieldNames = datasetConfig.DateTimeDatabaseField + ",*";
}
if (dbViewSQL != null)
{
txtSQL.Text = "SELECT " + dbFieldNames + " FROM " + datasetConfig.Name + " ORDER BY id DESC LIMIT " + 1000.ToString();
txtSQL.IsEnabled = true;
cmdSQL.IsEnabled = true;
txtSearch.IsEnabled = true;
cmdSearch.IsEnabled = true;
}
}
catch (Exception ex)
{
lblError.Content = ex.Message;
}
}
public TimeTriggerWithCheckForChangesThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary<ConnectionConfig, Object> activConnections, bool StartedAsService)
{
this.StartedAsService = StartedAsService;
this.dbInterface = dbInterface;
this.datasetConfig = datasetConfig;
this.activConnections = activConnections;
this.TriggerTimeSpan = datasetConfig.TriggerTimeSpan;
}
public GetToDoObjectService(
IDBInterface dBInterface,
IConfiguration configuration,
ILogger <GetToDoObjectService> logger)
{
_dBInterface = dBInterface;
_configuration = configuration;
_logger = logger;
}
public TimeTriggerWithCheckForChangesThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary <ConnectionConfig, Object> activConnections, bool StartedAsService)
{
this.StartedAsService = StartedAsService;
this.dbInterface = dbInterface;
this.datasetConfig = datasetConfig;
this.activConnections = activConnections;
this.TriggerTimeSpan = datasetConfig.TriggerTimeSpan;
}
public DatabaseTriggerThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary <ConnectionConfig, Object> activConnections, bool StartedAsService)
{
this.StartedAsService = StartedAsService;
this.dbInterface = dbInterface;
this.datasetConfig = datasetConfig;
this.activConnections = activConnections;
this.triggerConn = (DatabaseConnection)activConnections[datasetConfig.TriggerConnection];
ak_interval = NoDataInterval;
}
public DatabaseTriggerThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary<ConnectionConfig, Object> activConnections, bool StartedAsService)
{
this.StartedAsService = StartedAsService;
this.dbInterface = dbInterface;
this.datasetConfig = datasetConfig;
this.activConnections = activConnections;
this.triggerConn = (DatabaseConnection) activConnections[datasetConfig.TriggerConnection];
ak_interval = NoDataInterval;
}
public void Load()
{
if (ID != -1)
{
IDBInterface db = DBInterface.CreatePointer();
db.StoredProcedure("Visa_select_by_id");
db.AddParameter("@inVisa", MySql.Data.MySqlClient.MySqlDbType.Int32, ID);
db.AddOutParameter("@outIdPassport", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outName", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outVisaNumber", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outDateOn", MySql.Data.MySqlClient.MySqlDbType.DateTime);
db.AddOutParameter("@outdateFrom", MySql.Data.MySqlClient.MySqlDbType.DateTime);
db.AddOutParameter("@outDateUntil", MySql.Data.MySqlClient.MySqlDbType.DateTime);
db.AddOutParameter("@outIdCountry", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIdCountryUnion", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIdCountryEsquire", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outTypeVisa", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outEntriesNumber", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outDaysCount", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outUsedDays", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIssuedIn", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outIdDocument", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outNote", MySql.Data.MySqlClient.MySqlDbType.String);
db.Execute();
PassportID = db.GetOutParameterInt("@outIdPassport");
int countryID = db.GetOutParameterInt("@outIdCountry");
int unionID = db.GetOutParameterInt("@outIdCountryUnion");
CountryOfEmmitationID = db.GetOutParameterInt("@outIdCountryEsquire");
EntriesNumber = db.GetOutParameterInt("@outEntriesNumber");
DaysCount = db.GetOutParameterInt("@outDaysCount");
DaysUsed = db.GetOutParameterInt("@outUsedDays");
OwnerName = db.GetOutParameterStr("@outName");
Number = db.GetOutParameterStr("@outVisaNumber");
VizaType = db.GetOutParameterStr("@outTypeVisa");
Issued = db.GetOutParameterStr("@outIssuedIn");
Description = db.GetOutParameterStr("@outNote");
DateApproved = db.GetOutParameterDateTime("@outDateOn");
ValidFrom = db.GetOutParameterDateTime("@outdateFrom");
ValidTill = db.GetOutParameterDateTime("@outDateUntil");
SetTargetCountryOrUnion(countryID, unionID);
Changed = false;
}
}
public PLCTagTriggerThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary <ConnectionConfig, Object> activConnections, bool StartedAsService)
{
this.StartedAsService = StartedAsService;
this.dbInterface = dbInterface;
this.datasetConfig = datasetConfig;
this.activConnections = activConnections;
this.triggerConn = (PLCConnection)activConnections[datasetConfig.TriggerConnection];
this.readBit = datasetConfig.TriggerReadBit;
this.quittBit = datasetConfig.TriggerQuittBit;
ak_interval = NoDataInterval;
}
public PLCTagTriggerThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary<ConnectionConfig, Object> activConnections, bool StartedAsService)
{
this.StartedAsService = StartedAsService;
this.dbInterface = dbInterface;
this.datasetConfig = datasetConfig;
this.activConnections = activConnections;
this.triggerConn = (PLCConnection) activConnections[datasetConfig.TriggerConnection];
this.readBit = datasetConfig.TriggerReadBit;
this.quittBit = datasetConfig.TriggerQuittBit;
ak_interval = NoDataInterval;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="databaseDirectoryPath">Path to the directory where SMAQC.s3db should be created/updated</param>
/// <param name="showQueryText">When true, show the text of every query at the console</param>
public DBWrapper(string databaseDirectoryPath, bool showQueryText)
{
// Get path to db [needed for SQLite so we save in correct location]
var databaseFilePath = Path.Combine(databaseDirectoryPath, "SMAQC.s3db");
// Create db conn
mDatabaseConnection = new DBSQLite(databaseFilePath);
// Verify that the required columns are present
// Attach the event handler
mDatabaseConnection.ErrorEvent += DatabaseConnection_ErrorEvent;
mShowQueryText = showQueryText;
}
public void Load()
{
IDBInterface db = DBInterface.CreatePointer();
db.StoredProcedure("mile_card_select_by_id");
db.AddParameter("@inIdMileCard", MySql.Data.MySqlClient.MySqlDbType.Int32, ID);
db.AddOutParameter("@outIdMileCardStatus", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outNumber", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outPassword", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outMilesCount", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIdAviaCompanyUnion", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIdAviaCompany", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIdRespondedPerson", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outIdOwnerPerson", MySql.Data.MySqlClient.MySqlDbType.Int32);
db.AddOutParameter("@outNote", MySql.Data.MySqlClient.MySqlDbType.String);
db.Execute();
mileCardStatus.ID = db.GetOutParameterInt("@outIdMileCardStatus");
mileCardStatus.Load();
Silent = true;
Number = db.GetOutParameterStr("@outNumber");
Password = db.GetOutParameterStr("@outPassword");
MilesCount = db.GetOutParameterInt("@outMilesCount");
AviaCompanyUnionID = db.GetOutParameterInt("@outIdAviaCompanyUnion");
AviaCompanyID = db.GetOutParameterInt("@outIdAviaCompany");
PersonRespondedID = db.GetOutParameterInt("@outIdRespondedPerson");
Note = db.GetOutParameterStr("@outNote");
Silent = false;
Changed = false;
if (personOwner.ID != db.GetOutParameterInt("@outIdOwnerPerson"))
{
throw new DataException("Mile card doesnt relates to current person");
}
}
public void Load()
{
if (ID != -1)
{
IDBInterface db = DBInterface.CreatePointer();
db.StoredProcedure("Country_union_select_by_id");
db.AddParameter("@inIdCountryUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, ID);
db.AddOutParameter("@outUnionName", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outShortUnionName", MySql.Data.MySqlClient.MySqlDbType.String);
db.AddOutParameter("@outNote", MySql.Data.MySqlClient.MySqlDbType.String);
db.Execute();
Name = db.GetOutParameterStr("@outUnionName");
ShortName = db.GetOutParameterStr("@outShortUnionName");
Note = db.GetOutParameterStr("@outNote");
Changed = false;
}
}
private void OpenStoragesAndCreateTriggers(bool CreateTriggers, bool StartedAsService)
{
foreach (DatasetConfig datasetConfig in akConfig.Datasets)
{
try
{
IDBInterface akDBInterface = null;
akDBInterface = StorageHelper.GetStorage(datasetConfig, RemotingServer.ClientComms.CallNotifyEvent);
akDBInterface.ThreadExceptionOccured += new ThreadExceptionEventHandler(tmpTrigger_ThreadExceptionOccured);
DatabaseInterfaces.Add(datasetConfig, akDBInterface);
Logging.LogText("DB Interface: " + datasetConfig.Name + " is starting...", Logging.LogLevel.Information);
akDBInterface.Initiate(datasetConfig);
if (CreateTriggers)
{
if (datasetConfig.Trigger == DatasetTriggerType.Tags_Handshake_Trigger)
{
PLCTagTriggerThread tmpTrigger = new PLCTagTriggerThread(akDBInterface, datasetConfig, ConnectionList, StartedAsService);
tmpTrigger.StartTrigger();
tmpTrigger.ThreadExceptionOccured += tmpTrigger_ThreadExceptionOccured;
myDisposables.Add(tmpTrigger);
}
else if (datasetConfig.Trigger == DatasetTriggerType.Time_Trigger)
{
TimeTriggerThread tmpTrigger = new TimeTriggerThread(akDBInterface, datasetConfig, ConnectionList, StartedAsService);
tmpTrigger.StartTrigger();
tmpTrigger.ThreadExceptionOccured += tmpTrigger_ThreadExceptionOccured;
myDisposables.Add(tmpTrigger);
}
else if (datasetConfig.Trigger == DatasetTriggerType.Time_Trigger_With_Value_Comparison)
{
TimeTriggerWithCheckForChangesThread tmpTrigger = new TimeTriggerWithCheckForChangesThread(akDBInterface, datasetConfig, ConnectionList, StartedAsService);
tmpTrigger.StartTrigger();
tmpTrigger.ThreadExceptionOccured += tmpTrigger_ThreadExceptionOccured;
myDisposables.Add(tmpTrigger);
}
else if (datasetConfig.Trigger == DatasetTriggerType.Quartz_Trigger)
{
QuartzTriggerThread tmpTrigger = new QuartzTriggerThread(akDBInterface, datasetConfig, ConnectionList, StartedAsService);
tmpTrigger.StartTrigger();
tmpTrigger.ThreadExceptionOccured += tmpTrigger_ThreadExceptionOccured;
myDisposables.Add(tmpTrigger);
}
else if (datasetConfig.Trigger == DatasetTriggerType.Triggered_By_Incoming_Data_On_A_TCPIP_Connection)
{
TCPIPConfig tcpipConnConf = datasetConfig.TriggerConnection as TCPIPConfig;
tcpipConnConf.MultiTelegramme = tcpipConnConf.MultiTelegramme <= 0 ? 1 : tcpipConnConf.MultiTelegramme;
if (tcpipConnConf.MultiTelegramme == 0)
{
tcpipConnConf.MultiTelegramme = 1;
}
TCPFunctionsAsync tmpConn = new TCPFunctionsAsync(null, tcpipConnConf.IPasIPAddress, tcpipConnConf.Port, !tcpipConnConf.PassiveConnection, tcpipConnConf.DontUseFixedTCPLength ? -1 : ReadData.GetCountOfBytesToRead(datasetConfig.DatasetConfigRows) * tcpipConnConf.MultiTelegramme);
tmpConn.AllowMultipleClients = tcpipConnConf.AcceptMultipleConnections;
tmpConn.UseKeepAlive = tcpipConnConf.UseTcpKeepAlive;
tmpConn.AsynchronousExceptionOccured += tmpTrigger_ThreadExceptionOccured;
tmpConn.AutoReConnect = true;
var conf = datasetConfig;
tmpConn.DataRecieved += (bytes, tcpClient) =>
{
if (tcpipConnConf.MultiTelegramme == 0)
{
tcpipConnConf.MultiTelegramme = 1;
}
for (int j = 1; j <= tcpipConnConf.MultiTelegramme; j++)
{
var ln = bytes.Length / tcpipConnConf.MultiTelegramme;
byte[] tmpArr = new byte[ln];
Array.Copy(bytes, ((j - 1) * ln), tmpArr, 0, ln);
IEnumerable <object> values = ReadData.ReadDataFromByteBuffer(conf, conf.DatasetConfigRows, tmpArr, StartedAsService);
if (values != null)
{
akDBInterface.Write(values);
}
}
};
tmpConn.ConnectionEstablished += (TcpClient tcp) =>
{
Logging.LogText("Connection established: " + tcpipConnConf.IPasIPAddress + ", " + tcpipConnConf.Port, Logging.LogLevel.Information);
};
tmpConn.ConnectionClosed += (TcpClient tcp) =>
{
Logging.LogText("Connection closed: " + tcpipConnConf.IPasIPAddress + ", " + tcpipConnConf.Port, Logging.LogLevel.Information);
};
Logging.LogText("Connection prepared: " + tcpipConnConf.IPasIPAddress + ", " + tcpipConnConf.Port, Logging.LogLevel.Information);
tmpConn.Start();
ConnectionList.Add(tcpipConnConf, tmpConn);
myDisposables.Add(tmpConn);
}
}
}
catch (Exception ex)
{
Logging.LogText("Error in OpenStorragesAndCreateTriggers occured!", ex, Logging.LogLevel.Error);
}
}
}
private void cmbStorage_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
lblError.Content = null;
if (dbIf != null)
{
dbIf.Dispose();
}
dbIf = null;
dbView = null;
lblDataCount.Content = null;
grdDatasetFields.ItemsSource = null;
datasetConfig = cmbStorage.SelectedItem as DatasetConfig;
dbIf = StorageHelper.GetStorage(datasetConfig, null);
dbIf.Connect_To_Database(datasetConfig.Storage);
dbView = dbIf as IDBViewable;
dbViewSQL = dbIf as IDBViewableSQL;
txtSQL.IsEnabled = false;
cmdSQL.IsEnabled = false;
txtSearch.IsEnabled = false;
cmdSearch.IsEnabled = false;
try
{
if (dbView != null)
{
txtSQL.Text = "";
CurrentNumber = 0;
DataTable tbl = dbView.ReadData(datasetConfig, null, CurrentNumber, 1000, null, null);
if (tbl != null)
{
grdDatasetFields.ItemsSource = tbl.DefaultView;
}
lblDataCount.Content = dbView.ReadCount(datasetConfig);
}
if (!string.IsNullOrEmpty(datasetConfig.DateTimeDatabaseField))
{
/*dbFieldNames = datasetConfig.DateTimeDatabaseField;
* foreach (var datasetConfigRow in datasetConfig.DatasetConfigRows)
* {
* if (datasetConfigRow.DatabaseField.ToLower().Trim() != datasetConfig.DateTimeDatabaseField.ToLower().Trim())
* dbFieldNames += ", " + datasetConfigRow.DatabaseField;
* }*/
dbFieldNames = datasetConfig.DateTimeDatabaseField + ",*";
}
if (dbViewSQL != null)
{
txtSQL.Text = "SELECT " + dbFieldNames + " FROM " + datasetConfig.Name + " ORDER BY id DESC LIMIT " + 1000.ToString();
txtSQL.IsEnabled = true;
cmdSQL.IsEnabled = true;
txtSearch.IsEnabled = true;
cmdSearch.IsEnabled = true;
}
}
catch (Exception ex)
{
lblError.Content = ex.Message;
}
}
