GridView 的临时增删改查示例
SqlCommandBuilder 注意点:
1.只能更新一个表,不能更新两个或两个以上相关联的表
2.表中必须有主键
3.更新的表中字段不能有image类型的
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
create table GridViewDemoTable
(
[ID] int identity(1,1) primary key,
[Name] varchar(20) unique,
[Department] varchar(20)
)
insert into GridViewDemoTable ([Name],[Department]) select '张三','市场部'
union all select '李四','研发部'
union all select '王二','策划部'
union all select '薛七','销售部'
union all select '丁一','财务部'
select * from GridViewDemoTable
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
姓名:<asp:TextBox ID="txtName" runat="server"></asp:TextBox>部门:<asp:TextBox ID="txtDepartment" runat="server"></asp:TextBox>
<asp:Button ID="btnAdd" runat="server" Text="新增" OnClick="btnAdd_Click" /> <asp:Button ID="btnSave" Text="保存" runat="server" OnClick="btnSave_Click" />
<asp:GridView ID="gv" runat="server" AutoGenerateColumns="false" DataKeyNames="ID" OnRowDeleting="gv_RowDeleting" OnRowEditing="gv_RowEditing" OnRowUpdating="gv_RowUpdating" OnRowCancelingEdit="gv_RowCancelingEdit">
<Columns>
<asp:TemplateField HeaderText="姓名">
<ItemTemplate>
<%#Eval("Name") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%#Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="部门">
<ItemTemplate>
<%#Eval("Department") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtDepartment" runat="server" Text='<%#Eval("Department") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" />
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt = getDataTable();
ViewState["dt"] = dt;
gv.DataSource = dt;
gv.DataBind();
}
}
private DataTable getDataTable()
{
using (SqlConnection con = new SqlConnection("server=.;uid=xxx;pwd=xxx;database=Test"))
{
SqlDataAdapter sda = new SqlDataAdapter("select * from GridViewDemoTable ", con);
DataSet ds = new DataSet();
sda.Fill(ds, "GridViewDemoTable");
if (ds.Tables.Contains("GridViewDemoTable"))
{
return ds.Tables["GridViewDemoTable"];
}
return null;
}//end using block
}
protected void gv_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
object val = gv.DataKeys[e.RowIndex].Value;
DataTable dt = ViewState["dt"] as DataTable;
if (dt != null)
{
for (int i = dt.Rows.Count - 1; i > -1; i--)
{
if (dt.Rows[i][0].Equals(val))
{
dt.Rows[i].Delete();
//dt.Rows.RemoveAt(i);
}
}
ViewState["dt"] = dt;
gv.DataSource = dt;
gv.DataBind();
//Response.Write("<script language='JavaScript'>window.location=location;</script>"); //防止刷新重复提交
}
}
protected void gv_RowEditing(object sender, GridViewEditEventArgs e)
{
gv.EditIndex = e.NewEditIndex;
DataTable dt = ViewState["dt"] as DataTable;
if (dt != null)
{
gv.DataSource = dt;
gv.DataBind();
}
}
protected void gv_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int iRowIndex = e.RowIndex;
DataTable dt = ViewState["dt"] as DataTable;
if (dt != null)
{
dt.Rows[iRowIndex]["Name"] = ((sender as GridView).Rows[iRowIndex].FindControl("txtName") as TextBox).Text;
dt.Rows[iRowIndex]["Department"] = ((sender as GridView).Rows[iRowIndex].FindControl("txtDepartment") as TextBox).Text;
ViewState["dt"] = dt;
(sender as GridView).EditIndex = -1;
gv.DataSource = dt;
gv.DataBind();
}
}
protected void gv_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gv.EditIndex = -1;
DataTable dt = ViewState["dt"] as DataTable;
if (dt != null)
{
gv.DataSource = dt;
gv.DataBind();
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
DataTable dt = ViewState["dt"] as DataTable;
if (dt != null)
{
using (SqlConnection con = new SqlConnection("server=.;uid=xxx;pwd=xxx;database=Test"))
{
SqlDataAdapter sda = new SqlDataAdapter("select MAX(ID)+1 from GridViewDemoTable ", con);
DataSet ds = new DataSet();
sda.Fill(ds, "MaxID");
if (ds.Tables.Contains("MaxID") && ds.Tables["MaxID"].Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr[0] = ds.Tables["MaxID"].Rows[0][0];
dr[1] = txtName.Text;
dr[2] = txtDepartment.Text;
dt.Rows.Add(dr);
ViewState["dt"] = dt;
gv.DataSource = dt;
gv.DataBind();
}
}//end using block
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
DataTable dt = ViewState["dt"] as DataTable;
if (dt != null)
{
using (SqlConnection con = new SqlConnection("server=.;uid=xxx;pwd=xxx;database=Test"))
{
SqlDataAdapter sda = new SqlDataAdapter("select * from GridViewDemoTable ", con);
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
DataSet ds = new DataSet();
sda.Fill(ds, "GridViewDemoTable");
sda.Update(dt);
ClientScript.RegisterStartupScript(GetType(), "success", "alert('保存成功!');", true);
}//end using block
}
}