/// <summary> /// 根据条件查询全部数据 /// </summary> /// <param name="s">起始位置</param> /// <param name="e">结束位置</param> /// <param name="key">关键词</param> /// <param name="state">状态</param> /// <param name="date1">起始时间</param> /// <param name="date2">结束时间</param> /// <returns>IList<shuadan_pingjia></returns> public IList <shuadan_pingjia> Search(int s, int e, string key, string state, string _type, DateTime date1, DateTime date2, string top) { //string sql1 = "select top " + e.ToString() + " * from shuadan_pingjia where "; string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( spname like '%" + key + "%' or spremark like '%" + key + "%' ) "; string sql3 = string.IsNullOrEmpty(state) ? "" : " and spstate= '" + state + "' "; string sql4 = string.IsNullOrEmpty(_type) ? "" : " and sptype= '" + _type + "' "; string sql5 = date1 == new DateTime() ? "" : " and datediff('d','" + date1.ToString() + "',spdate)>=0 "; string sql6 = date2 == new DateTime() ? "" : " and datediff('d','" + date2.ToString() + "',spdate)<=0 "; //string sql7 = " order by spdate asc,spid desc ";//排序两个字段,否则数据容易错误 string sql1 = "select top " + e.ToString() + " * from " + (string.IsNullOrEmpty(top) ? " shuadan_pingjia where " : " ( select top " + top + " * from shuadan_pingjia where " + sql2 + sql3 + sql4 + sql5 + sql6 + " order by spdate desc,spid asc ) ") + (string.IsNullOrEmpty(top) ? (sql2 + sql3 + sql4 + sql5 + sql6) : ""); //DBHelper.sqlstr = "select * from(select top " + (e - s + 1).ToString() + " * from ( " + sql1 + sql2 + sql3 + sql4 + sql5 + sql6 + " order by spdate desc,spid asc ) " + sql7 + ") order by spdate desc,spid asc "; DBHelper.sqlstr = "select top " + (e - s + 1).ToString() + " * from " + "( " + sql1 + " order by spdate desc,spid asc ) order by spdate asc,spid desc "; List <shuadan_pingjia> list = new List <shuadan_pingjia>(); OleDbDataReader reader = DBHelper.ExecuteReader(); while (reader.Read()) { shuadan_pingjia Obj = GetByReader(reader); list.Add(Obj); } reader.Close(); return(list); }
/// <summary> /// 根据表,获取一个OleDbParameter数组 /// </summary> /// <returns>OleDbParameter[]</returns> public static OleDbParameter[] GetOleDbParameter(shuadan_pingjia shuadan_pingjiaExample) { List <OleDbParameter> list_param = new List <OleDbParameter>(); if (!string.IsNullOrEmpty(shuadan_pingjiaExample.spname)) { list_param.Add(new OleDbParameter("@spname", shuadan_pingjiaExample.spname)); } else { list_param.Add(new OleDbParameter("@spname", DBNull.Value)); } if (!string.IsNullOrEmpty(shuadan_pingjiaExample.spremark)) { list_param.Add(new OleDbParameter("@spremark", shuadan_pingjiaExample.spremark)); } else { list_param.Add(new OleDbParameter("@spremark", DBNull.Value)); } if (!string.IsNullOrEmpty(shuadan_pingjiaExample.spstandby1)) { list_param.Add(new OleDbParameter("@spstandby1", shuadan_pingjiaExample.spstandby1)); } else { list_param.Add(new OleDbParameter("@spstandby1", DBNull.Value)); } if (!string.IsNullOrEmpty(shuadan_pingjiaExample.spstate)) { list_param.Add(new OleDbParameter("@spstate", shuadan_pingjiaExample.spstate)); } else { list_param.Add(new OleDbParameter("@spstate", DBNull.Value)); } if (!string.IsNullOrEmpty(shuadan_pingjiaExample.sptype)) { list_param.Add(new OleDbParameter("@sptype", shuadan_pingjiaExample.sptype)); } else { list_param.Add(new OleDbParameter("@sptype", DBNull.Value)); } OleDbParameter[] param = new OleDbParameter[list_param.Count]; int index = 0; foreach (OleDbParameter p in list_param) { param[index] = p; index++; } return(param); }
/// <summary> /// 从一个OleDbDataReader里读数据 /// </summary> /// <param name="Reader">OleDbDataReader</param> /// <returns>shuadan_pingjiaExample</returns> public static shuadan_pingjia GetByReader(OleDbDataReader Reader) { shuadan_pingjia shuadan_pingjiaExample = new shuadan_pingjia(); shuadan_pingjiaExample.spdate = Reader["spdate"] == DBNull.Value ? new DateTime() : Convert.ToDateTime(Reader["spdate"]); shuadan_pingjiaExample.spid = Reader["spid"] == DBNull.Value ? 0 : (int)Reader["spid"]; shuadan_pingjiaExample.spname = Reader["spname"] == DBNull.Value ? null : Reader["spname"].ToString(); shuadan_pingjiaExample.spremark = Reader["spremark"] == DBNull.Value ? null : Reader["spremark"].ToString(); shuadan_pingjiaExample.spstandby1 = Reader["spstandby1"] == DBNull.Value ? null : Reader["spstandby1"].ToString(); shuadan_pingjiaExample.spstate = Reader["spstate"] == DBNull.Value ? null : Reader["spstate"].ToString(); shuadan_pingjiaExample.sptype = Reader["sptype"] == DBNull.Value ? null : Reader["sptype"].ToString(); return(shuadan_pingjiaExample); }
protected void btn_save_Click(object sender, EventArgs e) { try { string name = txt_name.Text.Trim(); string state = ddl_state.SelectedValue.ToString(); string _type = ddl_type.SelectedValue.ToString(); string remark = txt_remark.Text.Trim(); shuadan_pingjia sp = new shuadan_pingjia(); if (!string.IsNullOrEmpty(id)) { sp = BLL.shuadan_pingjiaManager.SearchByspid(Convert.ToInt32(id)); } else { sp.spdate = DateTime.Now; } sp.spname = name; sp.spremark = remark; sp.spstate = state; sp.sptype = _type; int res = 0; if (string.IsNullOrEmpty(id)) { if (BLL.shuadan_pingjiaManager.SearchNum(sp.spname, "", "", new DateTime(), new DateTime()) < 1) { res = BLL.shuadan_pingjiaManager.Insert(sp); } } else { res = BLL.shuadan_pingjiaManager.Update(sp); } if (res == 1) { Manager.Alert("保存成功", Page); } else { Manager.Alert("保存失败", Page); } } catch (Exception ex) { Manager.Alert(ex.ToString(), Page); } Manager.page_href_reload(Page); }
/// <summary> /// 查询全部数据 /// </summary> /// <returns>IList</returns> /*查看是否为视图*/ public IList <shuadan_pingjia> SearchAll() { Access.DBHelper.sqlstr = "select * from shuadan_pingjia "; List <shuadan_pingjia> list = new List <shuadan_pingjia>(); OleDbDataReader reader = Access.DBHelper.ExecuteReader(); while (reader.Read()) { shuadan_pingjia Obj = GetByReader(reader); list.Add(Obj); } reader.Close(); return(list); }
/// <summary> /// 根据spid,查询一条数据 /// </summary> /// <param name="spid">编号</param> /// <returns></returns> public shuadan_pingjia SearchByspid(int spid) { Access.DBHelper.sqlstr = "select * from shuadan_pingjia where spid = @spid"; OleDbParameter[] param = new OleDbParameter[] { new OleDbParameter("@spid", spid) }; OleDbDataReader reader = Access.DBHelper.ExecuteReader(param); shuadan_pingjia Obj = null; if (reader.Read()) { Obj = GetByReader(reader); } reader.Close(); return(Obj); }
protected void btn_save2_Click(object sender, EventArgs e) { try { string data = txt_data.Text.Trim(); string[] ss = data.Split(new char[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries); int count_ok = 0; int count_no = 0; foreach (string s in ss) { if (!string.IsNullOrEmpty(s)) { shuadan_pingjia sp = new shuadan_pingjia(); string state = ddl_state.SelectedValue.ToString(); string _type = ddl_type.SelectedValue.ToString(); string remark = txt_remark.Text.Trim(); sp.spstate = state; sp.sptype = _type; sp.spremark = remark; sp.spdate = DateTime.Now; sp.spname = s; if (BLL.shuadan_pingjiaManager.SearchNum(sp.spname, "", "", new DateTime(), new DateTime()) < 1) { if (BLL.shuadan_pingjiaManager.Insert(sp) == 1) { count_ok++; } else { count_no++; } } else { count_no++; } } } Manager.Alert("批处理操作完成:\\n成功添加 " + count_ok.ToString() + " 条信息" + (count_no == 0 ? "" : "\\n添加失败" + count_no.ToString() + "条信息!请注意核实!"), Page); } catch (Exception ex) { Manager.Alert(ex.ToString(), Page); } Manager.page_href_reload(Page); }
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { TextBox txt_name = GridView1.Rows[e.RowIndex].FindControl("txt_name") as TextBox; TextBox txt_remark = GridView1.Rows[e.RowIndex].FindControl("txt_remark") as TextBox; int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["spid"]); shuadan_pingjia sa = BLL.shuadan_pingjiaManager.SearchByspid(id); sa.spname = txt_name.Text.Trim(); sa.spremark = txt_remark.Text.Trim(); if (BLL.shuadan_pingjiaManager.Update(sa) != 1) { AJAXManager.Alert(this.UpdatePanel1, "修改失败"); } else { GridView1.EditIndex = -1; bind(); } }
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) { string state = e.CommandName; if (state == "state") { int id = Convert.ToInt32(GridView1.DataKeys[((e.CommandSource as LinkButton).NamingContainer as GridViewRow).RowIndex].Values["spid"].ToString()); if (id != 0) { shuadan_pingjia sa = BLL.shuadan_pingjiaManager.SearchByspid(id); sa.spstate = sa.spstate == "1" ? "2" : "1"; if (BLL.shuadan_pingjiaManager.Update(sa) != 1) { AJAXManager.Alert(this.UpdatePanel1, "修改失败"); } else { GridView1.EditIndex = -1; bind(); } } } else if (state == "type") { int id = Convert.ToInt32(GridView1.DataKeys[((e.CommandSource as LinkButton).NamingContainer as GridViewRow).RowIndex].Values["spid"].ToString()); if (id != 0) { shuadan_pingjia sa = BLL.shuadan_pingjiaManager.SearchByspid(id); sa.sptype = sa.sptype == "1" ? "2" : (sa.sptype == "2" ? "3" : "1"); if (BLL.shuadan_pingjiaManager.Update(sa) != 1) { AJAXManager.Alert(this.UpdatePanel1, "修改失败"); } else { GridView1.EditIndex = -1; bind(); } } } }
/// <summary> /// 获取评价数组 /// </summary> /// <param name="num">评价条数</param> /// <param name="s">评价句数</param> /// <param name="e">评价句数</param> /// <param name="sTime">评价起始时间</param> /// <param name="eTime">评价结束时间</param> /// <returns>List<string></returns> public static List <string> GetPingjia(int num, int s, int e, DateTime sTime, DateTime eTime) { List <string> list = new List <string>(); for (int i = 0; i < num; i++) { int temp = Manager.RandomNumber(s, e); string temp_str = ""; IList <shuadan_pingjia> list2 = SearchRnd(temp, "", "1", "1", sTime, eTime); //获取质量评语随机位置 int temp_zhiliang = Manager.RandomNumber(1, temp); int zhiliang = 1; foreach (shuadan_pingjia sp in list2) { if (temp % 2 == 0) { if (zhiliang == temp_zhiliang) { //判断是否为偶数,如果是偶数则添加质量评语 shuadan_pingjia sp_zhiliang = new shuadan_pingjia(); IList <shuadan_pingjia> temp_list = SearchRnd(1, "", "1", "2", new DateTime(), new DateTime()); if (temp_list.Count > 0) { sp_zhiliang = temp_list[0]; temp_str = temp_str + sp_zhiliang.spname + ","; } } zhiliang++; } temp_str = temp_str + sp.spname + ","; } IList <shuadan_pingjia> list3 = SearchRnd(1, "", "1", "3", sTime, eTime); temp_str = temp_str + (list3.Count > 0 ? list3[0].spname : ""); list.Add(temp_str); } return(list); }
/// <summary> /// 随机查询名字 /// </summary> /// <returns>IList</returns> /*查看是否为视图*/ public IList <shuadan_pingjia> SearchRnd(int num, string key, string state, string _type, DateTime date1, DateTime date2) { string sql1 = "SELECT top " + num.ToString() + " * FROM shuadan_pingjia where "; string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( spname like '%" + key + "%' or spremark like '%" + key + "%' ) "; string sql3 = string.IsNullOrEmpty(state) ? "" : " and spstate= '" + state + "' "; string sql4 = string.IsNullOrEmpty(_type) ? "" : " and sptype= '" + _type + "' "; string sql5 = date1 == new DateTime() ? "" : " and datediff('d','" + date1.ToString() + "',spdate)>=0 "; string sql6 = date2 == new DateTime() ? "" : " and datediff('d','" + date2.ToString() + "',spdate)<=0 "; string sql7 = " order by Rnd(spid) ";//排序两个字段,否则数据容易错误 Access.DBHelper.sqlstr = sql1 + sql2 + sql3 + sql4 + sql5 + sql6 + sql7; List <shuadan_pingjia> list = new List <shuadan_pingjia>(); OleDbDataReader reader = Access.DBHelper.ExecuteReader(); while (reader.Read()) { shuadan_pingjia Obj = GetByReader(reader); list.Add(Obj); } reader.Close(); return(list); }
void bind() { if (!string.IsNullOrEmpty(id)) { int temp = 0; if (int.TryParse(id, out temp)) { shuadan_pingjia sp = BLL.shuadan_pingjiaManager.SearchByspid(temp); if (sp != null) { try { txt_name.Text = sp.spname; txt_remark.Text = sp.spremark; ddl_state.SelectedValue = sp.spstate; ddl_type.SelectedValue = sp.sptype; liter_date.Text = sp.spdate.ToString(); } catch { } } } } }
/// <summary> /// 更新 /// </summary> /// <param name="shuadan_pingjia">shuadan_pingjia表实例</param> /// <returns>int</returns> public int Update(shuadan_pingjia shuadan_pingjiaExample) { Access.DBHelper.sqlstr = "update shuadan_pingjia set spdate='" + shuadan_pingjiaExample.spdate.ToString() + "',spname=@spname,spremark=@spremark,spstandby1=@spstandby1,spstate=@spstate,sptype=@sptype where spid=" + shuadan_pingjiaExample.spid; return(Access.DBHelper.ExecuteNonQuery(GetOleDbParameter(shuadan_pingjiaExample))); }
/// <summary> /// 插入方法 /// </summary> /// <param name="shuadan_pingjia">shuadan_pingjia表实例</param> /// <returns>int</returns> public int Insert(shuadan_pingjia shuadan_pingjiaExample) { Access.DBHelper.sqlstr = "insert into shuadan_pingjia (spdate,spname,spremark,spstandby1,spstate,sptype)values('" + shuadan_pingjiaExample.spdate.ToString() + "',@spname,@spremark,@spstandby1,@spstate,@sptype)"; return(Access.DBHelper.ExecuteNonQuery(GetOleDbParameter(shuadan_pingjiaExample))); }
/// <summary> /// 更新 /// </summary> /// <param name="shuadan_pingjia">shuadan_pingjia表实例</param> /// <returns>int</returns> public static int Update(shuadan_pingjia shuadan_pingjiaExample) { return(Service.Update(shuadan_pingjiaExample)); }
/// <summary> /// 插入方法 /// </summary> /// <param name="shuadan_pingjia">shuadan_pingjia表实例</param> /// <returns>int</returns> public static int Insert(shuadan_pingjia shuadan_pingjiaExample) { return(Service.Insert(shuadan_pingjiaExample)); }