Ejemplo n.º 1
0
        public static List <Lease> GetCustomerLeases()
        {
            List <Lease> leases = new List <Lease>(); //empty list
            Lease        ls;                          //just for reading      variables expressed before the commands!

            // Dummy variable for the customer ID - We need to use Session["ID"] - Not working so far.

            int custID = (int)HttpContext.Current.Session["ID"];


            //create the connection
            SqlConnection connection = MarinaDB.GetConnection();


            //create the command  for SELECT query to get the states
            string query = "SELECT ID, SlipID, CustomerID " +
                           "FROM Lease " +
                           "WHERE CustomerID = " + custID +
                           " ORDER by ID";

            SqlCommand cmd = new SqlCommand(query, connection);

            try
            {
                //open the connection
                connection.Open();
                //run the command
                SqlDataReader reader = cmd.ExecuteReader(); //built-in

                //each state data returned, make state object and add to the list
                while (reader.Read()) //while there still is data to read
                {
                    ls            = new Lease();
                    ls.ID         = (int)reader["ID"]; //[]  indexer from chapter 13
                    ls.SlipID     = (int)reader["SlipID"];
                    ls.CustomerID = (int)reader["CustomerID"];
                    leases.Add(ls);
                }
                reader.Close();
            }
            catch (Exception ex)  //error
            {
                throw ex;
            }
            finally  //executes always
            {
                connection.Close();
            }

            //return the list of states
            return(leases);
        }
Ejemplo n.º 2
0
        // update customer
        // retirn indicator of success
        public static bool UpdateCustomer(Customer old_Customer, Customer customer) // changed Customer names in brackets
        {
            bool success = false;                                                   // did not update

            // connection
            SqlConnection connection = MarinaDB.GetConnection();
            // update command
            string updateStatement =
                "UPDATE Customers SET " +
                "FirstName = @NewFirstName, " +
                "LastName = @NewLastName, " +
                "Phone= @NewPhone, " +
                "City = @NewCity, " +
                "WHERE CustomerID = @OldCustomerID " + // identifies ccustomer
                "AND FirstName = @OldFirstName " +     // remaining - for otimistic concurrency
                "AND LastName = @OldLastName " +
                "AND Phone = @OldPhone " +
                "AND City = @OldCity ";
            SqlCommand cmd = new SqlCommand(updateStatement, connection);

            // change customer.Name, etc to match line 164
            // change properties for Update Method, DataObjectName, Conflict Detection to Compared All Values
            cmd.Parameters.AddWithValue("@NewFirstName", customer.FirstName);
            cmd.Parameters.AddWithValue("@NewLastName", customer.LastName);
            cmd.Parameters.AddWithValue("@NewPhone", customer.Phone);
            cmd.Parameters.AddWithValue("@NewCity", customer.City);
            cmd.Parameters.AddWithValue("@OldCustomerID", old_Customer.ID);
            cmd.Parameters.AddWithValue("@OldFirstName", old_Customer.FirstName);
            cmd.Parameters.AddWithValue("@OldLastName", customer.LastName);
            cmd.Parameters.AddWithValue("@OldPhone", old_Customer.Phone);
            cmd.Parameters.AddWithValue("@OldCity", old_Customer.City);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    success = true; // updated
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(success);
        }
Ejemplo n.º 3
0
        // retrieve customer with given ID
        public static List <Customer> GetCustomersByLease(int ID)
        {
            List <Customer> customers = new List <Customer>(); // empty list
            Customer        cust      = null;                  // for reading

            // create connection
            SqlConnection connection = MarinaDB.GetConnection();

            // create SELECT command
            string query = "SELECT CustomerID, FirstName, LastName, Phone, City " +
                           "FROM Customers " +
                           "WHERE Lease = @ID";
            SqlCommand cmd = new SqlCommand(query, connection);

            // supply parameter value
            cmd.Parameters.AddWithValue("@ID", ID);

            // run the SELECT query
            try
            {
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                // add customer to the list
                while (reader.Read()) // while there are customers
                {
                    cust           = new Customer();
                    cust.ID        = (int)reader["CustomerID"];
                    cust.FirstName = reader["FirstName"].ToString();
                    cust.LastName  = reader["LastName"].ToString();
                    cust.Phone     = reader["Phone"].ToString();
                    cust.City      = reader["City"].ToString();
                    customers.Add(cust);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(customers);
        }
Ejemplo n.º 4
0
        //get customer
        //check if login is successfull
        //return customer ID if exist, otherwise, return -1
        //[DataObjectMethod(DataObjectMethodType.Select)]
        public static int getCustomer(string userName, string password)
        {
            int id = -1; //default to negative value

            string sql = "SELECT ID " +
                         "FROM Customer " + "WHERE Username = @uName AND Password = @pwd";

            SqlConnection connection = MarinaDB.GetConnection();

            SqlCommand cmd = new SqlCommand(sql, connection);


            cmd.Parameters.AddWithValue("@uName", userName);
            cmd.Parameters.AddWithValue("@pwd", password);

            try
            {
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                // build customer object to return
                if (reader.Read()) // if there is a customer with this ID
                {
                    Customer cust = new Customer();
                    //fill data from reader
                    cust.ID = (int)reader["ID"];
                    id      = cust.ID;
                    //cust.Name = reader["Name"].ToString();
                    //cust.Address = reader["Address"].ToString();
                    //cust.City = reader["City"].ToString();
                    //cust.State = reader["State"].ToString();
                    //cust.ZipCode = reader["ZipCode"].ToString();
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(id);
        }
Ejemplo n.º 5
0
        public static bool DeleteCustomer(Customer cust)
        {
            bool success = false;

            // create connection
            SqlConnection connection = MarinaDB.GetConnection();

            // create DELETE command
            string deleteStatement =
                "DELETE FROM Customers " +
                "WHERE CustomerID = @CustomerID " + // needed for identification
                "AND FirstName = @FirstName " +     // the rest - for optimistic concurrency
                "AND LastName = @LastName " +
                "AND Phone = @Phone " +
                "AND City = @City ";
            SqlCommand cmd = new SqlCommand(deleteStatement, connection);

            cmd.Parameters.AddWithValue("@CustomerID", cust.ID);
            cmd.Parameters.AddWithValue("@FirstName", cust.FirstName);
            cmd.Parameters.AddWithValue("@LastName", cust.LastName);
            cmd.Parameters.AddWithValue("@Phone", cust.Phone);
            cmd.Parameters.AddWithValue("@City", cust.City);

            try
            {
                connection.Open();

                // execute the command
                int count = cmd.ExecuteNonQuery();
                // check if successful
                if (count > 0)
                {
                    success = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(success);
        }
Ejemplo n.º 6
0
        // insert new row to table Customers
        // return new CustomerID
        public static int AddCustomer(Customer cust)
        {
            int custID = 0;

            // create connection
            SqlConnection connection = MarinaDB.GetConnection();

            // create INSERT command
            // CustomerID is IDENTITY so no value provided
            string insertStatement =
                "INSERT INTO Customers(FirstName, LastName, Phone, City) " +
                "OUTPUT inserted.CustomerID " +
                "VALUES(@FirstName, @LastName, @Phone, @City)";
            SqlCommand cmd = new SqlCommand(insertStatement, connection);

            cmd.Parameters.AddWithValue("@FirstName", cust.FirstName);
            cmd.Parameters.AddWithValue("@LastName", cust.LastName);
            cmd.Parameters.AddWithValue("@Phone", cust.Phone);
            cmd.Parameters.AddWithValue("@City", cust.City);

            try
            {
                connection.Open();

                // execute insert command and get inserted ID
                custID = (int)cmd.ExecuteScalar();
                //cmd.ExecuteNonQuery();

                // retrieve generate customer ID to return
                //string selectStatement =
                //    "SELECT IDENT_CURRENT('Customers')";
                //SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
                //custID = Convert.ToInt32(selectCmd.ExecuteScalar()); // returns single value
                //         // (int) does not work in this case
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(custID);
        }
Ejemplo n.º 7
0
        public static List <Slip> GetSlips()
        {
            List <Slip> slips = new List <Slip>(); //empty list
            Slip        s;                         //just for reading      variables expressed before the commands!
            //create the connection
            SqlConnection connection = MarinaDB.GetConnection();

            //create the command  for SELECT query to get the states
            string query = "SELECT ID, Width, Length, DockID, BookingStatus " +
                           "FROM Slip " +
                           "ORDER by Id";

            SqlCommand cmd = new SqlCommand(query, connection);

            try
            {
                //open the connection
                connection.Open();
                //run the command
                SqlDataReader reader = cmd.ExecuteReader(); //built-in

                //each state data returned, make state object and add to the list
                while (reader.Read()) //while there still is data to read
                {
                    s               = new Slip();
                    s.ID            = (int)reader["ID"]; //[]  indexer from chapter 13
                    s.Width         = (int)(reader["Width"]);
                    s.Length        = (int)(reader["Length"]);
                    s.DockID        = (int)(reader["DockID"]);
                    s.BookingStatus = (int)(reader["BookingStatus"]);
                    slips.Add(s);
                }
                reader.Close();
            }
            catch (Exception ex)  //error
            {
                throw ex;
            }
            finally  //executes always
            {
                connection.Close();
            }

            //return the list of slips
            return(slips);
        }
Ejemplo n.º 8
0
        public static List <Customer> GetCustomer()
        {
            List <Customer> customers = new List <Customer>(); //empty list
            Customer        cstmr;                             //just for reading      variables expressed before the commands!
            //create the connection
            SqlConnection connection = MarinaDB.GetConnection();

            //create the command  for SELECT query to get the states
            string query = "SELECT CustomerID, FirstName, LastName, Phone, City " +
                           "FROM Customers " +
                           "ORDER by LastName";

            SqlCommand cmd = new SqlCommand(query, connection);

            try
            {
                //open the connection
                connection.Open();
                //run the command
                SqlDataReader reader = cmd.ExecuteReader(); //built-in

                //each state data returned, make state object and add to the list
                while (reader.Read()) // while there are customers
                {
                    cstmr           = new Customer();
                    cstmr.ID        = (int)reader["CustomerID"];
                    cstmr.FirstName = reader["FirstName"].ToString();
                    cstmr.LastName  = reader["LastName"].ToString();
                    cstmr.Phone     = reader["Phone"].ToString();
                    cstmr.City      = reader["City"].ToString();
                    customers.Add(cstmr);
                }
                reader.Close();
            }
            catch (Exception ex)  //error
            {
                throw ex;
            }
            finally  //executes always
            {
                connection.Close();
            }

            //return the list of states
            return(customers);
        }
Ejemplo n.º 9
0
        public static List <Dock> GetSelectedDocks(int ID)
        {
            List <Dock> docks = new List <Dock>(); //empty list
            Dock        d;                         //just for reading      variables expressed before the commands!
            //create the connection
            SqlConnection connection = MarinaDB.GetConnection();

            //create the command  for SELECT query to get the states
            string query = "SELECT ID, Name, WaterService, ElectricalService " +
                           "FROM Dock " +
                           "WHERE ID = @ID";

            SqlCommand cmd = new SqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@ID", ID);
            try
            {
                //open the connection
                connection.Open();
                //run the command
                SqlDataReader reader = cmd.ExecuteReader(); //built-in

                //each state data returned, make state object and add to the list
                while (reader.Read()) //while there still is data to read
                {
                    d                   = new Dock();
                    d.ID                = (int)reader["ID"]; //[]  indexer from chapter 13
                    d.Name              = reader["Name"].ToString();
                    d.WaterService      = reader["WaterService"].ToString();
                    d.ElectricalService = reader["ElectricalService"].ToString();
                    docks.Add(d);
                }
                reader.Close();
            }
            catch (Exception ex)  //error
            {
                throw ex;
            }
            finally  //executes always
            {
                connection.Close();
            }

            //return the list of states
            return(docks);
        }
Ejemplo n.º 10
0
        public static Slip UpdateSlipStatus(int slipID)
        {
            Slip slip = null;

            // create connection
            SqlConnection connection = MarinaDB.GetConnection();

            // create SELECT command
            string query = "SELECT ID, Width, Length, DockID, BookingStatus " +
                           "FROM Slip " +
                           "WHERE ID = " + slipID;
            SqlCommand cmd = new SqlCommand(query, connection);

            // supply parameter value
            cmd.Parameters.AddWithValue("@CustomerID", slipID);

            // run the SELECT query
            try
            {
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                // build customer object to return
                if (reader.Read()) // if there is a customer with this ID
                {
                    slip               = new Slip();
                    slip.ID            = slipID;
                    slip.Width         = (int)reader["Width"];
                    slip.Length        = (int)reader["Length"];
                    slip.DockID        = (int)reader["DockID"];
                    slip.BookingStatus = 1;
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(slip);
        }
Ejemplo n.º 11
0
        public static bool AddLease(int slipID) // changed Customer names in brackets
        {
            bool success = false;               // did not update

            int custID = (int)HttpContext.Current.Session["ID"];

            //int custID = 1;

            // connection
            SqlConnection connection = MarinaDB.GetConnection();
            // update command
            string insertStatement =
                "INSERT INTO Lease(SlipID, CustomerID) " +
                "VALUES(@SlipID, @CustomerID)";

            SqlCommand cmd = new SqlCommand(insertStatement, connection);

            cmd.Parameters.AddWithValue("@SlipID", slipID);
            cmd.Parameters.AddWithValue("@CustomerID", custID);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    success = true; // updated
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(success);
        }
Ejemplo n.º 12
0
        public static bool UpdateSlip(int slipID) // changed Customer names in brackets
        {
            bool success = false;                 // did not update

            // connection
            SqlConnection connection = MarinaDB.GetConnection();
            // update command
            string updateStatement =
                "UPDATE Slip SET " +
                "BookingStatus = @BookingStatus " +
                "WHERE ID = @ID";
            SqlCommand cmd = new SqlCommand(updateStatement, connection);

            // change customer.Name, etc to match line 164
            // change properties for Update Method, DataObjectName, Conflict Detection to Compared All Values
            cmd.Parameters.AddWithValue("@BookingStatus", 1);
            cmd.Parameters.AddWithValue("@ID", slipID);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    success = true; // updated
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(success);
        }
Ejemplo n.º 13
0
        // retrieve customers from given state
        public static List <Slip> GetSlipsByDocks(int dockID)
        {
            List <Slip> slips = new List <Slip>(); // empty list
            Slip        slip  = null;              // for reading

            // create connection
            SqlConnection connection = MarinaDB.GetConnection();

            // create SELECT command
            string query = "SELECT ID, Width, Length, DockID, BookingStatus " +
                           "FROM Slip " +
                           "WHERE DockID = @DockID AND BookingStatus =0";

            SqlCommand cmd = new SqlCommand(query, connection);

            // supply parameter value
            cmd.Parameters.AddWithValue("@DockID", dockID);

            // run the SELECT query

            connection.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            // add customers to the list
            while (reader.Read()) // while there are customers
            {
                slip               = new Slip();
                slip.ID            = (int)reader["ID"];
                slip.Width         = (int)reader["Width"];
                slip.Length        = (int)reader["Length"];
                slip.DockID        = (int)reader["DockID"];
                slip.BookingStatus = (int)reader["BookingStatus"];
                slips.Add(slip);
            }
            reader.Close();
            return(slips);
        }
Ejemplo n.º 14
0
        public static List <Slip> GetAvailableSlipsDoNotUse()
        {
            List <Slip> slips = new List <Slip>(); //empty list
            Slip        s;                         //just for reading      variables expressed before the commands!
                                                   //create the connection
            int selectedDockID;

            //We need to know which dock has been selected
            System.Web.UI.Page currentPage = HttpContext.Current.Handler as System.Web.UI.Page;

            //Find controls of the LeaseSlip.aspx here
            DropDownList dropDown = (DropDownList)currentPage.FindControl("ddlLeaseSlipDock");

            //Sets the selectedDockID value to the index of the selected item in the dropdownlist
            selectedDockID = dropDown.SelectedIndex + 1;



            SqlConnection connection = MarinaDB.GetConnection();

            //create the command  for SELECT query to get the states
            string query = "SELECT ID, Width, Length, DockID, BookingStatus " +
                           "FROM Slip " +
                           "WHERE (BookingStatus = 0 " +
                           "AND DockID = " + selectedDockID + ") " +
                           "ORDER by Id";

            SqlCommand cmd = new SqlCommand(query, connection);

            try
            {
                //open the connection
                connection.Open();
                //run the command
                SqlDataReader reader = cmd.ExecuteReader(); //built-in

                //each state data returned, make state object and add to the list
                while (reader.Read()) //while there still is data to read
                {
                    s               = new Slip();
                    s.ID            = (int)reader["ID"]; //[]  indexer from chapter 13
                    s.Width         = (int)(reader["Width"]);
                    s.Length        = (int)(reader["Length"]);
                    s.DockID        = (int)(reader["DockID"]);
                    s.BookingStatus = (int)(reader["BookingStatus"]);
                    slips.Add(s);
                }
                reader.Close();
            }
            catch (Exception ex)  //error
            {
                throw ex;
            }
            finally  //executes always
            {
                connection.Close();
            }

            //return the list of slips
            return(slips);
        }