最近在研究gridview实现多行插入,更新,保存,删除的问题;
现在出来了,和大家分享一下;其实也就是对DataTable的操作
谢谢;如果觉得好的话请留言,以后我将继续努力
表:表明aaa
字段 id 主键,自动增长
字段 AAA varchar类型;
字段 BBB varchar类型;
页面代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>在GridView中实现数据绑定,添加多行,更新,删除并与数据库实现交互</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<center>
<asp:GridView ID="GridView1" runat="server" DataKeyNames="id" Style="position: relative" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowDeleting="GridView1_RowDeleting">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
</asp:GridView>
<asp:Button ID="btnAdd" runat="server" Style="position: relative" Text="保存" OnClick="btnAdd_Click" /><br>
</center>
</div>
</form>
</body>
</html>
C#类代码:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
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;
public partial class Default3 : System.Web.UI.Page
{
SqlConnection mysqlcon = new SqlConnection("server=.;database=lianxi;integrated security=SSPI");
protected DataTable dt = new DataTable();
protected DataRow dr;//声明行变量
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Session["Table"] == null)
{
dt = Tables();//得到DataTable的值
Session["Table"] = dt;
}
else {
dt=(DataTable)Session["Table"];
}
BindGridView();
}
}
protected void BindGridView()
{
//将数据与GridView1进行绑定
if (dt != null)
{
try
{
GridView1.DataSource = dt;
GridView1.DataBind();
GridView1.HeaderRow.Cells[2].Text = "工号";
GridView1.HeaderRow.Cells[3].Text = "姓名";
GridView1.HeaderRow.Cells[4].Text = "年龄";
}
catch (SqlException)
{
Response.Write("<script language='javascript'>alert('出错了!!!');</script>");
}
}
else {
Response.Write("没有你要的数据");
}
}
protected DataTable Tables()
{
//将数据保存到DataTable中
try
{
string sql = "select * from aaa";
SqlDataAdapter da = new SqlDataAdapter(sql, mysqlcon);
da.Fill(dt);
dr = dt.NewRow();//为DataTable添加一行(空值 以实现添加功能)
dt.Rows.Add(dr);
Session["Table"] = dt;//将dt保存在session中
}
catch (SqlException)
{
}
return dt;
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
//点击编辑后执行
GridView1.EditIndex = e.NewEditIndex;
dt = (DataTable)Session["Table"];
BindGridView();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
//取消编辑时执行
GridView1.EditIndex = -1;
dt = (DataTable)Session["Table"];
BindGridView();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//更新dt事执行
if (e.RowIndex == (GridView1.Rows.Count - 1))//判断选定的行是不是页面中GridView的最后一行,当是最后一行是执行
{
//为dt添加一新行
string aaa = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text.Trim();
string bbb = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text.Trim();
dt = (DataTable)Session["Table"];
dt.Rows.RemoveAt(e.RowIndex);//删除空行
dr = dt.NewRow();
dr[1] = aaa;
dr[2] = bbb;
dt.Rows.Add(dr);
dr = dt.NewRow();//在次为dt添加空行以实现页面再次添加新行
dt.Rows.Add(dr);
Session["Table"] = dt;
GridView1.EditIndex = -1;
BindGridView();
}
else {
//更新dt中现有行事执行
string aaa = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text.Trim();
string bbb = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text.Trim();
dt = (DataTable)Session["Table"];
dr = dt.Rows[e.RowIndex];
dr.BeginEdit();//开始更新
dr[1] = aaa;
dr[2] = bbb;
dr.EndEdit();//结束更新
Session["Table"] = dt;
GridView1.EditIndex = -1;
BindGridView();
}
}
protected bool Executer(string sql)
{
//SQL执行语句,实现数据库的更新,添加,删除
SqlCommand mysqlcom = new SqlCommand(sql,mysqlcon);
try {
mysqlcon.Open();
mysqlcom.ExecuteNonQuery();
return true;
}
catch (SqlException) {
return false;
}
finally {
mysqlcom.Dispose();
mysqlcon.Close();
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
//点击删除事执行
dt = (DataTable)Session["Table"];
if (e.RowIndex == (GridView1.Rows.Count - 1))//判断:当点击GridView中的最后一行时(最后一行是空值)不执行删除
{
}else if(GridView1.Rows[e.RowIndex].Cells[2].Text.Trim()!=null){
if (Session["aaa_id"] != null)//当删除掉的数据是数据库中原有的数据时执行,将原有数据的id保存到session中
{
Session["aaa_id"] += GridView1.Rows[e.RowIndex].Cells[2].Text.Trim();
}
else {
Session["aaa_id"] = GridView1.Rows[e.RowIndex].Cells[2].Text.Trim();
}
dt.Rows.RemoveAt(e.RowIndex);
Session["Table"] = dt;
GridView1.EditIndex = -1;
BindGridView();
}
else {
//删除后来添加到dt中的数据(此时还未与数据库进行交互)
dt.Rows.RemoveAt(e.RowIndex);
Session["Table"] = dt;
GridView1.EditIndex = -1;
BindGridView();
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
dt = (DataTable)Session["Table"];
foreach (DataRow drs in dt.Rows)
{
if (drs[0].ToString().Trim() != string.Empty)//判断dt中的id是否存在,如果存在则执行以下代码
{
//string id = drs["id"].ToString().Trim();
//int i = int.Parse(id);
//string aaa = drs["AAA"].ToString().Trim();
//string bbb = drs["BBB"].ToString().Trim();
string sql = string.Format("update aaa set AAA='" + drs["AAA"].ToString().Trim() + "',BBB='" + drs["BBB"].ToString().Trim() + "' where id=" + int.Parse(drs["id"].ToString().Trim()));
Executer(sql);
}
//当dt中的id是空值是,判断后面的数据是否是空值,如为空值则不添加到数据库
else if (drs[1].ToString() != string.Empty && drs[2].ToString() != string.Empty)
{
//string aaa = drs[1].ToString();
//string bbb = drs[2].ToString();
string sql = "insert into aaa values('" + drs["AAA"].ToString().Trim() + "','" + drs["BBB"].ToString().Trim() + "')";
Executer(sql);
}
}
Session.Remove("Table");//保存完毕后则请空session["Table"]
if (Session["aaa_id"] != null)
{
string aaa_id = Session["aaa_id"].ToString().Trim();//取出session["aaa_id"]的值
for (int i = 0; i < aaa_id.Length; i++)
{
//将得到id的值
int id = Convert.ToInt32(aaa_id.Substring(i,1));
string sql = string.Format("delete aaa where id=" + id);//删除数据库中原有的数据
Executer(sql);
}
}
Session.Remove("aaa_id");//保存完毕后则请空session["aaa_id"]
Response.Redirect("Default3.aspx");//跳转页面
}
}