public bool AuthenticateUser(string username, string password) { bool allowAccess = false; string conString = GetConnection.GetConnectionString(); SqlConnection con = new SqlConnection(conString); string query = "prc_authenticateUser"; con.Open(); SqlDataReader dr; SqlCommand cmd = new SqlCommand(query, con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@username", username); cmd.Parameters.AddWithValue("@password", password); dr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(dr); for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["CustomerId"].ToString().Equals(username)) { allowAccess = true; } } return(allowAccess); }
/// <summary> /// Search flight by specifying maximum price. For eg if a user enters /// Rs 10000 flights with a fare below Rs 10000 is shown /// </summary> /// <param name="source"></param> /// <param name="destination"></param> /// <param name="travelClass"></param> /// <param name="dateOfJourney"></param> /// <param name="numberOfAdults"></param> /// <param name="numberOfChildren"></param> /// <param name="maxPrice"></param> /// <returns></returns> public DataTable FilterByPrice ( string source, string destination, string travelClass, string dateOfJourney, int numberOfAdults, int numberOfChildren, int maxPrice ) { DataTable dt = new DataTable(); SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); SqlDataReader dr; SqlCommand cmd; try { //Establish connection with db con.Open(); string query1 = "prc_displayFlightsFilterByPrice"; //string query1 = "select * from Flight"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; int numberOfPassengers = numberOfAdults + numberOfChildren; cmd.Parameters.AddWithValue("@source", source); cmd.Parameters.AddWithValue("@destination", destination); cmd.Parameters.AddWithValue("@dateOfJourney", dateOfJourney); cmd.Parameters.AddWithValue("@numberOfPassengers", numberOfPassengers); cmd.Parameters.AddWithValue("@class", travelClass); cmd.Parameters.AddWithValue("@maxPrice", maxPrice); dr = cmd.ExecuteReader(); dt.Load(dr); dt.Columns.Add("Fare"); for (int i = 0; i < dt.Rows.Count; i++) { int a = Convert.ToInt32(dt.Rows[i]["AdultPrice"]); a = numberOfAdults * a; int b = Convert.ToInt32(dt.Rows[i]["ChildPrice"]); b = numberOfChildren * b; int c = a + b; dt.Rows[i]["Fare"] = c; } } catch (Exception ex) { Console.WriteLine(ex); } return(dt); }
public int updateBookedTicketBeforePayment ( string FlightId, string travelClass, string DateOfJourney, string DateOfBooking, int NumberOfAdults, int NumberOfChildren, string TicketFare, string CustomerId, string ReferenceId, string BookingStatus, string PaymentStatus, int AdultPrice, int ChildPrice ) { SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); SqlCommand cmd; int cnt = 0; try { //Establish connection with db con.Open(); string query1 = "prc_BookingStatusBeforePayment"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@FlightId", FlightId); cmd.Parameters.AddWithValue("@Class", travelClass); cmd.Parameters.AddWithValue("@DateOfJourney", DateOfJourney); cmd.Parameters.AddWithValue("@DateOfBooking", DateOfBooking); cmd.Parameters.AddWithValue("@NumberOfAdults", NumberOfAdults); cmd.Parameters.AddWithValue("@NumberOfChildren", NumberOfChildren); cmd.Parameters.AddWithValue("@TicketFare", TicketFare); cmd.Parameters.AddWithValue("@CustomerId", CustomerId); cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId); cmd.Parameters.AddWithValue("@BookingStatus", BookingStatus); cmd.Parameters.AddWithValue("@PaymentStatus", PaymentStatus); cmd.Parameters.AddWithValue("@AdultPrice", AdultPrice); cmd.Parameters.AddWithValue("@ChildPrice", ChildPrice); cnt = cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex); } return(cnt); }
public void ConfirmBooking(string ReferenceId, string BookingStatus, string PaymentStatus, string FlightId, string DateOfFlight, string travelClass, int numberOfPassengers) { SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); SqlCommand cmd; int cnt = 0; con.Open(); //SqlTransaction transaction; //transaction=con.BeginTransaction(); string query1 = "prc_ConfirmBooking"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId); cmd.Parameters.AddWithValue("@BookingStatus", BookingStatus); cmd.Parameters.AddWithValue("@PaymentStatus", PaymentStatus); cnt = cmd.ExecuteNonQuery(); string query2 = "prc_ConfirmPassengers"; cmd = new SqlCommand(query2, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId); cmd.Parameters.AddWithValue("@BookingStatus", BookingStatus); cnt = cmd.ExecuteNonQuery(); string query3 = "prc_DecrementAvailableSeats"; cmd = new SqlCommand(query3, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@FlightId", FlightId); cmd.Parameters.AddWithValue("@Class", travelClass); cmd.Parameters.AddWithValue("@DateOfFlight", DateOfFlight); cmd.Parameters.AddWithValue("@numberOfPassengers", numberOfPassengers); cnt = cmd.ExecuteNonQuery(); //transaction.Commit(); }
//Checking Email Id Of User And Verifying Whether It is present or not public int CheckingEmailIdInDataBase(string EmailId) { int cnt = 0; SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); SqlCommand cmd; try { //Establish connection with db con.Open(); string query1 = "Prc_EmailIdMatching"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EmailId", EmailId); var returnParameter = cmd.Parameters.Add("@Exists", SqlDbType.Int); returnParameter.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); int j = Convert.ToInt32(returnParameter.Value); //int j=Convert.ToInt32(cmd.ExecuteScalar()); if (j == 1) { cnt = j; } else { cnt = j; } } catch (Exception) { } return(cnt); }//checkEmail
/// <summary> /// A method for getting search results /// parameters taken in constructor /// 6 parameters are - source,destination,class,dateofjourney,dateofreturn,nadults,ninfants /// </summary> /// <returns>Data Table</returns> public DataTable SearchFlights() { DataTable dtSearchFlight = new DataTable(); SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); SqlDataReader dr; SqlCommand cmd; try { //Establish connection with db con.Open(); string query1 = "prc_displayFlights"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@source", source); cmd.Parameters.AddWithValue("@destination", destination); cmd.Parameters.AddWithValue("@dateOfJourney", dateOfJourney); cmd.Parameters.AddWithValue("@numberOfPassengers", numberOfPassengers); cmd.Parameters.AddWithValue("@class", travelClass); dr = cmd.ExecuteReader(); dtSearchFlight.Load(dr); dtSearchFlight.Columns.Add("Fare"); //Calculate total fare for (int i = 0; i < dtSearchFlight.Rows.Count; i++) { int adultprice = Convert.ToInt32(dtSearchFlight.Rows[i]["AdultPrice"]); adultprice = numberOfAdults * adultprice; int childprice = Convert.ToInt32(dtSearchFlight.Rows[i]["ChildPrice"]); childprice = numberOfChildren * childprice; int fare = childprice + adultprice; dtSearchFlight.Rows[i]["Fare"] = fare; } } catch (Exception ex) { Console.WriteLine(ex); } return(dtSearchFlight); }
public DataTable GetSource() { string conStr = GetConnection.GetConnectionString(); SqlConnection con = new SqlConnection(conStr); SqlDataReader dr; SqlCommand cmd; DataTable dt = new DataTable(); string query; con.Open(); query = "prc_SelectSource"; cmd = new SqlCommand(query, con); cmd.CommandType = CommandType.StoredProcedure; dr = cmd.ExecuteReader(); dt.Load(dr); con.Close(); return(dt); }
public DataTable GetDestination(string selectedSource) { string conStr = GetConnection.GetConnectionString(); SqlConnection con = new SqlConnection(conStr); SqlDataReader dr; SqlCommand cmd; DataTable dt = new DataTable(); string query; con.Open(); query = "prc_SelectDestination"; cmd = new SqlCommand(query, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@source", selectedSource); dr = cmd.ExecuteReader(); dt.Load(dr); con.Close(); return(dt); }
public int updatePassengersBeforePayment(int PassengerId, string ReferenceId, string PassengerName, int PassengerAge, char PassengerGender, string BookingStatus) { SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); SqlCommand cmd; int cnt = 0; try { //Establish connection with db con.Open(); string query1 = "prc_InsertPassengersBeforePayment"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@PassengerId", PassengerId); cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId); cmd.Parameters.AddWithValue("@PassengerName", PassengerName); cmd.Parameters.AddWithValue("@PassengerAge", PassengerAge); cmd.Parameters.AddWithValue("@PassengerGender", PassengerGender); cmd.Parameters.AddWithValue("@BookingStatus", BookingStatus); cnt = cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex); } return(cnt); }
public void CancelTicket(int PassengerId, string ReferenceId, string user) { try { SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); con.Open(); SqlCommand cmd; string query1 = "prc_UpdateCancelledStatus"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@PassengerId", PassengerId); cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId); cmd.Parameters.AddWithValue("@user", user); int cnt = cmd.ExecuteNonQuery(); con.Close(); } catch (Exception) { } }
public DataTable ViewBookingHistory(string CustomerId) { DataTable dt = new DataTable(); SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); con.Open(); SqlDataReader dr; SqlCommand cmd; string query1 = "prc_BookingHistory"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CustomerId", CustomerId); dr = cmd.ExecuteReader(); dt.Load(dr); con.Close(); return(dt); }
}//checkEmail public int SettingNewPassword(string EmailId, string pwd) { int n = 0; SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); SqlCommand cmd; try { //Establish connection with db con.Open(); string query1 = "Prc_SetNewUserPassword"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EmailId", EmailId); cmd.Parameters.AddWithValue("@NewPwd", pwd); n = cmd.ExecuteNonQuery(); //int j=Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception) { } return(n); }
public DataTable GetPassengers(string ReferenceId) { DataTable dtPassengers = new DataTable(); SqlConnection con = new SqlConnection(); con.ConnectionString = GetConnection.GetConnectionString(); SqlDataReader dr; SqlCommand cmd; con.Open(); string query1 = "prc_GetPassengerData"; cmd = new SqlCommand(query1, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ReferenceId", ReferenceId); dr = cmd.ExecuteReader(); dtPassengers.Load(dr); con.Close(); return(dtPassengers); }