public List <RTicket> GetPassengerTicket(string id)
{
string query = "SELECT * from Ticket WHERE Passsengerid = '" + id + "'";
List <RTicket> tList = new List <RTicket>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
RTicket t = new RTicket();
t.Ticketid = sdr["Ticketid"].ToString();
t.Journeydate = sdr["Journeydate"].ToString();
t.Tranid = sdr["Trainid"].ToString();
t.SeatClassid = sdr["SeatClassid"].ToString();
t.Passsengerid = sdr["Passsengerid"].ToString();
t.Seatno = sdr["Seatno"].ToString();
t.Price = Convert.ToDouble(sdr["Price"]);;
tList.Add(t);
}
dcc.CloseConnection();
return(tList);
}
public bool SeatDelete(string seatid)
{
try
{
string query = "DELETE From Seat WHERE Seatid='" + seatid + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool TrainUpdate(Train t)
{
try
{
string query = "UPDATE Train SET Trainname = '" + t.Trainname + "', StartingPoint ='" + t.From + "', Destination ='" + t.To + "', StartingTime ='" + t.StartingTime + "', ReachingTime ='" + t.ReachingTime + "', Noofseats = " + t.Noofseats + "WHERE Trainid ='" + t.Trainid + "' ";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool TrainAdd(Train t)
{
try
{
string query = "INSERT into Train VALUES ('" + t.Trainid + "', '" + t.Trainname + "', '" + t.From + "', '" + t.To + "', '" + t.StartingTime + "', '" + t.ReachingTime + "', " + t.Noofseats + ")";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool SeatAdd(Seat s)
{
try
{
string query = "INSERT into Seat VALUES ('" + s.Seatid + "', '" + s.Seatname + "', " + s.Price + ")";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool CancelReservation(string ticketid)
{
try
{
string query = "DELETE From Ticket WHERE Ticketid='" + ticketid + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool NoticeAdd(Notice n)
{
try
{
string query = "INSERT into Notice VALUES ('" + n.Noticeid + "', '" + n.Noticesubject + "', '" + n.Noticedetails + "')";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool CancelSchedule(string trainid)
{
try
{
string query = "DELETE From Train WHERE Trainid='" + trainid + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool TicketSell(RTicket t)
{
try
{
string query = "INSERT into Ticket VALUES ('" + t.Ticketid + "', '" + t.Journeydate + "', '" + t.Tranid + "', '" + t.SeatClassid + "', '" + t.Passsengerid + "', '" + t.Seatno + "', " + t.Price + ")";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool PassengerAdd(RPassenger p)
{
try
{
string query = "INSERT into Passenger VALUES ('" + p.Id + "', '" + p.Name + "', '" + p.Password + "', '" + p.Dateofbirth + "', '" + p.Gender + "', '" + p.Nationality + "', " + p.Type + ")";
string query2 = "INSERT into Login VALUES ('" + p.Id + "','" + p.Name + "', '" + p.Password + "', " + p.Type + ")";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
int y = dcc.ExecuteSQL(query2);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool EmployeeDelete(string id)
{
try
{
string query = "DELETE From Employee WHERE Id='" + id + "'";
string query2 = "DELETE From Login WHERE Id='" + id + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
int y = dcc.ExecuteSQL(query2);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool EmployeeAdd(REmployee e)
{
try
{
string query = "INSERT into Employee VALUES ('" + e.Id + "', '" + e.Name + "', '" + e.Designation + "', '" + e.Password + "', " + e.Salary + ", '" + e.Phoneno + "', '" + e.Email + "', " + e.Type + ")";
string query2 = "INSERT into Login VALUES ('" + e.Id + "', '" + e.Name + "','" + e.Password + "', " + e.Type + ")";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
int y = dcc.ExecuteSQL(query2);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool PasswordChange(REmployee e)
{
try
{
string query = "UPDATE Employee SET Password = '******'WHERE Id ='" + e.Id + "' ";
string query1 = "UPDATE Login SET Password = '******'WHERE Id ='" + e.Id + "' ";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
int y = dcc.ExecuteSQL(query1);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool AdminAdd(RAdmin a)
{
try
{
string query = "INSERT into Admin VALUES ('" + a.Id + "', '" + a.Name + "', '" + a.Position + "', '" + a.Password + "', " + a.Salary + ", " + a.Type + ")";
string query2 = "INSERT into Login VALUES ('" + a.Id + "', '" + a.Name + "','" + a.Password + "', " + a.Type + ")";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
int y = dcc.ExecuteSQL(query2);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool PassengerDelete(string id)
{
try
{
string query = "DELETE From Passenger WHERE Id='" + id + "'";
string query2 = "DELETE From Ticket WHERE Passsengerid='" + id + "'";
string query3 = "DELETE From Login WHERE Id='" + id + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
int x = dcc.ExecuteSQL(query);
int y = dcc.ExecuteSQL(query2);
int z = dcc.ExecuteSQL(query3);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public List <Seat> GetAllSeats()
{
string query = "SELECT * from Seat";
List <Seat> sList = new List <Seat>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Seat s = new Seat();
s.Seatid = sdr["Seatid"].ToString();
s.Seatname = sdr["Seatname"].ToString();
s.Price = Convert.ToDouble(sdr["Price"]);
sList.Add(s);
}
dcc.CloseConnection();
return(sList);
}
public RAdmin Profile(string id)
{
string query = "SELECT * from Admin WHERE Id = '" + id + "'";
RAdmin a = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
a = new RAdmin();
a.Id = sdr["Id"].ToString();
a.Name = sdr["Name"].ToString();
a.Position = sdr["Position"].ToString();
a.Password = sdr["Password"].ToString();
a.Salary = Convert.ToDouble(sdr["Salary"]);
a.Type = Convert.ToInt32(sdr["Type"]);
}
dcc.CloseConnection();
return(a);
}
public List <Notice> GetAllNotice()
{
string query = "SELECT * from Notice";
List <Notice> nList = new List <Notice>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Notice n = new Notice();
n.Noticeid = sdr["Noticeid"].ToString();
n.Noticesubject = sdr["Noticesubject"].ToString();
n.Noticedetails = sdr["Noticedetails"].ToString();
nList.Add(n);
}
dcc.CloseConnection();
return(nList);
}
public List <Seat> SearchSeatPrice(string text)
{
string query = "SELECT * from Seat Where Seatid like'%" + text + "%' or Seatname like'%" + text + "%'"; //or Price like %" + Convert.ToDouble(text) + "%";
List <Seat> sList = new List <Seat>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Seat s = new Seat();
s.Seatid = sdr["Seatid"].ToString();
s.Seatname = sdr["Seatname"].ToString();
s.Price = Convert.ToDouble(sdr["Price"]);
sList.Add(s);
}
dcc.CloseConnection();
return(sList);
}
public bool UserLoginValidation(RLogin l)
{
string query = "SELECT * from Login WHERE Id = '" + l.Id + "' AND Password='******'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
// dcc.CloseConnection();
if (sdr.Read())
{
l.Name = sdr["Name"].ToString();
l.Type = Convert.ToInt32(sdr["Type"]);
dcc.CloseConnection();
return(true);
}
else
{
dcc.CloseConnection();
return(false);
}
}
public RPassenger Profile(string id)
{
string query = "SELECT * from Passenger WHERE Id = '" + id + "'";
RPassenger p = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
p = new RPassenger();
p.Id = sdr["Id"].ToString();
p.Name = sdr["Name"].ToString();
p.Dateofbirth = sdr["Dateofbirth"].ToString();
p.Password = sdr["Password"].ToString();
p.Gender = sdr["Gender"].ToString();
p.Nationality = sdr["Nationality"].ToString();
p.Type = Convert.ToInt32(sdr["Type"]);
}
dcc.CloseConnection();
return(p);
}
public Train TrainSearch(string trainid)
{
string query = "SELECT * from Train WHERE Trainid = '" + trainid + "'";
Train t = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
t = new Train();
t.Trainid = sdr["Trainid"].ToString();
t.Trainname = sdr["Trainname"].ToString();
t.From = sdr["StartingPoint"].ToString();
t.To = sdr["Destination"].ToString();
t.StartingTime = sdr["StartingTime"].ToString();
t.ReachingTime = sdr["ReachingTime"].ToString();
t.Noofseats = Convert.ToInt32(sdr["Noofseats"]);
}
dcc.CloseConnection();
return(t);
}
public List <RPassenger> GetAllPassengers()
{
string query = "SELECT * from Passenger";
List <RPassenger> pList = new List <RPassenger>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
RPassenger p = new RPassenger();
p.Id = sdr["Id"].ToString();
p.Name = sdr["Name"].ToString();
//p.Password = sdr[""].ToString();
p.Dateofbirth = sdr["DateOfbirth"].ToString();
p.Gender = sdr["Gender"].ToString();
p.Nationality = sdr["Nationality"].ToString();
pList.Add(p);
}
dcc.CloseConnection();
return(pList);
}
public REmployee Profile(string id)
{
string query = "SELECT * from Employee WHERE Id = '" + id + "'";
REmployee e = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
e = new REmployee();
e.Id = sdr["Id"].ToString();
e.Name = sdr["Name"].ToString();
e.Designation = sdr["Designation"].ToString();
e.Password = sdr["Password"].ToString();
e.Salary = Convert.ToDouble(sdr["Salary"]);
e.Phoneno = sdr["Phoneno"].ToString();
e.Email = sdr["Email"].ToString();
e.Type = Convert.ToInt32(sdr["Type"]);
}
dcc.CloseConnection();
return(e);
}
public List <RPassenger> SearchPassenger(string text)
{
string query = "SELECT * from Passenger Where Id like'%" + text + "%' or Name like'%" + text + "%' or Dateofbirth like'%" + text + "%' or Nationality like'%" + text + "%'";
List <RPassenger> plist = new List <RPassenger>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
RPassenger p = new RPassenger();
p.Id = sdr["Id"].ToString();
p.Name = sdr["Name"].ToString();
//p.Password = sdr[""].ToString();
p.Dateofbirth = sdr["DateOfbirth"].ToString();
p.Gender = sdr["Gender"].ToString();
p.Nationality = sdr["Nationality"].ToString();
plist.Add(p);
}
dcc.CloseConnection();
return(plist);
}
