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; }
public List <VBrigadeEndedTasks> SelectAll() { var list = new List <VBrigadeEndedTasks>(); using (OracleConnection connection = new OracleConnection(OracleWcfService.CONNECTION_STRING)) { connection.Open(); OracleCommand oraCommand = new OracleCommand("SELECT * FROM BRIGADE_ENDED_TASKS WHERE change_date between add_months(trunc(sysdate,'mm'),-1) and last_day(add_months(trunc(sysdate,'mm'),0))", connection); OracleDataReader oraReader = oraCommand.ExecuteReader(); if (oraReader.HasRows) { while (oraReader.Read()) { var temp = new VBrigadeEndedTasks(); temp.task_id = oraReader.GetInt32(0); temp.request_name = oraReader.GetString(1); temp.request_description = oraReader.GetString(2); temp.task_note = oraReader.GetString(3); temp.change_date = oraReader.GetDateTime(4); temp.brigade_id = oraReader.GetInt32(5); temp.total_time = oraReader.GetInt32(6); list.Add(temp); } } } return(list); }
//return the maximum date for teams fixture public String getMaxFixtureDate(int t1Id, int t2Id) { //variable String maxDate; //Connect to db OracleConnection conn = new OracleConnection(DBConnect.oradb); //Define SQL Query String strSQL = "select max(fixturedate) from fixtures where team1id in (" + t1Id + "," + t2Id + ") or team2id in (" + t1Id + "," + t2Id + ")"; //Define Oracle command OracleCommand cmd = new OracleCommand(strSQL, conn); conn.Open(); OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); try { maxDate = dr.GetDateTime(0).ToString("dd-MMM-yy"); } catch { maxDate = null; } conn.Close(); return(maxDate); }
private static List <Game> Read(OracleDataReader reader) { List <Game> games = new List <Game>(); while (reader.Read()) { int i = -1; Game game = new Game(); game.Game_id = reader.GetInt32(++i); game.Name = reader.GetString(++i); game.Description = reader.GetString(++i); game.Developer = reader.GetString(++i); game.Rating = reader.GetString(++i); if (!reader.IsDBNull(++i)) { game.Release_date = reader.GetDateTime(i); } if (!reader.IsDBNull(++i)) { game.Average_user_review = reader.GetInt32(i); } if (!reader.IsDBNull(++i)) { game.Average_reviewer_score = reader.GetInt32(i); } games.Add(game); } return(games); }
/// 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; }
public IEnumerable <TaskStatusChange> SelectNewTasks() //новые задачи { List <TaskStatusChange> requests = new List <TaskStatusChange>(); using (OracleConnection connection = new OracleConnection(OracleWcfService.CONNECTION_STRING)) { connection.Open(); OracleCommand oraCommand = new OracleCommand("SELECT t.TASK_ID, tsc.TASK_STATUS_ID_ACTUAL, tsc.TASK_STATUS_ID_OLD, tsc.CHANGE_DATE " + "FROM system.TASK t join system.TASK_STATUS_CHANGE tsc on t.TASK_ID = tsc.TASK_ID " + "WHERE t.TASK_ID IN(SELECT tsc.TASK_ID from system.TASK_STATUS_CHANGE tsc " + "GROUP BY tsc.TASK_ID HAVING MAX(tsc.TASK_STATUS_ID_ACTUAL) =1) ", connection); OracleDataReader oraReader = oraCommand.ExecuteReader(); if (oraReader.HasRows) { while (oraReader.Read()) { var temp = new TaskStatusChange(); temp.id = oraReader.GetInt32(0); temp.new_status = oraReader.GetValue(1) == DBNull.Value ? temp.new_status = null : (int)oraReader.GetInt32(1); temp.old_status = oraReader.GetValue(2) == DBNull.Value ? temp.old_status = null : (int)oraReader.GetInt32(2); temp.change_date = oraReader.GetDateTime(3); requests.Add(temp); } } } return(requests); }
public IEnumerable <TaskStatusChange> Select(List <int> ids) { var list = new List <TaskStatusChange>(); foreach (var item in ids) { int id = item; using (OracleConnection connection = new OracleConnection(OracleWcfService.CONNECTION_STRING)) { connection.Open(); OracleCommand oraCommand = new OracleCommand("SELECT * " + "FROM system.TASK_STATUS_CHANGE WHERE TASK_ID = :id", connection); oraCommand.Parameters.Add("id", id); OracleDataReader oraReader = oraCommand.ExecuteReader(); if (oraReader.HasRows) { while (oraReader.Read()) { var temp = new TaskStatusChange(); temp.id = oraReader.GetInt32(0); temp.old_status = oraReader.GetValue(1) == DBNull.Value ? temp.old_status = null : (int)oraReader.GetInt32(1); temp.new_status = oraReader.GetValue(2) == DBNull.Value ? temp.new_status = null : (int)oraReader.GetInt32(2); temp.task_id = oraReader.GetInt32(3); temp.change_date = oraReader.GetDateTime(4); list.Add(temp); } } } } return(list); }
private Client DataReaderMapToClient(OracleDataReader oracleDataReader) { if (!oracleDataReader.HasRows) { return(null); } Client client = new Client(); client.FirstName = oracleDataReader.GetString(0); client.SecondName = oracleDataReader.GetString(1); client.LastName = oracleDataReader.GetString(2); client.SecondLastName = oracleDataReader.GetString(3); client.Cedula = oracleDataReader.GetString(4); client.Sex = oracleDataReader.GetString(5); client.DateOfBorn = oracleDataReader.GetDateTime(6); client.Age = (int)oracleDataReader.GetInt32(7); client.Telephone = oracleDataReader.GetString(8); client.E_mail = oracleDataReader.GetString(9); client.AddressHome = oracleDataReader.GetString(10); client.Neighborhood = oracleDataReader.GetString(11); client.City = oracleDataReader.GetString(12); return(client); }
public string readCellFromDb(OracleDataReader reader, int i) { string type = reader.GetFieldType(i).ToString(); switch (type) { case "System.String": return(reader.GetOracleString(i).IsNull ? "" : reader.GetString(i)); case "System.DateTime": { return(reader.GetOracleDate(i).IsNull ? "" : reader.GetDateTime(i).ToString()); } case "System.Character": { return(reader.GetChar(i).ToString()); } case "System.Decimal": { return(reader.GetOracleDecimal(i).IsNull ? "" : System.Convert.ToString(reader.GetDecimal(i))); } case "System.Int64": { return(System.Convert.ToString(reader.GetInt64(i))); } } return(""); }
//Gets a single Member Item from Members Table public void GetAMember(int memberID) { //Define an Oracle Connection OracleConnection conn = new OracleConnection(OracleDB.oradb); conn.Open(); //Define SQL Query String strSQL = "SELECT * FROM Members WHERE MemberID = " + memberID; //Oracle command OracleCommand cmd = new OracleCommand(strSQL, conn); //Execute the query using a DataReader OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); Member member = new Member(); //Instantiate object variables member.setMemberID(dr.GetInt32(0)); member.setName(dr.GetString(1)); member.setSurname(dr.GetString(2)); member.setDOB(dr.GetDateTime(3)); member.setEmail(dr.GetString(4)); member.setPhone(dr.GetInt32(5)); member.setMemStatus('A'); //Close Database connection conn.Close(); }
/// <summary> /// To Get CDAP Phase Details By ID /// </summary> /// <param name="PhaseID"></param> /// <returns></returns> public CDAPImplementationList GetCDAPPhaseDetailsByID(int PhaseID) { OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString); OracleCommand cmd; string proc = "USP_GET_TRN_CDAP_PHASESBYID"; cmd = new OracleCommand(proc, cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("CDAP_PHASEID_", PhaseID); cmd.Parameters.Add("Sp_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output; cmd.Connection.Open(); OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); CDAPImplementationBO obCDAPImplementationBO = null; CDAPImplementationList objCDAPImplementationList = new CDAPImplementationList(); while (dr.Read()) { obCDAPImplementationBO = new CDAPImplementationBO(); if (!dr.IsDBNull(dr.GetOrdinal("CDAP_PHASENO"))) { obCDAPImplementationBO.Cdap_phaseno = dr.GetInt32(dr.GetOrdinal("CDAP_PHASENO")); } if (!dr.IsDBNull(dr.GetOrdinal("CDAP_PHASEID"))) { obCDAPImplementationBO.Cdap_phaseid = dr.GetInt32(dr.GetOrdinal("CDAP_PHASEID")); } if (!dr.IsDBNull(dr.GetOrdinal("CDAP_PERIODFROM"))) { obCDAPImplementationBO.PeriodFrom = dr.GetDateTime(dr.GetOrdinal("CDAP_PERIODFROM")); } if (!dr.IsDBNull(dr.GetOrdinal("CDAP_PERIODTO"))) { obCDAPImplementationBO.PeriodTo = dr.GetDateTime(dr.GetOrdinal("CDAP_PERIODTO")); } if (!dr.IsDBNull(dr.GetOrdinal("PROJECTID"))) { obCDAPImplementationBO.ProjectedId = dr.GetInt32(dr.GetOrdinal("PROJECTID")); } if (!dr.IsDBNull(dr.GetOrdinal("EXPENDITURE"))) { obCDAPImplementationBO.EXPENDITURE = Convert.ToDecimal(dr.GetValue(dr.GetOrdinal("EXPENDITURE"))); } objCDAPImplementationList.Add(obCDAPImplementationBO); } dr.Close(); return(objCDAPImplementationList); }
/// <summary> /// To fetch details by ID /// </summary> /// <param name="EXPENSEID"></param> /// <returns></returns> public ExpenseBO GetExpenseByID(int EXPENSEID) { proc = "USP_TRN_GET_PROJ_EXPENSEBYID"; cnn = new OracleConnection(con); ExpenseBO ExpenseBOobj = null; cmd = new OracleCommand(proc, cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("EXPENSEID_", EXPENSEID); cmd.Parameters.Add("Sp_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output; try { cmd.Connection.Open(); OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { ExpenseBOobj = new ExpenseBO(); if (!dr.IsDBNull(dr.GetOrdinal("PROJECTEXPENSEID"))) { ExpenseBOobj.PROJECTEXPENSEID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("PROJECTEXPENSEID"))); } if (!dr.IsDBNull(dr.GetOrdinal("PROJECTID"))) { ExpenseBOobj.PROJECTID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("PROJECTID"))); } if (!dr.IsDBNull(dr.GetOrdinal("EXPENSETYPE"))) { ExpenseBOobj.EXPENSETYPE = dr.GetString(dr.GetOrdinal("EXPENSETYPE")); } if (!dr.IsDBNull(dr.GetOrdinal("ACCOUNTCODE"))) { ExpenseBOobj.ACCOUNTCODE = dr.GetString(dr.GetOrdinal("ACCOUNTCODE")); } if (!dr.IsDBNull(dr.GetOrdinal("EXPENSEAMOUNT"))) { ExpenseBOobj.EXPENSEAMOUNT = Convert.ToDecimal(dr.GetValue(dr.GetOrdinal("EXPENSEAMOUNT"))); } if (!dr.IsDBNull(dr.GetOrdinal("DATEOFEXPENSE"))) { ExpenseBOobj.DATEOFEXPENSE = dr.GetDateTime(dr.GetOrdinal("DATEOFEXPENSE")); } ExpenseBOobj.ISDELETED = dr.GetString(dr.GetOrdinal("ISDELETED")); } dr.Close(); } catch (Exception ex) { throw ex; } return(ExpenseBOobj); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Request.QueryString["id"] != null) { int.TryParse(MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()), out id); ReadSelectID(); } else { Response.Redirect("listproject-admin.aspx"); } using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("SELECT START_DATE,END_DATE FROM TB_PROJECT WHERE PRO_ID = '" + id + "'", con)) { using (OracleDataReader reader = com.ExecuteReader()) { while (reader.Read()) { int i = 0; string start = reader.GetDateTime(i).ToString("dd/MM/yyyy"); ++i; string end = reader.GetDateTime(i).ToString("dd/MM/yyyy"); ++i; if (!reader.IsDBNull(0) && !reader.IsDBNull(1)) { DateTime df = DateTime.Parse(start); DateTime dt = DateTime.Parse(end); int day = (int)(dt - df).TotalDays + 1; int year = (day / 365); int month = (day % 365) / 30; day = (day % 365) % 30; lbcalYear.Text = "" + year; lbcalMonth.Text = "" + month; lbcalDay.Text = "" + day; } } } } } } }
public DateTime?GetNullableDateTime(OracleDataReader reader, string name) { var col = reader.GetOrdinal(name); return(reader.IsDBNull(col) ? (DateTime?)null : (DateTime?)reader.GetDateTime(col)); }
public override DateTime GetDateTime(int ordinal) { //OracleDateTime dateTime = source.GetOracleDateTime(ordinal); //return dateTime.Value; var result = source.GetDateTime(ordinal); return(result); }
/// <summary> /// Laays danh sachs KH /// </summary> /// <returns></returns> public static List <QLyKHDTO> layDSKhachHang() { try { List <QLyKHDTO> list = new List <QLyKHDTO>(); OracleCommand oracleCommand = new OracleCommand(); oracleCommand.CommandText = "SELECT * FROM KHACH_HANG"; OracleDataReader oracleDataReader = DataProvider.GetOracleDataReader(oracleCommand); if (oracleDataReader != null && oracleDataReader.HasRows) { while (oracleDataReader.Read()) { QLyKHDTO khachHang = new QLyKHDTO(); khachHang.STKLK = oracleDataReader.GetString(0); khachHang.hoTenKH = oracleDataReader.GetString(1); khachHang.ngaySinhKH = oracleDataReader.GetDateTime(2); khachHang.soCMNNKH = oracleDataReader.GetString(3); khachHang.NgayCap = oracleDataReader.GetDateTime(4); khachHang.NoiCap = oracleDataReader.GetString(5); khachHang.gioiTinhKH = oracleDataReader.GetString(6); khachHang.diaChiKH = oracleDataReader.GetString(7); khachHang.ngayMoTKKH = oracleDataReader.GetDateTime(8); khachHang.SDTKH = oracleDataReader.GetString(9); khachHang.emailKH = oracleDataReader.GetString(10); khachHang.HanMucVay = oracleDataReader.GetInt32(11); khachHang.MaRo = oracleDataReader.GetString(12); khachHang.SoTienMat = oracleDataReader.GetInt64(13); khachHang.SoDuNo = oracleDataReader.GetInt64(14); list.Add(khachHang); } } oracleCommand.Connection.Dispose(); return(list); } catch (Exception e) { MessageBox.Show("Lỗi: " + e.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); return(null); } }
/// <summary> /// Lấy ra 1 khách hàng khi biết số TKLK /// </summary> /// <param name="soTKLK"></param> /// <returns></returns> public static QLyKHDTO layMotKhachHang(string soTKLK) { try { OracleCommand oracleCommand = new OracleCommand(); oracleCommand.CommandText = "SELECT * FROM KHACH_HANG WHERE SO_TKLK = :soTKLK"; oracleCommand.Parameters.Add(new OracleParameter("soTKLK", soTKLK)); OracleDataReader oracleDataReader = DataProvider.GetOracleDataReader(oracleCommand); if (oracleDataReader != null && oracleDataReader.HasRows) { oracleDataReader.Read(); QLyKHDTO khachHang = new QLyKHDTO(); khachHang.STKLK = oracleDataReader.GetString(0); khachHang.hoTenKH = oracleDataReader.GetString(1); khachHang.ngaySinhKH = oracleDataReader.GetDateTime(2); khachHang.soCMNNKH = oracleDataReader.GetString(3); khachHang.NgayCap = oracleDataReader.GetDateTime(4); khachHang.NoiCap = oracleDataReader.GetString(5); khachHang.gioiTinhKH = oracleDataReader.GetString(6); khachHang.diaChiKH = oracleDataReader.GetString(7); khachHang.ngayMoTKKH = oracleDataReader.GetDateTime(8); khachHang.SDTKH = oracleDataReader.GetString(9); khachHang.emailKH = oracleDataReader.GetString(10); khachHang.HanMucVay = oracleDataReader.GetInt32(11); khachHang.MaRo = oracleDataReader.GetString(12); khachHang.SoTienMat = oracleDataReader.GetInt64(13); khachHang.SoDuNo = oracleDataReader.GetInt64(14); oracleCommand.Connection.Dispose(); return(khachHang); } else { return(null); } } catch (Exception e) { MessageBox.Show("Lỗi: " + e.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error); return(null); } }
private Model.DeviceStatus GetDeviceStatusByOracleDataReader(OracleDataReader rdr) { string[] colsName; int[] colsOrdinal; const int colsCount = 5; Model.DeviceStatus deviceStatus; colsName = new string[colsCount] { "DBID", "DEVCODE", "LASTTIME", "STATUS", "SENSORTYPECODE" }; colsOrdinal = new int[colsCount]; if (GetColumnsOrdinal(rdr, colsName, colsOrdinal) < 0) { throw (new Exception("Error")); } deviceStatus = new Model.DeviceStatus(); for (int i = 0; i < colsCount; i++) { //if (colsOrdinal[i] < 0 || rdr.IsDBNull(colsOrdinal[i])) if (colsOrdinal[i] < 0) { continue; } switch (i) { case 0: deviceStatus.DBID = rdr.GetInt32(colsOrdinal[i]); break; case 1: deviceStatus.DEVCODE = rdr.GetString(colsOrdinal[i]); break; case 2: if (!rdr.IsDBNull(colsOrdinal[i])) { deviceStatus.LASTTIME = rdr.GetDateTime(colsOrdinal[i]); } break; case 3: deviceStatus.STATUS = false; break; case 4: deviceStatus.SensorTypeCode = rdr.GetString(colsOrdinal[i]); break; } } return(deviceStatus); }
public void OracleTypesInValueTest() { if (connection_string == null) { Assert.Ignore("Please consult README.tests."); } try { int test_int = 10; string test_string = "koza"; DateTime test_dateTime = DateTime.MinValue; using (command = connection.CreateCommand()) // reusing command from SetUp causes parameter names mismatch { // insert test values command.CommandText = "insert into oratypes_test (id,value1,value2)" + " values (:idx,:txtx,:datex)"; command.Parameters.Add( new OracleParameter("IDX", OracleType.Number)) .Direction = ParameterDirection.Input; command.Parameters.Add( new OracleParameter("TXTX", OracleType.VarChar)) .Direction = ParameterDirection.Input; command.Parameters.Add( new OracleParameter("DATEX", OracleType.DateTime)) .Direction = ParameterDirection.Input; command.Parameters ["IDX"].Value = new OracleNumber(test_int); command.Parameters ["TXTX"].Value = new OracleString(test_string); command.Parameters ["DATEX"].Value = new OracleDateTime(test_dateTime); command.ExecuteNonQuery(); // read test values command.CommandText = "select value1,value2 from oratypes_test where id = " + test_int; command.Parameters.Clear(); using (OracleDataReader reader = command.ExecuteReader()) { if (reader.Read()) { Assert.AreEqual(test_string, reader.GetString(0), "OracleString mismatched"); Assert.AreEqual(test_dateTime, reader.GetDateTime(1), "OracleDateTime mismatched"); } else { Assert.Fail("Expected records not found."); } } } } catch (ArgumentException e) { Assert.Fail("OracleType not handled: " + e.Message); } }
/// <summary> /// Gets the Data. /// </summary> private void GetData() { try { // Clear existing items. dataGridView1.Rows.Clear(); // Open a connection to the database. OracleConnection oracleSqlConnection = new OracleConnection(url); oracleSqlConnection.Open(); // Get the items from the People table. List <Person> people = new List <Person>(); OracleCommand oracleSqlCommand = new OracleCommand("SELECT * FROM root.People WHERE IsDeleted = 0", oracleSqlConnection); // Execute. OracleDataReader oracleSqlDataReader = oracleSqlCommand.ExecuteReader(); // Store the items in a Person list. while (oracleSqlDataReader.Read()) { people.Add(new Person(oracleSqlDataReader.GetInt32(0), oracleSqlDataReader.GetString(1), oracleSqlDataReader.GetString(2), oracleSqlDataReader.GetString(3), oracleSqlDataReader.GetDateTime(4), oracleSqlDataReader.GetInt32(5) == 1 ? true : false)); } // Close the datareader and the connection. oracleSqlDataReader.Close(); oracleSqlConnection.Close(); // Fill the dataGridView rows with the values of the People table. foreach (var oneItem in people) { DataGridViewRow row = new DataGridViewRow(); DataGridViewCell cell1 = new DataGridViewTextBoxCell(); DataGridViewCell cell2 = new DataGridViewTextBoxCell(); DataGridViewCell cell3 = new DataGridViewTextBoxCell(); DataGridViewCell cell4 = new DataGridViewTextBoxCell(); DataGridViewCell cell5 = new DataGridViewTextBoxCell(); cell1.Value = oneItem.Id.ToString(); cell2.Value = oneItem.Name; cell3.Value = oneItem.Mothername; cell4.Value = oneItem.Location; cell5.Value = oneItem.Birthdate.ToString(); row.Cells.Add(cell1); row.Cells.Add(cell2); row.Cells.Add(cell3); row.Cells.Add(cell4); row.Cells.Add(cell5); dataGridView1.Rows.Add(row); } } catch { } }
private void loadList() { listView1.Items.Clear(); string query = "SELECT * FROM ADMISSION WHERE u_id = " + uid; command = new OracleCommand(query, connection); connection.Open(); reader = command.ExecuteReader(); if (!reader.HasRows) { MessageBox.Show("No Previous Admissions Found!"); return; } int i = 1; while (reader.Read()) { DateTime admitTime = reader.GetDateTime(1); if (DBNull.Value.Equals(reader.GetValue(2))) { ListViewItem item = new ListViewItem(new string[] { i.ToString(), admitTime.Day + "/" + admitTime.Month + "/" + admitTime.Year, admitTime.Hour + ":" + admitTime.Minute + ":" + admitTime.Second, "-/-/-", "-:-:-" }); listView1.Items.Add(item); i++; } else { DateTime discTime = reader.GetDateTime(2); ListViewItem item = new ListViewItem(new string[] { i.ToString(), admitTime.Day + "/" + admitTime.Month + "/" + admitTime.Year, admitTime.Hour + ":" + admitTime.Minute + ":" + admitTime.Second, discTime.Day + "/" + discTime.Month + "/" + discTime.Year, discTime.Hour + ":" + discTime.Minute + ":" + discTime.Second, }); listView1.Items.Add(item); i++; } } reader.Close(); connection.Close(); }
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; }
private CommentModel ParseModel(OracleDataReader reader) { return(new CommentModel() { Comment = reader.GetString(0), Created = reader.GetDateTime(1), Author = reader.GetString(2) }); }
private dynamic getDataValue(int index, OracleDataReader reader) { string dataTypeName = reader.GetFieldType(index)?.Name; dynamic dataValue = null; try { switch (dataTypeName) { case "String": dataValue = reader.IsDBNull(index) ? null : RemoveSpecialCharacters(reader.GetString(index)); break; case "Int16": dataValue = reader.GetInt16(index); break; case "Int32": dataValue = reader.GetInt32(index); break; case "Int64": dataValue = reader.GetInt64(index); break; case "Double": dataValue = reader.GetDouble(index); break; case "Float": dataValue = reader.GetFloat(index); break; case "Decimal": dataValue = reader.GetDecimal(index); break; case "DateTime": dataValue = reader.GetDateTime(index); break; case "Guid": dataValue = reader.GetGuid(index); break; default: dataValue = null; break; } return(dataValue); } catch (SqlNullValueException) { return(dataValue); } }
public static DateTime GetDateTime(string strNomeCampo, OracleDataReader objDataReader) { try { return(objDataReader.GetDateTime(_IndexCampo(strNomeCampo, objDataReader))); } catch { } return(DateTime.MinValue); }
/// <summary> /// to fetch all details /// </summary> /// <param name="householdID"></param> /// <returns></returns> public GrievanceList Getgrievancedata(int householdID) { OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString); OracleCommand cmd; string proc = "USP_TRN_SEL_GRIEVANCE"; cmd = new OracleCommand(proc, cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("HHID_", householdID); cmd.Parameters.Add("Sp_recordset", Oracle.DataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output; cmd.Connection.Open(); OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); GrievancesBO GrievancesBOObj = null; GrievanceList GrievanceListObj = new GrievanceList(); while (dr.Read()) { GrievancesBOObj = new GrievancesBO(); if (!dr.IsDBNull(dr.GetOrdinal("GRIEVANCEID"))) { GrievancesBOObj.GrievanceID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("GRIEVANCEID"))); } if (!dr.IsDBNull(dr.GetOrdinal("PAPNAME"))) { GrievancesBOObj.PapName = dr.GetString(dr.GetOrdinal("PAPNAME")); } if (!dr.IsDBNull(dr.GetOrdinal("GRIEVANCECATEGORY"))) { GrievancesBOObj.GrievCategory = dr.GetString(dr.GetOrdinal("GRIEVANCECATEGORY")); } if (!dr.IsDBNull(dr.GetOrdinal("CREATEDDATE"))) { GrievancesBOObj.CreatedDate = dr.GetDateTime(dr.GetOrdinal("CREATEDDATE")); } if (!dr.IsDBNull(dr.GetOrdinal("GREVDESCRIPTION"))) { GrievancesBOObj.Description = dr.GetString(dr.GetOrdinal("GREVDESCRIPTION")); } if (!dr.IsDBNull(dr.GetOrdinal("ISDELETED"))) { GrievancesBOObj.IsDeleted = dr.GetString(dr.GetOrdinal("ISDELETED")); } if (!dr.IsDBNull(dr.GetOrdinal("RESOLUTIONSTATUS"))) { GrievancesBOObj.ResolutionStatus = dr.GetString(dr.GetOrdinal("RESOLUTIONSTATUS")); } GrievanceListObj.Add(GrievancesBOObj); } dr.Close(); return(GrievanceListObj); }
public List <ListaProducto> buscarnombProducto(string nomb) { try { Conexion con = new Conexion(); OracleConnection cn = con.getConexion(); cn.Open(); OracleCommand cmd = new OracleCommand("FN_BUSCARNOMB_PRODUCTO", cn); cmd.CommandType = CommandType.StoredProcedure; List <ListaProducto> listatipocl = new List <ListaProducto>(); OracleParameter output = cmd.Parameters.Add("C_PRODUCTOBUS", OracleDbType.RefCursor); output.Direction = ParameterDirection.ReturnValue; OracleParameter para = new OracleParameter("PARAMETRO_NOMB", OracleDbType.Varchar2); para.Direction = ParameterDirection.Input; para.Value = nomb; cmd.Parameters.Add(para); cmd.ExecuteNonQuery(); OracleDataReader lecturatipocl = ((OracleRefCursor)output.Value).GetDataReader(); while (lecturatipocl.Read()) { ListaProducto pro = new ListaProducto(); pro.idproducto = lecturatipocl.GetString(0); pro.nombproducto = lecturatipocl.GetString(1); int index = lecturatipocl.GetOrdinal("FECHA_VENC_PRODUCTO"); if (!lecturatipocl.IsDBNull(index)) { pro.fecha_vencproducto = Convert.ToDateTime(lecturatipocl.GetDateTime(2)).ToString("dd/MM/yyyy"); } else { pro.fecha_vencproducto = "00-00-0000"; } pro.preciounitario = lecturatipocl.GetInt32(3); pro.stock_producto = lecturatipocl.GetInt32(4); pro.stock_critic = lecturatipocl.GetInt32(5); pro.provee = lecturatipocl.GetString(6); pro.tipo = lecturatipocl.GetString(7); listatipocl.Add(pro); } output.Dispose(); cmd.Dispose(); cn.Dispose(); con = null; return(listatipocl); } catch (Exception e) { throw; } }
/// <summary> /// /// </summary> /// <param name="solicitud"></param> /// <returns></returns> public ReturnObject <List <Solicitud> > ConsultarSolicitudes(Solicitud solicitud) { ReturnObject <List <Solicitud> > obj = new ReturnObject <List <Solicitud> >(); obj.OneResult = new List <Solicitud>(); try { using (var oCnn = Cn.OracleCn()) { OracleCommand oCmd = null; oCnn.Open(); oCmd = new OracleCommand("SP_CONSULTAR_SOLICITUDES", oCnn); oCmd.CommandType = CommandType.StoredProcedure; oCmd.Parameters.Add(new OracleParameter("P_IDSOLICITUD", OracleDbType.Int64)).Value = solicitud.IdSolicitud; oCmd.Parameters.Add(new OracleParameter("P_ESTADO", OracleDbType.Varchar2)).Value = solicitud.Estado; oCmd.Parameters.Add(new OracleParameter("P_CODIGOALUMNO", OracleDbType.Varchar2)).Value = solicitud.CodigoAlumno; oCmd.Parameters.Add(new OracleParameter("P_NOMBREALUMNO", OracleDbType.Varchar2)).Value = solicitud.Nombre; oCmd.Parameters.Add(new OracleParameter("P_FECHA_INICIO", OracleDbType.Varchar2)).Value = solicitud.FechaInicio; oCmd.Parameters.Add(new OracleParameter("P_FECHA_FIN", OracleDbType.Varchar2)).Value = solicitud.FechaFin; oCmd.Parameters.Add(new OracleParameter("P_IDEMPLEADO", OracleDbType.Int64)).Value = solicitud.IdEmpleado; oCmd.Parameters.Add(new OracleParameter("P_IDALUMNO", OracleDbType.Int64)).Value = solicitud.IdAlumno; oCmd.Parameters.Add(new OracleParameter("P_RC", OracleDbType.RefCursor)).Direction = ParameterDirection.Output; OracleDataReader rd = oCmd.ExecuteReader(); if (rd.HasRows) { while (rd.Read()) { var solicitud_ = new Solicitud(); solicitud_.IdSolicitud = rd.GetInt32(rd.GetOrdinal("IDSOLICITUD")); solicitud_.FechaRegistro = rd.GetDateTime(rd.GetOrdinal("FECHAREGISTRO")); solicitud_.Canal = rd.GetValue(rd.GetOrdinal("CANAL")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("CANAL")); solicitud_.Intencion = rd.GetValue(rd.GetOrdinal("INTENCION")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("INTENCION")); solicitud_.CodigoAlumno = rd.GetValue(rd.GetOrdinal("CODIGOALUMNO")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("CODIGOALUMNO")); solicitud_.Nombre = rd.GetValue(rd.GetOrdinal("NOMBRE")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("NOMBRE")); solicitud_.ApellidoPat = rd.GetValue(rd.GetOrdinal("APELLIDOPAT")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("APELLIDOPAT")); solicitud_.ApellidoMat = rd.GetValue(rd.GetOrdinal("APELLIDOMAT")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("APELLIDOMAT")); solicitud_.Estado = rd.GetValue(rd.GetOrdinal("ESTADO")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("ESTADO")); solicitud_.Consulta = rd.GetValue(rd.GetOrdinal("CONSULTA")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("CONSULTA")); solicitud_.Solucion = rd.GetValue(rd.GetOrdinal("SOLUCION")) == DBNull.Value ? string.Empty : rd.GetString(rd.GetOrdinal("SOLUCION")); obj.OneResult.Add(solicitud_); } } obj.Success = true; } } catch (Exception ex) { obj.Success = false; obj.ErrorMessage = ex.Message; } return(obj); }
/// <summary> /// To get approval Comments /// </summary> /// <param name="objCOMPPACKBO"></param> /// <returns></returns> public CompensationPackagesBO getapprovalComments(CompensationPackagesBO objCOMPPACKBO) { proc = "USP_TRN_GET_PKGAPPCOMMENTS"; cnn = new OracleConnection(con); CompensationPackagesBO objCOMPACK = null; CompensationPackagesList COMPACKList = new CompensationPackagesList(); cmd = new OracleCommand(proc, cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("DocumentCode_", objCOMPPACKBO.DocumentCode); cmd.Parameters.Add("HHID_", objCOMPPACKBO.HHID); cmd.Parameters.Add("ApprovalLevel_", objCOMPPACKBO.ApprovalLevel); cmd.Parameters.Add("UserID_", objCOMPPACKBO.UserID); cmd.Parameters.Add("ProjectID_", objCOMPPACKBO.ProjectID); cmd.Parameters.Add("Sp_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output; try { cmd.Connection.Open(); OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { objCOMPACK = new CompensationPackagesBO(); if (!dr.IsDBNull(dr.GetOrdinal("APPROVERCOMMENTS"))) { objCOMPACK.ApprovalComents = dr.GetString(dr.GetOrdinal("APPROVERCOMMENTS")); } if (!dr.IsDBNull(dr.GetOrdinal("username"))) { objCOMPACK.UserName = dr.GetString(dr.GetOrdinal("username")); } if (!dr.IsDBNull(dr.GetOrdinal("APPROVAL_LEVEL"))) { objCOMPACK.ApprovalLevel = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("APPROVAL_LEVEL"))); } if (!dr.IsDBNull(dr.GetOrdinal("REVIEWDATE"))) { objCOMPACK.ApprovedDate = dr.GetDateTime(dr.GetOrdinal("REVIEWDATE")).ToString(UtilBO.DateFormatFull); } //COMPACKList.Add(objCOMPACK); } dr.Close(); } catch (Exception ex) { throw ex; } return(objCOMPACK); }
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; }
/// <summary> /// get all orders /// </summary> /// <returns></returns> public List <Order> GetAllOrder() { List <Order> retOrder = new List <Order>(); try { OracleCommand commn = dataConnection.ConnectToDatabase(); commn.CommandText = "select orderid, orderdate,shipdate,status,discount,employeeid,customerid from orders"; OracleDataReader odr = commn.ExecuteReader(); while (odr.Read()) { Order aOrder = new Order(); aOrder.OrderId = odr.GetInt32(0); aOrder.OrderDate = odr.GetDateTime(1); aOrder.ShipDate = odr.GetDateTime(2); aOrder.Status = odr.GetInt32(3); aOrder.Discount = odr.GetInt32(4); aOrder.EmployeeId = odr.GetInt32(5); aOrder.customerId = odr.GetInt32(6); //if (string.Compare(aCustomer.MutiAddress, "N") == 0) //{ // ShippingInfo shipinfo = new ShippingInfo(); // shipinfo.CustosmerId = aCustomer.CustomerId; // shipinfo.ShippingFirstName = odr.GetString(13); // shipinfo.ShippingStreet = odr.GetString(14); // shipinfo.ShippingState = odr.GetString(15); // shipinfo.ShippingCity = odr.GetString(16); // shipinfo.ShippingPhone = odr.GetInt32(17); // shipinfo.ShippingLastName = odr.GetString(18); // shipinfo.ShipppingPost = odr.GetString(19); // aCustomer.ShipInfo = shipinfo; // } retOrder.Add(aOrder); } dataConnection.CloseDatabase(); } catch (Exception e) { } finally { dataConnection.CloseDatabase(); } return(retOrder); }
public List <IngresoStock> listarVistasStockHistorico() { List <IngresoStock> ingresos = new List <IngresoStock>(); try { cmd.CommandText = "PACKAGE_STOCK.PR_LISTAR_VISTAS_HISTORICO"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("CURSOR_STOCK_HISTORICO", OracleDbType.RefCursor).Direction = ParameterDirection.Output; OracleDataReader reader = cmd.ExecuteReader(); foreach (var item in reader) { IngresoStock ingreso = new IngresoStock(); ingreso.IdIngresoStock = reader.GetInt32(0); Insumo insumo = new Insumo(); insumo.IdInsumo = reader.GetInt32(1); insumo.NombreInusmo = reader.GetString(2); insumo.UnidadMedidaInsumo = reader.GetString(3); insumo.Foto = reader.GetString(4); ingreso.Insumo = insumo; ingreso.FechaIngreso = reader.GetDateTime(5); ingreso.FechaCaducidad = reader.GetDateTime(6); ingreso.Cantidad = reader.GetInt32(7); ingreso.Estado = reader.GetInt32(8); ingresos.Add(ingreso); } con.Close(); return(ingresos); } catch (Exception) { return(ingresos); } }
public async Task <DbUser> Login(string email, string password) { await using var conn = new OracleConnection("DATA SOURCE=localhost/XE;USER ID=elsa; password=letitgo;"); conn.Open(); await using var command = conn.CreateCommand(); command.CommandText = @"begin :prm_Result := PKG_USER.LOGIN(:email, :pass); end;"; command.Parameters.Add(":prm_Result", OracleDbType.RefCursor, ParameterDirection.Output); command.Parameters.Add(":email", OracleDbType.Varchar2).Value = email; command.Parameters.Add(":pass", OracleDbType.Varchar2).Value = password; OracleDataReader rdr = command.ExecuteReader(); DbUser user = null; while (rdr.Read()) { user = new DbUser { UserId = rdr.GetInt32(0), Firstname = rdr.GetString(1), Surname = rdr.GetString(2), BirthDate = rdr.GetDateTime(3), Phone = rdr.GetString(4), Email = rdr.GetString(5), UserPassword = rdr.GetString(6), Salt = rdr.GetString(7) }; if (!rdr.IsDBNull(8)) { user.StudyYear = rdr.GetInt32(8); } if (!rdr.IsDBNull(9)) { user.ProfilePicture = rdr.GetOracleBlob(9).Value; } user.TimeCreated = rdr.GetDateTime(10); user.RoleId = rdr.GetInt32(11); user.AddressId = rdr.GetInt32(12); } return(user); }
public IEnumerable<Estudiante> GetEstudiantesBD3(int idPar) { string strCon = _configuration.GetConnectionString("OracleDBConnection"); // string strCon = "Data Source=DW; user id=webacad; password=cefalopatia; Min Pool Size=1"; List<Estudiante> listaEstudiantes = new List<Estudiante>(); using (OracleConnection connection = new OracleConnection(strCon)) using (OracleCommand command = connection.CreateCommand()) { try { connection.Open(); command.BindByName = true; // sirve para que puedas enviar en cualquier ordern command.CommandText = $"select * from estudiante where ID_ESTUDIANTE = :id" + " and nombres=:nombres and apellidos=:apellidos"; OracleParameter nombres = new OracleParameter(); nombres.ParameterName = "nombres"; nombres.Value = "juan"; command.Parameters.Add(nombres); OracleParameter apellidos = new OracleParameter(); apellidos.ParameterName = "apellidos"; apellidos.Value = "peres"; command.Parameters.Add(apellidos); OracleParameter id = new OracleParameter(); id.ParameterName = "id"; id.Value = idPar; command.Parameters.Add(id); OracleDataReader reader = command.ExecuteReader(); while (reader.Read()) { listaEstudiantes.Add(new Estudiante() { ID_ESTUDIANTE = reader.GetInt32(0), CI = reader.GetString(1), NOMBRES = reader.GetString(2), APELLIDOS = reader.GetString(3), FECHA_NACIMIENTO = reader.GetDateTime(4) }); } reader.Dispose(); } catch (Exception err) { throw new Exception(err.Message); } } return listaEstudiantes; }
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; }
/// <summary> /// Gets all users from the database /// </summary> /// <returns></returns> public List<User> GetAllUsers() { List<User> userList = new List<User>(); try { Connect(); cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = "SELECT g.ID, NAAM, ADRES, WOONPLAATS, TELEFOONNUMMER, HEEFTRIJBEWIJS, HEEFTAUTO, UITSCHRIJVINGSDATUM, ACCOUNTID, GEBRUIKERSNAAM, WACHTWOORD, EMAIL FROM TGEBRUIKER g, TACCOUNT WHERE g.accountid = taccount.id"; // QUERY cmd.CommandType = CommandType.Text; dr = cmd.ExecuteReader(); while (dr.Read()) { // Read from DB int id = dr.GetInt32(0); var naam = dr.GetString(1); var adres = dr.GetDateTime(2); var woonplaats = dr.GetString(3); var telefoonnummer = dr.GetString(4); bool heeftrijbewijs = (dr.GetString(5).ToUpper() == "TRUE"); bool heeftauto = (dr.GetString(6).ToUpper() == "TRUE"); DateTime uitschrijvingsdatum = dr.GetDateTime(7); int accountid = dr.GetInt32(8); var gebruikersnaam = dr.GetString(9); var wachtwoord = dr.GetString(10); var email = dr.GetString(11); /* User toadd; 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, "", "", ""); 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; } userList.Add(toadd);*/ } Disconnect(); return userList; } catch (InvalidCastException ex) { Disconnect(); MessageBox.Show(ex.ToString()); return null; } finally { Disconnect(); } }
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(); } }
public Users GetUser(string Email, string Wachtwoord) { Users Toadd = null; try { Connect(); cmd.Connection = con; cmd.CommandText = "SELECT UserID, Voornaam, Achternaam, Geboortedatum, Geslacht, Straat, Huisnummer, Postcode, Plaats,Telefoonnummer, Email, Abbonement FROM GEBRUIKER WHERE UserID=:UserID"; cmd.Parameters.Add("Email", Email); cmd.Parameters.Add("Wachtwoord", Wachtwoord); cmd.CommandType = CommandType.Text; dr = cmd.ExecuteReader(); dr.Read(); } catch { System.Diagnostics.Debug.WriteLine("Getting user failed"); 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); } } catch { return null; } return Toadd; }
// 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(); } }
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(); } }
internal static DateTime GetDateTime(OracleDataReader reader, int index, DateTime defaultTime) { if (reader.IsDBNull(index)) { return defaultTime; } return (DateTime)reader.GetDateTime(index); }
internal static DateTime? GetDateTimeNullable(OracleDataReader reader, int index) { if (reader.IsDBNull(index)) { return null; } return (DateTime)reader.GetDateTime(index); }
User loadUser(OracleDataReader odr, bool isCurrentUser) { DateTime? created = null, expiryDate = null; if (!odr.IsDBNull(4)) created = (DateTime)odr.GetDateTime(odr.GetOrdinal("created")); if (!odr.IsDBNull(5)) expiryDate = (DateTime)odr.GetDateTime(odr.GetOrdinal("expiry_date")); if (isCurrentUser) return new CurrentUser( odr.GetDecimal(odr.GetOrdinal("user_id")), odr.GetString(odr.GetOrdinal("username")), odr.GetString(odr.GetOrdinal("default_tablespace")), odr.GetString(odr.GetOrdinal("temporary_tablespace")), created, expiryDate, session); else return new User( odr.GetDecimal(odr.GetOrdinal("user_id")), odr.GetString(odr.GetOrdinal("username")), odr.GetString(odr.GetOrdinal("default_tablespace")), odr.GetString(odr.GetOrdinal("temporary_tablespace")), created, expiryDate, session); }
public static List<Question> GetAllQuestions() { List<Question> questionlist = new List<Question>(); try { Connect(); cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = "SELECT QUESTIONID, AUTEUR, VRAAG , BIJZONDERHEID, LOCATIE, AFSTAND , VERVOER , DATUM , OPGELOST, ANTWOORD, VOLUNTEERID FROM TQUESTION"; // 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 questionid = dr.GetInt32(0); var auteur = dr.GetInt32(1); var vraag = SafeReadString(dr, 2); var bijzonderheid = SafeReadString(dr, 3); var locatie = SafeReadString(dr, 4); var afstand = SafeReadString(dr, 5); var vervoer = SafeReadString(dr, 6); DateTime datum = dr.GetDateTime(7); var opgelost = SafeReadString(dr, 8); var antwoord = SafeReadString(dr, 9); int volunteerid = SafeReadInt(dr, (10)); Question toadd; toadd = new Question(null, auteur, locatie, vervoer, afstand, bijzonderheid, vraag, datum, opgelost); toadd.ID = questionid; toadd.Answer = antwoord; toadd.VolunteerID = volunteerid; questionlist.Add(toadd); } foreach (Question q in questionlist) { q.Client = (Client)GetUser(q.Auteur); } foreach (Question q in questionlist) { if (q.VolunteerID != -1) { q.Volunteer = (Volunteer)GetUser(q.VolunteerID); } } return questionlist; } catch (Exception ex) { MessageBox.Show(ex.Message); return null; } }
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 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; } }
public Volunteer GetVolunteerDetails(Volunteer volun) { try { Connect(); cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = "SELECT * FROM TVRIJWILLIGER WHERE ID = " + volun.UserID; cmd.CommandType = CommandType.Text; dr = cmd.ExecuteReader(); while (dr.Read()) { // Read from DB int id = dr.GetInt32(0); DateTime geboortedatum = dr.GetDateTime(1); var photo = SafeReadString(dr, 2); var vog = SafeReadString(dr, 3); var gebruikerid = SafeReadInt(dr, 4); // Fill volun.Photo = photo; volun.VOG = vog; } return volun; } catch (InvalidCastException ex) { MessageBox.Show(ex.ToString()); return null; } finally { Disconnect(); } }
public static Acteur GetActeur(string id) { string naam = string.Empty; DateTime dob = new DateTime(1, 1, 1); string woonplek = string.Empty; string baan = string.Empty; List<Prijs> prijzen = new List<Prijs>(); List<Film> films = new List<Film>(); using (connection = new OracleConnection(connectionstring)) { query = "SELECT f.filmid, f.titel FROM film f, filmacteurregel fcr WHERE f.filmid = fcr.filmid AND fcr.acteurid = :pid"; cmd.CommandText = query; cmd.Connection = connection; try { connection.Open(); cmd.Parameters.Clear(); cmd.Parameters.Add("id", Convert.ToInt32(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(); } } using (connection = new OracleConnection(connectionstring)) { query = "SELECT p.titel, p.jaar, p.prizeid FROM acteurprijsregel apr, prijs p WHERE p.prizeid = apr.prizeid AND apr.acteurid = :pid"; cmd.CommandText = query; cmd.Connection = connection; try { connection.Open(); cmd.Parameters.Clear(); cmd.Parameters.Add("id", Convert.ToInt32(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 a.naam, a.dob, a.woonplek, a.baan FROM acteur a WHERE a.acteurid = :pid"; cmd.CommandText = query; cmd.Connection = connection; try { connection.Open(); cmd.Parameters.Clear(); cmd.Parameters.Add("id", Convert.ToInt32(id)); reader = cmd.ExecuteReader(); while (reader.Read()) { naam = reader.GetString(0); dob = reader.GetDateTime(1); woonplek = reader.GetString(2); baan = reader.GetString(3); } } catch (Exception ex) { cmd.Dispose(); connection.Dispose(); reader.Close(); } } Acteur acteur = new Acteur(naam, dob, woonplek, baan, prijzen, films, Convert.ToInt32(id)); return acteur; }
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 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; }
/// <summary> /// Reads the user. /// </summary> /// <param name="reader">The reader.</param> /// <returns></returns> internal static LPHSUser ReadUser(OracleDataReader reader) { var user = new LPHSUser() { ID = (int)OracleDatabase.GetDecimal(reader, 0, 0), UserType = (LPHSUserTypes)(int)OracleDatabase.GetDecimal(reader, 1, 4), Name = OracleDatabase.GetString(reader, 2, null), DisplayName = OracleDatabase.GetString(reader, 3, null), EmailAddress = OracleDatabase.GetString(reader, 4, null), Active = OracleDatabase.GetBooleanFromString(reader, 5), LastLoggedOn = reader.IsDBNull(6) ? null : new Nullable<DateTime>(reader.GetDateTime(6)), SchoolID = (int?)OracleDatabase.GetDecimalNullable(reader, 7, null), Sid = (string)OracleDatabase.GetString(reader, 8, null) }; if (reader.VisibleFieldCount > 9) user.SumTotalUserName = reader.IsDBNull(9) ? null : reader.GetString(9); if (reader.VisibleFieldCount > 10) user.SumTotalPassword = reader.IsDBNull(10) ? null : reader.GetString(10); if (user.SumTotalPassword != null && user.SumTotalPassword.Length > 10) user.SumTotalPassword = user.SumTotalPassword; //if (user.SumTotalPassword != null) // user.SumTotalPassword = Security.Decrypt(user.SumTotalPassword); if (reader.VisibleFieldCount > 11) user.SumTotalID = reader.IsDBNull(11) ? null : reader.GetString(11); if (!user.SchoolID.HasValue) { user.SchoolID = -1; } //set default school return user; }
EventDto MapReaderToEventDto(OracleDataReader dataReader) { return new EventDto() { Id = dataReader.GetInt64(ColumnPositions.Id), CommandContext = ConvertToGuid(dataReader.GetOracleBinary(ColumnPositions.CommandContext)), Name = dataReader.GetString(ColumnPositions.Name), LogicalName = dataReader.GetString(ColumnPositions.LogicalName), EventSourceId = ConvertToGuid(dataReader.GetOracleBinary(ColumnPositions.EventSourceId)), EventSource = dataReader.GetString(ColumnPositions.EventSource), Generation = dataReader.GetInt32(ColumnPositions.Generation), Data = dataReader.GetOracleClob(ColumnPositions.Data).Value, CausedBy = dataReader.GetString(ColumnPositions.CausedBy), Origin = dataReader.GetString(ColumnPositions.Origin), Occurred = dataReader.GetDateTime(ColumnPositions.Occured), Version = Convert.ToDouble(dataReader.GetDecimal(ColumnPositions.Version)) }; }
public User GetUserNoConnect(int ids) { User toadd = null; try { cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = "SELECT ID, NAAM, ADRES, WOONPLAATS, TELEFOONNUMMER, HEEFTRIJBEWIJS, HEEFTAUTO, UITSCHRIJVINGSDATUM, ACCOUNTID FROM TGEBRUIKER WHERE ID = " + 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 int id = dr.GetInt32(0); var naam = dr.GetString(1); var adres = dr.GetDateTime(2); var woonplaats = dr.GetString(3); var telefoonnummer = dr.GetString(4); bool heeftrijbewijs = (dr.GetString(5).ToUpper() == "TRUE"); bool heeftauto = (dr.GetString(6).ToUpper() == "TRUE"); DateTime uitschrijvingsdatum = dr.GetDateTime(7); int accountid = dr.GetInt32(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; } */ } return toadd; } catch (InvalidCastException ex) { MessageBox.Show(ex.ToString()); return null; } }