C# DBConnector.CloseConnection方法代码示例

        public CustomerResource getCustomerById(int customerId)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getTicketCommand = new MySqlCommand();
            getTicketCommand.Connection = connector.connection;
            getTicketCommand.CommandText = "select * from Customer where id = @customer_id LIMIT 1";
            getTicketCommand.Parameters.AddWithValue("@customer_id", customerId);

            CustomerResource customerToReturn = null;
            try
            {
                MySqlDataReader reader = getTicketCommand.ExecuteReader();
                while (reader.Read())
                {
                    customerToReturn = ConvertSQLReaderRowToTicketResource(reader);
                }
                reader.Close();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            return customerToReturn;
        }

        public TicketAlterationResource GetAlterationsByTicketId(int ticketId)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getAlterationsCommand = new MySqlCommand();
            getAlterationsCommand.Connection = connector.connection;
            getAlterationsCommand.CommandText = @"SELECT * from Ticket_Alterations where ticket_id = @ticket_id ORDER BY order_index";
            getAlterationsCommand.Parameters.AddWithValue("@ticket_id", ticketId);

            List<TicketAlterationResourceItem> alterationItems = new List<TicketAlterationResourceItem>();
            try
            {
                MySqlDataReader reader = getAlterationsCommand.ExecuteReader();
                while (reader.Read())
                {
                    alterationItems.Add(new TicketAlterationResourceItem
                    {
                        Price = Convert.ToDouble(reader["price"]),
                        Quantity = Convert.ToInt32(reader["quantity"]),
                        Description = (String)reader["description"],
                        Taxable = Convert.ToInt32(reader["taxable"])
                    });
                }
                reader.Close();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            TicketAlterationResource alterationRes = TicketAlterationFactory.CreateTicketAlterationResource(alterationItems);
            return alterationRes;
        }

 public void DeleteAlterationsByTicketID(int ticketId)
 {
     DBConnector connector = new DBConnector();
     MySqlCommand deleteCommand = new MySqlCommand();
     deleteCommand.Connection = connector.connection;
     deleteCommand.CommandText = @"DELETE from Ticket_Alterations where ticket_id = @ticket_id";
     deleteCommand.Parameters.AddWithValue("@ticket_id", ticketId);
     try
     {
         deleteCommand.ExecuteNonQuery();
         connector.CloseConnection();
     }
     catch (MySqlException ex)
     {
         MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
     }
 }

        public void UpdateSetting(ConfigrationSetting setting)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand updateCommand = new MySqlCommand();
            updateCommand.Connection = connector.connection;
            updateCommand.CommandText = "UPDATE configuration SET value = @value WHERE setting = @setting";
            updateCommand.Parameters.AddWithValue("@setting", setting.Setting);
            updateCommand.Parameters.AddWithValue("@value", setting.Value);

            try
            {
                updateCommand.ExecuteNonQuery();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }
        }

        public void DeleteAndReinsertAlterations(TicketAlterationResource alterationResource, int ticketId)
        {
            DBConnector deleteConnector = new DBConnector();
            MySqlCommand deleteCommand = new MySqlCommand();
            deleteCommand.Connection = deleteConnector.connection;
            deleteCommand.CommandText = @"DELETE from Ticket_Alterations WHERE ticket_id = @ticket_id";
            deleteCommand.Parameters.AddWithValue("@ticket_id", ticketId);
            try
            {
                deleteCommand.ExecuteNonQuery();
                deleteConnector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error deleting alterations. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            InsertAlterations(alterationResource);
        }

        public double getTotalBilled(DateTime startDate, DateTime endDate, Boolean isOrder)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getBilledCommand = new MySqlCommand();
            getBilledCommand.Connection = connector.connection;

            string sql = @"SELECT price, taxable
                                                from Tickets tix
                                                join Ticket_Alterations ta
                                                on tix.ticket_id = ta.ticket_id
                                                AND date_in >= @start_date
                                                AND date_in <= @end_date ";

            if (isOrder)
            {
                sql += @"AND order_id != ''
                             AND order_id IS NOT NULL";
            }
            else
            {
                sql += @"AND (order_id = ''
                            or order_id IS NULL)";
            }

            getBilledCommand.CommandText = sql;
            getBilledCommand.Parameters.AddWithValue("@start_date", ConvertDateTimeToUTCString(startDate));
            getBilledCommand.Parameters.AddWithValue("@end_date", ConvertDateTimeToUTCString(endDate));

            try
            {
                MySqlDataReader reader = getBilledCommand.ExecuteReader();
                double total = getPriceTotalFromReader(reader);
                reader.Close();
                connector.CloseConnection();
                return total;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }
            return 0;
        }

        public IEnumerable<ConfigrationSetting> GetConfigrationSettings()
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getTicketsCommand = new MySqlCommand();
            getTicketsCommand.Connection = connector.connection;
            getTicketsCommand.CommandText = "SELECT * FROM  configuration";
            List<ConfigrationSetting> list = new List<ConfigrationSetting>();
            try
            {
                MySqlDataReader reader = getTicketsCommand.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(ConvertSQLReaderRowToConfigrationSetting(reader));
                }
                reader.Close();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            return list;
        }

        public double GetTotalPaidNonOrdersBetweenDates(DateTime startDate, DateTime endDate, Boolean taxable)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getPaidCommand = new MySqlCommand();
            getPaidCommand.Connection = connector.connection;
            getPaidCommand.CommandText = @"select price, taxable
                                            from Tickets tix
                                            join Ticket_Alterations ta
                                            on tix.ticket_id = ta.ticket_id
                                            AND completed_date >= @start_date
                                            AND completed_date <= @end_date
                                            AND taxable = @taxable
                                            AND status = 'd'
                                            AND (order_id = ''
                                            or order_id IS NULL)";
            getPaidCommand.Parameters.AddWithValue("@start_date", ConvertDateTimeToUTCString(startDate));
            getPaidCommand.Parameters.AddWithValue("@end_date", ConvertDateTimeToUTCString(endDate));
            int taxableInt = taxable == true ? 1 : 0;
            getPaidCommand.Parameters.AddWithValue("@taxable", taxableInt);

            try
            {
                MySqlDataReader reader = getPaidCommand.ExecuteReader();
                double total = getPriceTotalFromReader(reader);
                reader.Close();
                connector.CloseConnection();
                return total;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }
            return 0;
        }

        public List<TicketResource> GetActiveTicketsBetweenDates(DateTime startDate, DateTime endDate)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getTicketsCommand = new MySqlCommand();
            getTicketsCommand.Connection = connector.connection;
            getTicketsCommand.CommandText = "SELECT * from Tickets join Customer on Tickets.CustomerId = Customer.id where date_ready >= @startDate AND date_ready <= @endDate AND status = 'a'";
            getTicketsCommand.Parameters.AddWithValue("@startDate", ConvertDateTimeToUTCString(startDate));
            getTicketsCommand.Parameters.AddWithValue("@endDate", ConvertDateTimeToUTCString(endDate));

            List<TicketResource> ticketResources = new List<TicketResource>();
            try
            {
                MySqlDataReader reader = getTicketsCommand.ExecuteReader();
                while (reader.Read())
                {
                    ticketResources.Add(ConvertSQLReaderRowToTicketResource(reader));
                }
                reader.Close();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            return ticketResources;
        }

        public void UpdateTicket(TicketResource ticket)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand updateCommand = new MySqlCommand();
            updateCommand.Connection = connector.connection;
            updateCommand.CommandText = "UPDATE Tickets SET ticket_id = @ticket_id, status = @status ,last_modified_timestamp = @last_modified_timestamp, date_in = @date_in, date_ready = @date_ready, comments = @comments, total_price = @total_price, picked_up = @picked_up, deposit = @deposit, tailor_name = @tailor_name, order_id = @order_id, completed_date = @completed_date WHERE ticket_id = @ticket_id";
            updateCommand.Parameters.AddWithValue("@ticket_id", ticket.TicketId);
            updateCommand.Parameters.AddWithValue("@status", ticket.Status);
            updateCommand.Parameters.AddWithValue("@last_modified_timestamp", ConvertDateTimeToUTCString(DateTime.Now));
            updateCommand.Parameters.AddWithValue("@date_in", ConvertDateTimeToUTCString(ticket.DateIn));
            updateCommand.Parameters.AddWithValue("@date_ready", ConvertDateTimeToUTCString(ticket.DateReady));
            updateCommand.Parameters.AddWithValue("@comments", ticket.Comments);
            updateCommand.Parameters.AddWithValue("@total_price", ticket.TotalPrice);
            updateCommand.Parameters.AddWithValue("@picked_up", ticket.PickedUp);
            updateCommand.Parameters.AddWithValue("@deposit", ticket.Deposit);
            updateCommand.Parameters.AddWithValue("@tailor_name", ticket.TailorName);
            updateCommand.Parameters.AddWithValue("@order_id", ticket.OrderId);
            updateCommand.Parameters.AddWithValue("@completed_date", ConvertDateTimeToUTCString(ticket.CompletedDate));

            try
            {
                updateCommand.ExecuteNonQuery();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }
        }

        /* @return the generated ticketID of the new ticket */
        public int InsertNewTicket(TicketResource ticket)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand insertCommand = new MySqlCommand();
            insertCommand.Connection = connector.connection;
            insertCommand.CommandText = @"INSERT into Tickets (ticket_id, status, last_modified_timestamp,        date_in, date_ready, comments,   total_price, picked_up, deposit, tailor_name, name_title, order_id, completed_date, CustomerId) values (@ticket_id, @status, @last_modified_timestamp, @date_in, @date_ready, @comments, @total_price, @picked_up, @deposit, @tailor_name, @name_title, @order_id, @completed_date, @CustomerId)";
            insertCommand.Parameters.AddWithValue("@ticket_id", ticket.TicketId);
            insertCommand.Parameters.AddWithValue("@status", ticket.Status);
            insertCommand.Parameters.AddWithValue("@last_modified_timestamp", ConvertDateTimeToUTCString(DateTime.Now));
            insertCommand.Parameters.AddWithValue("@date_in", ConvertDateTimeToUTCString(ticket.DateIn));
            insertCommand.Parameters.AddWithValue("@date_ready", ConvertDateTimeToUTCString(ticket.DateReady));
            insertCommand.Parameters.AddWithValue("@comments", ticket.Comments);

            insertCommand.Parameters.AddWithValue("@total_price", ticket.TotalPrice);
            insertCommand.Parameters.AddWithValue("@picked_up", ticket.PickedUp);
            insertCommand.Parameters.AddWithValue("@deposit", ticket.Deposit);
            insertCommand.Parameters.AddWithValue("@tailor_name", ticket.TailorName);
            insertCommand.Parameters.AddWithValue("@name_title", ticket.Title);
            insertCommand.Parameters.AddWithValue("@order_id", ticket.OrderId);
            insertCommand.Parameters.AddWithValue("@CustomerId", ticket.CustomerID);
            insertCommand.Parameters.AddWithValue("@completed_date", ConvertDateTimeToUTCString(ticket.CompletedDate));

            //MySqlCommand insertCustomerCommand = new MySqlCommand();
            //insertCustomerCommand.Connection = connector.connection;
            //insertCustomerCommand.CommandText = @"INSERT into Customer (first_name, middle_name, last_name, address, city, state, zip, phone, email) values (@first_name, @middle_name, @last_name, @address, @city, @state, @zip, @phone, @email)";
            //insertCustomerCommand.Parameters.AddWithValue("@first_name", ticket.FirstName);
            //insertCustomerCommand.Parameters.AddWithValue("@middle_name", ticket.MiddleName);
            //insertCustomerCommand.Parameters.AddWithValue("@last_name", ticket.LastName);
            //insertCustomerCommand.Parameters.AddWithValue("@address", ticket.Address);
            //insertCustomerCommand.Parameters.AddWithValue("@city", ticket.City);
            //insertCustomerCommand.Parameters.AddWithValue("@state", ticket.State);
            //insertCustomerCommand.Parameters.AddWithValue("@zip", ticket.Zip);
            //insertCustomerCommand.Parameters.AddWithValue("@phone", ticket.Telephone);
            //insertCustomerCommand.Parameters.AddWithValue("@email", ticket.Email);

            //race condition prone. First on list to convert to stored proc
            MySqlCommand getLastRowCommand = new MySqlCommand();
            getLastRowCommand.Connection = connector.connection;
            getLastRowCommand.CommandText = "SELECT ticket_id FROM Tickets ORDER BY ticket_id DESC LIMIT 1;";
            try
            {
                insertCommand.ExecuteNonQuery();
                //insertCustomerCommand.ExecuteNonQuery();
                MySqlDataReader reader = getLastRowCommand.ExecuteReader();
                int ticketId = 0;
                while (reader.Read())
                {
                    ticketId = (int)reader["ticket_id"];
                }
                connector.CloseConnection();
                reader.Close();
                return ticketId;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }
            return 1;
        }

        public void UpdateCustomer(CustomerResource customerResource)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand insertCommand = new MySqlCommand();
            insertCommand.Connection = connector.connection;
            insertCommand.CommandText = @"UPDATE Customer SET title = @title, first_name = @first_name, middle_name = @middle_name, last_name = @last_name, address = @address, address2 = @address2, city = @city, state = @state, zip = @zip, phone = @phone, email = @email where id = @customerId";
            insertCommand.Parameters.AddWithValue("@title", customerResource.Title);
            insertCommand.Parameters.AddWithValue("@first_name", customerResource.FirstName);
            insertCommand.Parameters.AddWithValue("@middle_name", customerResource.MiddleName);
            insertCommand.Parameters.AddWithValue("@last_name", customerResource.LastName);
            insertCommand.Parameters.AddWithValue("@address", customerResource.Address);
            insertCommand.Parameters.AddWithValue("@address2", customerResource.Address2);
            insertCommand.Parameters.AddWithValue("@city", customerResource.City);
            insertCommand.Parameters.AddWithValue("@state", customerResource.State);
            insertCommand.Parameters.AddWithValue("@zip", customerResource.Zip);
            insertCommand.Parameters.AddWithValue("@phone", customerResource.Telephone);
            insertCommand.Parameters.AddWithValue("@email", customerResource.Email);
            insertCommand.Parameters.AddWithValue("@customerId", customerResource.CustomerId);

            try
            {
                insertCommand.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            connector.CloseConnection();
        }

        public List<TicketResource> GetDistinctMailingAddressBetweenDates(DateTime startDate, DateTime endDate)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getTicketsCommand = new MySqlCommand();
            getTicketsCommand.Connection = connector.connection;
            getTicketsCommand.CommandText = "select distinct Address, first_name, last_name, city, state, zip from Tickets join Customer on Tickets.CustomerId = Customer.id where date_in >= @startDate AND date_in <= @endDate";
            getTicketsCommand.Parameters.AddWithValue("@startDate", ConvertDateTimeToUTCString(startDate));
            getTicketsCommand.Parameters.AddWithValue("@endDate", ConvertDateTimeToUTCString(endDate));

            List<TicketResource> ticketResources = new List<TicketResource>();
            try
            {
                MySqlDataReader reader = getTicketsCommand.ExecuteReader();
                while (reader.Read())
                {
                    TicketResource resource = new TicketResource();
                    resource.FirstName = Convert.ToString(reader["first_name"]);
                    resource.LastName = Convert.ToString(reader["last_name"]);
                    resource.Address = Convert.ToString(reader["address"]);
                    resource.City = Convert.ToString(reader["city"]);
                    resource.State = Convert.ToString(reader["state"]);
                    resource.Zip = Convert.ToString(reader["zip"]);
                    ticketResources.Add(resource);
                }
                reader.Close();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            return ticketResources;
        }

        public IEnumerable<AlterationToatalForDay> GetAlterationToatalForDays()
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getTicketsCommand = new MySqlCommand();
            getTicketsCommand.Connection = connector.connection;
            getTicketsCommand.CommandText = "SELECT date_ready, SUM( price ) as price FROM Tickets t JOIN Ticket_Alterations ta ON t.ticket_id = ta.ticket_id WHERE date_ready >= CURDATE( ) AND  taxable =0 and t.order_id = '' and price > 0 GROUP BY date_ready";
            List<AlterationToatalForDay> list = new List<AlterationToatalForDay>();
            try
            {
                MySqlDataReader reader = getTicketsCommand.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(ConvertSQLReaderRowToAlterationToatalForDay(reader));
                }
                reader.Close();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            return list;
        }

        public List<CustomerResource> GetCustomersByArguments(Dictionary<CustomerProperty, object> arguments)
        {
            DBConnector connector = new DBConnector();
            MySqlCommand getTicketsCommand = new MySqlCommand();
            getTicketsCommand.Connection = connector.connection;
            String sql = "select * from Customer where ";

            int counter = 0;
            foreach (KeyValuePair<CustomerProperty, object> entry in arguments)
            {

                if (entry.Key == CustomerProperty.FirstName)
                {
                    sql += "first_name LIKE @first_name";
                    getTicketsCommand.Parameters.AddWithValue("@first_name", "%" + (String)entry.Value + "%");
                }
                if (entry.Key == CustomerProperty.MiddleName)
                {
                    sql += "middle_name LIKE @middle_name";
                    getTicketsCommand.Parameters.AddWithValue("@middle_name", "%" + (String)entry.Value + "%");
                }
                if (entry.Key == CustomerProperty.LastName)
                {
                    sql += "last_name LIKE @last_name";
                    getTicketsCommand.Parameters.AddWithValue("@last_name", "%" + (String)entry.Value + "%");
                }
                if (entry.Key == CustomerProperty.Address)
                {
                    sql += "address LIKE @address";
                    getTicketsCommand.Parameters.AddWithValue("@address", "%" + (String)entry.Value + "%");
                }
                if (entry.Key == CustomerProperty.Address2)
                {
                    sql += "address2 LIKE @address2";
                    getTicketsCommand.Parameters.AddWithValue("@address2", "%" + (String)entry.Value + "%");
                }
                if (entry.Key == CustomerProperty.City)
                {
                    sql += "city LIKE @city";
                    getTicketsCommand.Parameters.AddWithValue("@city", "%" + (String)entry.Value + "%");
                }
                if (entry.Key == CustomerProperty.State)
                {
                    sql += "state = @state";
                    getTicketsCommand.Parameters.AddWithValue("@state", (String)entry.Value);
                }
                if (entry.Key == CustomerProperty.Zip)
                {
                    sql += "zip = @zip";
                    getTicketsCommand.Parameters.AddWithValue("@zip", (String)entry.Value);
                }
                if (entry.Key == CustomerProperty.Telephone)
                {
                    sql += "phone = @phone";
                    getTicketsCommand.Parameters.AddWithValue("@phone", (String)entry.Value);
                }
                if (entry.Key == CustomerProperty.Email)
                {
                    sql += "email LIKE @email";
                    getTicketsCommand.Parameters.AddWithValue("@email", "%" + (String)entry.Value + "%");
                }

                counter++;
                if (counter < arguments.Count)
                {
                    sql += " and ";
                }
            }

            //sql += " order by ticket_id desc";

            getTicketsCommand.CommandText = sql;

            List<CustomerResource> customerResources = new List<CustomerResource>();
            try
            {
                MySqlDataReader reader = getTicketsCommand.ExecuteReader();
                while (reader.Read())
                {
                    customerResources.Add(ConvertSQLReaderRowToTicketResource(reader));
                }
                reader.Close();
                connector.CloseConnection();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("There was an error. Contact Jay with this message: " + ex.Message + " error code: " + ex.Number);
            }

            return customerResources;
        }