private void dgv2_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
//如果点击页头
if (e.RowIndex == -1)
{
//如果是全选
if (e.ColumnIndex == 0)
{
foreach (DataGridViewRow row in dgv2.Rows)
{
if (row.Index != -1)
{
DataGridViewCheckBoxCell cbx = (DataGridViewCheckBoxCell)row.Cells[0];
cbx.Value = true;
}
}
}
//排序事件
try
{
//dgv1.Sort(dgv1.Columns[e.ColumnIndex], ListSortDirection.Descending);
}
catch (Exception ex)
{
Debug.WriteLine("排序出错=========================" + ex.Message);
}
}
else if (dgv1.Columns[e.ColumnIndex] is DataGridViewButtonColumn)
{
yh_zaoci zc = (Entity.yh_zaoci)dgv2.CurrentRow.DataBoundItem;
if (zc != null)
{
if (MessageBox.Show("确定要删除 【" + zc.zname + "】么?", "", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
if (BLL2.yh_zaociManager.Delete(zc.zid) == 1)
{
MessageBox.Show("删除成功");
bind();
}
else
{
MessageBox.Show("删除失败");
}
}
}
}
else if (dgv1.Columns[e.ColumnIndex] is DataGridViewLinkColumn)
{
yh_zaoci zc = (Entity.yh_zaoci)dgv2.CurrentRow.DataBoundItem;
if (zc != null)
{
zc.zstate = zc.zstate == "1" ? "2" : "1";
BLL2.yh_zaociManager.Update(zc);
}
}
}
/// <summary>
/// 根据表,获取一个OleDbParameter数组
/// </summary>
/// <returns>OleDbParameter[]</returns>
public static OleDbParameter[] GetOleDbParameter(yh_zaoci yh_zaociExample)
{
List <OleDbParameter> list_param = new List <OleDbParameter>();
if (yh_zaociExample.gtid != 0)
{
list_param.Add(new OleDbParameter("@gtid", yh_zaociExample.gtid));
}
else
{
list_param.Add(new OleDbParameter("@gtid", DBNull.Value));
}
if (!string.IsNullOrEmpty(yh_zaociExample.gtname))
{
list_param.Add(new OleDbParameter("@gtname", yh_zaociExample.gtname));
}
else
{
list_param.Add(new OleDbParameter("@gtname", DBNull.Value));
}
if (yh_zaociExample.zdate != new DateTime() && yh_zaociExample.zdate != null)
{
list_param.Add(new OleDbParameter("@zdate", yh_zaociExample.zdate.ToString("yyyy-MM-dd")));
}
else
{
list_param.Add(new OleDbParameter("@zdate", DBNull.Value));
}
if (!string.IsNullOrEmpty(yh_zaociExample.zname))
{
list_param.Add(new OleDbParameter("@zname", yh_zaociExample.zname));
}
else
{
list_param.Add(new OleDbParameter("@zname", DBNull.Value));
}
if (!string.IsNullOrEmpty(yh_zaociExample.zstate))
{
list_param.Add(new OleDbParameter("@zstate", yh_zaociExample.zstate));
}
else
{
list_param.Add(new OleDbParameter("@zstate", 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>yh_zaociExample</returns>
public static yh_zaoci GetByReader(OleDbDataReader Reader)
{
yh_zaoci yh_zaociExample = new yh_zaoci();
yh_zaociExample.gtid = Reader["gtid"] == DBNull.Value ? 0 : (int)Reader["gtid"];
yh_zaociExample.gtname = Reader["gtname"] == DBNull.Value ? null : Reader["gtname"].ToString();
yh_zaociExample.zdate = Reader["zdate"] == DBNull.Value ? new DateTime() : Convert.ToDateTime(Reader["zdate"]);
yh_zaociExample.zid = Reader["zid"] == DBNull.Value ? 0 : (int)Reader["zid"];
yh_zaociExample.zname = Reader["zname"] == DBNull.Value ? null : Reader["zname"].ToString();
yh_zaociExample.zstate = Reader["zstate"] == DBNull.Value ? null : Reader["zstate"].ToString();
return(yh_zaociExample);
}
/// <summary>
/// 查询全部数据
/// </summary>
/// <returns>IList</returns>
/*查看是否为视图*/
public IList <yh_zaoci> SearchAll()
{
DBHelper.sqlstr = "select * from yh_zaoci ";
List <yh_zaoci> list = new List <yh_zaoci>();
MySqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
yh_zaoci Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
private void dgv1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
//如果点击页头
if (e.RowIndex == -1)
{
//排序事件
try
{
//dgv1.Sort(dgv1.Columns[e.ColumnIndex], ListSortDirection.Descending);
}
catch (Exception ex)
{
Debug.WriteLine("排序出错=========================" + ex.Message);
}
}
else if (dgv1.Columns[e.ColumnIndex] is DataGridViewButtonColumn)
{
yh_zaoci zc = (Entity.yh_zaoci)dgv1.CurrentRow.DataBoundItem;
if (zc != null)
{
if (MessageBox.Show("确定要删除 【" + zc.zname + "】么?", "", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
if (BLL2.yh_zaociManager.Delete(zc.zid) == 1)
{
//删除二级数据
foreach (yh_zaoci z in BLL2.yh_zaociManager.Search(1, 9999, "", "", zc.zid, new DateTime(), new DateTime(), ""))
{
BLL2.yh_zaociManager.Delete(z.zid);
}
MessageBox.Show("删除成功");
bind();
}
else
{
MessageBox.Show("删除失败");
}
}
}
}
else if (dgv1.Columns[e.ColumnIndex] is DataGridViewLinkColumn)
{
yh_zaoci zc = (Entity.yh_zaoci)dgv1.CurrentRow.DataBoundItem;
if (zc != null)
{
zc.zstate = zc.zstate == "1" ? "2" : "1";
BLL2.yh_zaociManager.Update(zc);
bind();
}
}
}
protected void btn_quickadd_Click(object sender, EventArgs e)
{
string str = txt_quickadd.Text.Trim();
string remark = txt_remark.Text.Trim();
string state = ddl_state.SelectedValue.ToString();
string gt = ddl_goodstype.SelectedValue;
yh_zaoci zc = new yh_zaoci();
zc.gtid = Convert.ToInt32(gt);
zc.gtname = remark;
zc.zstate = state;
int res1 = 0;
int res2 = 0;
if (string.IsNullOrEmpty(str))
{
return;
}
try
{
string[] ss = str.Split(new char[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string s in ss)
{
string temp = s.Trim();
if (!string.IsNullOrEmpty(temp))
{
zc.zname = temp;
zc.zdate = DateTime.Now;
if (BLL.yh_zaociManager.Insert(zc) == 1)
{
res1++;
}
else
{
res2++;
}
}
}
Manager.Alert("批处理操作完成:\\n成功添加 " + res1.ToString() + " 条信息" + (res2 == 0 ? "" : "\\n添加失败" + res2.ToString() + "条信息！请注意核实！"), Page);
}
catch (Exception ex)
{
Manager.Alert(ex.ToString(), Page);
}
}
/// <summary>
/// 根据zid,查询一条数据
/// </summary>
/// <param name="zid">编号</param>
/// <returns></returns>
/*查看是否为视图*/
public yh_zaoci SearchByID(int zid)
{
DBHelper.sqlstr = "select * from yh_zaoci where zid = @zid";
MySqlParameter[] param = new MySqlParameter[] {
new MySqlParameter("@zid", zid)
};
MySqlDataReader reader = DBHelper.ExecuteReader(param);
yh_zaoci Obj = null;
if (reader.Read())
{
Obj = GetByReader(reader);
}
reader.Close();
return(Obj);
}
/// <summary>
/// 根据zid,查询一条数据
/// </summary>
/// <param name="zid">噪词ID</param>
/// <returns></returns>
public yh_zaoci SearchByzid(int zid)
{
Access.DBHelper.sqlstr = "select * from yh_zaoci where zid = @zid";
OleDbParameter[] param = new OleDbParameter[] {
new OleDbParameter("@zid", zid)
};
OleDbDataReader reader = Access.DBHelper.ExecuteReader(param);
yh_zaoci Obj = null;
if (reader.Read())
{
Obj = GetByReader(reader);
}
reader.Close();
return(Obj);
}
protected void btn_save_Click(object sender, EventArgs e)
{
try
{
string name = txt_name.Text.Trim();
string remark = txt_remark.Text.Trim();
string state = ddl_state.SelectedValue.ToString();
string gt = ddl_goodstype.SelectedValue;
yh_zaoci zc = new yh_zaoci();
if (!string.IsNullOrEmpty(id))
{
zc = BLL.yh_zaociManager.SearchByID(Convert.ToInt32(id));
}
else
{
zc.zdate = DateTime.Now;
}
zc.gtid = Convert.ToInt32(gt);
zc.gtname = remark;
zc.zname = name;
zc.zstate = state;
int res;
if (string.IsNullOrEmpty(id))
{
res = BLL.yh_zaociManager.Insert(zc);
}
else
{
res = BLL.yh_zaociManager.Update(zc);
}
if (res == 1)
{
Manager.Alert("保存成功", Page);
}
else
{
Manager.Alert("保存失败", Page);
}
}
catch (Exception ex)
{
Manager.Alert(ex.ToString());
}
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;
TextBox txt_sort = GridView1.Rows[e.RowIndex].FindControl("txt_sort") as TextBox;
int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["zid"]);
yh_zaoci zc = BLL.yh_zaociManager.SearchByID(id);
zc.zname = string.IsNullOrEmpty(txt_name.Text) ? "" : txt_name.Text.Trim();
zc.gtname = string.IsNullOrEmpty(txt_name.Text) ? "" : txt_remark.Text.Trim();
if (BLL.yh_zaociManager.Update(zc) != 1)
{
Manager.Alert("修改失败", Page);
}
Manager.GridView_ondblclick_Cancel(Page);
}
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["zid"].ToString());
if (id != 0)
{
yh_zaoci zc = BLL.yh_zaociManager.SearchByID(id);
zc.zstate = zc.zstate == "1" ? "2" : "1";
if (BLL.yh_zaociManager.Update(zc) != 1)
{
Manager.Alert("修改失败", Page);
}
}
Manager.page_href_reload(Page);
}
}
private void dgv2_CellParsing(object sender, DataGridViewCellParsingEventArgs e)
{
try
{
if (dgv2.CurrentCell.ColumnIndex == 1)
{
yh_zaoci zc = (yh_zaoci)dgv2.CurrentRow.DataBoundItem;
if (zc != null)
{
string name = dgv2.Rows[e.RowIndex].Cells[1].EditedFormattedValue == null ? zc.zname : dgv2.Rows[e.RowIndex].Cells[1].EditedFormattedValue.ToString();
zc.zname = name;
BLL2.yh_zaociManager.Update(zc);
}
}
}
catch (Exception ex)
{
Debug.WriteLine("修改信息失败 " + ex.Message);
}
}
private void btn_fuadd_Click(object sender, EventArgs e)
{
string name = txt_funame.Text.Trim();
if (string.IsNullOrEmpty(name))
{
MessageBox.Show("请输入正确的名称");
txt_funame.Focus();
return;
}
else
{
yh_zaoci zc = new yh_zaoci();
zc.zname = name;
zc.zdate = DateTime.Now;
zc.zsort = 100;
zc.zstate = "1";
zc.gtid = 0;
BLL2.yh_zaociManager.Insert(zc);
bind();
}
}
/// <summary>
/// 条件搜索
/// </summary>
/// <param name="s">起始位置</param>
/// <param name="e">结束位置</param>
/// <param name="key">关键词</param>
/// <param name="gt">商品类型</param>
/// <param name="state">状态 空:不查,1:启用,2:禁用</param>
/// <param name="dt1_add">添加起始时间</param>
/// <param name="dt2_add">添加结束时间</param>
/// <param name="orderby">排序方式,直接输入order by后面的语句</param>
/// <returns>IList<yh_zaoci></returns>
public IList <yh_zaoci> Search(int s, int e, string key, int gt, string state, DateTime dt1_add, DateTime dt2_add, string orderby)
{
string sql1 = "select top " + (e - s + 1).ToString() + " * from yh_zaoci where ";
string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( zname like '%" + key + "%' or gtname like '%" + key + "%' ) ";
string sql3 = gt == 0 ? "" : " and gtid=" + gt.ToString();
string sql4 = string.IsNullOrEmpty(state) ? "" : " and zstate= '" + state + "' ";
string sql5 = dt1_add == new DateTime() ? "" : " and datediff('d','" + dt1_add.ToString() + "',zdate)>=0 ";
string sql6 = dt2_add == new DateTime() ? "" : " and datediff('d','" + dt2_add.ToString() + "',zdate)<=0 ";
string sql8 = string.IsNullOrEmpty(orderby) ? " order by zdate desc,zid asc " : " order by " + orderby;
string sql7 = s == 1 ? "" : " and zid not in ( select top " + (s - 1).ToString() + " zid from yh_zaoci where " + sql2 + sql3 + sql4 + sql5 + sql6 + sql8 + " ) ";
Access.DBHelper.sqlstr = sql1 + sql2 + sql3 + sql4 + sql5 + sql6 + sql7 + sql8;
List <yh_zaoci> list = new List <yh_zaoci>();
OleDbDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
yh_zaoci Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
private void btn_del_Click(object sender, EventArgs e)
{
if (MessageBox.Show("是否要删除所有勾选噪词?", "", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
foreach (DataGridViewRow row in dgv2.Rows)
{
if (row.Index != -1)
{
DataGridViewCheckBoxCell cbx = (DataGridViewCheckBoxCell)row.Cells[0];
if ((bool)cbx.FormattedValue)
{
yh_zaoci zc = (Entity.yh_zaoci)row.DataBoundItem;
if (zc != null)
{
BLL2.yh_zaociManager.Delete(zc.zid);
}
}
}
}
}
bind2();
MessageBox.Show("删除成功");
}
void bind()
{
if (!string.IsNullOrEmpty(id))
{
int temp = 0;
if (int.TryParse(id, out temp))
{
yh_zaoci zc = yh_zaociManager.SearchByID(temp);
if (zc != null)
{
try
{
txt_name.Text = zc.zname;
txt_remark.Text = zc.gtname;
lbl_date.Text = zc.zdate.ToString();
ddl_goodstype.SelectedValue = zc.gtid.ToString();
ddl_state.SelectedValue = zc.zstate;
}
catch { }
}
}
}
}
/// <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<yh_zaoci></returns>
public IList <yh_zaoci> 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 yh_zaoci where ";
string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( zname like '%" + key + "%' or zstate like '%" + key + "%' or gtname like '%" + key + "%' )"; //删除无用字段，删除最后一个or
string sql3 = string.IsNullOrEmpty(state) ? "" : " and zstate= '" + state + "' "; //状态字段，无用删除
string sql4 = " and gtid='" + id.ToString() + "' "; //Int字段，无用删除
string sql5 = startdate == new DateTime() ? "" : " and DATEDIFF(D,'" + startdate.ToString("yyyy-MM-dd") + "',zdate)>=0 "; //时间字段，无用删除
string sql6 = enddate == new DateTime() ? "" : " and DATEDIFF(D,'" + enddate.ToString("yyyy-MM-dd") + "',zdate)<=0 "; //时间字段，无用删除
string sql7 = string.IsNullOrEmpty(orderby) ? " order by zsort asc " : " order by " + orderby;
string sql8 = s == 1 ? "" : " and zid not in ( select top " + (s - 1).ToString() + " zid from yh_zaoci where " + sql2 + sql3 + sql4 + sql5 + sql6 + sql7 + " ) ";
DBHelper.sqlstr = sql1 + sql2 + sql3 + sql4 + sql5 + sql6 + sql8 + sql7;
List <yh_zaoci> list = new List <yh_zaoci>();
SqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
yh_zaoci Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
/// <summary>
/// 添加噪词
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_add_Click(object sender, EventArgs e)
{
string names = txt_names.Text;
if (!string.IsNullOrEmpty(names))
{
string[] datas = System.Text.RegularExpressions.Regex.Split(names, "\n", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
yh_zaoci zc = new yh_zaoci();
zc.gtid = Convert.ToInt32(cb_type.SelectedValue);
zc.zdate = DateTime.Now;
zc.zsort = 100;
zc.zstate = "1";
foreach (string temp in datas)
{
if (!string.IsNullOrEmpty(temp))
{
zc.zname = temp.Replace("\r", "");;
BLL2.yh_zaociManager.Insert(zc);
}
}
MessageBox.Show("噪词添加成功");
bind2();
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="yh_zaoci">yh_zaoci表实例</param>
/// <returns>int</returns>
public static int Update(yh_zaoci yh_zaociExample)
{
return(Service.Update(yh_zaociExample));
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="yh_zaoci">yh_zaoci表实例</param>
/// <returns>int</returns>
public static int Insert(yh_zaoci yh_zaociExample)
{
return(Service.Insert(yh_zaociExample));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="yh_zaoci">yh_zaoci表实例</param>
/// <returns>int</returns>
public int Update(yh_zaoci yh_zaociExample)
{
DBHelper.sqlstr = "update yh_zaoci set zname=@zname,zdate=@zdate,zstate=@zstate,gtid=@gtid,gtname=@gtname,zsort=@zsort where zid=" + yh_zaociExample.zid;
return(DBHelper.ExecuteNonQuery(GetSqlParameters(yh_zaociExample)));
}
/// <summary>
/// 插入方法
/// </summary>
/// <param name="yh_zaoci">yh_zaoci表实例</param>
/// <returns>int</returns>
public int Insert(yh_zaoci yh_zaociExample)
{
DBHelper.sqlstr = "insert into yh_zaoci (zname,zdate,zstate,gtid,gtname,zsort)values(@zname,@zdate,@zstate,@gtid,@gtname,@zsort)";
return(DBHelper.ExecuteNonQuery(GetSqlParameters(yh_zaociExample)));
}
/// <summary>
/// 根据表,获取一个MySqlParameter数组
/// </summary>
/// <returns>MySqlParameter[]</returns>
public static MySqlParameter[] GetSqlParameters(yh_zaoci yh_zaociExample)
{
List <MySqlParameter> list_param = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(yh_zaociExample.zname))
{
list_param.Add(new MySqlParameter("@zname", yh_zaociExample.zname));
}
else
{
list_param.Add(new MySqlParameter("@zname", DBNull.Value));
}
if (yh_zaociExample.zdate != new DateTime() && yh_zaociExample.zdate != null)
{
list_param.Add(new MySqlParameter("@zdate", yh_zaociExample.zdate));
}
else
{
list_param.Add(new MySqlParameter("@zdate", DBNull.Value));
}
if (!string.IsNullOrEmpty(yh_zaociExample.zstate))
{
list_param.Add(new MySqlParameter("@zstate", yh_zaociExample.zstate));
}
else
{
list_param.Add(new MySqlParameter("@zstate", DBNull.Value));
}
//if (yh_zaociExample.gtid != 0)
//{
list_param.Add(new MySqlParameter("@gtid", yh_zaociExample.gtid));
//}
//else
//{
// list_param.Add(new MySqlParameter("@gtid", DBNull.Value));
//}
if (!string.IsNullOrEmpty(yh_zaociExample.gtname))
{
list_param.Add(new MySqlParameter("@gtname", yh_zaociExample.gtname));
}
else
{
list_param.Add(new MySqlParameter("@gtname", DBNull.Value));
}
if (yh_zaociExample.zsort != 0)
{
list_param.Add(new MySqlParameter("@zsort", yh_zaociExample.zsort));
}
else
{
list_param.Add(new MySqlParameter("@zsort", 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="yh_zaoci">yh_zaoci表实例</param>
/// <returns>int</returns>
public int Insert(yh_zaoci yh_zaociExample)
{
Access.DBHelper.sqlstr = "insert into yh_zaoci (gtid,gtname,zdate,zname,zstate)values(@gtid,@gtname,'" + yh_zaociExample.zdate.ToString() + "',@zname,@zstate)";
return(Access.DBHelper.ExecuteNonQuery(GetOleDbParameter(yh_zaociExample)));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="yh_zaoci">yh_zaoci表实例</param>
/// <returns>int</returns>
public int Update(yh_zaoci yh_zaociExample)
{
Access.DBHelper.sqlstr = "update yh_zaoci set gtid=@gtid,gtname=@gtname,zdate='" + yh_zaociExample.zdate.ToString() + "',zname=@zname,zstate=@zstate where zid=" + yh_zaociExample.zid;
return(Access.DBHelper.ExecuteNonQuery(GetOleDbParameter(yh_zaociExample)));
}