public static List <Purchases> GetPurchasesByEmail(string custEmail) { List <Purchases> purc = new List <Purchases>(); Purchases puchase = null; SqlConnection con = TravelExpertsDB.GetConnection(); var total = 0; // Create the SQL select statement. string selectStatement = " select b.BookingDate as BookingDate, (p.ProdName + ' ' + s.SupName) as name, " + "(bd.BasePrice + bd.AgencyCommission) as price " + "from Bookings b " + "inner " + "join BookingDetails bd on b.BookingId = bd.BookingId " + "inner join Customers c on b.CustomerId = c.CustomerId " + "inner join Products_Suppliers ps on bd.ProductSupplierId = ps.ProductSupplierId " + "inner join Products p on p.ProductId = ps.ProductId " + "inner join Suppliers s on s.SupplierId = ps.SupplierId " + "where c.CustEmail = @CustEmail " + "union " + "select b.BookingDate as BookingDate , p.PkgName as name, " + "(p.PkgBasePrice + p.PkgAgencyCommission) as price " + "from Bookings b " + "inner " + "join Packages p on b.PackageId = p.PackageId " + "inner join Customers c on b.CustomerId = c.CustomerId " + "where c.CustEmail = @CustEmail "; SqlCommand cmd = new SqlCommand(selectStatement, con); cmd.Parameters.AddWithValue("@CustEmail", custEmail); try { // Open the connection and run the insert command con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { puchase = new Purchases(); puchase.BookingDate = Convert.ToDateTime(reader["BookingDate"]); puchase.Name = reader["name"].ToString(); puchase.Price = Convert.ToDecimal(reader["price"]); purc.Add(puchase); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(purc); }
public static Customer GetCustomerByEmail(string custEmail) { Customer cust = null; SqlConnection con = TravelExpertsDB.GetConnection(); // Create the SQL select statement. string selectStatement = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, " + "CustCity, CustProv, CustPostal, CustCountry, " + "CustHomePhone, CustBusPhone, CustEmail " + "FROM Customers " + "WHERE CustEmail = @CustEmail"; SqlCommand cmd = new SqlCommand(selectStatement, con); cmd.Parameters.AddWithValue("@CustEmail", custEmail); try { // Open the connection and run the insert command con.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { cust = new Customer(); cust.CustomerId = Convert.ToInt32(reader["Customerid"]); cust.CustFirstName = reader["CustFirstName"].ToString(); cust.CustLastName = reader["CustLastName"].ToString(); cust.CustAddress = reader["CustAddress"].ToString(); cust.CustCity = reader["CustCity"].ToString(); cust.CustProv = reader["CustProv"].ToString(); cust.CustPostal = reader["CustPostal"].ToString(); cust.CustCountry = reader["CustCountry"].ToString(); cust.CustHomePhone = reader["CustHomePhone"].ToString(); cust.CustBusPhone = reader["CustBusPhone"].ToString(); cust.CustEmail = reader["CustEmail"].ToString(); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(cust); }
/// <summary> /// the function is used to update the data for the customer logged in /// </summary> /// <param name="oldCust"></param> /// <param name="newCust"></param> /// <returns></returns> public static bool updateCustomer(CustomerRegistration oldCust, CustomerRegistration newCust) { SqlConnection con = TravelExpertsDB.GetConnection(); string updateStatement = "update Customers set " + "CustFirstName = @newCustFirstName, " + "CustLastName = @newCustLastName, " + "CustAddress = @newCustAddress, " + "CustCity = @newCustCity, " + "CustProv = @newCustProv, " + "CustPostal = @newCustPostal, " + "CustCountry = @newCustCountry, " + "CustHomePhone = @newCustHomePhone, " + "CustBusPhone = @newCustBusPhone, " + "CustEmail = @newCustEmail " + "where CustomerID = @oldCustomerID"; SqlCommand cmd = new SqlCommand(updateStatement, con); cmd.Parameters.AddWithValue("@newCustFirstName", newCust.CustFirstName); cmd.Parameters.AddWithValue("@newCustLastName", newCust.CustLastName); cmd.Parameters.AddWithValue("@newCustAddress", newCust.CustAddress); cmd.Parameters.AddWithValue("@newCustCity", newCust.CustCity); cmd.Parameters.AddWithValue("@newCustProv", newCust.CustProv); cmd.Parameters.AddWithValue("@newCustPostal", newCust.CustPostal); cmd.Parameters.AddWithValue("@newCustCountry", newCust.CustCountry); cmd.Parameters.AddWithValue("@newCustHomePhone", newCust.CustHomePhone); cmd.Parameters.AddWithValue("@newCustBusPhone", newCust.CustBusPhone); cmd.Parameters.AddWithValue("@newCustEmail", newCust.CustEmail); cmd.Parameters.AddWithValue("@oldCustomerID", oldCust.CustomerID); try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute public static List <Booking> GetBooking(string CustomerID) { List <Booking> bookings = new List <Booking>(); // make an empty list Booking mybooking; // reference to new state object SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection // create select command string selectString = "Select Bookings.BookingID, BookingDate, TravelerCount, TripStart, TripEnd, [Description], Destination, BasePrice, CustomerId " + "From Bookings inner join BookingDetails on Bookings.BookingId = BookingDetails.BookingId " + "where CustomerId = @CustomerId " + "order by CustomerId"; SqlCommand selectCommand = new SqlCommand(selectString, connection); selectCommand.Parameters.AddWithValue("@CustomerId", CustomerID); try { // open connection connection.Open(); // run the select command and process the results adding states to the list SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read())// process next row { mybooking = new Booking(); mybooking.BookingId = (int)reader["BookingId"]; mybooking.BookingDate = (DateTime)reader["BookingDate"]; mybooking.TravelerCount = (double)reader["TravelerCount"]; mybooking.TripStart = (DateTime)reader["TripStart"]; mybooking.TripEnd = (DateTime)reader["TripEnd"]; mybooking.Description = reader["Description"].ToString(); mybooking.Destination = reader["Destination"].ToString(); mybooking.BasePrice = (decimal)reader["BasePrice"]; mybooking.CustomerId = (int)reader["CustomerId"]; bookings.Add(mybooking); //adding them to technician list } reader.Close(); // close reader } catch (Exception ex) { throw ex; // throw it to the form to handle } finally { connection.Close(); //close connection } return(bookings); // returns the technicans list }
public static int InsertCustomer(Customer newCust) { SqlConnection con = TravelExpertsDB.GetConnection(); // Create the SQL insert statement. Note that the CustomerId will be automatically // generated. The AgentId is left null at this point. string insertStatement = "INSERT INTO Customers " + "(CustFirstName, CustLastName, CustAddress, CustCity, " + "CustProv, CustPostal, CustCountry, CustHomePhone, " + "CustBusPhone, CustEmail) " + "VALUES (@CustFirstName, @CustLastName, @CustAddress, " + "@CustCity, @CustProv, @CustPostal, @CustCountry, " + "@CustHomePhone, @CustBusPhone, @CustEmail)"; SqlCommand cmd = new SqlCommand(insertStatement, con); cmd.Parameters.AddWithValue("@CustFirstName", newCust.CustFirstName); cmd.Parameters.AddWithValue("@CustLastName", newCust.CustLastName); cmd.Parameters.AddWithValue("@CustAddress", newCust.CustAddress); cmd.Parameters.AddWithValue("@CustCity", newCust.CustCity); cmd.Parameters.AddWithValue("@CustProv", newCust.CustProv); cmd.Parameters.AddWithValue("@CustPostal", newCust.CustPostal); cmd.Parameters.AddWithValue("@CustCountry", newCust.CustCountry); cmd.Parameters.AddWithValue("@CustHomePhone", newCust.CustHomePhone); cmd.Parameters.AddWithValue("@CustBusPhone", newCust.CustBusPhone); cmd.Parameters.AddWithValue("@CustEmail", newCust.CustEmail); try { // Open the connection and run the insert command con.Open(); cmd.ExecuteNonQuery(); // Get the CustomerId (primary key) of the newly inserted customer record string selectQuery = "SELECT IDENT_CURRENT('Customers') FROM Customers"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); int newCustID = Convert.ToInt32(selectCmd.ExecuteScalar()); return(newCustID); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute public static List <Package> GetPackage() { List <Package> packages = new List <Package>(); // make an empty list Package pkg; // reference to new state object SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection // create select command string selectString = "Select PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice " + "from Packages " + "order by PackageId"; SqlCommand selectCommand = new SqlCommand(selectString, connection); try { // open connection connection.Open(); // run the select command and process the results adding states to the list SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read())// process next row { pkg = new Package(); pkg.PackageId = (int)reader["PackageId"]; pkg.PkgName = reader["PkgName"].ToString(); pkg.PkgStartDate = (DateTime)reader["PkgStartDate"]; pkg.PkgEndDate = (DateTime)reader["PkgEndDate"]; pkg.PkgDesc = reader["PkgDesc"].ToString(); pkg.PkgBasePrice = (decimal)reader["PkgBasePrice"]; packages.Add(pkg); //adding them to package list } reader.Close(); // close reader } catch (Exception ex) { throw ex; // throw it to the form to handle } finally { connection.Close(); //close connection } return(packages); // returns the packages list }
/// <summary> /// gets the customer details depending on the customer ID /// </summary> /// <param name="customerEmail"></param> /// <returns></returns> public static CustomerRegistration GetCustomers(int customerID) { SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "select CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail from Customers " + "where CustomerID = @CustomerID"; SqlCommand cmd = new SqlCommand(selectStatement, con); cmd.Parameters.AddWithValue("@CustomerID", customerID); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { CustomerRegistration CR = new CustomerRegistration() { CustFirstName = reader["CustFirstName"].ToString(), CustLastName = reader["CustLastName"].ToString(), CustAddress = reader["CustAddress"].ToString(), CustCity = reader["CustCity"].ToString(), CustProv = reader["CustProv"].ToString(), CustPostal = reader["CustPostal"].ToString(), CustCountry = reader["CustCountry"].ToString(), CustHomePhone = reader["CustHomePhone"].ToString(), CustBusPhone = reader["CustBusPhone"].ToString(), CustEmail = reader["CustEmail"].ToString() }; return(CR); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute public static List <Agents> GetAgencyTwoAgents() { List <Agents> agents = new List <Agents>(); // make an empty list Agents agt; // reference to new state object SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection // create select command string selectString = "Select AgentId, AgtFirstName, AgtMiddleInitial, AgtLastName, AgtBusPhone, AgtEmail, AgtPosition " + "from Agents " + "where AgencyId = 2" + "order by AgentId"; SqlCommand selectCommand = new SqlCommand(selectString, connection); try { // open connection connection.Open(); // run the select command and process the results adding states to the list SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read())// process next row { agt = new Agents(); agt.AgentId = (int)reader["AgentId"]; agt.AgtFirstName = reader["AgtFirstName"].ToString(); agt.AgtMiddleInitial = reader["AgtMiddleInitial"].ToString(); agt.AgtLastName = reader["AgtLastName"].ToString(); agt.AgtBusPhone = reader["AgtBusPhone"].ToString(); agt.AgtEmail = reader["AgtEmail"].ToString(); agt.AgtPosition = reader["AgtPosition"].ToString(); agents.Add(agt); //adding them to technician list } reader.Close(); // close reader } catch (Exception ex) { throw ex; // throw it to the form to handle } finally { connection.Close(); //close connection } return(agents); // returns the technicans list }
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute public static List <PackageTotal> GetPackageTotal(string CustomerID) { List <PackageTotal> totals = new List <PackageTotal>(); // make an empty list PackageTotal tot; // reference to new state object SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection // create select command string selectString = "Select BasePrice, CustomerId " + "From Bookings inner join BookingDetails on Bookings.BookingId = BookingDetails.BookingId " + "where CustomerId = @CustomerId " + "order by CustomerId"; SqlCommand selectCommand = new SqlCommand(selectString, connection); selectCommand.Parameters.AddWithValue("@CustomerId", CustomerID); try { // open connection connection.Open(); // run the select command and process the results adding states to the list SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read())// process next row { tot = new PackageTotal(); tot.BasePrice = (decimal)reader["BasePrice"]; tot.CustomerId = (int)reader["CustomerId"]; totals.Add(tot); //adding them to totals list } reader.Close(); // close reader } catch (Exception ex) { throw ex; // throw it to the form to handle } finally { connection.Close(); //close connection } return(totals); // returns the totals list }
[DataObjectMethod(DataObjectMethodType.Select)] //data object method select attribute public static List <Booking> GetCustomerID() { List <Booking> customerId = new List <Booking>(); // make an empty list Booking cust; // reference to new state object SqlConnection connection = TravelExpertsDB.GetConnection(); // create connection // create select command string selectString = "Select distinct CustomerId " + "from Bookings " + "order by CustomerId"; SqlCommand selectCommand = new SqlCommand(selectString, connection); try { // open connection connection.Open(); // run the select command and process the results adding states to the list SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read())// process next row { cust = new Booking(); cust.CustomerId = (int)reader["CustomerId"]; customerId.Add(cust); //adding them to technician list } reader.Close(); // close reader } catch (Exception ex) { throw ex; // throw it to the form to handle } finally { connection.Close(); //close connection } return(customerId); // returns the technicans list }
public static int UpdateCustomer(Customer original_Customer, Customer customer) { int updateCount = 0; string sql = "UPDATE Customers " + "SET CustFirstName = @CustFirstName, " + "CustLastName = @CustLastName, " + "CustAddress = @CustAddress, " + "CustCity = @CustCity, " + "CustProv = @CustProv, " + "CustPostal = @CustPostal, " + "CustCountry = @CustCountry, " + "CustHomePhone = @CustHomePhone, " + "CustBusPhone = @CustBusPhone, " + "CustEmail = @CustEmail " + "WHERE CustomerID = @original_CustomerID " + "AND CustFirstName = @original_CustFirstName " + "AND CustLastName = @original_CustLastName " + "AND CustAddress = @original_CustAddress " + "AND CustCity = @original_CustCity " + "AND CustProv = @original_CustProv " + "AND CustPostal = @original_CustPostal " + "AND CustCountry = @original_CustCountry " + "AND CustHomePhone = @original_CustHomePhone " + "AND CustBusPhone = @original_CustBusPhone " + "AND CustEmail = @original_CustEmail"; SqlConnection con = TravelExpertsDB.GetConnection(); // { // using (SqlCommand cmd = new SqlCommand(sql, con)) SqlCommand cmd = new SqlCommand(sql, con); //{ cmd.Parameters.AddWithValue("@CustFirstName", customer.CustFirstName); cmd.Parameters.AddWithValue("@CustLastName", customer.CustLastName); cmd.Parameters.AddWithValue("@CustAddress", customer.CustAddress); cmd.Parameters.AddWithValue("@CustCity", customer.CustCity); cmd.Parameters.AddWithValue("@CustProv", customer.CustProv); cmd.Parameters.AddWithValue("@CustPostal", customer.CustPostal); cmd.Parameters.AddWithValue("@CustCountry", customer.CustCountry); cmd.Parameters.AddWithValue("@CustHomePhone", customer.CustHomePhone); cmd.Parameters.AddWithValue("@CustBusPhone", customer.CustBusPhone); cmd.Parameters.AddWithValue("@CustEmail", customer.CustEmail); cmd.Parameters.AddWithValue("@original_CustomerID", original_Customer.CustomerId); cmd.Parameters.AddWithValue("@original_CustFirstName", original_Customer.CustFirstName); cmd.Parameters.AddWithValue("@original_CustLastName", original_Customer.CustLastName); cmd.Parameters.AddWithValue("@original_CustAddress", original_Customer.CustAddress); cmd.Parameters.AddWithValue("@original_CustCity", original_Customer.CustCity); cmd.Parameters.AddWithValue("@original_CustProv", original_Customer.CustProv); cmd.Parameters.AddWithValue("@original_CustPostal", original_Customer.CustPostal); cmd.Parameters.AddWithValue("@original_CustCountry", original_Customer.CustCountry); cmd.Parameters.AddWithValue("@original_CustHomePhone", original_Customer.CustHomePhone); cmd.Parameters.AddWithValue("@original_CustBusPhone", original_Customer.CustBusPhone); cmd.Parameters.AddWithValue("@original_CustEmail", original_Customer.CustEmail); // } try { // Open the connection and run the insert command con.Open(); updateCount = cmd.ExecuteNonQuery(); // Get the CustomerId (primary key) of the newly inserted customer record return(updateCount); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }