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