C# DBConnector类代码示例

        public static bool validateLogin(UserLog ul)
        {
            DBConnector dbcon = new DBConnector();
            dbcon.openConnection();

            //try {

            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = "SELECT * FROM user WHERE username='" + ul.getUsername() + "' AND password=MD5('" + ul.getPassword() + "')";
            cmd.Connection = dbcon.connection;

            MySqlDataReader login = cmd.ExecuteReader();

            if (login.Read())
            {
                LoginSession.setSession(login.GetString("iduser"));

                //login.Close();
                dbcon.closeConnection();
                return true;
            }
            else
            {
                //login.Close();
                dbcon.closeConnection();
                return false;
            }
            //}
            //catch (MySqlException e){
            //int errorcode = e.Number;
            //return false;
            //}
        }

        public Task<IEnumerable<PaymentInfo>> GetPaymentsAsync(DateTime start, CancellationToken cancellationToken)
        {
            DB.RegisterFactory("MySql", MySql.Data.MySqlClient.MySqlClientFactory.Instance);
            DB.RegisterFactory("MySql.Data.MySqlClient", MySql.Data.MySqlClient.MySqlClientFactory.Instance);

            var db = new DBConnector(Config.GetElement("Databases", "WordPress"));

            using (var reader = db.ExecuteReader(@"
            SELECT p.id, s.name,
            us.billingFirstName, us.billingLastName, us.billingCompany,
            us.billingAddress, us.billingCity, us.billingState, us.billingZip, us.billingCountry, us.billingPhone,
            us.emailAddress,
            us.lastFourDigitsOfCreditCard, p.xAmount, p.paymentDate,
            us.subscriptionNotes, us.userIP,
            CONVERT(p.fullAuthorizeNetResponse USING utf8) AS response
            FROM wp_authnet_user_subscription us
            JOIN wp_authnet_payment p ON us.ID = p.user_subscription_id
            LEFT JOIN wp_authnet_subscription s ON s.ID = p.xSubscriptionId
            WHERE p.paymentDate > ?start", new { start })) {
                return Task.FromResult<IEnumerable<PaymentInfo>>(reader.Cast<IDataRecord>()
                             .Select(dr => new PaymentInfo {
                                 Id = dr.GetString(dr.GetOrdinal("id")),
                                 Email = dr.GetString(dr.GetOrdinal("emailAddress")),
                                 FinalFour = dr.GetString(dr.GetOrdinal("lastFourDigitsOfCreditCard")),
                                 CardIssuer = dr.GetString(dr.GetOrdinal("response")).Split('|')[51],
                                 Date = dr.GetDateTime(dr.GetOrdinal("paymentDate")),
                                 Amount = dr.GetDecimal(dr.GetOrdinal("xAmount")),
                                 Comments = dr.GetString(dr.GetOrdinal("subscriptionNotes"))
                                          + "\nIP Address: " + dr.GetString(dr.GetOrdinal("userIP"))
                             })
                             .ToList());
            }
        }

        public static bool addResignationRecord()
        {
            DBConnector dbcon = new DBConnector();

            //try
            //{
            if (dbcon.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "INSERT INTO resignation (letter_submitted_date, resignation_date, employee_idemployee) VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', " + Employee.employee_id + ")";
                cmd.Connection = dbcon.connection;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                dbcon.closeConnection();
                return true;
            }
            else
            {
                dbcon.closeConnection();
                return false;
            }

            /*}
            catch (MySqlException e)
            {
                int errorcode = e.Number;
                dbcon.closeConnection();
                return false;
            }*/
        }

        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 static bool addQualification(Qualification q)
        {
            DBConnector dbcon = new DBConnector();

            try
            {
                if (dbcon.openConnection())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = "INSERT INTO qualification (qualification_no, institute, months, occupation_relevant, highest_qualification, status, note, qualification, year, employee_idemployee) VALUES (N'" + q.qualification_no + "', N'" + q.institute + "', N'" + q.months + "', " + q.occupation_relevant + ", " + q.highest_qualification + ", N'" + q.status + "', N'" + q.note + "', N'" + q.qualification + "', N'" + q.year + "', " + Employee.employee_id + ")";
                    cmd.Connection = dbcon.connection;
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();

                    dbcon.closeConnection();
                    return true;
                }
                else
                {
                    dbcon.closeConnection();
                    return false;
                }

            }
            catch (MySqlException e)
            {
                int errorcode = e.Number;
                dbcon.closeConnection();
                return false;
            }
        }

        /// <summary>
        /// Insert data to database
        /// </summary>
        /// <param name="sqlString">Query striing</param>
        /// <returns></returns>
        public bool InsertData(string sqlString)
        {
            dbConnectorObject = new DBConnector();
            sqlConnectionObject = new SqlConnection();
            sqlConnectionObject = dbConnectorObject.GetConnection;

            try
            {
                sqlConnectionObject.Open();
                SqlCommand sqlCommandObject = new SqlCommand(sqlString, sqlConnectionObject);

                int i = sqlCommandObject.ExecuteNonQuery();
                if (i > 0)
                    return true;
                else
                    return false;
            }

            catch (SqlException ex)
            {
                throw new Exception(" Database problem.\n" + ex.Message);
            }

            finally
            {
                if (sqlConnectionObject.State == ConnectionState.Open)
                {
                    sqlConnectionObject.Close();
                }
            }
        }

        public static bool addDisciplinaryDetailsRecord()
        {
            DBConnector dbcon = new DBConnector();

            //try
            //{
            if (dbcon.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "INSERT INTO desciplinary_details (breaking_date, given_sentence_date, verdict_of_director_general_date, suspended_date, rejoined_date, employee_idemployee) VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', " + Employee.employee_id + ")";
                cmd.Connection = dbcon.connection;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                dbcon.closeConnection();
                return true;
            }
            else
            {
                dbcon.closeConnection();
                return false;
            }

            /*}
            catch (MySqlException e)
            {
                int errorcode = e.Number;
                dbcon.closeConnection();
                return false;
            }*/
        }

        public static bool addFinanceBank(FinanceBank fb)
        {
            DBConnector dbcon = new DBConnector();

            //try
            //{
            if (dbcon.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "INSERT INTO finance_bank (bank_name, branch_name, account_number, account_type, begin_date, end_date, qualification, qual_year, employee_idemployee) VALUES (N'" + fb.bank_name + "', N'" + fb.branch_name + "', N'" + fb.account_number + "', N'" + fb.account_type + "', '" + fb.getBegin_date().ToString("yyyy-MM-dd") + "', '" + fb.getEnd_date().ToString("yyyy-MM-dd") + "', N'" + fb.qualification + "', N'" + fb.Qual_year + "', " + Employee.employee_id + ")";
                cmd.Connection = dbcon.connection;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                dbcon.closeConnection();
                return true;
            }
            else
            {
                dbcon.closeConnection();
                return false;
            }

            //}
            //catch (MySqlException e)
            //{
            //int errorcode = e.Number;
            //dbcon.closeConnection();
            //return false;
            //}
        }

        public static bool addRemuneration(Remuneration r)
        {
            DBConnector dbcon = new DBConnector();

            //try
            //{
            if (dbcon.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "INSERT INTO remuneration (rank, post, salary_grade, salary_slip_no, basic_salary, transport_allowance, fuel_allowance, mobile_phone_allowance, housing_allowance, other_allowance, salary_increase_date, salary_increase_amount, current_total_salary, employee_idemployee) VALUES (N'" + r.rank + "', N'" + r.post + "', N'" + r.salary_grade + "', N'" + r.salary_slip_no + "', " + r.basic_salary + ", " + r.transport_allowance + ", " + r.fuel_allowance + ", " + r.mobile_phone_allowance + ", " + r.housing_allowance + ", " + r.other_allowance + ", '" + r.getsalary_increase_date().ToString("yyyy-MM-dd") + "', " + r.salary_increase_amount + ", " + r.current_total_salary + ", " + Employee.employee_id + ")";
                cmd.Connection = dbcon.connection;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                dbcon.closeConnection();
                return true;
            }
            else
            {
                dbcon.closeConnection();
                return false;
            }

            //}
            //catch (MySqlException e)
            //{
            //int errorcode = e.Number;
            //dbcon.closeConnection();
            //return false;
            //}
        }

        public static bool addPensionAndDeath(PensionAndDeath pd)
        {
            DBConnector dbcon = new DBConnector();

            //try
            //{
            if (dbcon.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "INSERT INTO pension_death (retired, dead, died_date, informed_date, note, employee_idemployee) VALUES (" + pd.retired + ", " + pd.dead + ", '" + pd.died_date.ToString("yyyy-MM-dd") + "', '" + pd.informed_date.ToString("yyyy-MM-dd") + "', N'" + pd.note + "', " + Employee.employee_id + ")";
                cmd.Connection = dbcon.connection;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                dbcon.closeConnection();
                return true;
            }
            else
            {
                dbcon.closeConnection();
                return false;
            }

            /*}
            catch (MySqlException e)
            {
                int errorcode = e.Number;
                dbcon.closeConnection();
                return false;
            }*/
        }

        public static bool addNotification(Notification n)
        {
            DBConnector dbcon = new DBConnector();

            try
            {
                if (dbcon.openConnection())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = "INSERT INTO notification (title, content, date, employee_name, employee_number, employee_idemployee, user_iduser) VALUES (N'" + n.Title + "', N'" + n.Content + "', '" + n.Date.ToString("yyyy-MM-dd") + "', N'" + n.Employee_name + "', N'" + n.Employee_number + "', " + n.Employee_idemployee + ", " + n.User_iduser + ")";
                    cmd.Connection = dbcon.connection;
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();

                    dbcon.closeConnection();
                    return true;
                }
                else
                {
                    dbcon.closeConnection();
                    return false;
                }

            }
            catch (MySqlException e)
            {
                int errorcode = e.Number;
                Console.Write(e.Message + "\n");
                dbcon.closeConnection();
                return false;
            }
        }

        public static bool addTraining(Training t)
        {
            DBConnector dbcon = new DBConnector();

            //try
            //{
            if (dbcon.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "INSERT INTO training (course_name, course_type, institute, payments, started_date, ending_date, extended_days, new_ending_date, country, result, employee_idemployee) VALUES (N'" + t.course_name + "', N'" + t.course_type + "', N'" + t.institute + "', N'" + t.payments + "', '" + t.started_date.ToString("yyyy-MM-dd") + "', '" + t.ending_date.ToString("yyyy-MM-dd") + "', N'" + t.extended_days + "', '" + t.new_ending_date.ToString("yyyy-MM-dd") + "', N'" + t.country + "', N'" + t.result + "', " + Employee.employee_id + ")";
                cmd.Connection = dbcon.connection;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                dbcon.closeConnection();
                return true;
            }
            else
            {
                dbcon.closeConnection();
                return false;
            }

            /*}
            catch (MySqlException e)
            {
                int errorcode = e.Number;
                dbcon.closeConnection();
                return false;
            }*/
        }

        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 static bool addJobStatusRecord()
        {
            DBConnector dbcon = new DBConnector();

            //try
            //{
            if (dbcon.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "INSERT INTO job_status (contract_started_date, contract_ended_date, probation_started_date, probation_ended_date, probation_to_permanent_date, contract_to_permanent_date, employee_idemployee) VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', " + Employee.employee_id + ")";
                cmd.Connection = dbcon.connection;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                dbcon.closeConnection();
                return true;
            }
            else
            {
                dbcon.closeConnection();
                return false;
            }

            /*}
            catch (MySqlException e)
            {
                int errorcode = e.Number;
                dbcon.closeConnection();
                return false;
            }*/
        }

        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;
        }