C# Gridview行操作

来源：互联网发布：imp命令导入远程数据库编辑：程序博客网时间：2024/06/07 15:16

C# Gridview行操作–行更新和行添加

GridView可以以表格的形式显示数据源中的数据，应用的场合比较多，我现在对其使用情况做一个简单的整理，也许会写成一系列文章，先上第一篇：行更新和行添加
行更新是用到GridView的事件OnRowEditing，OnRowUpdating，OnRowCancelingEdit，能更新的字段是在GridView的 TemplateField中添加有EditItemTemplate的，行添加是要为每一列添加FooterTemplate，添加的时候将其ShowFooter设置为true
如下图所示：
这里写图片描述

最终界面如下图所示，当点击编辑，可以对该行中的某些字段进行编辑并更新数据库

点击编辑会出现如下图所示，这里只设置了UnitPrice可以编辑，

点击添加界面如下：

下面附上代码：本例子中没有考虑字段的校验（比如UnitPrice只能是数字等），代码均已调试通过

aspx页面代码

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridviewEdit.aspx.cs" Inherits="GridviewEdit" %><!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server">    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />    <title></title></head><body>    <form id="form1" runat="server">        <div>            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"  OnRowEditing="GridView1_RowEditing"                OnRowUpdating="GridView1_RowUpdating" OnRowCancelingEdit="GridView1_RowCancelingEdit">                <Columns>                    <asp:TemplateField HeaderText="ProductID">                        <ItemTemplate>                            <%#Eval("ProductID") %>                        </ItemTemplate>                         <FooterTemplate>                            <asp:TextBox ID="txtAddProductID" runat="server" Text="ProductID" CssClass="footerctl"></asp:TextBox>                                                    </FooterTemplate>                    </asp:TemplateField>                    <asp:TemplateField HeaderText="ProductName">                        <ItemTemplate>                            <%#Eval("ProductName") %>                        </ItemTemplate>                         <FooterTemplate>                            <asp:TextBox ID="txtAddProductName" runat="server" Text="ProductName" CssClass="footerctl"></asp:TextBox>                                                   </FooterTemplate>                    </asp:TemplateField>                    <asp:TemplateField HeaderText="UnitPrice">                         <EditItemTemplate>                            <asp:TextBox ID="txtUnitPrice" runat="server" Text='<%# Eval("UnitPrice")%>'></asp:TextBox>                                                   </EditItemTemplate>                        <ItemTemplate>                            <%#Eval("UnitPrice") %>                        </ItemTemplate>                        <FooterTemplate>                            <asp:TextBox ID="txtAddUnitPrice" runat="server" Text="UnitPrice" CssClass="footerctl"></asp:TextBox>                                                    </FooterTemplate>                    </asp:TemplateField>                    <asp:TemplateField HeaderText="CategoryID">                        <ItemTemplate>                            <%#Eval("CategoryID") %>                        </ItemTemplate>                          <FooterTemplate>                            <asp:TextBox ID="txtAddCategoryID" runat="server" Text="CategoryID" CssClass="footerctl"></asp:TextBox>                                                    </FooterTemplate>                                           </asp:TemplateField>                    <asp:TemplateField HeaderText="Description">                        <ItemTemplate>                            <%#Eval("Description") %>                        </ItemTemplate>                         <FooterTemplate>                            <asp:TextBox ID="txtAddDescription" runat="server" Text="Description" CssClass="footerctl"></asp:TextBox>                                                    </FooterTemplate>                                            </asp:TemplateField>                    <asp:CommandField HeaderText="操作" HeaderStyle-Width="40px" ShowEditButton="True" ShowHeader="True" />                </Columns>                <HeaderStyle HorizontalAlign="Center" />            </asp:GridView>            <asp:LinkButton runat="server" Text="添加" ID="btnAdd" OnClick="btnAdd_Click"></asp:LinkButton>            <asp:LinkButton runat="server" Text="确定" ID="btnAddOK" OnClick="btnAddOK_Click" Visible="false"></asp:LinkButton>            <asp:LinkButton runat="server" Text="取消" ID="btnAddCancel" OnClick="btnAddCancel_Click" Visible="false"></asp:LinkButton>        </div>    </form></body></html>

后台代码

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 GridviewEdit : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {        if (!IsPostBack)        {            BindGrid();        }    }      public void BindGrid()    {        string sql = "select * from Product";        DataTable dt = ExecuteSQLQuery(sql).Tables[0];        this.GridView1.DataKeyNames = new string[] { "ProductID" };        this.GridView1.DataSource = dt;        this.GridView1.DataBind();    }    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)    {        GridView1.EditIndex = e.NewEditIndex;        GridView1.ShowFooter = false;        BindGrid();    }    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)    {        var row = GridView1.Rows[e.RowIndex];        var id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0].ToString());        string price = ((TextBox)row.FindControl("txtUnitPrice")).Text.Trim();        string sql1 = string.Format(@"update Product set UnitPrice='{0}' where productid='{1}'", Convert.ToDecimal(price), id);        ExecuteUpdateAndInsert(sql1);        this.GridView1.EditIndex = -1;//没有这一句，修改后页面不会自动刷新        BindGrid();    }    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)    {        GridView1.EditIndex = -1;        BindGrid();    }    protected void btnAdd_Click(object sender, EventArgs e)    {        GridView1.EditIndex = -1;        GridView1.ShowFooter = true;        BindGrid();        ToggleAddCtrl(false);    }    protected void btnAddOK_Click(object sender, EventArgs e)    {        var row = GridView1.FooterRow;               string ProductID = ((TextBox)row.FindControl("txtAddProductID")).Text.Trim();        string Description = ((TextBox)row.FindControl("txtAddDescription")).Text.Trim();        string ProductName = ((TextBox)row.FindControl("txtAddProductName")).Text.Trim();        string UnitPrice = ((TextBox)row.FindControl("txtAddUnitPrice")).Text.Trim();        string CategoryID = ((TextBox)row.FindControl("txtAddCategoryID")).Text.Trim();        string sql = string.Format(@"select * from Product where ProductID='{0}'", ProductID);        DataTable dt = ExecuteSQLQuery(sql).Tables[0];        if (dt.Rows.Count != 0)        {            Response.Write("<script>alert('there is records in the list')</script>");        }        else        {            string insert = string.Format(@"insert into Product(ProductID,ProductName,Description,UnitPrice,CategoryID) values('{0}','{1}','{2}',{3},'{4}')", ProductID, ProductName, Description, UnitPrice, CategoryID);            ExecuteUpdateAndInsert(insert);        }        BindGrid();    }    protected void btnAddCancel_Click(object sender, EventArgs e)    {        GridView1.ShowFooter = false;        BindGrid();        ToggleAddCtrl(true);    }    protected void ToggleAddCtrl(bool isRegular)    {        if (isRegular)        {            this.btnAdd.Visible = true;            this.btnAddOK.Visible = false;            this.btnAddCancel.Visible = false;        }        else        {            this.btnAdd.Visible = false;            this.btnAddOK.Visible = true;            this.btnAddCancel.Visible = true;        }    }    public SqlConnection GetConnection()    {        string myStr = ConfigurationManager.ConnectionStrings["Production"].ToString();        //string myStr = "Data Source=.;Initial Catalog=Product";        SqlConnection myConn = new SqlConnection(myStr);        return myConn;    }    /// <summary>    /// 查询数据库    /// </summary>    /// <param name="sql"></param>    /// <returns></returns>    public DataSet ExecuteSQLQuery(string sql)    {        //打开数据库连接        SqlConnection mycon = GetConnection();        mycon.Open();                SqlDataAdapter sda = new SqlDataAdapter(sql, mycon);        DataSet ds = new DataSet();        sda.Fill(ds);        mycon.Close();        return ds;    }    /// <summary>    /// 更新数据库    /// </summary>    /// <param name="sql"></param>    public void ExecuteUpdateAndInsert(string sql)    {        //打开数据库连接        SqlConnection mycon = GetConnection();        mycon.Open();        //SQL语句                SqlCommand cmd = new SqlCommand(sql, mycon);        cmd.ExecuteNonQuery();        mycon.Close();    }}

数据库建表代码如下：

/****** Object:  Table [dbo].[Product]    Script Date: 2017/5/11 12:53:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Product](    [ProductID] [int] NOT NULL,    [ProductName] [varchar](100) NULL,    [Description] [varchar](max) NULL,    [ImagePath] [varchar](1000) NULL,    [UnitPrice] [decimal](18, 2) NULL,    [CategoryID] [int] NULL,PRIMARY KEY CLUSTERED (    [ProductID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Category] FOREIGN KEY([CategoryID])REFERENCES [dbo].[Category] ([CategoryID])GOALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category]GO

连接字符串为

<connectionStrings>    <add name="ELearning"   connectionString="Data Source=.;Initial Catalog=Product;integrated security=True;multipleactiveresultsets=True;"   providerName="System.Data.SqlClient" />   </connectionStrings>

0 0