public void SelectMain(long id)
{
List <string> output = new List <string>();
// Open the connection to the psqlDb
using (var conn = new NpgsqlConnection(ConnectToNpgSQL.GetConnection()))
try
{
conn.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand($"SELECT asset, mc FROM coal_table WHERE main_table_id = {id}", conn))
using (NpgsqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
// Console.WriteLine(reader.GetString(0), reader.GetString(1));
conn.Close();
}
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex);
long err = 0;
Console.WriteLine(err);
}
}
// Insert new record to tb_music.
public void InsertFuelRecord(string targetTable, long pk, string asset, string mc, string tng, string dcr)
{
// Open the connection to the psqlDb
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectToNpgSQL.GetConnection()))
try
{
conn.Open();
// Console.WriteLine("Connection Initilized");
// Create insert command.
NpgsqlCommand command = new NpgsqlCommand("INSERT INTO " +
$"{targetTable}(main_table_id, asset, mc, tng, dcr) VALUES(:main_table_id, :asset, :mc, " +
":tng, :dcr)", conn);
// Add paramaters.
command.Parameters.Add(new NpgsqlParameter("main_table_id",
NpgsqlTypes.NpgsqlDbType.Bigint));
command.Parameters.Add(new NpgsqlParameter("asset",
NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new NpgsqlParameter("mc",
NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new NpgsqlParameter("tng",
NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new NpgsqlParameter("dcr",
NpgsqlTypes.NpgsqlDbType.Text));
// Prepare the command.
command.Prepare();
// Add value to the paramater.
command.Parameters[0].Value = pk;
command.Parameters[1].Value = asset;
command.Parameters[2].Value = mc;
command.Parameters[3].Value = tng;
command.Parameters[4].Value = dcr;
// Execute SQL command.
int recordAffected = command.ExecuteNonQuery();
if (Convert.ToBoolean(recordAffected))
{
// Console.WriteLine("Data successfully saved!");
}
conn.Close();
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex);
}
}
public void InsertSummary(string targetTable, long pk, string name, string value)
{
// Open the connection to the psqlDb
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectToNpgSQL.GetConnection()))
try
{
conn.Open();
// Console.WriteLine("Connection Initilized");
// Create insert command.
NpgsqlCommand command = new NpgsqlCommand("INSERT INTO " +
$"{targetTable}(main_table_id, name, value) VALUES(:main_table_id, :name, :value)", conn);
// Add paramaters.
command.Parameters.Add(new NpgsqlParameter("main_table_id",
NpgsqlTypes.NpgsqlDbType.Bigint));
command.Parameters.Add(new NpgsqlParameter("name",
NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new NpgsqlParameter("value",
NpgsqlTypes.NpgsqlDbType.Text));
// Prepare the command.
command.Prepare();
// Add value to the paramater.
command.Parameters[0].Value = pk;
command.Parameters[1].Value = name;
command.Parameters[2].Value = value;
// Execute SQL command.
int recordAffected = command.ExecuteNonQuery();
if (Convert.ToBoolean(recordAffected))
{
// Console.WriteLine("Data successfully saved!");
}
conn.Close();
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex);
}
}
public long InsertMain(string targetTable, long timeStamp)
{
// Open the connection to the psqlDb
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectToNpgSQL.GetConnection()))
try
{
conn.Open();
// Console.WriteLine("Connection Initilized");
// Create insert command.
NpgsqlCommand command = new NpgsqlCommand("INSERT INTO " +
$"{targetTable}(time_stamp) VALUES(:time_stamp) returning *;", conn);
// Add paramaters.
command.Parameters.Add(new NpgsqlParameter("time_stamp",
NpgsqlTypes.NpgsqlDbType.Bigint));
// Prepare the command.
command.Prepare();
// Add value to the paramater.
command.Parameters[0].Value = timeStamp;
// Execute SQL command.
object recordAffected = command.ExecuteScalar();
if (Convert.ToBoolean(recordAffected))
{
return(Convert.ToInt64(recordAffected));
}
conn.Close();
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex);
long err = 0;
return(err);
}
return(0);
}
public void DbMigrate()
{
NpgsqlConnection conn = null;
using (conn = new NpgsqlConnection(ConnectToNpgSQL.GetConnection()))
{
conn.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand(
"DROP TABLE IF EXISTS main_table, coal_table, hydro_table, wind_table, biomass_table, interchange_table, summary_table, simple_cycle_table, cogeneration_table, combined_cycle_table; " +
"CREATE TABLE main_table(id SERIAL, time_stamp BIGINT);" +
"CREATE TABLE coal_table(id SERIAL, main_table_id INTEGER, asset TEXT, mc TEXT, tng TEXT, dcr TEXT); " +
"CREATE TABLE hydro_table(id SERIAL, main_table_id INTEGER, asset TEXT, mc TEXT, tng TEXT, dcr TEXT);" +
"CREATE TABLE wind_table(id SERIAL, main_table_id INTEGER, asset TEXT, mc TEXT, tng TEXT, dcr TEXT);" +
"CREATE TABLE biomass_table(id SERIAL, main_table_id INTEGER, asset TEXT, mc TEXT, tng TEXT, dcr TEXT);" +
"CREATE TABLE simple_cycle_table(id SERIAL, main_table_id INTEGER, asset TEXT, mc TEXT, tng TEXT, dcr TEXT);" +
"CREATE TABLE cogeneration_table(id SERIAL, main_table_id INTEGER, asset TEXT, mc TEXT, tng TEXT, dcr TEXT);" +
"CREATE TABLE combined_cycle_table(id SERIAL, main_table_id INTEGER, asset TEXT, mc TEXT, tng TEXT, dcr TEXT);" +
"CREATE TABLE summary_table(id SERIAL, main_table_id INTEGER, name TEXT, value TEXT);" +
"CREATE TABLE interchange_table(id SERIAL, main_table_id INTEGER, name TEXT, value TEXT);",
conn))
cmd.ExecuteNonQuery();
Console.WriteLine("Migration success");
}
}