public TruckInDepo FillByNomobilNotOut(string nomobil) { TruckInDepo truckInDepo = new TruckInDepo(); try { using (NpgsqlConnection npgsqlConnection = AppConfig.GetConnection()) { if (npgsqlConnection.State == ConnectionState.Closed) { npgsqlConnection.Open(); } string query = "SELECT truckindepoid,nomobil,dtmin,dtmout,remark, CURRENT_TIMESTAMP AS serverdtm, refno, muatan, tipe, angkutan, note FROM truckindepo WHERE dtmout IS NULL AND (nomobil=@nomobil1 OR nomobil=@nomobil2) ORDER BY truckindepoid "; using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(query, npgsqlConnection)) { npgsqlCommand.Parameters.AddWithValue("@nomobil1", nomobil); npgsqlCommand.Parameters.AddWithValue("@nomobil2", nomobil); using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader()) { if (npgsqlDataReader.Read()) { MappingDataReaderToTruckInDepo(npgsqlDataReader, truckInDepo); } } } } } catch (Exception ex) { throw ex; } return(truckInDepo); }
/// <summary> /// Insert Truck In Depo /// </summary> /// <param name="truckInDepo"></param> /// <returns></returns> public int InsertTruckInDepo(TruckInDepo truckInDepo) { int affectedRow = 0; try { using (NpgsqlConnection npgsqlConnection = AppConfig.GetUserConnection()) { if (npgsqlConnection.State == ConnectionState.Closed) { npgsqlConnection.Open(); } string query = string.Format("INSERT INTO {1}({0}) " + " VALUES(nextval('truckindepo_truckindepoid_seq'), @NoMobil, @DtmIn, @DtmOut, @Remark, @RefNo, " + " @Muatan, @Tipe, @Angkutan, @Note)", DEFAULT_COLUMN, DEFAULT_TABLE); using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(query, npgsqlConnection)) { npgsqlCommand.Parameters.AddWithValue("@NoMobil", truckInDepo.NoMobil); npgsqlCommand.Parameters.AddWithValue("@DtmIn", truckInDepo.DtmIn); if (truckInDepo.DtmOut.HasValue) { npgsqlCommand.Parameters.AddWithValue("@DtmOut", truckInDepo.DtmOut); } else { npgsqlCommand.Parameters.AddWithValue("@DtmOut", DBNull.Value); } npgsqlCommand.Parameters.AddWithValue("@Remark", truckInDepo.Remark); npgsqlCommand.Parameters.AddWithValue("@RefNo", truckInDepo.RefNo); npgsqlCommand.Parameters.AddWithValue("@Muatan", truckInDepo.Muatan); npgsqlCommand.Parameters.AddWithValue("@Tipe", truckInDepo.Tipe); npgsqlCommand.Parameters.AddWithValue("@Angkutan", truckInDepo.Angkutan); npgsqlCommand.Parameters.AddWithValue("@Note", truckInDepo.Note); affectedRow = npgsqlCommand.ExecuteNonQuery(); } } } catch (Exception ex) { throw ex; } return(affectedRow); }
private void MappingDataReaderToTruckInDepo(NpgsqlDataReader npgsqlDataReader, TruckInDepo truckInDepo) { truckInDepo.TruckInDepoId = npgsqlDataReader.GetInt64(0); truckInDepo.NoMobil = npgsqlDataReader.GetString(1); truckInDepo.DtmIn = npgsqlDataReader.GetDateTime(2); truckInDepo.DtmOut = npgsqlDataReader.GetDateTime(3); truckInDepo.Remark = npgsqlDataReader.GetString(4); truckInDepo.ServerDTM = npgsqlDataReader.GetDateTime(5); if (truckInDepo.DtmOut.ToString().Length == 0) { truckInDepo.DurasiMenit = (int)truckInDepo.ServerDTM.Value.Minute; } else { truckInDepo.DurasiMenit = (int)truckInDepo.DtmOut.Value.Minute; } truckInDepo.RefNo = npgsqlDataReader.GetString(6); truckInDepo.Muatan = npgsqlDataReader.GetString(7); truckInDepo.Tipe = npgsqlDataReader.GetString(8); truckInDepo.Angkutan = npgsqlDataReader.GetString(9); truckInDepo.Note = npgsqlDataReader.GetString(10); }