功能齐全的DataGrid

        private string constring="";        
        
        private void Page_Load(object sender, System.EventArgs e)
        {
            if(!Page.IsPostBack)
            {
                ListProvince();
                ListCity();
                ListArea();

                btndelete.Attributes.Add("onclick","return Delete();");
                SortExpression="";
                sort="desc";
                DataBind();
            }            
        }

        DataBind#region DataBind
        private void DataBind()
        {
            string sql="select a.UserID,a.UserName,b.province,c.city,d.area,a.Enabled,b.provinceID,c.cityID,d.areaID from testgrid a ";
            sql+=" left join province b on a.provinceID=b.provinceID";
            sql+=" left join city c on a.cityID=c.cityID";
            sql+=" left join area d on a.areaID=d.areaID";
            DataSet ds=GetDataSet(sql);
            dt=ds.Tables[0];
            dt.DefaultView.Sort="UserID asc";
            dt.AcceptChanges();
            this.DataGrid1.DataSource=dt;
            this.DataGrid1.DataBind();
        }
        #endregion

        Web Form Designer generated code#region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
            this.DataGrid1.ItemDataBound+=new DataGridItemEventHandler(DataGrid1_ItemDataBound);
            this.DataGrid1.SortCommand+=new DataGridSortCommandEventHandler(DataGrid1_SortCommand);
            this.DataGrid1.ItemCreated+=new DataGridItemEventHandler(DataGrid1_ItemCreated);
            this.DataGrid1.PageIndexChanged+=new DataGridPageChangedEventHandler(DataGrid1_PageIndexChanged);
            this.btndelete.Click += new System.EventHandler(this.btndelete_Click);
            this.DropDownList1.SelectedIndexChanged += new System.EventHandler(this.DropDownList1_SelectedIndexChanged);
            this.DropDownList2.SelectedIndexChanged += new System.EventHandler(this.DropDownList2_SelectedIndexChanged);
            this.Button1.Click += new System.EventHandler(this.Button1_Click);
            this.Button2.Click += new System.EventHandler(this.Button2_Click);
        }
        
        /**//// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {    
            this.DropDownList1.SelectedIndexChanged += new System.EventHandler(this.DropDownList1_SelectedIndexChanged);
            this.DropDownList2.SelectedIndexChanged += new System.EventHandler(this.DropDownList2_SelectedIndexChanged);
            this.Button1.Click += new System.EventHandler(this.Button1_Click);
            this.Button2.Click += new System.EventHandler(this.Button2_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        GetDataSet#region GetDataSet
        private DataSet GetDataSet(string sql)
        {
            constring=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
            SqlDataAdapter    sda =new SqlDataAdapter(sql,constring);
            DataSet ds=new DataSet();
            sda.Fill(ds);
            return ds;
        }
        #endregion
        
        DataGrid1_ItemDataBound#region DataGrid1_ItemDataBound
        private void DataGrid1_ItemDataBound(object sender, DataGridItemEventArgs e)
        {            
            e.Item.Cells[1].Text=Convert.ToString(e.Item.ItemIndex+1);//产生序号
            if(e.Item.ItemType!=ListItemType.Header)
            {
                if(e.Item.ItemIndex%2==0)
                {
                    e.Item.Attributes.Add( "onmouseout","this.style.backgroundColor='PaleGoldenrod'");
                }
                else
                {
                    e.Item.Attributes.Add( "onmouseout","this.style.backgroundColor='LightGoldenrodYellow'");
                }
　　            e.Item.Attributes.Add( "onmouseover","this.style.backgroundColor='#C1D2EE'");
            }
            switch (e.Item.ItemType)
            {
                case ListItemType.Item:
                case ListItemType.AlternatingItem:
                    e.Item.Attributes.Add("ondblclick","alert('"+e.Item.ItemIndex+"');");                    
                    break;
                case ListItemType.Header:
                    e.Item.Cells[0].ColumnSpan=2;//合并单元格
                    e.Item.Cells[1].Visible=false;
                    break;
                case ListItemType.EditItem:
                    for (int i=4; i < DataGrid1.Columns.Count-2; i++)//只调整被编辑的列
                    {
　　                    if(e.Item.ItemType==ListItemType.EditItem)
　　                    {
　　　                        e.Item.Cells[i].Attributes.Add("Width", "70px");
　　                    }
                    }
                    string sqlprovince="select * from province";
                    DataSet dsprovince=GetDataSet(sqlprovince);
                    //一级
                    if(e.Item.ItemType==ListItemType.EditItem)
                    {
                        DropDownList ddlprovince=(DropDownList)e.Item.FindControl("province");
                        ddlprovince.DataSource=dsprovince;
                        ddlprovince.DataTextField="province";
                        ddlprovince.DataValueField="provinceID";
                        ddlprovince.DataBind();
                        ddlprovince.Items.FindByValue(Convert.ToString(DataBinder.Eval(e.Item.DataItem,"provinceID"))).Selected=true;
                        //二级
                        if(e.Item.ItemType==ListItemType.EditItem)
                        {
                            if(ddlprovince.SelectedIndex!=-1)
                            {
                                string sqlcity="select * from city where father='"+ddlprovince.SelectedValue+"'";
                                DropDownList ddlcity=(DropDownList)e.Item.FindControl("city");
                                DataSet dscity=GetDataSet(sqlcity);                
                                ddlcity.DataSource=dscity;
                                ddlcity.DataTextField="city";
                                ddlcity.DataValueField="cityID";
                                ddlcity.DataBind();
                                ddlcity.Items.FindByValue(Convert.ToString(DataBinder.Eval(e.Item.DataItem,"cityID"))).Selected=true;
                                //三级
                                if(e.Item.ItemType==ListItemType.EditItem)
                                {
                                    if(ddlcity.SelectedIndex!=-1)
                                    {
                                        string sqlarea="select * from area where father='"+ddlcity.SelectedValue+"'";
                                        DropDownList ddlarea=(DropDownList)e.Item.FindControl("area");
                                        DataSet dsarea=GetDataSet(sqlarea);                
                                        ddlarea.DataSource=dsarea;
                                        ddlarea.DataTextField="area";
                                        ddlarea.DataValueField="areaID";
                                        ddlarea.DataBind();
                                        ddlarea.Items.FindByValue(Convert.ToString(DataBinder.Eval(e.Item.DataItem,"areaID"))).Selected=true;
                                    }
                                }
                            }
                        }
                    }
                    break;
                default:
                    break;
            }            
        }
        #endregion

        DataGrid1_SortCommand#region DataGrid1_SortCommand
        private void DataGrid1_SortCommand(object source, DataGridSortCommandEventArgs e)
        {
            if(SortExpression==e.SortExpression.ToString())
            {
                if(sort=="asc")
                {
                    sort="desc";
                }
                else
                {
                    sort="asc";
                }
            }
            else
            {
                if(sort=="asc")
                {
                    sort="desc";
                }
                else
                {
                    sort="asc";
                }
            }
            SortExpression=e.SortExpression;
            dt.DefaultView.Sort=e.SortExpression+" "+sort;
            DataGrid1.DataSource=dt;
            DataGrid1.DataBind();
        }
        #endregion

        DataGrid1_ItemCreated#region DataGrid1_ItemCreated
        private void DataGrid1_ItemCreated(object sender, DataGridItemEventArgs e)
        {
            for (int i=3; i < DataGrid1.Columns.Count-2; i++)//-2的意思是两个button列不用排序
            {
                if (i == 0) continue;
                if(e.Item.ItemIndex==-1)
                {
                    if (DataGrid1.Columns[i].SortExpression == SortExpression)
                    {
                        try
                        {
                            TableCell tableCell = e.Item.Cells[i];
                            Label label = new Label();
                            label.Font.Name = "webdings";
                            if(sort=="asc")
                            {
                                label.Text="6";
                            }
                            else
                            {
                                label.Text="5";
                            }
                            label.Width =20;
                            tableCell.Controls.Add(label);
                        }
                        catch{}
                    }
                }
            }
            foreach(DataGridItem di in this.DataGrid1.Items)
　            {
                if(di.ItemType==ListItemType.Item||di.ItemType==ListItemType.AlternatingItem)
　　            {
　　　                ((Button)di.Cells[10].Controls[0]).Attributes.Add("onclick","return confirm('确认删除此项吗?');");
　　            }
            }
        }

        #endregion

        property#region property
        private DataTable dt
        {
            get
            {
                if(ViewState["dt"]!=null)
                {
                    return (DataTable)ViewState["dt"];
                }
                else
                {
                    return null;
                }
            }
            set
            {
                ViewState["dt"]=value;
            }
        }
        private string SortExpression
        {
            get
            {
                if(ViewState["SortExpression"]!=null && ViewState["SortExpression"].ToString()!=string.Empty)
                {
                    return ViewState["SortExpression"].ToString();
                }
                else
                {
                    return "";
                }
            }
            set
            {
                ViewState["SortExpression"]=value;
            }
        }
        private string sort
        {
            get
            {
                if(ViewState["sort"]!=null && ViewState["sort"].ToString()!=string.Empty)
                {
                    return ViewState["sort"].ToString();
                }
                else
                {
                    return "";
                }
            }
            set
            {
                ViewState["sort"]=value;
            }
        }
        #endregion

        Edit#region Edit
        protected void Edit(object sender,DataGridCommandEventArgs e)
        {
            this.DataGrid1.EditItemIndex=e.Item.ItemIndex;
            DataBind();
        }
        #endregion

        Cancel#region Cancel
        protected void Cancel(object sender,DataGridCommandEventArgs e)
        {
            this.DataGrid1.EditItemIndex=-1;
            DataBind();
        }
        #endregion

        Update#region Update
        protected void Update(object sender,DataGridCommandEventArgs e)
        {
            if(e.Item.ItemType==ListItemType.EditItem)
            {
                SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
                SqlCommand comm=new SqlCommand("update testgrid set UserName=@UserName,provinceID=@province,cityID=@city,areaID=@area,Enabled=@Enabled where UserID=@UserID",conn);
                SqlParameter parm1=new SqlParameter("@UserName",SqlDbType.NVarChar,50);
                parm1.Value=((TextBox)e.Item.FindControl("UserName")).Text;

                SqlParameter parm2=new SqlParameter("@province",SqlDbType.NVarChar,50);
                parm2.Value=((DropDownList)e.Item.FindControl("province")).SelectedValue;

                SqlParameter parm3=new SqlParameter("@city",SqlDbType.NVarChar,50);
                parm3.Value=((DropDownList)e.Item.FindControl("city")).SelectedValue;    
            
                SqlParameter parm4=new SqlParameter("@area",SqlDbType.NVarChar,50);
                parm4.Value=((DropDownList)e.Item.FindControl("area")).SelectedValue;

                SqlParameter parm5=new SqlParameter("@Enabled",SqlDbType.Bit);
                parm5.Value=((CheckBox)e.Item.FindControl("chkenabled")).Checked;

                SqlParameter parm6=new SqlParameter("@UserID",SqlDbType.Int);
                parm6.Value=this.DataGrid1.DataKeys[e.Item.ItemIndex];

                comm.Parameters.Add(parm1);
                comm.Parameters.Add(parm2);
                comm.Parameters.Add(parm3);
                comm.Parameters.Add(parm4);
                comm.Parameters.Add(parm5);
                comm.Parameters.Add(parm6);
                conn.Open();
                comm.ExecuteNonQuery();
                conn.Close();
                this.DataGrid1.EditItemIndex=-1;
                DataBind();
            }
        }
        #endregion

        Delete event#region Delete event
        protected void Delete(object sender,DataGridCommandEventArgs e)
        {
            delete(this.DataGrid1.DataKeys[e.Item.ItemIndex].ToString());
        }
        #endregion

        ddlprovince_SelectedIndexChanged#region ddlprovince_SelectedIndexChanged
        public void ddlprovince_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList ddlprovince = sender as DropDownList;
            if(ddlprovince.SelectedIndex!=-1)
            {
                DropDownList ddlcity = ((Control)(((Control)sender)).Parent).FindControl("city") as DropDownList;
                string sqlcity="select * from city where father='"+ddlprovince.SelectedValue+"'";
                DataSet dscity=GetDataSet(sqlcity);
                ddlcity.DataSource=dscity;
                ddlcity.DataTextField="city";
                ddlcity.DataValueField="cityID";
                ddlcity.DataBind();
            }
        }
        #endregion

        ddlcity_SelectedIndexChanged#region ddlcity_SelectedIndexChanged
        public void ddlcity_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList ddlcity = sender as DropDownList;
            if(ddlcity.SelectedIndex!=-1)
            {
                DropDownList ddlarea = ((Control)(((Control)sender)).Parent).FindControl("city") as DropDownList;
                string sqlarea="select * from area where father='"+ddlcity.SelectedValue+"'";
                DataSet dsarea=GetDataSet(sqlarea);
                ddlarea.DataSource=dsarea;
                ddlarea.DataTextField="area";
                ddlarea.DataValueField="areaID";
                ddlarea.DataBind();
            }
        }
        #endregion

        btndelete_Click#region btndelete_Click
        private void btndelete_Click(object sender, System.EventArgs e)
        {
            StringBuilder s=new StringBuilder();
            foreach(DataGridItem di in DataGrid1.Items)
            {
　                if(((CheckBox)(di.FindControl("chkExport"))).Checked==true)
　                {
　　                s.Append(",").Append(DataGrid1.DataKeys[di.ItemIndex].ToString());
　                }
            }
            if(s.ToString().StartsWith(",")==true)
            {
                delete(s.ToString().Substring(1,s.ToString().Length-1));
            }
        }
        #endregion

        delete#region delete
        private void delete(string id)
        {
            SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
            string sql=@"declare @sql nvarchar(400)
                        set @sql = 'delete from testgrid where UserID in('+@UserID+')'
                        exec( @sql)";
            SqlCommand comm=new SqlCommand(sql,conn);
            SqlParameter parm1=new SqlParameter("@UserID",SqlDbType.VarChar,200);
            parm1.Value=id;
            comm.Parameters.Add(parm1);
            conn.Open();
            comm.ExecuteNonQuery();
            conn.Close();
            DataBind();
        }
        #endregion

        ListProvince#region ListProvince
        private void ListProvince()
        {
            string sql="select * from province";
            DataSet ds=GetDataSet(sql);
            DropDownList1.DataSource=ds;
            DropDownList1.DataTextField="province";
            DropDownList1.DataValueField="provinceID";
            DropDownList1.DataBind();
        }
        #endregion

        ListCity#region ListCity
        private void ListCity()
        {
            if(DropDownList1.SelectedIndex!=-1)
            {
                string sql="select * from city where father='"+DropDownList1.SelectedValue+"'";
                DataSet ds=GetDataSet(sql);
                DropDownList2.DataSource=ds;
                DropDownList2.DataTextField="city";
                DropDownList2.DataValueField="cityID";
                DropDownList2.DataBind();
            }
        }
        #endregion

        ListArea#region ListArea
        private void ListArea()
        {
            if(DropDownList2.SelectedIndex!=-1)
            {
                string sql="select * from area where father='"+DropDownList2.SelectedValue+"'";
                DataSet ds=GetDataSet(sql);            
                DropDownList3.DataSource=ds;
                DropDownList3.DataTextField="area";
                DropDownList3.DataValueField="areaID";
                DropDownList3.DataBind();
            }
        }
        #endregion

        save#region save
        private void Button1_Click(object sender, System.EventArgs e)
        {
            SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand comm=new SqlCommand("insert into testgrid (UserName,provinceID,cityID,areaID,Enabled) values(@UserName,@provinceID,@cityID,@areaID,@Enabled)",conn);
            SqlParameter parm1=new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            parm1.Value=this.TextBox1.Text;

            SqlParameter parm2=new SqlParameter("@provinceID",SqlDbType.NVarChar,50);
            parm2.Value=this.DropDownList1.SelectedValue;

            SqlParameter parm3=new SqlParameter("@cityID",SqlDbType.NVarChar,50);
            parm3.Value=this.DropDownList2.SelectedValue;    
            
            SqlParameter parm4=new SqlParameter("@areaID",SqlDbType.NVarChar,50);
            parm4.Value=this.DropDownList3.SelectedValue;

            SqlParameter parm5=new SqlParameter("@Enabled",SqlDbType.Bit);
            parm5.Value=this.CheckBox1.Checked;

            comm.Parameters.Add(parm1);
            comm.Parameters.Add(parm2);
            comm.Parameters.Add(parm3);
            comm.Parameters.Add(parm4);
            comm.Parameters.Add(parm5);
            conn.Open();
            comm.ExecuteNonQuery();
            conn.Close();
            DataBind();
        }
        #endregion

        private void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            ListCity();
        }

        private void DropDownList2_SelectedIndexChanged(object sender, System.EventArgs e)
        {
            ListArea();
        }

        private void Button2_Click(object sender, System.EventArgs e)
        {
            ToExcel(this.DataGrid1,"meng");
        }

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[province]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[province]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[city]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[city]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[area]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[area]
GO

Create TABLE [dbo].[TestGrid] (
    [UserID] [int] IDENTITY (1, 1) NOT NULL ,
    [UserName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [provinceID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [cityID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [areaID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [Enabled] [bit] NULL
) ON [PRIMARY]
GO

Create TABLE [dbo].[province] (
    [id] [int] NOT NULL ,
    [provinceID] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
    [province] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

Create TABLE [dbo].[city] (
    [id] [int] NOT NULL ,
    [cityID] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
    [city] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [father] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO