導航

 public class MysqlHelper
    {
        private static string constr = "server=127.0.0.1;User Id=root;password=000000;Database=aaaaa";
        public static void InsertBusines(BusinessReviewDTO dto)
        {
            MySqlConnection mycon = new MySqlConnection(constr);
            MySqlCommand cmd;
            mycon.Open();
            try
            {
                cmd = mycon.CreateCommand();
                cmd.CommandText ="";
                //cmd.Parameters.AddWithValue("@Id", dto.Id);
                
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (mycon.State == ConnectionState.Open)
                {
                    mycon.Close();
                }
            }
        }
        public static int SelectBusines()
        {
            MySqlConnection mycon = new MySqlConnection(constr);
            try
            {
                string sql = @"select max(id) from businessreviewtable";
                MySqlCommand cmd = new MySqlCommand(sql, mycon);
                mycon.Open();
                MySqlDataReader reader= cmd.ExecuteReader();
                if (reader.Read())
                {
                    return reader.GetInt32(0);
                }
                return 0;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (mycon.State == ConnectionState.Open)
                {
                    mycon.Close();
                }
            }
        }
        /// <summary> 
        /// 執行多條SQL語句，實現數據庫事務。 
        /// </summary>mysql數據庫 
        /// <param name="SQLStringList">多條SQL語句</param> 
        public static void ExecuteSqlTran(List<PaymentDTO> SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(constr))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = InsertPay(SQLStringList[n]);
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                        //后來加上的 
                        if (n > 0 && (n % 500 == 0 || n == SQLStringList.Count - 1))
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                    //tx.Commit();//原來一次性提交 
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        private static string InsertPay(PaymentDTO dto)
        {
            return @"INSERT INTO payment(BillId,pay,paycount,paymoney,payincome) VALUES(" + string.Format("'{0}','{1}',{2},{3},{4}", dto.BillId, dto.Pay, dto.PayCount, dto.PaysMoney, dto.PayIncome) + ")";
        }
        /// <summary> 
        /// 執行多條SQL語句，實現數據庫事務。 
        /// </summary>mysql數據庫 
        /// <param name="SQLStringList">多條SQL語句</param> 
        public static void ExecuteSqlTran(List<DishesTypeDTO> SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(constr))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = InsertDis(SQLStringList[n]);
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                        //后來加上的 
                        if (n > 0 && (n % 500 == 0 || n == SQLStringList.Count - 1))
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                    //tx.Commit();//原來一次性提交 
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        private static string InsertDis(DishesTypeDTO dto)
        {
            return @"INSERT INTO disType(BillId,distype,discount,dismoney,disincome) VALUES(" + string.Format("'{0}','{1}',{2},{3},{4}", dto.BillId, dto.DisType, dto.DisCount, dto.DisMoney, dto.DisIncome) + ")";
        }
    }