C# OracleDataReader.GetDateTime方法代码示例

        internal override DtoBase PopulateDto(OracleDataReader reader)
        {
            var turno = new TurneroDto();
            //
            if (!reader.IsDBNull(_ordTurId)) { turno.TurId = reader.GetInt32(_ordTurId); }
            //
            if (!reader.IsDBNull(_ordTurTitulo)) { turno.TurTitulo = reader.GetString(_ordTurTitulo); }
            //
            if (!reader.IsDBNull(_ordTurDescripcion)) { turno.TurDescripcion = reader.GetString(_ordTurDescripcion); }
            //
            if (!reader.IsDBNull(_ordTurFechaIni)) { turno.TurFechaIni = reader.GetDateTime(_ordTurFechaIni); }
            //
            if (!reader.IsDBNull(_ordTurFechaFin)) { turno.TurFechaFin = reader.GetDateTime(_ordTurFechaFin); }
            //
            if (!reader.IsDBNull(_ordTurTodoDia)) { turno.TurTodoDia = reader.GetString(_ordTurTodoDia); }
            //
            if (!reader.IsDBNull(_ordTurPae)) { turno.TurPae = reader.GetInt32(_ordTurPae); }
            //
            if (!reader.IsDBNull(_ordTurPro)) { turno.TurPro= reader.GetInt32(_ordTurPro); }
            //
            if (!reader.IsDBNull(_ordTurMonto)) { turno.TurMonto = reader.GetDecimal(_ordTurMonto); }
            //
            if (!reader.IsDBNull(_ordTurOspId)) { turno.TurOspId = reader.GetInt32(_ordTurOspId); }
            // IsNew
            turno.IsNew = false;

            return turno;
        }

 /// Safely reads datetime values from the database if they are null
 ///<returns>datetime minimimvalue if null otherwise value from DB</returns>
 DateTime SafeReadDateTime(OracleDataReader odr, int colindex)
 {
     if (!odr.IsDBNull(colindex))
         return odr.GetDateTime(colindex);
     else
         return DateTime.MinValue;
 }

        internal override DtoBase PopulateDto(OracleDataReader reader)
        {
            var agenda = new AgendaDto();
            //
            if (!reader.IsDBNull(_ordAgeId)) { agenda.AgeId = reader.GetInt32(_ordAgeId); }
            //
            if (!reader.IsDBNull(_ordAgeHoraDesde)) { agenda.AgeHoraDesde = reader.GetDateTime(_ordAgeHoraDesde); }
            //
            if (!reader.IsDBNull(_ordAgeHoraHasta)) { agenda.AgeHoraHasta = reader.GetDateTime(_ordAgeHoraHasta); }
            //
            if (!reader.IsDBNull(_ordAgeProId)) { agenda.AgeProId = reader.GetInt32(_ordAgeProId); }
            //
            if (!reader.IsDBNull(_ordAgeDiaId)) { agenda.AgeDiaId = reader.GetInt32(_ordAgeDiaId); }
            // IsNew
            agenda.IsNew = false;

            return agenda;
        }

        internal override DtoBase PopulateDto(OracleDataReader reader)
        {
            var lic = new LicenciasDto();
            //
            if (!reader.IsDBNull(_ordLicId)) { lic.LicId = reader.GetInt32(_ordLicId); }
            //
            if (!reader.IsDBNull(_ordLicFechaDesde)) { lic.LicFechaDesde = reader.GetDateTime(_ordLicFechaDesde); }
            //
            if (!reader.IsDBNull(_ordLicFechaHasta)) { lic.LicFechaHasta = reader.GetDateTime(_ordLicFechaHasta); }
            //
            if (!reader.IsDBNull(_ordLicLctId)) { lic.LicLctId = reader.GetInt32(_ordLicLctId); }
            //
            if (!reader.IsDBNull(_ordLicProId)) { lic.LicProId = reader.GetInt32(_ordLicProId); }
            // IsNew
            lic.IsNew = false;

            return lic;
        }

        internal override DtoBase PopulateDto(OracleDataReader reader)
        {
            var historiaClinica = new HistoriaClinicaDto();
            //
            if (!reader.IsDBNull(_ordHcaId)) { historiaClinica.HcaId = reader.GetInt32(_ordHcaId); }
            //
            if (!reader.IsDBNull(_ordHcaNroSesiones)) { historiaClinica.HcaNroSesiones = reader.GetInt32(_ordHcaNroSesiones); }
            //
            if (!reader.IsDBNull(_ordHcaFecha)) { historiaClinica.HcaFecha = reader.GetDateTime(_ordHcaFecha); }
            //
            if (!reader.IsDBNull(_ordHcaAfnId)) { historiaClinica.HcaAfnId = reader.GetInt32(_ordHcaAfnId); }
            //
            if (!reader.IsDBNull(_ordHcaPaeId)) { historiaClinica.HcaPaeId = reader.GetInt32(_ordHcaPaeId); }
            //
            if (!reader.IsDBNull(_ordHcaCantEvaluaciones)) { historiaClinica.HcaCantEvaluaciones = reader.GetInt32(_ordHcaCantEvaluaciones); }
            // IsNew
            historiaClinica.IsNew = false;

            return historiaClinica;
        }

 internal static DateTime? GetDateTimeNullable(OracleDataReader reader, int index)
 {
     if (reader.IsDBNull(index)) { return null; }
     return (DateTime)reader.GetDateTime(index);
 }

        public static MessageInfo LoadFromDB(OracleDataReader reader)
        {
            var msg = new MessageInfo();

            //npu, correlationNpu, businessid, creationdate, processid, interfacetoinvoke, operation, payload

            msg.NPU = reader.GetString(0);
            msg.CorrelationNPU = reader.IsDBNull(1) ? null : reader.GetString(1);
            msg.BusinessId = reader.IsDBNull(2) ? null : reader.GetString(2);
            msg.ReceivedDatetime = reader.IsDBNull(3) ? DateTime.Now : reader.GetDateTime(3);
            msg.ProcessId = reader.IsDBNull(4) ? null : reader.GetString(4);
            msg.InterfaceToInvoke = reader.IsDBNull(5) ? null : reader.GetString(5);

            var oper = msg.NPU.Substring(4, 10);
            msg.Operation = oper;

            if (!string.IsNullOrEmpty(msg.CorrelationNPU))
                oper = msg.CorrelationNPU.Substring(4, 10);

            msg.Name = GetPrettyName(oper, null);

            var blob = reader.GetOracleBlob(7);

            var sb = new StringBuilder();

            var buffer = new byte[1000];

            while ((blob.Read(buffer, 0, buffer.Length)) > 0)
            {

                sb.Append(Encoding.UTF8.GetString(buffer));
                Array.Clear(buffer, 0, buffer.Length);

            }

            msg.Payload = sb.ToString().Trim('\0');

            return msg;
        }

        public static User GetUser(int ids)
        {
            User toadd = null;
            try
            {
                Connect();
                cmd = new OracleCommand();
                cmd.Connection = con;
                cmd.CommandText = "SELECT USERID, NAAM, GEBOORTEDATUM, GESLACHT, WOONPLAATS, ADRES, EMAIL, WACHTWOORD, TYPE FROM TUSER WHERE USERID = " + ids; // QUERY
                cmd.CommandType = CommandType.Text;
                dr = cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                Disconnect();
                return null;
            }

            try
            {
                while (dr.Read())
                {
                    // Read from DB
                    var id = dr.GetInt32(0);
                    var name = dr.GetString(1);
                    var dateOfBirth = dr.GetDateTime(2);
                    var gender = dr.GetString(3);
                    var city = dr.GetString(4);
                    var adress = dr.GetString(5);
                    var email = dr.GetString(6);
                    var password = dr.GetString(7);

                    var type = dr.GetString(8);

                    switch (type)
                    {
                        case "CLIENT":
                            Client newClient = new Client(name, dateOfBirth, gender, city, adress, email, password);
                            toadd = newClient;
                            toadd.UserID = id;
                            break;
                        case "VOLUNTEER":
                            toadd = null;
                            Volunteer newUser = new Volunteer(name, dateOfBirth, gender, city, adress, email, password, false, "Niet Opgegeven", "ONBEKEND", "ONBEKEND");
                            toadd = newUser;
                            toadd.UserID = id;
                            break;
                        case "ADMIN":
                            Admin newAdmin = new Admin(name, dateOfBirth, gender, city, adress, email, password);
                            toadd = newAdmin;
                            toadd.UserID = id;
                            break;
                        default:
                            toadd = null;
                            break;
                    }

                }
                Disconnect();
                return toadd;
            }
            catch (InvalidCastException ex)
            {
                Disconnect();
                MessageBox.Show(ex.ToString());
                return null;
            }
        }

 internal static DateTime GetDateTime(OracleDataReader reader, int index, DateTime defaultTime)
 {
     if (reader.IsDBNull(index)) { return defaultTime; }
     return (DateTime)reader.GetDateTime(index);
 }

        public static List<Review> GetMyReviews(Volunteer volun)
        {
            List<Review> returnlist = new List<Review>();
            try
            {
                Connect();
                cmd = new OracleCommand();
                cmd.Connection = con;
                cmd.CommandText = "SELECT CLIENT, DATUM, RATING, TEKST FROM TREVIEW WHERE VOLUNTEER = :newUSERID";
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("newUSERID", OracleDbType.Varchar2).Value = volun.UserID;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var clientid = dr.GetInt32(0);
                    var datetime = dr.GetDateTime(1);
                    var rating = dr.GetInt32(2);
                    var content = dr.GetString(3);

                    returnlist.Add(new Review(datetime, GetUserNoConnect(clientid) as Client, volun , rating, content));
                }
                return returnlist;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return returnlist;
            }
            finally
            {
                Disconnect();
            }
        }

        public static List<Message> GetNewMessages(int chatid, int berichtid)
        {
            List<Message> chatmessages = new List<Message>();
            try
            {
                Connect();
                cmd = new OracleCommand();
                cmd.Connection = con;
                cmd.CommandText = "SELECT GESPREKID, BERICHTID, AFZENDER, DATUM, BERICHT FROM TCHATBERICHT WHERE GESPREKID = " + chatid + " AND BERICHTID > " + berichtid;
                cmd.CommandType = CommandType.Text;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var id = dr.GetInt32(0);
                    var messageid = dr.GetInt32(1);
                    var authorid = dr.GetInt32(2);
                    var date = dr.GetDateTime(3);
                    var content = dr.GetString(4);

                    chatmessages.Add(new Message(id, messageid, authorid, date, content));
                }

                foreach (Message m in chatmessages)
                {
                    m.Author = GetUser(m.AuthorID);
                }

                return chatmessages;

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        public static Regisseur GetRegisseur(int id)
        {
            string naam = string.Empty;
            DateTime dob = new DateTime(1, 1, 1);
            string woonplek = string.Empty;
            int regisseurid = 0;
            List<Film> films = new List<Film>();
            List<Prijs> prijzen = new List<Prijs>();
            using (connection = new OracleConnection(connectionstring))
            {
                query = "SELECT r.naam, r.dob, r.woonplek, r.regisseurid FROM regisseur r WHERE r.regisseurid = :pid";
                cmd.CommandText = query;
                cmd.Connection = connection;

                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("id", id);
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        naam = reader.GetString(0);
                        dob = reader.GetDateTime(1);
                        woonplek = reader.GetString(2);
                        regisseurid = reader.GetInt32(3);
                    }
                }
                catch (Exception ex)
                {
                    cmd.Dispose();
                    connection.Dispose();
                    reader.Close();
                }
            }

            using (connection = new OracleConnection(connectionstring))
            {
                query = "SELECT p.titel, p.jaar, p.prizeid FROM prijs p, regisseurprijsregel rpr WHERE p.prizeid = rpr.prizeid AND rpr.regisseurid = :pid";
                cmd.CommandText = query;
                cmd.Connection = connection;

                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("id", id);
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        prijzen.Add(new Prijs(reader.GetString(0), reader.GetInt32(1), reader.GetInt32(2)));
                    }
                }
                catch (Exception ex)
                {
                    cmd.Dispose();
                    connection.Dispose();
                    reader.Close();
                }
            }

            using (connection = new OracleConnection(connectionstring))
            {
                query = "SELECT f.filmid, f.titel FROM film f, regisseur r WHERE r.regisseurid = :pid AND f.regisseurid = :pid";
                cmd.CommandText = query;
                cmd.Connection = connection;

                try
                {
                    connection.Open();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("id", id);
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        films.Add(new Film(reader.GetInt32(0).ToString(), reader.GetString(1)));
                    }
                }
                catch (Exception ex)
                {
                    cmd.Dispose();
                    connection.Dispose();
                    reader.Close();
                }
            }
            return new Regisseur(naam, dob, woonplek, prijzen, films, regisseurid);
        }

        public static List<Review> GetAllReviews()
        {
            List<Review> returnlist = new List<Review>();
            try
            {
                Connect();
                cmd = new OracleCommand();
                cmd.Connection = con;
                cmd.CommandText = "SELECT R.REVIEWID, R.RATING, R.TEKST, R.DATUM, C.USERID, C.NAAM, C.GEBOORTEDATUM, C.GESLACHT, C.WOONPLAATS, C.ADRES, C.EMAIL, C.WACHTWOORD, C.TYPE, "
               + "V.USERID, V.NAAM, V.GEBOORTEDATUM, V.GESLACHT, V.WOONPLAATS, V.ADRES, V.EMAIL, V.WACHTWOORD, V.TYPE FROM TUSER V, TUSER C, TREVIEW R WHERE V.USERID = R.VOLUNTEER AND C.USERID = R.CLIENT";
                cmd.CommandType = CommandType.Text;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var id = dr.GetInt32(0);
                    var rating = dr.GetInt32(1);
                    var content = dr.GetString(2);
                    var date = dr.GetDateTime(3);

                    var clientid = dr.GetInt32(4);
                    var clientname = dr.GetString(5);
                    var clientdob = dr.GetDateTime(6);
                    var clientgender = dr.GetString(7);
                    var clientcity = dr.GetString(8);
                    var clientaddress = dr.GetString(9);
                    var clientemail = dr.GetString(10);
                    var clientpassword = dr.GetString(11);
                    var clientype = dr.GetString(12);

                    var volunid = dr.GetInt32(13);
                    var volunname = dr.GetString(14);
                    var volundob = dr.GetDateTime(15);
                    var volungender = dr.GetString(16);
                    var voluncity = dr.GetString(17);
                    var volunaddress = dr.GetString(18);
                    var volunemail = dr.GetString(19);
                    var volunpassword = dr.GetString(20);
                    var voluntype = dr.GetString(21);

                    Client someclient = new Client(clientname, clientdob, clientgender, clientcity, clientaddress, clientemail, clientpassword);
                    someclient.UserID = clientid;
                    Volunteer somevolun = new Volunteer(volunname, volundob, volungender, voluncity, volunaddress, volunemail, volunpassword, false, "", "", "");
                    somevolun.UserID = volunid;

                    returnlist.Add(new Review(date, someclient, somevolun, rating, content));

                    returnlist[returnlist.Count - 1].ReviewID = id;
                }

                return returnlist;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                Disconnect();
            }
        }

        // deze methode gaat alle users uit de database opvragen.
        public List<Users> GetAllUsers()
        {

            List<Users> Userlist = new List<Users>();
            try
            {
                Connect();
                cmd.Connection = con;
                cmd.CommandText = "SELECT UserID, Voornaam, Achternaam, Geboortedatum, Geslacht, Straat, Huisnummer, Postcode, Plaats,Telefoonnummer, Email, Abbonement FROM GEBRUIKER";
                cmd.CommandType = System.Data.CommandType.Text;
                dr = cmd.ExecuteReader();
                dr.Read();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return null;
            }
            finally
            {
                Disconnect();
            }
            try
            {
                while (dr.Read())
                {
                    var id = dr.GetInt32(0);
                    var voornaam = SafeReadString(dr, 1);
                    var achternaam = SafeReadString(dr, 2);
                    var wachtwoord = SafeReadString(dr, 3);
                    var geboortedatum = dr.GetDateTime(4);
                    var geslacht = SafeReadString(dr, 5);
                    var straat = SafeReadString(dr, 7);
                    string huisnummer = SafeReadString(dr, 8);
                    var postcode = SafeReadString(dr, 9);
                    var plaats = SafeReadString(dr, 10);
                    var telefoonnummer = SafeReadString(dr, 11);
                    var email = SafeReadString(dr, 12);
                    var abbonement = SafeReadString(dr, 13);

                    Users user = new Users(id, voornaam, achternaam, wachtwoord, geboortedatum, geslacht, straat, huisnummer, postcode, plaats, telefoonnummer, email, abbonement);
                    Userlist.Add(user);
                }
                Disconnect();
                return Userlist;

            }
            catch
            {
                return null;
            }

        }

        public List<Chat> GetChat(Client client, Volunteer volunteer)
        {
            List<Chat> chatmessages = new List<Chat>();
            try
            {
                Connect();
                cmd = new OracleCommand();
                cmd.Connection = con;
                cmd.CommandText = "SELECT * FROM(SELECT BERICHT,tijdstip,VANHULPBEHOEVENDE FROM TCHAT WHERE HULPBEHOEVENDEID = :clientid AND VRIJWILLIGERID = :volunteerid ORDER BY TIJDSTIP DESC) WHERE ROWNUM <= 10 ORDER BY ROWNUM DESC";
                cmd.Parameters.Add("clientid", client.ClientID);
                cmd.Parameters.Add("volunteerid", volunteer.VolunteerID);

               cmd.CommandType = CommandType.Text;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var bericht = dr.GetString(0);
                    var tijdstip = dr.GetDateTime(1);
                    var sender = dr.GetInt32(2);
                    chatmessages.Add(new Chat(bericht, tijdstip, sender));
                }

                return chatmessages;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
            finally
            {
                Disconnect();
            }
        }