Пример #1
0
        /// <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);
                }
            }
        }
Пример #2
0
        /// <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);
                }
            }
        }
Пример #3
0
        /// <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(); }
            }
        }
Пример #4
0
        /// <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);
                }
            }
        }
Пример #5
0
        /// <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(); }
            }
        }
Пример #6
0
        /// <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);
                }
            }
        }
Пример #7
0
        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(); }
            }
        }
Пример #8
0
        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);
                }
            }
        }
Пример #9
0
        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(); }
            }
        }
Пример #10
0
        /// <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(); }
            }
        }
Пример #11
0
        /// <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);
                }
            }
        }
Пример #12
0
        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(); }
            }
        }
Пример #15
0
        /// <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(); }
            }
        }
Пример #16
0
        /// <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(); }
            }
        }
Пример #17
0
        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");
                }
            }
        }
Пример #18
0
        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(); }
            }
        }
Пример #19
0
        /// <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(); }
            }
        }
Пример #20
0
        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(); }
            }
        }
Пример #21
0
        /// <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(); }
            }
        }
Пример #22
0
        /// <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(); }
            }
        }
Пример #23
0
        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(); }
            }
        }
Пример #24
0
 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(); }
     }
 }
Пример #25
0
 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);
         }
     }
 }
Пример #26
0
        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();
                }
            }
        }
Пример #27
0
        /// <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(); }
            }
        }
Пример #28
0
        /// <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(); }
            }
        }
Пример #29
0
        /// <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);
                }
            }
        }
Пример #30
0
        /// <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(); }
            }
        }