/// <summary>
/// update
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int update(ref station obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE stations set " +
" socket_port = :socket_port, station_name =:station_name, " +
" station_id =:station_id," +
" ftpserver =:ftpserver, ftpusername=:ftpusername," +
" ftppassword =:ftppassword, ftpfolder=:ftpfolder," +
" ftpflag =:ftpflag," +
" modified = :modified " +
//" goip = :goip " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":socket_port", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.socket_port;
cmd.Parameters.Add(":station_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.station_name;
cmd.Parameters.Add(":station_id", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.station_id;
cmd.Parameters.Add(":modified", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.modified;
cmd.Parameters.Add(":ftpserver", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpserver;
cmd.Parameters.Add(":ftpusername", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpusername;
cmd.Parameters.Add(":ftppassword", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftppassword;
cmd.Parameters.Add(":ftpfolder", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpfolder;
cmd.Parameters.Add(":ftpflag", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.ftpflag;
//cmd.Parameters.Add(":goip", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.goip;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
/// <summary>
/// update
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int update(ref module obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE modules set " +
" item_name = :item_name, module_id =:module_id, " +
" on_value = :on_value, off_value =:off_value, " +
" input_min = :input_min, input_max =:input_max, " +
" output_min = :output_min, output_max =:output_max, " +
" error_min = :error_min, error_max =:error_max, " +
" channel_number =:channel_number, off_set =:off_set " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":item_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.item_name;
cmd.Parameters.Add(":module_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.module_id;
cmd.Parameters.Add(":channel_number", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.channel_number;
cmd.Parameters.Add(":on_value", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.on_value;
cmd.Parameters.Add(":off_value", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.off_value;
cmd.Parameters.Add(":input_min", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.input_min;
cmd.Parameters.Add(":input_max", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.input_max;
cmd.Parameters.Add(":output_min", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.output_min;
cmd.Parameters.Add(":output_max", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.output_max;
cmd.Parameters.Add(":error_min", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.error_min;
cmd.Parameters.Add(":error_max", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.error_max;
cmd.Parameters.Add(":off_set", NpgsqlTypes.NpgsqlDbType.Double).Value = obj.off_set;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch (NpgsqlException ex)
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
/// <summary>
/// add new
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int add(ref water_sampler obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO water_samplers (equipment_name, " +
" response_time,refrigeration_Temperature, " +
" bottle_position, equipment_status, comm_port, created)" +
" VALUES (:equipment_name, " +
" :response_time, :refrigeration_Temperature, " +
" :bottle_position, :equipment_status, :comm_port, :created)";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":equipment_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.equipment_name;
cmd.Parameters.Add(":response_time", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.response_time;
cmd.Parameters.Add(":refrigeration_Temperature", NpgsqlTypes.NpgsqlDbType.Double).Value = obj.refrigeration_Temperature;
cmd.Parameters.Add(":bottle_position", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.bottle_position;
cmd.Parameters.Add(":equipment_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.equipment_status;
cmd.Parameters.Add(":comm_port", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.comm_port;
cmd.Parameters.Add(":created", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.created;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// update
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int update(ref measured_device obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE measured_devices set " +
" station_id = :station_id, device_name =:device_name, " +
" device_code =:device_code, " +
" automatic_status = :automatic_status, " +
" manual_status = :manual_status, " +
" comm_port = :comm_port, " +
" created = :created " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":station_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.station_id;
cmd.Parameters.Add(":device_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.device_name;
cmd.Parameters.Add(":device_code", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.device_code;
cmd.Parameters.Add(":automatic_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.automatic_status;
cmd.Parameters.Add(":manual_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.manual_status;
cmd.Parameters.Add(":comm_port", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.comm_port;
cmd.Parameters.Add(":created", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.created;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
/// <summary>
/// add new
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int add(ref measured_device obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO measured_devices (station_id, device_name, " +
" device_code, automatic_status, manual_status, comm_port, created)" +
" VALUES (:station_id, :device_name, " +
" :device_code, :automatic_status, :manual_status, :comm_port, :created)";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":station_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.station_id;
cmd.Parameters.Add(":device_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.device_name;
cmd.Parameters.Add(":device_code", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.device_code;
cmd.Parameters.Add(":automatic_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.automatic_status;
cmd.Parameters.Add(":manual_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.manual_status;
cmd.Parameters.Add(":comm_port", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.comm_port;
cmd.Parameters.Add(":created", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.created;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// update
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int update(ref water_sampler obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE water_samplers set " +
" equipment_name = :equipment_name, response_time =:response_time, " +
" refrigeration_Temperature =:refrigeration_Temperature, " +
" bottle_position = :bottle_position, equipment_status =:equipment_status, " +
" comm_port =:comm_port, " +
" created = :created " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":equipment_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.equipment_name;
cmd.Parameters.Add(":response_time", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.response_time;
cmd.Parameters.Add(":refrigeration_Temperature", NpgsqlTypes.NpgsqlDbType.Double).Value = obj.refrigeration_Temperature;
cmd.Parameters.Add(":bottle_position", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.bottle_position;
cmd.Parameters.Add(":equipment_status", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.equipment_status;
cmd.Parameters.Add(":comm_port", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.comm_port;
cmd.Parameters.Add(":created", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.created;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
public int add(ref push_server obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO push_server ( ftp_ip, " +
" ftp_username, ftp_pwd, ftp_folder, ftp_flag, ftp_lasted, ftp_lasted_manual)" +
" VALUES (:ftp_ip, " +
" :ftp_username, :ftp_pwd, :ftp_folder, :ftp_flag, :ftp_lasted, :ftp_lasted_manual)";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":ftp_ip", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_ip;
cmd.Parameters.Add(":ftp_username", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_username;
cmd.Parameters.Add(":ftp_pwd", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_pwd;
cmd.Parameters.Add(":ftp_folder", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_folder;
cmd.Parameters.Add(":ftp_flag", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.ftp_flag;
cmd.Parameters.Add(":ftp_lasted", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.ftp_lasted;
cmd.Parameters.Add(":ftp_lasted_manual", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.ftp_lasted_manual;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
public int update_with_id(ref push_server obj, int id)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE push_server set " +
" ftp_ip = :ftp_ip, " +
" ftp_username =:ftp_username, " +
" ftp_pwd =:ftp_pwd, " +
" ftp_folder = :ftp_folder, " +
" ftp_flag = :ftp_flag, " +
" ftp_lasted = :ftp_lasted, " +
" ftp_lasted_manual = :ftp_lasted_manual " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":ftp_ip", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_ip;
cmd.Parameters.Add(":ftp_username", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_username;
cmd.Parameters.Add(":ftp_pwd", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_pwd;
cmd.Parameters.Add(":ftp_flag", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.ftp_flag;
cmd.Parameters.Add(":ftp_folder", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftp_folder;
cmd.Parameters.Add(":ftp_lasted", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.ftp_lasted;
cmd.Parameters.Add(":ftp_lasted_manual", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.ftp_lasted_manual;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch (Exception ex)
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
public int add(ref user obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO users (user_name, " +
" password, name, id_number, user_groups_id)" +
" VALUES (:user_name, " +
" :password, :name, :id_number, :user_groups_id)";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":user_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.user_name;
cmd.Parameters.Add(":password", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.password;
cmd.Parameters.Add(":name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.name;
cmd.Parameters.Add(":id_number", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.id_number;
cmd.Parameters.Add(":user_groups_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.user_groups_id;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// add new
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int add(ref setting obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO settings ( setting_key, " +
" setting_value, setting_type, note, setting_datetime)" +
" VALUES (:setting_key, " +
" :setting_value, :setting_type, :note, :setting_datetime)";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":setting_key", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.setting_key;
cmd.Parameters.Add(":setting_value", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.setting_value;
cmd.Parameters.Add(":setting_type", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.setting_type;
cmd.Parameters.Add(":note", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.note;
cmd.Parameters.Add(":setting_datetime", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.setting_datetime;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// update
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int update(ref user obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE users set " +
" user_name = :user_name, " +
" name = :name, " +
" id_number = :id_number, " +
" user_groups_id = :user_groups_id, " +
" password = :password " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":user_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.user_name;
cmd.Parameters.Add(":password", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.password;
cmd.Parameters.Add(":name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.name;
cmd.Parameters.Add(":id_number", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.id_number;
cmd.Parameters.Add(":user_groups_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.user_groups_id;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
public int update_with_id(ref setting obj, int id)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE settings set " +
//" setting_key = :setting_key, " +
" setting_value =:setting_value, " +
" setting_type =:setting_type, " +
" note = :note, " +
" setting_datetime = :setting_datetime " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
//cmd.Parameters.Add(":setting_key", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.setting_key;
cmd.Parameters.Add(":setting_value", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.setting_value;
cmd.Parameters.Add(":setting_type", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.setting_type;
cmd.Parameters.Add(":note", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.note;
cmd.Parameters.Add(":setting_datetime", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.setting_datetime;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch (Exception ex)
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
/// <summary>
/// get info by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public maintenance_log get_info_by_id(int id)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
maintenance_log obj = null;
if (db.open_connection())
{
string sql_command = "SELECT maintenance_logs.*, users.user_name, users.name FROM maintenance_logs, users ";
sql_command += " WHERE maintenance_logs.user_id = users.id ";
sql_command += " AND id = " + id;
sql_command += " LIMIT 1";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = new maintenance_log();
obj = (maintenance_log)_get_info(reader);
break;
}
reader.Close();
db.close_connection();
return(obj);
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{ db.close_connection(); }
}
}
public IEnumerable <maintenance_log> get_all_by_date(string strDate)
{
List <maintenance_log> listmaintenance_log = new List <maintenance_log>();
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "SELECT maintenance_logs.*, users.user_name, users.name FROM maintenance_logs, users ";
sql_command += " WHERE maintenance_logs.user_id = users.id ";
sql_command += " AND date(start_time) = '" + strDate + "'";
sql_command += " ORDER BY start_time ASC";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
maintenance_log obj = new maintenance_log();
obj = (maintenance_log)_get_info(reader);
listmaintenance_log.Add(obj);
}
reader.Close();
db.close_connection();
return(listmaintenance_log);
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// get info by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public module get_info_by_id(int id)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
module obj = null;
if (db.open_connection())
{
string sql_command = "SELECT * FROM modules WHERE id = " + id;
sql_command += " LIMIT 1";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = new module();
obj = (module)_get_info(reader);
break;
}
reader.Close();
db.close_connection();
return(obj);
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// get flower by user_name
/// </summary>
/// <param name="user_name"></param>
/// <returns>return flower: if null then not exist flower with id, else flower object</returns>
public user getUserInfoByUserName(string user_name)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
user obj = null;
if (db.open_connection())
{
string sql_command = "SELECT * FROM users where user_name = '" + user_name + "'";
sql_command += " LIMIT 1";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = new user();
obj = (user)_get_info(reader);
break;
}
reader.Close();
db.close_connection();
return(obj);
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{ db.close_connection(); }
}
}
public String getMeasureTime(String table, String time)
{
String measuretime;
//String time = "created";
//String table = "data_5minute_values";
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "SELECT " + time + " from " + table + " order by ID desc limit 1";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader dr = cmd.ExecuteReader();
DataTable data = new DataTable();
// call load method of datatable to copy content of reader
data.Load(dr); // Load
string strvalue = "";
foreach (DataRow row in data.Rows)
{
strvalue = Convert.ToString(row["created"]);
}
strvalue = DateFormat(strvalue);
//Console.WriteLine(strvalue);
//Console.Read();
db.close_connection();
return(strvalue);
}
}
else
{
db.close_connection();
return("ERROR");
}
}
catch (Exception ex)
{
Console.WriteLine("\nMessage ---\n{0}", ex.Message);
Console.WriteLine("\nMessage ---\n{0}", ex.StackTrace);
return("ERROR");
}
}
}
public DateTime get_datetime_by_id(int id)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
DateTime obj = new DateTime();
if (db.open_connection())
{
string sql_command = "SELECT setting_datetime FROM settings WHERE id = " + id;
//sql_command += " LIMIT 1";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = (DateTime)_get_datetime_by_id(reader);
break;
}
reader.Close();
db.close_connection();
return(obj);
}
}
else
{
db.close_connection();
return(new DateTime());
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(new DateTime());
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// get info by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public station get_info()
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
station obj = null;
if (db.open_connection())
{
string sql_command = "SELECT * FROM stations LIMIT 1";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = new station();
obj = (station)_get_info(reader);
break;
}
reader.Close();
db.close_connection();
return(obj);
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{ db.close_connection(); }
}
}
public int get_id_by_key(string setting_key)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int obj = -1;
if (db.open_connection())
{
string sql_command = "SELECT id FROM settings WHERE setting_key = " + "\'" + setting_key + "\'";
//sql_command += " LIMIT 1";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = (int)_get_id_by_key(reader);
break;
}
reader.Close();
db.close_connection();
return(obj);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch (Exception ex)
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// Get all
/// </summary>
/// <returns></returns>
public IEnumerable <module> get_all()
{
List <module> listUser = new List <module>();
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "SELECT * FROM modules";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
module obj = new module();
obj = (module)_get_info(reader);
listUser.Add(obj);
}
reader.Close();
db.close_connection();
return(listUser);
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// delete
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool delete(int id)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
bool result = false;
if (db.open_connection())
{
string sql_command = "DELETE from modules where id = " + id;
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
result = cmd.ExecuteNonQuery() > 0;
db.close_connection();
return(true);
}
}
else
{
db.close_connection();
return(result);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(false);
}
finally
{ db.close_connection(); }
}
}
public bool executeNonQueryCommand(string strQuery)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
bool result = false;
if (db.open_connection())
{
string NpgsqlCommand = strQuery;
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = NpgsqlCommand;
result = cmd.ExecuteNonQuery() > 0;
db.close_connection();
return(result);
}
}
else
{
db.close_connection();
return(result);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(false);
}
finally
{ db.close_connection(); }
}
}
public bool checkExistRecordWithQueryCommand(string strQuery)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string NpgsqlCommand = strQuery;
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = NpgsqlCommand;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
return(true);
}
return(false);
}
}
else
{
db.close_connection();
return(false);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(false);
}
finally
{ db.close_connection(); }
}
}
public void UpdateData(String username, String newpass)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
//String connstring = "Server = localhost;Port = 5432; User Id = postgres;Password = 123;Database = DataLoggerDB";
//NpgsqlConnection conn = new NpgsqlConnection(connstring);
//conn.Open();
newpass = Crypto.HashPassword(newpass);
string sql_command = "UPDATE auth SET password = "******"\'" + newpass + "\'" + " WHERE user_name = " + "\'" + username + "\'";
//NpgsqlCommand cmd = new NpgsqlCommand("UPDATE auth SET password = "******"\'" + newpass + "\'" + " WHERE user_name = " + "\'" + username + "\'", conn);
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.ExecuteNonQuery();
db.close_connection();
}
}
else
{
db.close_connection();
}
}
catch (Exception ex)
{
string[] Error = new String[1] {
"error"
};
Console.WriteLine("\nMessage ---\n{0}", ex.Message);
Console.WriteLine("\nMessage ---\n{0}", ex.StackTrace);
}
}
}
public NpgsqlDataReader executeReaderCommand(string strQuery)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string NpgsqlCommand = strQuery;
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = NpgsqlCommand;
return(cmd.ExecuteReader());
}
}
else
{
db.close_connection();
return(null);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(null);
}
finally
{
//db.close_connection();
}
}
}
/// <summary>
/// add new
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int add(ref module obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO modules ( item_name, " +
" on_value, off_value, input_min, input_max," +
" output_min, output_max," +
" error_min, error_max," +
" module_id, channel_number, :offset)" +
" VALUES (:item_name, " +
" :on_value, :off_value, :input_min, :input_max," +
" :output_min, :output_max," +
" :error_min, :error_max," +
" :module_id, :channel_number, :off_set," +
" :unit, :value_column, :status_column," +
" :type_value, :display_name " +
" )";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":item_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.item_name;
cmd.Parameters.Add(":module_id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.module_id;
cmd.Parameters.Add(":channel_number", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.channel_number;
cmd.Parameters.Add(":on_value", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.on_value;
cmd.Parameters.Add(":off_value", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.off_value;
cmd.Parameters.Add(":input_min", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.input_min;
cmd.Parameters.Add(":input_max", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.input_max;
cmd.Parameters.Add(":output_min", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.output_min;
cmd.Parameters.Add(":output_max", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.output_max;
cmd.Parameters.Add(":error_min", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.error_min;
cmd.Parameters.Add(":error_max", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.error_max;
cmd.Parameters.Add(":off_set", NpgsqlTypes.NpgsqlDbType.Double).Value = obj.off_set;
cmd.Parameters.Add(":unit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.unit;
cmd.Parameters.Add(":value_column", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.value_column;
cmd.Parameters.Add(":status_column", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.status_column;
cmd.Parameters.Add(":type_value", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.type_value;
cmd.Parameters.Add(":display_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.display_name;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// add new
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int add(ref station obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO stations (station_name, " +
" station_id,socket_port," +
" sampler_comport, tn_comport, tp_comport, toc_comport, " +
" mps_comport, module_comport, mps_protocol, tn_protocol, " +
" tp_protocol, toc_protocol, " +
" do1_caption, do2_caption, " +
" do3_caption, do4_caption, " +
" do5_caption, do6_caption, " +
" do7_caption, do8_caption, " +
" do1_caption_vi, do2_caption_vi, " +
" do3_caption_vi, do4_caption_vi, " +
" do5_caption_vi, do6_caption_vi, " +
" do7_caption_vi, do8_caption_vi, " +
" ftpserver, ftpusername, " +
" ftppassword, ftpfolder, " +
" ftpflag, " +
" modified)" +
" VALUES (:station_name, " +
" :station_id, :socket_port," +
" :sampler_comport, :tn_comport, :tp_comport, :toc_comport, " +
" :mps_comport, :module_comport, :mps_protocol, :tn_protocol, " +
" :tp_protocol, :toc_protocol, " +
" :do1_caption, :do2_caption, " +
" :do3_caption, :do4_caption, " +
" :do5_caption, :do6_caption, " +
" :do7_caption, :do8_caption, " +
" :do1_caption_vi, :do2_caption_vi, " +
" :do3_caption_vi, :do4_caption_vi, " +
" :do5_caption_vi, :do6_caption_vi, " +
" :do7_caption_vi, :do8_caption_vi, " +
" :ftpserver, :ftpusername, " +
" :ftppassword, :ftpfolder, " +
" :ftpflag, " +
" :modified)";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":socket_port", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.socket_port;
cmd.Parameters.Add(":station_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.station_name;
cmd.Parameters.Add(":station_id", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.station_id;
cmd.Parameters.Add(":modified", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.modified;
cmd.Parameters.Add(":sampler_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.sampler_comport;
cmd.Parameters.Add(":tn_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.tn_comport;
cmd.Parameters.Add(":tp_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.tp_comport;
cmd.Parameters.Add(":toc_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.toc_comport;
cmd.Parameters.Add(":mps_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.mps_comport;
cmd.Parameters.Add(":module_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.module_comport;
cmd.Parameters.Add(":mps_protocol", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.mps_protocol;
cmd.Parameters.Add(":tn_protocol", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.tn_protocol;
cmd.Parameters.Add(":tp_protocol", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.tp_protocol;
cmd.Parameters.Add(":toc_protocol", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.toc_protocol;
cmd.Parameters.Add(":do1_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do1_caption;
cmd.Parameters.Add(":do2_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do2_caption;
cmd.Parameters.Add(":do3_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do3_caption;
cmd.Parameters.Add(":do4_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do4_caption;
cmd.Parameters.Add(":do5_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do5_caption;
cmd.Parameters.Add(":do6_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do6_caption;
cmd.Parameters.Add(":do7_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do7_caption;
cmd.Parameters.Add(":do8_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do8_caption;
cmd.Parameters.Add(":do1_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do1_caption_vi;
cmd.Parameters.Add(":do2_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do2_caption_vi;
cmd.Parameters.Add(":do3_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do3_caption_vi;
cmd.Parameters.Add(":do4_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do4_caption_vi;
cmd.Parameters.Add(":do5_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do5_caption_vi;
cmd.Parameters.Add(":do6_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do6_caption_vi;
cmd.Parameters.Add(":do7_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do7_caption_vi;
cmd.Parameters.Add(":do8_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do8_caption_vi;
cmd.Parameters.Add(":ftpserver", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpserver;
cmd.Parameters.Add(":ftpusername", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpusername;
cmd.Parameters.Add(":ftppassword", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftppassword;
cmd.Parameters.Add(":ftpfolder", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpfolder;
cmd.Parameters.Add(":ftpflag", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.ftpflag;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}
/// <summary>
/// update
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int update(ref station obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
if (db.open_connection())
{
string sql_command = "UPDATE stations set " +
" socket_port = :socket_port, station_name =:station_name, " +
" station_id =:station_id, sampler_comport=:sampler_comport," +
" tn_comport =:tn_comport, tp_comport=:tp_comport," +
" toc_comport =:toc_comport, mps_comport=:mps_comport," +
" module_comport =:module_comport, mps_protocol=:mps_protocol," +
" tn_protocol =:tn_protocol, tp_protocol=:tp_protocol," +
" toc_protocol =:toc_protocol," +
" do1_caption =:do1_caption, do2_caption=:do2_caption," +
" do3_caption =:do3_caption, do4_caption=:do4_caption," +
" do5_caption =:do5_caption, do6_caption=:do6_caption," +
" do7_caption =:do7_caption, do8_caption=:do8_caption," +
" do1_caption_vi =:do1_caption_vi, do2_caption_vi=:do2_caption_vi," +
" do3_caption_vi =:do3_caption_vi, do4_caption_vi=:do4_caption_vi," +
" do5_caption_vi =:do5_caption_vi, do6_caption_vi=:do6_caption_vi," +
" do7_caption_vi =:do7_caption_vi, do8_caption_vi=:do8_caption_vi," +
" ftpserver =:ftpserver, ftpusername=:ftpusername," +
" ftppassword =:ftppassword, ftpfolder=:ftpfolder," +
" ftpflag =:ftpflag," +
" modified = :modified " +
" where id = :id";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":socket_port", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.socket_port;
cmd.Parameters.Add(":station_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.station_name;
cmd.Parameters.Add(":station_id", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.station_id;
cmd.Parameters.Add(":modified", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.modified;
cmd.Parameters.Add(":sampler_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.sampler_comport;
cmd.Parameters.Add(":tn_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.tn_comport;
cmd.Parameters.Add(":tp_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.tp_comport;
cmd.Parameters.Add(":toc_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.toc_comport;
cmd.Parameters.Add(":mps_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.mps_comport;
cmd.Parameters.Add(":module_comport", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.module_comport;
cmd.Parameters.Add(":mps_protocol", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.mps_protocol;
cmd.Parameters.Add(":tn_protocol", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.tn_protocol;
cmd.Parameters.Add(":tp_protocol", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.tp_protocol;
cmd.Parameters.Add(":toc_protocol", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.toc_protocol;
cmd.Parameters.Add(":do1_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do1_caption;
cmd.Parameters.Add(":do2_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do2_caption;
cmd.Parameters.Add(":do3_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do3_caption;
cmd.Parameters.Add(":do4_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do4_caption;
cmd.Parameters.Add(":do5_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do5_caption;
cmd.Parameters.Add(":do6_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do6_caption;
cmd.Parameters.Add(":do7_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do7_caption;
cmd.Parameters.Add(":do8_caption", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do8_caption;
cmd.Parameters.Add(":do1_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do1_caption_vi;
cmd.Parameters.Add(":do2_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do2_caption_vi;
cmd.Parameters.Add(":do3_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do3_caption_vi;
cmd.Parameters.Add(":do4_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do4_caption_vi;
cmd.Parameters.Add(":do5_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do5_caption_vi;
cmd.Parameters.Add(":do6_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do6_caption_vi;
cmd.Parameters.Add(":do7_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do7_caption_vi;
cmd.Parameters.Add(":do8_caption_vi", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.do8_caption_vi;
cmd.Parameters.Add(":ftpserver", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpserver;
cmd.Parameters.Add(":ftpusername", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpusername;
cmd.Parameters.Add(":ftppassword", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftppassword;
cmd.Parameters.Add(":ftpfolder", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpfolder;
cmd.Parameters.Add(":ftpflag", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.ftpflag;
cmd.Parameters.Add(":id", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.id;
cmd.ExecuteNonQuery();
db.close_connection();
return(obj.id);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
}
}
/// <summary>
/// add new
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public int add(ref station obj)
{
using (NpgsqlDBConnection db = new NpgsqlDBConnection())
{
try
{
int ID = -1;
if (db.open_connection())
{
string sql_command = "INSERT INTO stations (station_name, " +
" station_id,socket_port," +
" ftpserver, ftpusername, " +
" ftppassword, ftpfolder, " +
" ftpflag, " +
" modified)" +
" VALUES (:station_name, " +
" :station_id, :socket_port," +
" :ftpserver, :ftpusername, " +
" :ftppassword, :ftpfolder, " +
" :ftpflag, " +
" :modified)";
sql_command += " RETURNING id;";
using (NpgsqlCommand cmd = db._conn.CreateCommand())
{
cmd.CommandText = sql_command;
cmd.Parameters.Add(":socket_port", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.socket_port;
cmd.Parameters.Add(":station_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.station_name;
cmd.Parameters.Add(":station_id", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.station_id;
cmd.Parameters.Add(":modified", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = obj.modified;
cmd.Parameters.Add(":ftpserver", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpserver;
cmd.Parameters.Add(":ftpusername", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpusername;
cmd.Parameters.Add(":ftppassword", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftppassword;
cmd.Parameters.Add(":ftpfolder", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.ftpfolder;
cmd.Parameters.Add(":ftpflag", NpgsqlTypes.NpgsqlDbType.Integer).Value = obj.ftpflag;
ID = Convert.ToInt32(cmd.ExecuteScalar());
obj.id = ID;
db.close_connection();
return(ID);
}
}
else
{
db.close_connection();
return(-1);
}
}
catch
{
if (db != null)
{
db.close_connection();
}
return(-1);
}
finally
{ db.close_connection(); }
}
}