internal void update_counter_database(int value)
{
PostgresConnection databaseConnection; //= new PostgresConnection();
databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
var cmd = new NpgsqlCommand("update existing_bags set gather_data_time =:p1 where var_table =:p2 ", databaseConnection.connection());
// cmd.Parameters.AddWithValue("p1", (now.TimeOfDay).ToString());
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlTypes.NpgsqlDbType.Integer));
cmd.Parameters.Add(new NpgsqlParameter("p2", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters[0].Value = value;
cmd.Parameters[1].Value = table_name;
cmd.Prepare();
cmd.ExecuteNonQuery();
databaseConnection.connection().Close();
}
protected void DataBags_Load(object sender, EventArgs e)
{
try
{
PostgresConnection databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
DataSet ds = new System.Data.DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select var_table , alive ,gather_data_time from existing_bags eb ", databaseConnection.connection());
da.Fill(ds, "DataBags");
DataBags_gridview.DataSource = ds.Tables[0];
DataBags_gridview.ReadOnly = true;
DataBags_gridview.AutoResizeColumns();
foreach (DataGridViewRow row in DataBags_gridview.Rows)
{
row.Height = (DataBags_gridview.ClientRectangle.Height - DataBags_gridview.ColumnHeadersHeight) / DataBags_gridview.Rows.Count;
}
//DataBags_gridview.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True;
//DataBags_gridview.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders;
databaseConnection.connection().Close();
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void GatherData_save_to_databse(string value)
{
DateTime now = DateTime.Now;
string datetime = DateTime.Today.ToString();
PostgresConnection databaseConnection; //= new PostgresConnection();
databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
try
{
var cmd = new NpgsqlCommand("update tools_data set last_changed_time =:p1, value =:p2, last_changed_date =:p3 where var =:p4 ", databaseConnection.connection());
// cmd.Parameters.AddWithValue("p1", (now.TimeOfDay).ToString());
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p2", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p3", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p4", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters[0].Value = (now.TimeOfDay).ToString();
cmd.Parameters[1].Value = value;
cmd.Parameters[2].Value = datetime.ToString();
cmd.Parameters[3].Value = "MB5";
cmd.Prepare();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
databaseConnection.connection().Close();
}
public void GatherData_save_to_databse()
{
DateTime now = DateTime.Now;
string datetime = DateTime.Today.ToString();
PostgresConnection databaseConnection; //= new PostgresConnection();
databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
// need to create lambda expression instead of foreach!
// foreach (var pair in opcVariables.getMap())
foreach (KeyValuePair <string, string> pair in this.getMap_plc())
{
var cmd = new NpgsqlCommand("update " + table_name + " set last_changed_time =:p1, value =:p2, last_changed_date =:p3 where var =:p4 ", databaseConnection.connection());
// cmd.Parameters.AddWithValue("p1", (now.TimeOfDay).ToString());
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p2", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p3", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p4", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters[0].Value = (now.TimeOfDay).ToString();
cmd.Parameters[1].Value = pair.Value;
cmd.Parameters[2].Value = datetime.ToString();
cmd.Parameters[3].Value = pair.Key;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
databaseConnection.connection().Close();
}
private void insert_example_values_to_table(string temporarty_bag_record, PostgresConnection con)
{
var m_createdb_cmd = new NpgsqlCommand(@"insert into " + temporarty_bag_record + @"
(var ,var_meaning ,created_on ,last_changed_date ,status ,var_path ,value ,last_changed_time )
values ('MB5','testing','2020-07-21','2020-07-21',true,'/Plc/','true','15:20:20')"
, con.connection());
m_createdb_cmd.ExecuteNonQuery();
}
private bool records_exists_in_table(infinite_loops infinite_loops)
{
PostgresConnection con = new PostgresConnection();
con.connection().Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT CASE WHEN EXISTS (SELECT * FROM " + infinite_loops.getTable_name() + " LIMIT 1) THEN 1 ELSE 0 END", con.connection());
NpgsqlDataReader rdr = cmd.ExecuteReader();
int value = -1;
while (rdr.Read())
{
value = rdr.GetInt16(0);
}
rdr.Close();
con.connection().Close();
return(value == 1 ? true : false);
}
private void showElementsOfBag(string table_name)
{
try
{
PostgresConnection databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
DataSet ds = new System.Data.DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select id,last_changed_time ,status ,var ,var_meaning , value ,var_path ,created_on from " + table_name
+ " pdv order by id ", databaseConnection.connection());
da.Fill(ds, "DataBags");
valuesInBag_gridview.DataSource = ds.Tables[0];
valuesInBag_gridview.ReadOnly = true;
valuesInBag_gridview.AutoResizeColumns();
valuesInBag_gridview.ScrollBars = ScrollBars.Both;
valuesInBag_gridview.PerformLayout();
foreach (DataGridViewRow row in valuesInBag_gridview.Rows)
{
row.Height = (valuesInBag_gridview.ClientRectangle.Height - valuesInBag_gridview.ColumnHeadersHeight) / valuesInBag_gridview.Rows.Count;
}
databaseConnection.connection().Close();
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void create_additional_table(string temporarty_bag_record, PostgresConnection con)
{
/*
* CAREFUL, SQL INJECTION RIGHT BELOW! CORRECT IT!
*/
var m_createdb_cmd = new NpgsqlCommand(@"create table if not exists " + temporarty_bag_record + @"
(
id serial primary key,
last_changed_time varchar(255) not null,
status varchar(255) not null,
var varchar(255) not null,
last_changed_date varchar(255) not null,
var_meaning varchar(255) not null,
value varchar(255) not null,
var_path varchar(255) not null,
created_on varchar(255) not null
)", con.connection());
m_createdb_cmd.ExecuteNonQuery();
}
public OpcVairables(string tablename)
{
table_name = tablename;
// empty, previously save to database, migrated to another function
PostgresConnection databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
AddPlcVariablesToMap(databaseConnection);
//finall result of collected items gathered by OPC_server
results = OpcUastartup.Instance.get_m_server().ReadValues(nodesToRead);
int index = 0;
foreach (var pair in this.map_from_database)
{
map_plc.Add(pair.Key, results[index]);
index++;
}
}
private void button1_Click_2(object sender, EventArgs e)
{
List <string> list_of_var_in_database = new List <string>();
// empty, previously save to database, migrated to another function
PostgresConnection databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
// NpgsqlCommand cmd = new NpgsqlCommand("select plc_var from plc_data_variables order by id_var", databaseConnection.connection());
// NpgsqlDataReader rdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
// SqlDataAdapter da = new SqlDataAdapter("select plc_var from plc_data_variables order by id_var", databaseConnection.connection());
// NpgsqlDataAdapter da = new NpgsqlDataAdapter("select concat(plc_var_path , plc_var) as var from plc_data_variables order by id_var ", databaseConnection.connection());
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select id_var , concat(plc_var_path , plc_var) as var, created_on da from plc_data_variables order by id_var", databaseConnection.connection());
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.ReadOnly = true;
DataGridViewColumn column = dataGridView1.Columns[0];
column.Width = 32;
DataGridViewColumn column2 = dataGridView1.Columns[1];
column2.Width = 110;
DataGridViewColumn column3 = dataGridView1.Columns[2];
column3.Width = 110;
}
private void createBag()
{
existing_bags = new Dictionary <string, int>();
string varu = null;
PostgresConnection con = new PostgresConnection();
con.connection().Open();
// NpgsqlCommand cmd = new NpgsqlCommand("select var_table , gather_data_time from existing_bags eb order by id ", con.connection());
// testting purpose
NpgsqlCommand cmd = new NpgsqlCommand("select var_table , gather_data_time from existing_bags eb where alive = true order by id ", con.connection());
NpgsqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
varu = rdr.GetString(0);
existing_bags.Add(rdr.GetString(0), rdr.GetInt32(1));
}
rdr.Close();
}
private void AddPlcVariablesToMap(PostgresConnection con)
{
NpgsqlCommand cmd = new NpgsqlCommand("SELECT var_path , var FROM " + table_name + " pdv ", con.connection());
NpgsqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
map_from_database.Add(rdr.GetString(1), rdr.GetString(0));
}
rdr.Close();
foreach (var pair in this.map_from_database)
{
string dup = pair.Value;
string dupa = pair.Key;
nodesToRead.Add(new NodeId(pair.Value + pair.Key, 2).ToString());
}
}
private void testing_add_record_Click(object sender, EventArgs e)
{
string temporarty_bag_record = null;
temporarty_bag_record = "test_record";
PostgresConnection con = new PostgresConnection();
con.connection().Open();
NpgsqlCommand cmd = new NpgsqlCommand("select var_table from existing_bags eb order by id ", con.connection());
NpgsqlDataReader rdr = cmd.ExecuteReader();
bool table_already_exists = false;
while (rdr.Read())
{
if (rdr.GetString(0).Equals(temporarty_bag_record))
{
table_already_exists = true;
}
}
rdr.Close();
if (table_already_exists == false)
{
// NpgsqlCommand cmd_2 = new NpgsqlCommand("insert into existing_bags(var_table, alive, gather_data_time) values('"+ temporarty_bag_record + "',true,2000 ", con.connection());
// NpgsqlDataReader rdr_2 = cmd_2.ExecuteReader();
using (var cmd_2 = new NpgsqlCommand("insert into existing_bags(var_table, alive, gather_data_time) values(@p1,@p2,@p3)", con.connection()))
{
cmd_2.Parameters.Add(new NpgsqlParameter("p1", NpgsqlTypes.NpgsqlDbType.Text));
cmd_2.Parameters.Add(new NpgsqlParameter("p2", NpgsqlTypes.NpgsqlDbType.Boolean));
cmd_2.Parameters.Add(new NpgsqlParameter("p3", NpgsqlTypes.NpgsqlDbType.Integer));
cmd_2.Parameters[0].Value = temporarty_bag_record;
cmd_2.Parameters[1].Value = true;
cmd_2.Parameters[2].Value = 2000;
cmd_2.ExecuteNonQuery();
}
infinite_loops inf_temp = new infinite_loops(2000, temporarty_bag_record);
inifite_loop_list.Add(inf_temp);
create_additional_table(temporarty_bag_record, con);
MessageBox.Show("table created", "Database information", MessageBoxButtons.OK);
insert_example_values_to_table(temporarty_bag_record, con);
MessageBox.Show("values added", "Database information", MessageBoxButtons.OK);
inf_temp.initizalizer();
MessageBox.Show("loop initialized", "Database information", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("Rekord aleady exists", "Database information", MessageBoxButtons.OK);
}
con.connection().Close();
}