/// <summary>
/// 根据表,获取一个MySqlParameter数组
/// </summary>
/// <returns>MySqlParameter[]</returns>
public static MySqlParameter[] GetSqlParameters(lh_rankinglist lh_rankinglistExample)
{
List <MySqlParameter> list_param = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(lh_rankinglistExample.rkey))
{
list_param.Add(new MySqlParameter("@rkey", lh_rankinglistExample.rkey));
}
else
{
list_param.Add(new MySqlParameter("@rkey", DBNull.Value));
}
if (!string.IsNullOrEmpty(lh_rankinglistExample.rstate))
{
list_param.Add(new MySqlParameter("@rstate", lh_rankinglistExample.rstate));
}
else
{
list_param.Add(new MySqlParameter("@rstate", DBNull.Value));
}
if (lh_rankinglistExample.rdate != new DateTime() && lh_rankinglistExample.rdate != null)
{
list_param.Add(new MySqlParameter("@rdate", lh_rankinglistExample.rdate.ToString("yyyy-MM-dd")));
}
else
{
list_param.Add(new MySqlParameter("@rdate", DBNull.Value));
}
if (lh_rankinglistExample.hid != 0)
{
list_param.Add(new MySqlParameter("@hid", lh_rankinglistExample.hid));
}
else
{
list_param.Add(new MySqlParameter("@hid", DBNull.Value));
}
if (!string.IsNullOrEmpty(lh_rankinglistExample.hremark))
{
list_param.Add(new MySqlParameter("@hremark", lh_rankinglistExample.hremark));
}
else
{
list_param.Add(new MySqlParameter("@hremark", DBNull.Value));
}
MySqlParameter[] param = new MySqlParameter[list_param.Count];
int index = 0;
foreach (MySqlParameter p in list_param)
{
param[index] = p;
index++;
}
return(param);
}
/// <summary>
/// 从一个MySqlDataReader里读数据
/// </summary>
/// <param name="Reader">MySqlDataReader</param>
/// <returns>lh_rankinglistExample</returns>
public static lh_rankinglist GetByReader(MySqlDataReader Reader)
{
lh_rankinglist lh_rankinglistExample = new lh_rankinglist();
lh_rankinglistExample.rid = Reader["rid"] == DBNull.Value ? 0 : (int)Reader["rid"];
lh_rankinglistExample.rkey = Reader["rkey"] == DBNull.Value ? null : Reader["rkey"].ToString();
lh_rankinglistExample.rstate = Reader["rstate"] == DBNull.Value ? null : Reader["rstate"].ToString();
lh_rankinglistExample.rdate = Reader["rdate"] == DBNull.Value ? new DateTime() : Convert.ToDateTime(Reader["rdate"]);
lh_rankinglistExample.hid = Reader["hid"] == DBNull.Value ? 0 : (int)Reader["hid"];
lh_rankinglistExample.hremark = Reader["hremark"] == DBNull.Value ? null : Reader["hremark"].ToString();
return(lh_rankinglistExample);
}
/// <summary>
/// 查询全部数据
/// </summary>
/// <returns>IList</returns>
/*查看是否为视图*/
public IList <lh_rankinglist> SearchAll()
{
DBHelper.sqlstr = "select * from lh_rankinglist ";
List <lh_rankinglist> list = new List <lh_rankinglist>();
MySqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
lh_rankinglist Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
/// <summary>
/// 根据rid,查询一条数据
/// </summary>
/// <param name="rid">排行榜id</param>
/// <returns></returns>
/*查看是否为视图*/
public lh_rankinglist SearchByID(int rid)
{
DBHelper.sqlstr = "select * from lh_rankinglist where rid = @rid";
MySqlParameter[] param = new MySqlParameter[] {
new MySqlParameter("@rid", rid)
};
MySqlDataReader reader = DBHelper.ExecuteReader(param);
lh_rankinglist Obj = null;
if (reader.Read())
{
Obj = GetByReader(reader);
}
reader.Close();
return(Obj);
}
/// <summary>
/// 模糊搜索
/// </summary>
/// <param name="key">关键词</param>
/// <param name="state">状态</param>
/// <param name="id">int字段</param>
/// <param name="startdate">起始时间</param>
/// <param name="enddate">结束时间</param>
/// <param name="orderby">排序</param>
/// <returns>IList<lh_rankinglist></returns>
public IList <lh_rankinglist> Search(int s, int e, string key, string state, int id, DateTime startdate, DateTime enddate, string orderby)
{
string sql1 = "select top " + (e - s + 1).ToString() + " * from lh_rankinglist where ";
string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( rkey like '%" + key + "%' or rstate like '%" + key + "%' or hremark like '%" + key + "%' )"; //删除无用字段，删除最后一个or
string sql3 = string.IsNullOrEmpty(state) ? "" : " and rstate= '" + state + "' "; //状态字段，无用删除
string sql4 = id == 0 ? "" : " and hid='" + id.ToString() + "' "; //Int字段，无用删除
string sql5 = startdate == new DateTime() ? "" : " and DATEDIFF(D,'" + startdate.ToString("yyyy-MM-dd") + "',rdate)>=0 "; //时间字段，无用删除
string sql6 = enddate == new DateTime() ? "" : " and DATEDIFF(D,'" + enddate.ToString("yyyy-MM-dd") + "',rdate)<=0 "; //时间字段，无用删除
string sql7 = string.IsNullOrEmpty(orderby) ? " order by rid desc " : " order by " + orderby;
string sql8 = s == 1 ? "" : " and rid not in ( select top " + (s - 1).ToString() + " rid from lh_rankinglist where " + sql2 + sql3 + sql4 + sql5 + sql6 + sql7 + " ) ";
DBHelper.sqlstr = sql1 + sql2 + sql3 + sql4 + sql5 + sql6 + sql8 + sql7;
List <lh_rankinglist> list = new List <lh_rankinglist>();
SqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
lh_rankinglist Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="lh_rankinglist">lh_rankinglist表实例</param>
/// <returns>int</returns>
public static int Update(lh_rankinglist lh_rankinglistExample)
{
return(Service.Update(lh_rankinglistExample));
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="lh_rankinglist">lh_rankinglist表实例</param>
/// <returns>int</returns>
public static int Insert(lh_rankinglist lh_rankinglistExample)
{
return(Service.Insert(lh_rankinglistExample));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="lh_rankinglist">lh_rankinglist表实例</param>
/// <returns>int</returns>
public int Update(lh_rankinglist lh_rankinglistExample)
{
DBHelper.sqlstr = "update lh_rankinglist set rkey=@rkey,rstate=@rstate,rdate=@rdate,hid=@hid,hremark=@hremark where rid=" + lh_rankinglistExample.rid;
return(DBHelper.ExecuteNonQuery(GetSqlParameters(lh_rankinglistExample)));
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="lh_rankinglist">lh_rankinglist表实例</param>
/// <returns>int</returns>
public int Insert(lh_rankinglist lh_rankinglistExample)
{
DBHelper.sqlstr = "insert into lh_rankinglist (rkey,rstate,rdate,hid,hremark)values(@rkey,@rstate,@rdate,@hid,@hremark)";
return(DBHelper.ExecuteNonQuery(GetSqlParameters(lh_rankinglistExample)));
}
