public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig) { foreach (var s in storages) { s.CreateOrModify_TablesAndFields(dataTable, datasetConfig); } }
public QuartzTriggerThread(IDBInterface dbInterface, DatasetConfig datasetConfig, Dictionary<ConnectionConfig, Object> activConnections, bool StartedAsService) { this.StartedAsService = StartedAsService; this.dbInterface = dbInterface; this.datasetConfig = datasetConfig; this.activConnections = activConnections; }
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 override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig) { this.dataTable = dataTable; this.datasetConfig = datasetConfig; this.fieldList = datasetConfig.DatasetConfigRows; writeHeader(myConfig.ParseTextFilname(dataTable)); }
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 override void Initiate(DatasetConfig dsConfig) { foreach (var s in storages) { dsConfig.Storage = storrageConfigs[s]; s.Initiate(dsConfig); } dsConfig.Storage = myConfig; }
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 Int64 ReadCount(DatasetConfig datasetConfig) { using (CsvReader csv = new CsvReader(new StreamReader(myConfig.ParseTextFilname(dataTable)), true, myConfig.Seperator)) { int n = 0; while (csv.ReadNextRecord()) { n++; } return n; } }
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 DataTable ReadData(DatasetConfig datasetConfig, string filter, long Start, int Count, DateTime? Fromdate, DateTime? ToDate) { try { Workbook workbook = Workbook.Load(myConfig.ParseFileName()); Worksheet akWorksheet = null; foreach (Worksheet worksheet in workbook.Worksheets) { if (worksheet.Name == datasetConfig.Name) akWorksheet = worksheet; } if (akWorksheet != null) { int fieldCount = akWorksheet.Cells.LastColIndex; DataTable tbl = new DataTable(); int n = 0; for (int i = 0; i <= fieldCount; i++) tbl.Columns.Add(akWorksheet.Cells[0, i].StringValue); for (int j =(int) Start+1; j < Start + Count; j++) { if (j < akWorksheet.Cells.Rows.Count) { string[] values = new string[fieldCount+1]; for (int i = 0; i <= fieldCount; i++) values[i] = akWorksheet.Cells[j, i].StringValue; tbl.Rows.Add(values); } n++; } return tbl; } } catch (Exception ex) { } return null; }
public static IDBInterface GetStorage(DatasetConfig cfg, Action<string> NewDataCallback) { if (cfg.Storage is SQLiteConfig) return new SQLLiteStorage(NewDataCallback); else if (cfg.Storage is CSVConfig) return new CSVStorage(NewDataCallback); else if (cfg.Storage is ExcelConfig) return new ExcelStorage(NewDataCallback); else if (cfg.Storage is PostgreSQLConfig) return new PostgreSQLStorage(NewDataCallback); else if (cfg.Storage is MySQLConfig) return new MySQLStorage(NewDataCallback); else if (cfg.Storage is Excel2007Config) return new Excel2007Storage(NewDataCallback); else if (cfg.Storage is MsSQLConfig) return new MsSQLStorage(NewDataCallback); else if (cfg.Storage is PLCConfig) return new PLCStorage(NewDataCallback); return null; }
public Int64 ReadCount(DatasetConfig datasetConfig) { if (File.Exists(myConfig.ParseFileName())) { Workbook workbook = Workbook.Load(myConfig.ParseFileName()); Worksheet akWorksheet = null; foreach (Worksheet worksheet in workbook.Worksheets) { if (worksheet.Name == datasetConfig.Name) akWorksheet = worksheet; } if (akWorksheet != null) { return akWorksheet.Cells.Rows.Count; } } return 0; }
public static IEnumerable<object> ReadDataFromByteBuffer(DatasetConfig datasetConfig, IEnumerable<DatasetConfigRow> datasetConfigRows, byte[] bytes, bool StartedAsService) { int pos = 0; foreach (var itm in datasetConfigRows) { if (pos < bytes.Length) { itm.PLCTag.ParseValueFromByteArray(bytes, pos); pos += itm.PLCTag.ReadByteSize; } } List<object> retVal = new List<object>(); foreach (var datasetConfigRow in datasetConfigRows) { retVal.Add(datasetConfigRow.Value(datasetConfig.UseFloatIfMultiplierIsUsed)); } return retVal; }
public DataTable ReadData(DatasetConfig datasetConfig, string filter, long Start, int Count, DateTime? FromDate, DateTime? ToDate) { //try //{ CheckAndEstablishReadConnection(); readCmd.Connection = readDBConn; string where = ""; if (!string.IsNullOrEmpty(filter)) { where = " WHERE "; bool first = true; foreach (var rows in datasetConfig.DatasetConfigRows) { if (!first) where += "OR "; where += rows.DatabaseField + " LIKE '%" + filter + "%' "; first = false; } } if (FromDate!=null) { if (string.IsNullOrEmpty(where)) where = " WHERE "; else where += " AND "; where += datasetConfig.DateTimeDatabaseField; where += " >= '" + FromDate.Value.ToString("yyyyMMdd HH:mm:ss.mmm") + "'"; } if (ToDate != null) { if (string.IsNullOrEmpty(where)) where = " WHERE "; else where += " AND "; where += datasetConfig.DateTimeDatabaseField; where += " <= '" + ToDate.Value.ToString("yyyyMMdd HH:mm:ss.mmm") + "'"; } readCmd.CommandText = "WITH " + datasetConfig.Name + "_withRowNumber AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber' FROM " + datasetConfig.Name + where + " ) SELECT * FROM " + datasetConfig.Name + "_withRowNumber WHERE RowNumber BETWEEN " + Start.ToString() + " AND " + (Start + Count).ToString() + ";"; //readCmd.CommandText = "SELECT *, ROW_NUMBER() OVER(ORDER BY id DESC) AS [RowNum] FROM " + datasetConfig.Name + " WHERE RowNum BETWEEN " + Start.ToString() + " AND " + (Start + Count).ToString(); //readCmd.CommandText = "SELECT * FROM " + datasetConfig.Name + " ORDER BY id DESC LIMIT " + Count.ToString() + " OFFSET " + Start.ToString(); DbDataReader akReader = readCmd.ExecuteReader(); DataTable myTbl = new DataTable(); myTbl.Load(akReader); akReader.Close(); return myTbl; }
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig) { TableName = datasetConfig.Name; writeHeader(myConfig.ParseFileName()); }
public virtual void Initiate(DatasetConfig dsConfig) { datasetConfig = dsConfig; storCfg = datasetConfig.Storage; Initiate(); }
public abstract void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig);
public void TestTriggers(DatasetConfig testDataset) { if (testDataset.Trigger == DatasetTriggerType.Tags_Handshake_Trigger) { EstablishConnections(); PLCConnection conn = ConnectionList[testDataset.TriggerConnection] as PLCConnection; if (conn != null) { conn.ReadValue(testDataset.TriggerReadBit); conn.ReadValue(testDataset.TriggerQuittBit); } } }
public DataTable ReadData(DatasetConfig datasetConfig, string filter, long Start, int Count, DateTime? FromDate, DateTime? ToDate) { var s = (IDBViewable)storages.First(); return s.ReadData(datasetConfig, filter, Start, Count, FromDate, ToDate); }
public void TestDataRead(DatasetConfig testDataset) { ReadData.ReadDataFromDataSources(testDataset, testDataset.DatasetConfigRows, ConnectionList, false); }
public void Initiate(DatasetConfig dsConfig) { datasetConfig = dsConfig; Initiate(); }
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig) { if (!string.IsNullOrEmpty(datasetConfig.DatasetTableName)) //Add the posibility to use a specific table_name (for using the table more then ones) this.dataTable = datasetConfig.DatasetTableName; else this.dataTable = dataTable; this.datasetConfig = datasetConfig; this.fieldList = datasetConfig.DatasetConfigRows; string sql = ""; try { sql = "CREATE DATABASE " + myConfig.Database + ";"; myCmd.Connection = myDBConn; myCmd.CommandText = sql; myCmd.ExecuteNonQuery(); } catch (MySqlException ex) { if (ex.Number != 1007) { Logging.LogText("Database could not be created. Storage: " + myConfig.Name, ex, Logging.LogLevel.Error); throw ex; } } myDBConn.ChangeDatabase(myConfig.Database); //Look if Table exists, when not, create it! try { sql = "SELECT * FROM " + dataTable + ";"; myCmd.Connection = myDBConn; myCmd.CommandText = sql; myReader = myCmd.ExecuteReader(); } catch (MySqlException ex) { if (ex.Number == 1146) { try { sql = "CREATE TABLE " + dataTable + " ("; sql += "id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY); "; myCmd.CommandText = sql; myCmd.ExecuteNonQuery(); sql = "SELECT * FROM " + dataTable + ";"; myCmd.CommandText = sql; myReader = myCmd.ExecuteReader(); } catch (Exception ex_ex) { Logging.LogText("Database-table could not be created. Storage: " + myConfig.Name + ", Table: " + dataTable, ex, Logging.LogLevel.Error); throw ex_ex; } } else { Logging.LogText("Error accessing Table. Storage: " + myConfig.Name, ex, Logging.LogLevel.Error); throw ex; } } //Look for the Fields, create or alter them! List<String> existDBFelderliste = new List<string>(); for (int n = 0; n < myReader.FieldCount; n++) { existDBFelderliste.Add(myReader.GetName(n)); } myReader.Close(); foreach (DatasetConfigRow myFeld in fieldList) { foreach (string existMyFeld in existDBFelderliste) { if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower()) { goto nextFeld; } } //Feld existiert nicht -> erzeugen string dbfieldtype = myFeld.DatabaseFieldType; switch (dbfieldtype) { case "bigint": dbfieldtype = "bigint NOT NULL default 0"; break; case "real": dbfieldtype = "real NOT NULL default 0"; break; case "datetime": dbfieldtype = "TIMESTAMP NOT NULL"; break; case "varchar": dbfieldtype = "VARCHAR(" + myFeld.DatabaseFieldSize + ") NOT NULL DEFAULT ''"; break; } sql = "ALTER TABLE " + dataTable + " ADD COLUMN " + QuoteField(myFeld.DatabaseField) + " " + dbfieldtype; try { myCmd.Connection = myDBConn; myCmd.CommandText = sql; myCmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } nextFeld: //Irgendeine anweisung, da sonst der Sprung nicht geht... { } } //Create Insert Command string wertliste = "", felderliste = "", updateliste = ""; foreach (DatasetConfigRow myFeld in fieldList) { if (wertliste != "") { wertliste += ","; felderliste += ","; updateliste += ","; } felderliste += QuoteField(myFeld.DatabaseField); wertliste += "?" + ValueName(myFeld.DatabaseField); updateliste += QuoteField(myFeld.DatabaseField) + "= ?" + ValueName(myFeld.DatabaseField); } insertCommand = "INSERT INTO " + this.dataTable + "(" + felderliste + ") values(" + wertliste + ")"; updateCommand = "UPDATE " + this.dataTable + " SET " + updateliste; }
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig) { this.datasetConfig = datasetConfig; if (!string.IsNullOrEmpty(datasetConfig.DatasetTableName)) //Add the posibility to use a specific table_name (for using the table more then ones) this.dataTable = datasetConfig.DatasetTableName; else this.dataTable = dataTable; this.fieldList = datasetConfig.DatasetConfigRows; List<DatasetConfigRow> createFieldList; //Look if Table exists, when not, create it! try { string sql = "SELECT TOP 1 * FROM " + dataTable + ";"; myCmd.Connection = myDBConn; myCmd.CommandText = sql; myReader = myCmd.ExecuteReader(); } catch (SqlException ex) { if (ex.Number == 208) { try { string sql = "CREATE TABLE " + dataTable + " (id int IDENTITY(1,1)PRIMARY KEY CLUSTERED); "; myCmd.CommandText = sql; myCmd.ExecuteNonQuery(); sql = "SELECT * FROM " + dataTable + ";"; myCmd.CommandText = sql; myReader = myCmd.ExecuteReader(); } catch (SqlException ex_ex) { throw ex_ex; } } else { throw ex; } } //Look for the Fields, create or alter them! List<String> existDBFelderliste = new List<string>(); for (int n = 0; n < myReader.FieldCount; n++) { existDBFelderliste.Add(myReader.GetName(n)); } myReader.Close(); //Wenn Date Time Feld gesetzt... dateFieldName = datasetConfig.DateTimeDatabaseField; createFieldList = new List<DatasetConfigRow>(fieldList); if (!string.IsNullOrEmpty(datasetConfig.DateTimeDatabaseField)) createFieldList.Add(new DatasetConfigRow() {DatabaseField = dateFieldName, DatabaseFieldType = "datetime"}); foreach (DatasetConfigRow myFeld in createFieldList) { foreach (string existMyFeld in existDBFelderliste) { if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower()) { goto nextFeld; } } //Feld existiert nicht -> erzeugen string sql = "ALTER TABLE " + dataTable + " ADD " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType; try { myCmd.Connection = myDBConn; myCmd.CommandText = sql; myCmd.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } nextFeld: //Irgendeine anweisung, da sonst der Sprung nicht geht... { } } //Create Insert Command string wertliste = "", felderliste = "", updateliste = ""; foreach (DatasetConfigRow myFeld in createFieldList) { if (wertliste != "") { wertliste += ","; felderliste += ","; updateliste += ","; } felderliste += myFeld.DatabaseField; wertliste += "@" + myFeld.DatabaseField; updateliste += myFeld.DatabaseField + "=@" + myFeld.DatabaseField; } insertCommand = "INSERT INTO " + this.dataTable + "(" + felderliste + ") values(" + wertliste + ")"; updateCommand = "UPDATE " + this.dataTable + " SET " + updateliste; }
public long ReadCount(DatasetConfig datasetConfig) { var s = (IDBViewable)storages.First(); return s.ReadCount(datasetConfig); }
public DataTable ReadData(DatasetConfig datasetConfig, string sql, int Count) { //try { CheckAndEstablishReadConnection(); readCmd.Connection = readDBConn; readCmd.CommandText = sql.Trim(); if (readCmd.CommandText.EndsWith(";")) readCmd.CommandText = readCmd.CommandText.Substring(0, readCmd.CommandText.Length - 1); //if (!readCmd.CommandText.Contains("ORDER BY")) // readCmd.CommandText += " ORDER BY id DESC"; //if (!readCmd.CommandText.Contains("LIMIT") && !readCmd.CommandText.Contains("OFFSET")) // readCmd.CommandText += " LIMIT " + Count.ToString(); DbDataReader akReader = readCmd.ExecuteReader(); DataTable myTbl = new DataTable(); myTbl.Load(akReader); akReader.Close(); return myTbl; } //catch (Exception ex) { } return null; }
public void TestDataReadWrite(DatasetConfig testDataset) { DatabaseInterfaces[testDataset].Write(ReadData.ReadDataFromDataSources(testDataset, testDataset.DatasetConfigRows, ConnectionList, false)); }
public Int64 ReadCount(DatasetConfig datasetConfig) { try { CheckAndEstablishReadConnection(); readCmd.Connection = readDBConn; readCmd.CommandText = "SELECT COUNT(*) FROM " + datasetConfig.Name; return Convert.ToInt64(readCmd.ExecuteScalar()); } catch (Exception ex) { } return 0; }
public DataTable ReadData(DatasetConfig datasetConfig, string filter, long Start, int Count, DateTime? Fromdate, DateTime? ToDate) { //try //{ CheckAndEstablishReadConnection(); var dbFieldNames = "*"; 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 + ",*"; } string where = ""; if (!string.IsNullOrEmpty(filter)) { where = " WHERE "; bool first = true; foreach (var rows in datasetConfig.DatasetConfigRows) { if (!first) where += "OR "; where += rows.DatabaseField + " LIKE '%" + filter + "%' "; first = false; } } readCmd.Connection = readDBConn; readCmd.CommandText = "SELECT " + dbFieldNames + " FROM " + datasetConfig.Name + where + " ORDER BY id DESC LIMIT " + Count.ToString() + " OFFSET " + Start.ToString(); DbDataReader akReader = readCmd.ExecuteReader(); DataTable myTbl = new DataTable(); myTbl.Load(akReader); akReader.Close(); return myTbl; //} //catch (Exception ex) //{ } //return null; }
protected override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig) { this.dataTable = dataTable; this.datasetConfig = datasetConfig; this.fieldList = datasetConfig.DatasetConfigRows; //Look if Table exists, when not, create it! try { string sql = "SELECT * FROM " + dataTable + ";"; myCmd = new FbCommand(sql, myDBConn); myReader = myCmd.ExecuteReader(); } catch (FbException ex) { /*if (ex.ErrorCode == 0) { try { string sql = "CREATE TABLE " + dataTable + " (id INTEGER PRIMARY KEY ASC AUTOINCREMENT); "; myCmd = new FbCommand(sql, myDBConn); myCmd.ExecuteNonQuery(); sql = "SELECT * FROM " + dataTable + ";"; myCmd = new FbCommand(sql, myDBConn); myReader = myCmd.ExecuteReader(); } catch (System.Data.SQLite.SQLiteException ex_ex) { throw ex_ex; } } else { throw ex; }*/ } //Look for the Fields, create or alter them! List<String> existDBFelderliste = new List<string>(); for (int n = 0; n < myReader.FieldCount; n++) { existDBFelderliste.Add(myReader.GetName(n)); } myReader.Close(); foreach (DatasetConfigRow myFeld in fieldList) { foreach (string existMyFeld in existDBFelderliste) { if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower()) { goto nextFeld; } } //Feld existiert nicht -> erzeugen string sql = "ALTER TABLE " + dataTable + " ADD COLUMN " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType; try { myCmd = new FbCommand(sql, myDBConn); myCmd.ExecuteNonQuery(); } catch (FbException ex) { throw ex; } nextFeld: //Irgendeine anweisung, da sonst der Sprung nicht geht... { } } //Create Insert Command string wertliste = "", felderliste = ""; foreach (DatasetConfigRow myFeld in fieldList) { if (wertliste != "") { wertliste += ","; felderliste += ","; } felderliste += myFeld.DatabaseField; wertliste += "@" + myFeld.DatabaseField; } insertCommand = "INSERT INTO " + dataTable + "(" + felderliste + ") values(" + wertliste + ")"; }
public DataTable ReadData(DatasetConfig datasetConfig, string filter, long Start, int Count, DateTime? Fromdate, DateTime? ToDate) { try { CheckAndEstablishReadConnection(); readCmd.Connection = readDBConn; readCmd.CommandText = "SELECT * FROM " + datasetConfig.Name + " LIMIT " + Count.ToString() + " OFFSET " + Start.ToString(); DbDataReader akReader = readCmd.ExecuteReader(); DataTable myTbl = new DataTable(); myTbl.Load(akReader); akReader.Close(); return myTbl; } catch (Exception ex) { } return null; }