/// <summary>
/// 查询全部数据
/// </summary>
/// <returns>IList</returns>
/*查看是否为视图*/
public IList <keys_lexicon> SearchAll()
{
DBHelper.sqlstr = "select * from keys_lexicon ";
List <keys_lexicon> list = new List <keys_lexicon>();
MySqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
keys_lexicon Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
/// <summary>
/// 从一个MySqlDataReader里读数据
/// </summary>
/// <param name="Reader">MySqlDataReader</param>
/// <returns>keys_lexiconExample</returns>
public static keys_lexicon GetByReader(MySqlDataReader Reader)
{
keys_lexicon keys_lexiconExample = new keys_lexicon();
keys_lexiconExample.klid = Reader["klid"] == DBNull.Value ? 0 : (int)Reader["klid"];
keys_lexiconExample.klname = Reader["klname"] == DBNull.Value ? null : Reader["klname"].ToString();
keys_lexiconExample.klsort = Reader["klsort"] == DBNull.Value ? 0 : (int)Reader["klsort"];
keys_lexiconExample.kldate = Reader["kldate"] == DBNull.Value ? new DateTime() : Convert.ToDateTime(Reader["kldate"]);
keys_lexiconExample.klstate = Reader["klstate"] == DBNull.Value ? null : Reader["klstate"].ToString();
keys_lexiconExample.klremark = Reader["klremark"] == DBNull.Value ? null : Reader["klremark"].ToString();
keys_lexiconExample.klremark1 = Reader["klremark1"] == DBNull.Value ? null : Reader["klremark1"].ToString();
keys_lexiconExample.klremark2 = Reader["klremark2"] == DBNull.Value ? null : Reader["klremark2"].ToString();
return(keys_lexiconExample);
}
/// <summary>
/// 根据klid,查询一条数据
/// </summary>
/// <param name="klid">编号</param>
/// <returns></returns>
/*查看是否为视图*/
public keys_lexicon SearchByID(int klid)
{
DBHelper.sqlstr = "select * from keys_lexicon where klid = @klid";
MySqlParameter[] param = new MySqlParameter[] {
new MySqlParameter("@klid", klid)
};
MySqlDataReader reader = DBHelper.ExecuteReader(param);
keys_lexicon 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<keys_lexicon></returns>
public IList <keys_lexicon> Search(int s, int e, string key, string state, DateTime startdate, DateTime enddate, string orderby)
{
string sql1 = "select top " + (e - s + 1).ToString() + " * from keys_lexicon where ";
string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( klname like '%" + key + "%' or klstate like '%" + key + "%' or klremark like '%" + key + "%' or klremark1 like '%" + key + "%' or klremark2 like '%" + key + "%' )"; //删除无用字段，删除最后一个or
string sql3 = string.IsNullOrEmpty(state) ? "" : " and klstate= '" + state + "' "; //状态字段，无用删除
string sql5 = startdate == new DateTime() ? "" : " and DATEDIFF(D,'" + startdate.ToString("yyyy-MM-dd") + "',kldate)>=0 "; //时间字段，无用删除
string sql6 = enddate == new DateTime() ? "" : " and DATEDIFF(D,'" + enddate.ToString("yyyy-MM-dd") + "',kldate)<=0 "; //时间字段，无用删除
string sql7 = string.IsNullOrEmpty(orderby) ? " order by klid desc " : " order by " + orderby;
string sql8 = s == 1 ? "" : " and klid not in ( select top " + (s - 1).ToString() + " klid from keys_lexicon where " + sql2 + sql3 + sql5 + sql6 + sql7 + " ) ";
DBHelper.sqlstr = sql1 + sql2 + sql3 + sql5 + sql6 + sql8 + sql7;
List <keys_lexicon> list = new List <keys_lexicon>();
SqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
keys_lexicon Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="keys_lexicon">keys_lexicon表实例</param>
/// <returns>int</returns>
public int Insert(keys_lexicon keys_lexiconExample)
{
DBHelper.sqlstr = "insert into keys_lexicon (klname,klsort,kldate,klstate,klremark,klremark1,klremark2)values(@klname,@klsort,@kldate,@klstate,@klremark,@klremark1,@klremark2)";
return(DBHelper.ExecuteNonQuery(GetSqlParameters(keys_lexiconExample)));
}
/// <summary>
/// 根据表,获取一个MySqlParameter数组
/// </summary>
/// <returns>MySqlParameter[]</returns>
public static MySqlParameter[] GetSqlParameters(keys_lexicon keys_lexiconExample)
{
List <MySqlParameter> list_param = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(keys_lexiconExample.klname))
{
list_param.Add(new MySqlParameter("@klname", keys_lexiconExample.klname));
}
else
{
list_param.Add(new MySqlParameter("@klname", DBNull.Value));
}
if (keys_lexiconExample.klsort != 0)
{
list_param.Add(new MySqlParameter("@klsort", keys_lexiconExample.klsort));
}
else
{
list_param.Add(new MySqlParameter("@klsort", DBNull.Value));
}
if (keys_lexiconExample.kldate != new DateTime() && keys_lexiconExample.kldate != null)
{
list_param.Add(new MySqlParameter("@kldate", keys_lexiconExample.kldate.ToString("yyyy-MM-dd")));
}
else
{
list_param.Add(new MySqlParameter("@kldate", DBNull.Value));
}
if (!string.IsNullOrEmpty(keys_lexiconExample.klstate))
{
list_param.Add(new MySqlParameter("@klstate", keys_lexiconExample.klstate));
}
else
{
list_param.Add(new MySqlParameter("@klstate", DBNull.Value));
}
if (!string.IsNullOrEmpty(keys_lexiconExample.klremark))
{
list_param.Add(new MySqlParameter("@klremark", keys_lexiconExample.klremark));
}
else
{
list_param.Add(new MySqlParameter("@klremark", DBNull.Value));
}
if (!string.IsNullOrEmpty(keys_lexiconExample.klremark1))
{
list_param.Add(new MySqlParameter("@klremark1", keys_lexiconExample.klremark1));
}
else
{
list_param.Add(new MySqlParameter("@klremark1", DBNull.Value));
}
if (!string.IsNullOrEmpty(keys_lexiconExample.klremark2))
{
list_param.Add(new MySqlParameter("@klremark2", keys_lexiconExample.klremark2));
}
else
{
list_param.Add(new MySqlParameter("@klremark2", 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>
/// 更新
/// </summary>
/// <param name="keys_lexicon">keys_lexicon表实例</param>
/// <returns>int</returns>
public int Update(keys_lexicon keys_lexiconExample)
{
DBHelper.sqlstr = "update keys_lexicon set klname=@klname,klsort=@klsort,kldate=@kldate,klstate=@klstate,klremark=@klremark,klremark1=@klremark1,klremark2=@klremark2 where klid=" + keys_lexiconExample.klid;
return(DBHelper.ExecuteNonQuery(GetSqlParameters(keys_lexiconExample)));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="keys_lexicon">keys_lexicon表实例</param>
/// <returns>int</returns>
public static int Update(keys_lexicon keys_lexiconExample)
{
return(Service.Update(keys_lexiconExample));
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="keys_lexicon">keys_lexicon表实例</param>
/// <returns>int</returns>
public static int Insert(keys_lexicon keys_lexiconExample)
{
return(Service.Insert(keys_lexiconExample));
}