private module_device _get_info(NpgsqlDataReader dataReader) { module_device obj = new module_device(); try { if (!DBNull.Value.Equals(dataReader["id"])) { obj.id = Convert.ToInt32(dataReader["id"].ToString().Trim()); } else { obj.id = 0; } if (!DBNull.Value.Equals(dataReader["station_id"])) { obj.station_id = Convert.ToInt32(dataReader["station_id"].ToString().Trim()); } else { obj.station_id = 0; } if (!DBNull.Value.Equals(dataReader["module_name"])) { obj.module_name = dataReader["module_name"].ToString().Trim(); } else { obj.module_name = ""; } if (!DBNull.Value.Equals(dataReader["module_code"])) { obj.module_code = dataReader["module_code"].ToString().Trim(); } else { obj.module_code = ""; } if (!DBNull.Value.Equals(dataReader["created"])) { obj.created = Convert.ToDateTime(dataReader["created"].ToString().Trim()); } else { obj.created = DateTime.Now; } } catch (Exception ex) { throw ex; } return(obj); }
/// <summary> /// add new /// </summary> /// <param name="obj"></param> /// <returns></returns> public int add(ref module_device obj) { using (NpgsqlDBConnection db = new NpgsqlDBConnection()) { try { int ID = -1; if (db.open_connection()) { string sql_command = "INSERT INTO module_devices (station_id, module_name, " + " module_code, created)" + " VALUES (:station_id, :module_name, " + " :module_code, :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(":module_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.module_name; cmd.Parameters.Add(":module_code", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.module_code; 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 module_device obj) { using (NpgsqlDBConnection db = new NpgsqlDBConnection()) { try { if (db.open_connection()) { string sql_command = "UPDATE module_devices set " + " station_id = :station_id, module_name =:module_name, " + " module_code =:module_code, " + " 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(":module_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.module_name; cmd.Parameters.Add(":module_code", NpgsqlTypes.NpgsqlDbType.Varchar).Value = obj.module_code; 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> /// get info by id /// </summary> /// <param name="id"></param> /// <returns></returns> public module_device get_info_by_id(int id) { using (NpgsqlDBConnection db = new NpgsqlDBConnection()) { try { module_device obj = null; if (db.open_connection()) { string sql_command = "SELECT * FROM module_devices 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_device(); obj = (module_device)_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 all /// </summary> /// <returns></returns> public IEnumerable <module_device> get_all() { List <module_device> listUser = new List <module_device>(); using (NpgsqlDBConnection db = new NpgsqlDBConnection()) { try { if (db.open_connection()) { string sql_command = "SELECT * FROM module_devices"; using (NpgsqlCommand cmd = db._conn.CreateCommand()) { cmd.CommandText = sql_command; NpgsqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { module_device obj = new module_device(); obj = (module_device)_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(); } } }