做项目开发的时候,经常会碰到插入与修改很多数据到服务器,如何做到批量修改,减少往返提交数据,是每个程序员需要考虑的问题.下面的例子就是以NORTHWIND表中EMPLOYEES表中的数据在GRIDVIEW进行一次性编辑
前台代码....我只写控件部分
<asp:GridView ID="GridView1" runat="server" Font-Size="12px" CellPadding="3" AutoGenerateColumns="False"
DataKeyNames="employeeid" >
<HeaderStyle BackColor="#EDEDED" />
<Columns>
<asp:TemplateField HeaderText="编号">
<ItemTemplate>
<asp:TextBox ID="employeeid" runat="server" Text='<%#Eval("employeeid") %>' ></asp:TextBox>
</ItemTemplate></asp:TemplateField>
<asp:TemplateField HeaderText="姓">
<ItemTemplate>
<asp:TextBox ID="lastname" runat="server" Text='<%#Eval("lastname") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="名">
<ItemTemplate>
<asp:TextBox ID="firstname" runat="server" Text='<%#Eval("firstname") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="保存所有修改" OnClick="Button1_Click" />
后台代码:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class main : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//绑定GRIDVIEW打开页面显示数据
GridViewbind();
}
}
private void GridViewbind()
{
//连接字符串WEB.CONFIG里面的
string conn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString1"].ToString();
SqlConnection con = new SqlConnection(conn);
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from employees";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "employee");
this.GridView1.DataSource = ds.Tables["employee"];
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
foreach (GridViewRow gvr in GridView1.Rows)
{
string con = System.Configuration.ConfigurationManager.AppSettings["ConnectionString1"].ToString();
SqlConnection conn = new SqlConnection(con);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update employees set lastname=@lastname,firstname=@firstname where employeeid=@employeeid";
cmd.Parameters.Add("@lastname",SqlDbType.NVarChar,20);
cmd.Parameters.Add("@firstname",SqlDbType.NVarChar,10);
cmd.Parameters.Add("@employeeid",SqlDbType.Int);
cmd.Parameters["@lastname"].Value=((TextBox)gvr.FindControl("lastname")).Text;
cmd.Parameters["@firstname"].Value = ((TextBox)gvr.FindControl("firstname")).Text;
cmd.Parameters["@employeeid"].Value = ((TextBox)gvr.FindControl("employeeid")).Text;
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
finally
{
if (conn != null)
conn.Dispose();
}
}
}
}
WEB.CONFIG里<appSettings></appSettings>中间添加
<appSettings>
<add key="ConnectionString1" value="server=.;database=northwind;uid=sa;pwd=;"/>
</appSettings>
