using System;

 using System.Collections.Generic;

 using System.Configuration;

 using System.Data;

 using System.Data.SqlClient;

 using System.Linq;

 using System.Web;

 using System.Web.UI;

 using System.Web.UI.WebControls;

 public partial class _Default : System.Web.UI.Page

 {

     private string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

     SqlHelper helper = new SqlHelper();

     protected void Page_Load(object sender, EventArgs e)

     {

         if (!Page.IsPostBack)

         {

             using (SqlConnection conn = new SqlConnection(constr))

             {

                  SqlDataAdapter ad = new SqlDataAdapter("select * from tbuser", conn);

                  SqlCommandBuilder builder = new SqlCommandBuilder(ad);

                  DataTable dt = new DataTable();

                  ad.Fill(dt);

                  this.GridView1.DataSource = dt;

                  this.DataBind();

             }

         }

     }

     private void userlistbind()

     {

         GridView1.DataSource = helper.SelectSqlReturnDataset("select id, username as '用户名',birthday  as '生日', '部门号'=(select departmentname from tbdepartment where tbdepartment.departmentid=tbUser.departmentid) from tbUser").Tables[];

     }

     protected void btnAdd_Click(object sender, EventArgs e)

     {

         string sql = "insert into tbUser(username,birthday,departmentid) values('" + txtUserName.Text + "','" + txtBirthday.Text + "'," + ddlDepartment.SelectedValue.ToString() + ")";

         int count = helper.ExecuteReturnInt(sql, null, System.Data.CommandType.Text);

         if (count != )

         {

             userlistbind();

             Response.Write("<script>alert('数据添加成功！')</script>");

             txtBirthday.Text = "";

             txtUserName.Text = "";

             GridView1.DataBind();

         }

     }

     protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

     {

     }

     protected void bind()

     {

         SqlConnection myconn = new SqlConnection(constr);

         myconn.Open();

         string sql = "select id,username 用户名,birthday 生日,departmentid 部门号 from tbUser";

         SqlDataAdapter myda = new SqlDataAdapter(sql, myconn);

         DataSet myds = new DataSet();

         myda.Fill(myds);

         GridView1.DataSource = myds;

         GridView1.DataKeyNames = new string[] { "id" };

         GridView1.DataBind();

         myda.Dispose();

         myds.Dispose();

         myconn.Close();

     }

     protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

     {

         GridView1.EditIndex = e.NewEditIndex;

         this.bind();

     }

     protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

     {

         int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());

         string name = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[].Controls[])).Text.ToString();

         string birthday = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[].Controls[])).Text.ToString();

         string department = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[].Controls[])).Text.ToString();

         string sql = "update tbUser set username='" + name + "',birthday='"+birthday+"',departmentid="+department+" where id=" + id + "";

         SqlConnection myconn = new SqlConnection(constr);

         myconn.Open();

         SqlCommand mycmd = new SqlCommand(sql, myconn);

         mycmd.ExecuteNonQuery();

         mycmd.Dispose();

         myconn.Close();

         GridView1.EditIndex = -;

         this.bind();

     }

     protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

     {

         GridView1.EditIndex = -;

         this.bind();

     }

     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

     {

         int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());

         string sql = "delete from tbUser where id=" + id + "";

         SqlConnection myconn = new SqlConnection(constr);

         myconn.Open();

         SqlCommand mycmd = new SqlCommand(sql, myconn);

         mycmd.ExecuteNonQuery();

         mycmd.Dispose();

         myconn.Close();

         GridView1.EditIndex = -;

         this.bind();

     }

     protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

     {

         if (e.Row.RowType == DataControlRowType.DataRow)

         {

             ((LinkButton)e.Row.Cells[].Controls[]).Attributes.Add("onclick", "return confirm('确定要删除吗')");

         }

     }

 }