public PlayerBuyVirtualShoppingItemRecord[] GetPlayerBuyVirtualShoppingItemRecordByID(int userID, int itemID, MyDateTime beginBuyTime, MyDateTime endBuyTime, int pageItemCount, int pageIndex) { PlayerBuyVirtualShoppingItemRecord[] items = null; bool isOK = MyDBHelper.Instance.ConnectionCommandSelect(mycmd => { string sqlTextA = "SELECT n.* FROM playerbuyvirtualshoppingitemrecord n "; StringBuilder builder = new StringBuilder(); if (userID > 0) { builder.Append(" n.UserID = @UserID "); mycmd.Parameters.AddWithValue("@UserID", userID); } if (itemID > 0) { if (builder.Length != 0) { builder.Append(" and "); } builder.Append(" n.VirtualShoppingItemID = @VirtualShoppingItemID "); mycmd.Parameters.AddWithValue("@VirtualShoppingItemID", itemID); } if (beginBuyTime != null && !beginBuyTime.IsNull && endBuyTime != null && !endBuyTime.IsNull) { if (builder.Length != 0) { builder.Append(" and "); } DateTime beginTime = beginBuyTime.ToDateTime(); DateTime endTime = endBuyTime.ToDateTime(); if (beginTime >= endTime) { return; } builder.Append(" n.BuyTime >= @beginTime and n.BuyTime < @endTime "); mycmd.Parameters.AddWithValue("@beginTime", beginTime); mycmd.Parameters.AddWithValue("@endTime", endTime); } string sqlWhere = ""; if (builder.Length > 0) { sqlWhere = " where " + builder.ToString(); } string sqlOrderLimit = " order by n.ID desc "; if (pageItemCount > 0) { int start = pageIndex <= 0 ? 0 : (pageIndex - 1) * pageItemCount; sqlOrderLimit += " limit " + start.ToString() + ", " + pageItemCount; } string sqlAllText = "select ttt.*, s.UserName as UserName, v.Name as VirtualShoppingItemName from " + " ( " + sqlTextA + sqlWhere + sqlOrderLimit + " ) ttt " + " left join playersimpleinfo s on ttt.UserID = s.id " + " left join virtualshoppingitem v on ttt.VirtualShoppingItemID=v.ID"; mycmd.CommandText = sqlAllText; DataTable table = new DataTable(); MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd); adapter.Fill(table); items = MetaDBAdapter <PlayerBuyVirtualShoppingItemRecord> .GetPlayerBuyVirtualShoppingItemRecordFromDataTable(table); table.Dispose(); adapter.Dispose(); }); return(items); }