GridView 的临时增删改查示例

SqlCommandBuilder  注意点: 

  1.只能更新一个表,不能更新两个或两个以上相关联的表 
  2.表中必须有主键 
  3.更新的表中字段不能有image类型的


create table GridViewDemoTable
(
    
[ID] int identity(1,1primary key,
    
[Name] varchar(20unique,
    
[Department] varchar(20)
)
insert into GridViewDemoTable ([Name],[Department]select '张三','市场部'
union all select '李四','研发部'
union all select '王二','策划部'
union all select '薛七','销售部'
union all select '丁一','财务部'

select * from GridViewDemoTable

 

Code
<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" />&nbsp;&nbsp;&nbsp;&nbsp;<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>

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                
        }
    }

 

 

 

posted @ 2009-08-19 09:47  xumingming  阅读(1296)  评论(0编辑  收藏  举报