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;
}
}
