Esempio n. 1
0
        public static List <string> getSlipsByCustomer(int ID)
        {
            List <string> leasedSlips = new List <string>();
            SqlConnection connection  = MarinaDBConnnection.GetConnection();

            try
            {
                string sql = "SELECT d.Name as DockName, d.ElectricalService + ',' + d.WaterService as Services, l.SlipID as Slip#, CONCAT_WS(' x ', s.Length, s.Width) as SlipDimensions(LxW);";
                sql += " FROM Lease l";
                sql += "JOIN Slip s on s.ID = l.SlipID";
                sql += "JOIN DOCK d on s.DockID = d.ID ";
                sql += "JOIN Customer c on c.ID = l.CustomerID";
                sql += "WHERE l.CustomerID = " + ID;
                sql += "ORDER BY l.ID DESC";

                SqlCommand    command = new SqlCommand(sql, connection);
                SqlDataReader reader  = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    leasedSlips.Add(reader["DockName"].ToString());            //0
                    leasedSlips.Add(reader["Services"].ToString());            //1
                    leasedSlips.Add(reader["Slip#"].ToString());               //2
                    leasedSlips.Add(reader["SlipDimensions(LxW)"].ToString()); //3
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                connection.Close();
            }
            return(leasedSlips);
        }
        //string username = UserName.Text;

        public static Customer GetCustomer(int ID)
        {
            SqlConnection connection = MarinaDBConnnection.GetConnection();
            Customer      customer   = new Customer();

            try
            {
                string sql = "SELECT ID, FirstName, LastName, Phone, City, UserName, Password" +
                             " FROM Customer " +
                             " WHERE ID =" + ID;
                SqlCommand    command = new SqlCommand(sql, connection);
                SqlDataReader reader  = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    customer.FirstName = reader["FirstName"].ToString();
                    customer.LastName  = reader["LastName"].ToString();
                    customer.Phone     = Convert.ToInt32(reader["Phone"].ToString());
                    customer.City      = reader["City"].ToString();
                    customer.UserName  = reader["UserName"].ToString();
                    customer.Password  = reader["Password"].ToString();
                    customer.ID        = Convert.ToInt32(reader["ID"].ToString());
                }
            }
            catch (Exception excp)
            {
                MessageBox.Show($"Something went wrong while trying to connect to your database, {excp}");
            }
            finally { connection.Close(); }
            return(customer);
        }
Esempio n. 3
0
        public static string getCustomerInfoBy(int ID)
        {
            String        customerName = "";
            SqlConnection connection   = MarinaDBConnnection.GetConnection();

            try
            {
                string sql = "SELECT FirstName + ', ' + LastName as FullName ";
                sql += "FROM Customer WHERE ID = " + ID;

                SqlCommand    command = new SqlCommand(sql, connection);
                SqlDataReader reader  = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    customerName = reader["FullName"].ToString();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                connection.Close();
            }
            return(customerName);
        }
Esempio n. 4
0
        public static List <string> getSlipInfoBy(int ID)
        {
            List <string> slipInfo   = new List <string>();
            SqlConnection connection = MarinaDBConnnection.GetConnection();

            try
            {
                string sql = "SELECT s.ID, s.Length, s.Width, d.Name, d.ElectricalService, d.WaterService ";
                sql += "FROM Slip s ";
                sql += "JOIN DOCK d on s.DockID = d.ID ";
                sql += "WHERE s.ID = " + ID;

                SqlCommand    command = new SqlCommand(sql, connection);
                SqlDataReader reader  = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    slipInfo.Add(reader["ID"].ToString());                //0
                    slipInfo.Add(reader["Length"].ToString());            //1
                    slipInfo.Add(reader["Width"].ToString());             //2
                    slipInfo.Add(reader["Name"].ToString());              //3
                    slipInfo.Add(reader["ElectricalService"].ToString()); //4
                    slipInfo.Add(reader["WaterService"].ToString());      //5
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                connection.Close();
            }
            return(slipInfo);
        }
Esempio n. 5
0
        public static bool LeaseSlip(int slipID, int CustomerID)
        {
            string     sql     = "INSERT INTO Lease (SlipID, CustomerID) VALUES (@slipID, @customerID)";
            SqlCommand command = new SqlCommand(sql);

            command.Parameters.AddWithValue("@slipID", slipID);
            command.Parameters.AddWithValue("@customerID", CustomerID);
            return(MarinaDBConnnection.insertRow(command));
        }
        public static bool isCustomerExist(string username)
        {
            SqlConnection connection = MarinaDBConnnection.GetConnection();
            string        sql        = "SELECT * FROM Customer WHERE UserName = @UserName";
            SqlCommand    command    = new SqlCommand(sql, connection);

            command.Parameters.AddWithValue("@UserName", username);
            SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            return(reader.HasRows);
        }
        public static void AddCustomer(string FirstName, string LastName, string Phone, string City, string UserName, string Password)
        {
            string sql = "INSERT INTO Customer" +
                         " (FirstName, LastName, Phone, City, UserName, Password) " +
                         " VALUES " +
                         "(@FirstName, @LastName, @Phone, @City, @UserName, @Password)";

            SqlConnection connection = MarinaDBConnnection.GetConnection();
            SqlCommand    command    = new SqlCommand(sql, connection);

            command.Parameters.AddWithValue("@FirstName", FirstName);
            command.Parameters.AddWithValue("@LastName", LastName);
            command.Parameters.AddWithValue("@Phone", Phone);
            command.Parameters.AddWithValue("@City", City);
            command.Parameters.AddWithValue("@UserName", UserName);
            command.Parameters.AddWithValue("@Password", Password);
            command.ExecuteNonQuery();
        }
Esempio n. 8
0
        public static List <Dock> getDocks()
        {
            DataTable   dt    = MarinaDBConnnection.getRows("SELECT ID, Name, WaterService, ElectricalService FROM Dock");
            List <Dock> docks = new List <Dock>();

            foreach (DataRow dr in dt.Rows)
            {
                int         id                = (int)dr["ID"];
                string      name              = dr["Name"].ToString();
                bool        waterService      = (bool)dr["WaterService"];
                bool        electricalService = (bool)dr["ElectricalService"];
                List <Slip> slips             = getAvailableSlips(id);

                Dock dock = new Dock(id, name, waterService, electricalService, slips);
                docks.Add(dock);
            }
            return(docks);
        }
Esempio n. 9
0
        private static List <Slip> getAvailableSlips(int dockID)
        {
            string sql = "SELECT s.ID, s.Width, s.Length ";

            sql += "FROM Slip as s ";
            sql += "left Join Lease as l on l.SlipID = s.ID ";
            sql += "WHERE l.SlipID is NULL AND s.DockID = " + dockID.ToString();
            DataTable   dt    = MarinaDBConnnection.getRows(sql);
            List <Slip> slips = new List <Slip>();

            foreach (DataRow dr in dt.Rows)
            {
                int id     = (int)dr["ID"];
                int width  = (int)dr["Width"];
                int length = (int)dr["Length"];

                Slip slip = new Slip(id, width, length);
                slips.Add(slip);
            }
            return(slips);
        }