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();
}
}
}