public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
{
foreach (var s in storages)
{
s.CreateOrModify_TablesAndFields(dataTable, datasetConfig);
}
}
public OnsDbSeeder(OnsDbContext ctx, IParserContext parserContext, IOptions <DatasetConfig> datasetConfig, ILogger <OnsDbSeeder> logger)
{
_ctx = ctx;
_parserContext = parserContext;
_datasetConfig = datasetConfig.Value;
_logger = logger;
}
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 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("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 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;
}
protected override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
{
this.dataTable = dataTable;
this.datasetConfig = datasetConfig;
this.fieldList = datasetConfig.DatasetConfigRows;
writeHeader(myConfig.ParseTextFilname());
}
private void cmdAddDataset_Click(object sender, RoutedEventArgs e)
{
string val = "Table_" + (ProtokollerConfiguration.ActualConfigInstance.Datasets.Count + 1);
if (DotNetSiemensPLCToolBoxLibrary.General.InputBox.Show("Tablename", "Name of the Dataset (used as Tablename)", ref val) == System.Windows.Forms.DialogResult.OK)
{
DatasetConfig myConfig = new DatasetConfig();
myConfig.Name = val;
ProtokollerConfiguration.ActualConfigInstance.Datasets.Add(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 DataTable ReadData(DatasetConfig datasetConfig, string filter, long Start, int Count, DateTime?Fromdate, DateTime?ToDate)
{
try
{
using (CsvReader csv = new CsvReader(new StreamReader(myConfig.ParseTextFilname(dataTable)), true, myConfig.Seperator))
{
int fieldCount = csv.FieldCount;
string[] headers = csv.GetFieldHeaders();
DataTable tbl = new DataTable();
int n = 0;
long ende = Start + Count;
for (int i = 0; i < fieldCount; i++)
{
tbl.Columns.Add(headers[i]);
}
while (csv.ReadNextRecord())
{
if (n >= Start && n < ende)
{
string[] values = new string[fieldCount];
for (int i = 0; i < fieldCount; i++)
{
values[i] = csv[i];
if (!string.IsNullOrEmpty(filter))
{
if (csv[i].ToLower().Contains(filter.ToLower()))
{
goto brk;
}
}
}
tbl.Rows.Add(values);
}
brk:
if (n > ende)
{
return(tbl);
}
n++;
}
return(tbl);
}
}
catch (Exception ex)
{
Logging.LogText("Exception: ", ex, Logging.LogLevel.Error);
}
return(null);
}
public Int64 ReadCount(DatasetConfig datasetConfig)
{
using (CsvReader csv = new CsvReader(new StreamReader(myConfig.ParseTextFilname()), 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;
}
private void cmdEditReadBit_Click(object sender, RoutedEventArgs e)
{
if (grdDatasets.SelectedItem != null)
{
DatasetConfig conf = grdDatasets.SelectedItem as DatasetConfig;
var tmp = DotNetSiemensPLCToolBoxLibrary.Communication.PLCTagEditor.ShowPLCTagEditor(conf.TriggerReadBit);
if (tmp != null)
{
tmp.TagDataType = DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Bool;
conf.TriggerReadBit = tmp;
}
}
}
private void cmdSelectReadBitFromProject_Click(object sender, RoutedEventArgs e)
{
if (grdDatasets.SelectedItem != null)
{
DatasetConfig conf = grdDatasets.SelectedItem as DatasetConfig;
var tmp = DotNetSiemensPLCToolBoxLibrary.Projectfiles.SelectProjectPart.SelectTAG("");
if (tmp != null)
{
tmp.TagDataType = DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Bool;
conf.TriggerReadBit = tmp;
}
}
}
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)
{
Logging.LogText("Exception: ", ex, Logging.LogLevel.Error);
}
return(null);
}
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;
}
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 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);
}
private void cmdAddDatasetRow_Click(object sender, RoutedEventArgs e)
{
DatasetConfig conf = grdDatasets.SelectedItem as DatasetConfig;
ConnectionConfig akConn = null;
string FieldType = "";
if (conf.DatasetConfigRows.Count > 0)
{
akConn = conf.DatasetConfigRows[conf.DatasetConfigRows.Count - 1].Connection;
FieldType = conf.DatasetConfigRows[conf.DatasetConfigRows.Count - 1].DatabaseFieldType;
}
conf.DatasetConfigRows.Add(new DatasetConfigRow()
{
DatabaseField = "Row_" + (conf.DatasetConfigRows.Count + 1).ToString(), Connection = akConn, DatabaseFieldType = FieldType
});
}
private void cmdRemoveDatasetRow_Click(object sender, RoutedEventArgs e)
{
DatasetConfig conf = grdDatasets.SelectedItem as DatasetConfig;
if (grdDatasetFields.Visibility == System.Windows.Visibility.Visible)
{
if (grdDatasetFields.SelectedItem != null)
{
conf.DatasetConfigRows.Remove((DatasetConfigRow)grdDatasetFields.SelectedItem);
}
}
else
{
if (grdDatasetFieldsEthernet.SelectedItem != null)
{
conf.DatasetConfigRows.Remove((DatasetConfigRow)grdDatasetFieldsEthernet.SelectedItem);
}
}
}
private void cmdAddDatasetRowsFromProjectFile_Click(object sender, RoutedEventArgs e)
{
string DataBaseTyp;
string DataBlockTyp;
//Anpassungen von Henning Göpfert-Dürwald & Christoph Reinshaus
var tags = DotNetSiemensPLCToolBoxLibrary.Projectfiles.SelectProjectPart.SelectTAGs("");
if (tags != null)
{
foreach (PLCTag tag in tags)
{
#region Create new row
DatasetConfig conf = grdDatasets.SelectedItem as DatasetConfig;
ConnectionConfig akConn = null;
string FieldType = "";
if (conf.DatasetConfigRows.Count > 0)
{
akConn = conf.DatasetConfigRows[conf.DatasetConfigRows.Count - 1].Connection;
FieldType = conf.DatasetConfigRows[conf.DatasetConfigRows.Count - 1].DatabaseFieldType;
}
conf.DatasetConfigRows.Add(new DatasetConfigRow()
{
DatabaseField = "Row_" + (conf.DatasetConfigRows.Count + 1).ToString(), Connection = akConn, DatabaseFieldType = FieldType
});
#endregion
DatasetConfigRow confRow = grdDatasetFields.Items[grdDatasetFields.Items.Count - 1] as DatasetConfigRow;
confRow.PLCTag = tag;
confRow.DatabaseField = tag.ValueName;
if (conf.Storage != null)
{
DataBaseTyp = conf.Storage.GetDefaultDatabaseFieldTypeForLibNoDaveTag(tag);
confRow.DatabaseFieldType = DataBaseTyp;
}
}
}
}
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);
}
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 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 long ReadCount(DatasetConfig datasetConfig)
{
var s = (IDBViewable)storages.First();
return(s.ReadCount(datasetConfig));
}
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 override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
{
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 " + 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 += myFeld.DatabaseField;
wertliste += "?" + myFeld.DatabaseField;
updateliste += myFeld.DatabaseField + "= ?" + myFeld.DatabaseField;
}
insertCommand = "INSERT INTO " + dataTable + "(" + felderliste + ") values(" + wertliste + ")";
updateCommand = "UPDATE " + dataTable + " SET " + updateliste;
}
public void TestDataReadWrite(DatasetConfig testDataset)
{
DatabaseInterfaces[testDataset].Write(ReadData.ReadDataFromDataSources(testDataset, testDataset.DatasetConfigRows, ConnectionList, false));
}
public void TestDataRead(DatasetConfig testDataset)
{
ReadData.ReadDataFromDataSources(testDataset, testDataset.DatasetConfigRows, ConnectionList, false);
}
