public void RideUpdate(Ride r)
{
SqlConnection con = DACHelper.GetConnection();
SqlCommand cmd = new SqlCommand("Ride_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", r.Id);
cmd.Parameters.AddWithValue("@DepartureTime", r.DepartureTime);
cmd.Parameters.AddWithValue("@DepartureDate", r.DepartureDate);
cmd.Parameters.AddWithValue("@PostDate", r.PostDate);
cmd.Parameters.AddWithValue("@AvailableSeats", r.AvailableSeats);
cmd.Parameters.AddWithValue("@CostPerKm", r.CostPerKm);
cmd.Parameters.AddWithValue("@VehicleType", string.IsNullOrEmpty(r.VehicleType) ? Convert.DBNull : r.VehicleType);
cmd.Parameters.AddWithValue("@RideFrequency", string.IsNullOrEmpty(r.RideFrequency) ? Convert.DBNull : r.RideFrequency);
cmd.Parameters.AddWithValue("@Smoking", r.Smoking);
cmd.Parameters.AddWithValue("@FoodDrinks", r.FoodDrinks);
cmd.Parameters.AddWithValue("@Source", r.Source);
cmd.Parameters.AddWithValue("@Destination", r.Destination);
cmd.Parameters.AddWithValue("@CheckPoints", r.Checkpoints);
cmd.Parameters.AddWithValue("@UserId", r.UserId);
cmd.Parameters.AddWithValue("@RideStatusId", r.RideStatusId);
con.Open();
using (con)
{
Convert.ToInt32(cmd.ExecuteScalar());
}
}
public List <Ride> SelectAllRides(Ride r)
{
SqlCommand cmd = new SqlCommand(SELECT_All_RIDES, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@RideStatusId", r.RideStatusId);
return(fetchRides(cmd));
}
public List <RideRequest> SelectALLAcceptedRequestByRideId(RideRequest rr)
{
SqlCommand cmd = new SqlCommand(SELECT_All_ACCEPTED_REQUEST_BY_RIDEID, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@RideId", rr.RideId);
cmd.Parameters.AddWithValue("@Status", rr.RequestStatus);
return(fetchRideRequest(cmd));
}
public Rating SelectByUserId(int id)
{
SqlCommand cmd = new SqlCommand(SELECT_BY_USERID, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@UserId", id);
List <Rating> temp = fetchRating(cmd);
return((temp != null) ? temp[0] : null);
}
public Users SelectByPhone(string phone)
{
SqlCommand cmd = new SqlCommand(SELECT_BY_Phone, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@Phone", phone);
List <Users> temp = fetchUsers(cmd);
return((temp != null) ? temp[0] : null);
}
public RideRequest SelectByPassengerId(int id)
{
SqlCommand cmd = new SqlCommand(SELECT_RIDE_REQUEST_BY_PASSENGERID, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@PassengerId", id);
List <RideRequest> temp = fetchRideRequest(cmd);
return((temp != null) ? temp[0] : null);
}
public Users SelectById(int id)
{
SqlCommand cmd = new SqlCommand(SELECT_BY_ID, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@Id", id);
List <Users> temp = fetchUsers(cmd);
return((temp != null) ? temp[0] : null);
}
public Users SelectByStdEmpId(string id)
{
SqlCommand cmd = new SqlCommand(SELECT_BY_Std_EMPID, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@Student_EmployeeId", id);
List <Users> temp = fetchUsers(cmd);
return((temp != null) ? temp[0] : null);
}
public Users SelectByStdEmpIdAndPassword(string std_empId, string password)
{
SqlCommand cmd = new SqlCommand(SELECT_BY_STD_EMPID_AND_PASSWORD, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@Student_EmployeeId", std_empId);
cmd.Parameters.AddWithValue("@Password", password);
List <Users> temp = fetchUsers(cmd);
return((temp != null) ? temp[0] : null);
}
public void DeleteUser(int id)
{
SqlCommand cmd = new SqlCommand("User_Delete", DACHelper.GetConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", id);
SqlConnection con = cmd.Connection;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public void DeleteAllRideRequestsByRideId(int rid)
{
SqlCommand cmd = new SqlCommand("Delete_AllRequests_ByRideId", DACHelper.GetConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RideId", rid);
SqlConnection con = cmd.Connection;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public void UpdatePassword(Users u)
{
SqlConnection con = DACHelper.GetConnection();
SqlCommand cmd = new SqlCommand("User_Password_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Std_EmpId", u.Student_EmployeeId);
cmd.Parameters.AddWithValue("@Password", string.IsNullOrEmpty(u.Password) ? Convert.DBNull : u.Password);
con.Open();
using (con)
{
Convert.ToInt32(cmd.ExecuteScalar());
}
}
public void RideRequestStatusUpdate(RideRequest r)
{
SqlConnection con = DACHelper.GetConnection();
SqlCommand cmd = new SqlCommand("RideRequest_Status_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RideId", r.RideId);
cmd.Parameters.AddWithValue("@Status", r.RequestStatus);
con.Open();
using (con)
{
Convert.ToInt32(cmd.ExecuteScalar());
}
}
public void DeleteRideRequest(RideRequest rr)
{
SqlCommand cmd = new SqlCommand("RideRequest_Delete", DACHelper.GetConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PassengerId", rr.PassengerId);
cmd.Parameters.AddWithValue("@RideId", rr.RideId);
cmd.Parameters.AddWithValue("@Status", rr.RequestStatus);
SqlConnection con = cmd.Connection;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public int Insert(Users u)
{
SqlConnection con = DACHelper.GetConnection();
SqlCommand cmd = new SqlCommand("User_Insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Std_EmpId", u.Student_EmployeeId);
cmd.Parameters.AddWithValue("@UserName", string.IsNullOrEmpty(u.UserName) ? Convert.DBNull : u.UserName);
cmd.Parameters.AddWithValue("@Phone", string.IsNullOrEmpty(u.Phone) ? Convert.DBNull : u.Phone);
cmd.Parameters.AddWithValue("@PhoneStatus", u.PhoneStatus);
cmd.Parameters.AddWithValue("@Password", string.IsNullOrEmpty(u.Password) ? Convert.DBNull : u.Password);
con.Open();
using (con)
{
int row = Convert.ToInt32(cmd.ExecuteScalar());
return(row);
}
}
public void RatingUpdate(Rating r)
{
SqlConnection con = DACHelper.GetConnection();
SqlCommand cmd = new SqlCommand("Rating_Update", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@OneStar", r.OneStar);
cmd.Parameters.AddWithValue("@TwoStar", r.TwoStar);
cmd.Parameters.AddWithValue("@ThreeStar", r.ThreeStar);
cmd.Parameters.AddWithValue("@FourStar", r.FourStar);
cmd.Parameters.AddWithValue("@FiveStar", r.FiveStar);
cmd.Parameters.AddWithValue("@UserId", r.UserId);
con.Open();
using (con)
{
Convert.ToInt32(cmd.ExecuteScalar());
}
}
public int Insert(Rating r)
{
SqlConnection con = DACHelper.GetConnection();
SqlCommand cmd = new SqlCommand("Rating_Insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@OneStar", r.OneStar.HasValue ? r.OneStar : 0);
cmd.Parameters.AddWithValue("@TwoStar", r.TwoStar.HasValue ? r.TwoStar : 0);
cmd.Parameters.AddWithValue("@ThreeStar", r.ThreeStar.HasValue ? r.ThreeStar : 0);
cmd.Parameters.AddWithValue("@FourStar", r.FourStar.HasValue ? r.FourStar : 0);
cmd.Parameters.AddWithValue("@FiveStar", r.FiveStar.HasValue ? r.FiveStar : 0);
cmd.Parameters.AddWithValue("@UserId", r.UserId);
con.Open();
using (con)
{
int row = Convert.ToInt32(cmd.ExecuteScalar());
return(row);
}
}
public int Insert(RideRequest r)
{
SqlConnection con = DACHelper.GetConnection();
SqlCommand cmd = new SqlCommand("RideRequest_Insert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RideId", r.RideId);
cmd.Parameters.AddWithValue("@PassengerId", r.PassengerId);
cmd.Parameters.AddWithValue("@Status", r.RequestStatus);
cmd.Parameters.AddWithValue("@PickupLocation", r.PickupLocation);
cmd.Parameters.AddWithValue("@Destination", r.Destination);
cmd.Parameters.AddWithValue("@TotalAmount", r.TotalAmount);
con.Open();
using (con)
{
int row = Convert.ToInt32(cmd.ExecuteScalar());
return(row);
}
}
public List <RideRequest> SelectAllPendingRideRequestByRideId(int rid)
{
SqlCommand cmd = new SqlCommand(SELECT_ALL_PENDING_RIDE_REQUEST_BY_RIDEID, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@RideId", rid);
cmd.Parameters.AddWithValue("@Status", 1);
List <RideRequest> rlist = fetchRideRequest(cmd);
return(rlist);
}
public List <Users> SelectAllUsers()
{
SqlCommand cmd = new SqlCommand(SELECT_ALL_USERS, DACHelper.GetConnection());
return(fetchUsers(cmd));
}
public RideRequest SelectRequestByRideIdAndPassengerId(int pid, int rid)
{
SqlCommand cmd = new SqlCommand(SELECT_EXISTING_REQUEST_BY_PASSENGERID_AND_RIDEID, DACHelper.GetConnection());
cmd.Parameters.AddWithValue("@RideId", rid);
cmd.Parameters.AddWithValue("@PassengerId", pid);
List <RideRequest> temp = fetchRideRequest(cmd);
return((temp != null) ? temp[0] : null);
}
