/// <summary> /// 根据企业ID获取公众号信息 /// </summary> /// <param name="trans"></param> /// <param name="conn"></param> /// <param name="companyId"></param> /// <returns></returns> public List<MQiushibaike> GetJokeByRandom(IDbTransaction trans, IDbConnection conn) { string sqlText = @"SELECT * FROM qiushibaike AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(KeyID) FROM qiushibaike)-(SELECT MIN(KeyID) FROM qiushibaike))+(SELECT MIN(KeyID) FROM qiushibaike)) AS KeyID) AS t2 WHERE t1.KeyID >= t2.KeyID ORDER BY t1.KeyID LIMIT 5;"; List<MQiushibaike> qiushibaikeList = new List<MQiushibaike>(); MQiushibaike qiushibaike = null; using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sqlText, null)) { while (reader.Read()) { qiushibaike = new MQiushibaike(); qiushibaike.KeyID = reader["KeyID"] == DBNull.Value ? string.Empty : reader["KeyID"].ToString(); qiushibaike.JokerName = reader["JokerName"] == DBNull.Value ? string.Empty : reader["JokerName"].ToString(); qiushibaike.JokeContent = reader["JokeContent"] == DBNull.Value ? string.Empty : reader["JokeContent"].ToString(); qiushibaike.Lauds = reader["Lauds"] == DBNull.Value ? 0 : Convert.ToInt32(reader["Lauds"]); qiushibaikeList.Add(qiushibaike); } } return qiushibaikeList; }
/// <summary> /// 根据企业ID获取公众号信息 /// </summary> /// <param name="trans"></param> /// <param name="conn"></param> /// <param name="companyId"></param> /// <returns></returns> public List<MQiushibaike> GetJokeByKeyWords(IDbTransaction trans, IDbConnection conn, string keywords) { string sqlText = @"select KeyID,JokerName,JokeContent,Lauds,IsDelete from Qiushibaike WHERE JokeContent like @JokeContent and IsDelete = 0 limit 3"; MySqlParameters destionParameters = new MySqlParameters(); destionParameters.Add(new MySqlParameter() { ParameterName = "@JokeContent", MySqlDbType = MySqlDbType.VarChar, Value = string.Format("%{0}%", keywords) }); List<MQiushibaike> qiushibaikeList = new List<MQiushibaike>(); MQiushibaike qiushibaike = null; using (MySqlDataReader reader = Common.DBUtility.MySqlHelper.ExecuteReader(trans, conn, sqlText, destionParameters.ToArray())) { while (reader.Read()) { qiushibaike = new MQiushibaike(); qiushibaike.KeyID = reader["KeyID"] == DBNull.Value ? string.Empty : reader["KeyID"].ToString(); qiushibaike.JokerName = reader["JokerName"] == DBNull.Value ? string.Empty : reader["JokerName"].ToString(); qiushibaike.JokeContent = reader["JokeContent"] == DBNull.Value ? string.Empty : reader["JokeContent"].ToString(); qiushibaike.Lauds = reader["Lauds"] == DBNull.Value ? 0 : Convert.ToInt32(reader["Lauds"]); qiushibaikeList.Add(qiushibaike); } } return qiushibaikeList; }