SqlConnection con; SqlDataAdapter da; SqlCommand com; SqlDataReader dr; DataSet ds; static string real = "ASC "; protected void Page_Load(object sender, EventArgs e) { Page.SmartNavigation = true; if (!this.IsPostBack) { this.DropBind(this.droptj); this.Bind(); } } /***************************************基本方法**********************************************/ #region//数据库连接字符串 public void conString() { //string constr = "server=.;uid=sa;pwd=;database=Demo "; string constr = "server=.;uid=sa;pwd=;database=pubs "; con = new SqlConnection(constr); } #endregion #region//得到记录集方法 public DataSet GetData(string sql) { this.conString(); da = new SqlDataAdapter(sql,con); ds = new DataSet(); da.Fill(ds); return ds; } #endregion #region//下拉列表绑定 public void DropBind(DropDownList ddl) { this.conString(); ListItem lq = new ListItem( "全部 ", "0 "); ddl.Items.Add(lq); string sql = "select DISTINCT state from authors "; com = new SqlCommand(sql,con); con.Open(); dr = com.ExecuteReader(); while (dr.Read()) { ListItem li = new ListItem(); li.Text = dr[ "state "].ToString(); li.Value = dr[ "state "].ToString(); ddl.Items.Add(li); } dr.Close(); con.Close(); } #endregion #region//绑定GridView方法 public void Bind() { this.conString(); string sql = "select * from authors "; DataSet dsbind = this.GetData(sql); this.GridView1.DataSource = dsbind; this.GridView1.DataBind(); } #endregion /***********************************选择及分页事件********************************************/ #region//GridView选择事件 protected void GridView1_SelectedIndexChanged(object sender, EventArgs e) { this.lblkey.Text = this.GridView1.SelectedRow.Cells[0].Text; /*如果选中的行分页后还会选中的是那一行(例如：如果第一页选中第一行,那么翻到第二页的话还是选中的第一行) * 下面方法解决此问题 */ Session[ "pageIndex "]=this.GridView1.PageIndex; Session[ "pageRow "] = this.GridView1.SelectedIndex; } #endregion #region//GridView分页事件 protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { /*如果选中的行分页后还会选中的是那一行(例如：如果第一页选中第一行,那么翻到第二页的话还是选中的第一行) * 下面方法解决此问题 */ int pageIndex = e.NewPageIndex;//得到当前页索引 if (pageIndex == Convert.ToInt32(Session[ "pageIndex "])) { GridView1.SelectedIndex = Convert.ToInt32(Session[ "pageRow "]); } else { GridView1.SelectedIndex = -1; } this.GridView1.PageIndex = e.NewPageIndex; this.Bind(); } #endregion /************************************条件查询,GridView行绑定事件******************************/ #region//条件查询 protected void droptj_SelectedIndexChanged(object sender, EventArgs e) { if (this.droptj.SelectedIndex == 0)//选择全部 { string sql = "select * from authors "; DataSet ds = this.GetData(sql); this.GridView1.DataSource = ds; this.GridView1.DataBind(); } else//有条件选择 { string sql = "select * from authors where state= ' " + this.droptj.SelectedItem.Text + " ' "; DataSet ds = this.GetData(sql); this.GridView1.DataSource = ds; this.GridView1.DataBind(); } } #endregion #region//行绑定事件 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { //鼠标略过行改变行颜色 e.Row.Attributes.Add( "onmouseover ", "c=style.backgroundColor;style.backgroundColor= 'skyblue '; "); e.Row.Attributes.Add( "onmouseout ", "style.backgroundColor=c "); //state列如果有值等于 "KS ",则把这行的颜色改变成黄色 for (int i = 0; i < this.GridView1.Rows.Count; i++) { string lblstr = " "; lblstr = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "state ")); if (lblstr == "KS ") { e.Row.BackColor = Color.Yellow; } } } #endregion /***************************************GridView排序*****************************************/ #region//排序事件 protected void GridView1_Sorting(object sender, GridViewSortEventArgs e) { string dvsql = " "; if (real == "ASC ") { real = "DESC "; dvsql = "select * from authors order by " + e.SortExpression + " " + real; } else { real = "ASC "; dvsql = "select * from authors order by " + e.SortExpression + " " + real; } DataSet ds=this.GetData(dvsql); GridView1.DataSource = ds; GridView1.DataBind(); } #endregion /***************************************GridView编辑列事件************************************/ #region//展开编辑列事件 protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { this.GridView1.EditIndex = (int)e.NewEditIndex; this.Bind(); } #endregion #region//更新展开编辑列事件 protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { string nameStr = " "; string addressStr = " "; string cityStr = " "; string stateStr = " "; string zipStr = " "; string updateStr = "update authors set au_lname=@name,address=@address,city=@city,state=@state,zip=@zip where au_id=@id "; this.conString(); com = new SqlCommand(updateStr,con); com.Parameters.Add( "@name ",System.Data.SqlDbType.VarChar,40); com.Parameters.Add( "@address ",System.Data.SqlDbType.VarChar,40); com.Parameters.Add( "@city ",System.Data.SqlDbType.VarChar,20); com.Parameters.Add( "@state ",System.Data.SqlDbType.Char,2) ; com.Parameters.Add( "@zip ",System.Data.SqlDbType.Char,5); com.Parameters.Add( "@id ",System.Data.SqlDbType.Int,4); GridViewRow gvRow = this.GridView1.Rows[e.RowIndex];//建立GridView行对象 TextBox nametxt = (TextBox)gvRow.Controls[1].Controls[0]; TextBox addresstxt = (TextBox)gvRow.Controls[2].Controls[0]; TextBox citytxt = (TextBox)gvRow.Controls[3].Controls[0]; TextBox statetxt = (TextBox)gvRow.Controls[4].Controls[0]; TextBox ziptxt = (TextBox)gvRow.Controls[5].Controls[0]; nameStr = nametxt.Text; addressStr = addresstxt.Text; cityStr = citytxt.Text; stateStr = statetxt.Text; zipStr = ziptxt.Text; string ValStr = this.ValUpdate(nameStr,addressStr,cityStr,stateStr,zipStr); if (ValStr == "ok ") { com.Parameters[ "@id "].Value = this.GridView1.DataKeys[(int)e.RowIndex].Value.ToString(); com.Parameters[ "@name "].Value = nameStr; com.Parameters[ "@address "].Value = addressStr; com.Parameters[ "@city "].Value = cityStr; com.Parameters[ "@state "].Value = stateStr; com.Parameters[ "@zip "].Value = zipStr; try { con.Open(); com.ExecuteNonQuery(); this.Label3.Text = "更新成功! "; con.Close(); } catch (Exception ex) { this.Label3.Text = "无法更新! " + ex.Message; } finally { this.GridView1.EditIndex = -1; this.Bind(); } } else { this.Label3.Text = ValStr; } } #endregion #region//取消展开编辑列事件 protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { this.GridView1.EditIndex = -1; this.Bind(); } #endregion #region//GridView更新时验证方法 public string ValUpdate(string name,string address,string city,string state,string zip) { string ok = " "; if (name == " " || address == " " || city == " " || state == " " || zip == " ")// && ) { ok += "不能有空值 "; } else if (state.Length > 2) { ok += "洲的长度不能大于2 "; } else if (zip.Length != 5) { ok += "邮编的长度必须等于5位 "; } else { ok = "ok "; } return ok; } #endregion protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) { //单选删除例子 CheckBox chk; if (e.CommandName == "del ") { foreach (GridViewRow dvr in GridView1.Rows) { chk = (CheckBox)dvr.FindControl( "CheckBox1 "); if (chk.Checked) { string id = e.CommandArgument.ToString();//前台把主键值绑定在按钮上 string id1 = dvr.Cells[1].Text;//通过页面上的绑定列取得主键 //通过GridView的DataKeys集合取得主键 string id2 = GridView1.DataKeys[(int)dvr.RowIndex].Value.ToString(); } } } } //批量删除例子 protected void alldelbtn_Click(object sender, EventArgs e) { CheckBox chk; foreach (GridViewRow gvr in GridView1.Rows) { chk = (CheckBox)gvr.FindControl( "CheckBox1 "); if (chk.Checked) { Button btn=(Button)gvr.FindControl( "delbtn ");//借用单选删除按钮上绑定主键值 string id = btn.CommandArgument.ToString(); string id1 = gvr.Cells[1].Text;//通过页面上的绑定列取得主键 //通过GridView的DataKeys集合取得主键 string id2 = GridView1.DataKeys[(int)gvr.RowIndex].Value.ToString(); } } }
