public List <ShopRecordBean> GetAll(DateTime start, DateTime end, PlayerBean player, WorldBean world) { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select r.id as record_id, r.amount as record_amount, r.type as record_type, r.time as record_time, i.first_id as item_first_id, i.second_id as item_second_id, i.name as item_name, i.english_name as item_english_name, i.price as item_price, i.group_amount as item_group_amount from tb_shop_record r left join tb_shop_item i on r.item_first_id = i.first_id and r.item_second_id = i.second_id where r.player_id = @playerId and r.world_id = @worldId and r.time > @startTime and r.time < @endTime order by r.time desc"; cmd.Parameters.AddWithValue("@playerId", player.Id); cmd.Parameters.AddWithValue("@worldId", player.Id); cmd.Parameters.AddWithValue("@startTime", start); cmd.Parameters.AddWithValue("@endTime", end); MySqlDataReader reader = cmd.ExecuteReader(); List <ShopRecordBean> records = new List <ShopRecordBean>(); while (reader.Read()) { ShopRecordBean record = new ShopRecordBean { Id = reader.GetInt64("record_id"), PlayerId = player.Id, WorldId = world.Id, ItemFirstId = reader.GetInt32("item_first_id"), ItemSecondId = reader.GetInt32("item_second_id"), ItemName = reader.GetString("item_name"), Amount = reader.GetInt32("record_amount"), Type = reader.GetInt32("record_type"), Time = reader.GetDateTime("record_time") }; records.Add(record); } reader.Close(); DBUtils.CloseConnection(conn); return(records); }
public int Insert(T obj) { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = GetInsertSQL(); //List<string> values = new List<string>(); int index = -1; for (int i = 0; i < fields.Count; i++) { PropertyInfo prop = (PropertyInfo)(fields[i][0]); if (prop.GetCustomAttribute(typeof(DatabaseInsertIgnoreAttributeAttribute)) != null) { continue; } object value = prop.GetValue(obj); //values.Add(value.ToString()); cmd.Parameters.AddWithValue("@" + fields[i][1], value); if ((fields[i][1]).Equals("Id")) { index = i; } } int result = cmd.ExecuteNonQuery(); ((PropertyInfo)fields[index][0]).SetValue(obj, cmd.LastInsertedId); DBUtils.CloseConnection(conn); return(result); }
public List <ShopItemBean> Search(string search) { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from tb_shop_item where name like @search or english_name like @search"; if (search.Length == 0) { search = "%"; } else { search = "%" + search + "%"; } cmd.Parameters.AddWithValue("@search", search); MySqlDataReader reader = cmd.ExecuteReader(); List <ShopItemBean> items = new List <ShopItemBean>(); while (reader.Read()) { ShopItemBean shopItem = new ShopItemBean { FirstId = reader.GetInt32("first_id"), SecondId = reader.GetInt32("second_id"), Name = reader.GetString("name"), EnglishName = reader.GetString("english_name"), Price = reader.GetInt32("price"), GroupAmount = reader.GetInt32("group_amount") }; items.Add(shopItem); } reader.Close(); DBUtils.CloseConnection(conn); return(items); }
public BankAccountBean GetByPlayerIdAndWorldId(long playerId, long worldId) { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = GetSelectAllSQL() + " where player_id = @playerId and world_id = @worldId"; cmd.Parameters.AddWithValue("@playerId", playerId); cmd.Parameters.AddWithValue("@worldId", worldId); MySqlDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { return(null); } BankAccountBean account = new BankAccountBean(); //List<String> values = new List<>(); foreach (object[] arr in fields) { object value = reader[StringUtils.CamelCaseToUnderline((string)arr[1])]; ((PropertyInfo)arr[0]).SetValue(account, value); //values.add(value.toString()); } DBUtils.CloseConnection(conn); return(account); }
public T GetById(long id) { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = GetSelectSQL(); cmd.Parameters.AddWithValue("@id", id); MySqlDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { return(default(T)); } T obj = (T)TargetType.GetConstructor(new Type[0]).Invoke(new object[0]); //List<String> values = new List<>(); foreach (object[] arr in fields) { object value = reader[(string)arr[2]]; ((PropertyInfo)arr[0]).SetValue(obj, value); //values.add(value.toString()); } DBUtils.CloseConnection(conn); return(obj); }
public int Delete(long id) { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = GetDeleteSQL(); cmd.Parameters.AddWithValue("@id", id); int result = cmd.ExecuteNonQuery(); DBUtils.CloseConnection(conn); return(result); }
public long Count() { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = GetCountSQL(); MySqlDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { return(-1); } long result = (long)reader[0]; DBUtils.CloseConnection(conn); return(result); }
public int Update(T obj) { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = GetUpdateSQL(); //List<string> values = new List<string>(); for (int i = 0; i < fields.Count; i++) { object value = ((PropertyInfo)fields[i][0]).GetValue(obj); //values.Add(value.ToString); cmd.Parameters.AddWithValue("@" + fields[i][1], value); } int result = cmd.ExecuteNonQuery(); DBUtils.CloseConnection(conn); return(result); }
public List <T> GetAll() { MySqlConnection conn = DBUtils.GetConnection(); MySqlCommand cmd = conn.CreateCommand(); cmd.CommandText = GetSelectAllSQL(); MySqlDataReader reader = cmd.ExecuteReader(); List <T> result = new List <T>(); while (reader.Read()) { T obj = (T)TargetType.GetConstructor(new Type[0]).Invoke(new object[0]); //List<String> values = new List<>(); foreach (object[] arr in fields) { object value = reader[(string)arr[1]]; ((PropertyInfo)arr[0]).SetValue(obj, value); //values.add(value.toString()); } result.Add(obj); } DBUtils.CloseConnection(conn); return(result); }
private void Trade(int mode) { ShopItemBean shopItem = (ShopItemBean)listBoxShopItems.SelectedItem; if (shopItem == null) { MessageBox.Show("未选择任何条目"); return; } int amount; try { amount = int.Parse(textBoxAmount.Text); } catch (Exception) { MessageBox.Show("数量输入不正确"); return; } if (comboBoxAmount.SelectedItem.Equals("组")) { amount = amount * shopItem.GroupAmount; } int price = shopItem.Price * amount; if (mode < 0 && price > account.Balance) { MessageBox.Show("账户余额不足"); return; } DialogResult result = MessageBox.Show(string.Format("确认要{0}吗?", mode < 0 ? "买入" : "卖出"), "确认交易", MessageBoxButtons.OKCancel); if (result == DialogResult.OK) { account.Balance += (price * mode); system.Balance -= (price * mode); ShopRecordBean shopRecord = new ShopRecordBean { PlayerId = player.Id, WorldId = world.Id, ItemFirstId = shopItem.FirstId, ItemSecondId = shopItem.SecondId, ItemName = shopItem.Name, Amount = amount, Type = mode, Time = DateTime.Now }; BankAccountRecordBean bankRecord = new BankAccountRecordBean { Src = mode > 0 ? 0 : account.Id, Dest = mode > 0 ? account.Id : 0, Amount = amount, Time = DateTime.Now, Note = "系统商店" }; DBUtils.BeginTransaction(); if (bankAccountDAO.Update(account) > 0 && bankAccountDAO.Update(system) > 0 && shopRecordDAO.Insert(shopRecord) > 0 && bankAccountRecordDAO.Insert(bankRecord) > 0) { textBoxPlayerMoney.Text = account.Balance.ToString(); DBUtils.Commit(); DBUtils.CloseConnection(null); } else { DBUtils.Rollback(); } } }