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('确定要删除吗')");
}
}
}
web.config
<configuration>
<connectionStrings>
<add name="constr" connectionString="server=.\sqlexpress;database=db2016;uid=sa;pwd=123;" />
<add name="db2016ConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=db2016;Persist Security Info=True;User ID=sa;Password=123"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0" />
</system.web>
</configuration>
GridView的datakeyname属性 设为id
数据库表
create table tbUser
(
id int primary key identity(1,1),
username nvarchar(20),
userpass varbinary(128),
birthday datetime,
departmentid int foreign key references tbdepartment(departmentid)
)