/// <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(); } } }