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"); } }