asp.net gridview新增、删除与编辑。
1、Asp.Net页面
2、后台代码
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;
public partial class GridViewAdd : System.Web.UI.Page
{
SqlHelper sqlHelper = new SqlHelper();
private static DataTable dtUser;
protected void Page_Load(object sender, EventArgs e)
{
if(!Page.IsPostBack)
bindData(true);
this.BtSave.Enabled = false;
}
/// <summary>
/// 数据绑定
/// </summary>
/// <param name="refresh"></param>
private void bindData(bool refresh)
{
if (refresh || dtUser == null)
{
DataSet ds = sqlHelper.getDs("Select * from MyUser", CommandType.Text, null, "MyUser");
dtUser = ds.Tables[0];
}
this.myGridView.DataSource = dtUser.DefaultView;
this.myGridView.DataBind();
}
/// <summary>
/// 删除
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void txtDel_Click(object sender, EventArgs e)
{
int id = Convert.ToInt32(((LinkButton)sender).CommandArgument);
//从数据库中删除
string Sql;
Sql = "Delete from MyUser where ID=" + id;
sqlHelper.ExecuteSql(Sql,CommandType.Text,null);
//从内存中删除
DataRow[] dr = dtUser.Select("ID=" + id);
if(dr.Length > 0)
dtUser.Rows.Remove(dr[0]);
this.bindData(false);
}
/// <summary>
/// 新增
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtAdd_Click(object sender, EventArgs e)
{
DataRow row = dtUser.NewRow();
row["ID"] = (int)getMaxIdInTable(dtUser, "ID") + 1;
row["BirthDay"] = DateTime.Now;
dtUser.Rows.Add(row);
this.myGridView.EditIndex = dtUser.Rows.Count - 1;
this.bindData(false);
this.BtSave.Enabled = true;
}
/// <summary>
/// 保存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void BtSave_Click(object sender, EventArgs e)
{
int i = this.myGridView.EditIndex;
string userName = ((TextBox)this.myGridView.Rows[i].FindControl("txtUserName")).Text.ToString();
string password = ((TextBox)this.myGridView.Rows[i].FindControl("txtPassword")).Text.ToString();
string describe = ((TextBox)this.myGridView.Rows[i].FindControl("txtDescribe")).Text.ToString();
DateTime birthDay = ((Calendar)this.myGridView.Rows[i].FindControl("txtBirthDay")).SelectedDate;
string Sql;
Sql = "Insert Into MyUser(UserName,Password,Describe,BirthDay) values('" + userName + "','" + password + "','" + describe + "','" + birthDay + "')";
sqlHelper.ExecuteSql(Sql, CommandType.Text, null);
this.myGridView.EditIndex = -1;
this.bindData(true);
this.BtSave.Enabled = false;
}
/// <summary>
/// 得到指定表中关键字的最大值
/// </summary>
/// <param name="table"></param>
/// <param name="keyID"></param>
/// <returns></returns>
private object getMaxIdInTable(DataTable table, string keyID)
{
if (table.Rows.Count == 0)
return 0;
DataView dv = new DataView();
dv.Table = table;
dv.Sort = keyID + " Desc";
return dv[0][keyID];
}
/// <summary>
/// 用户取消事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void myGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
this.myGridView.EditIndex = -1;
this.bindData(true);
}
/// <summary>
/// 用户更新事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void myGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int i = this.myGridView.EditIndex;
if (dtUser.Rows[i].RowState == DataRowState.Added)
{
BtSave_Click(sender, e);
dtUser.Rows[i].AcceptChanges();
}
else
{ //表示修改
int id = Convert.ToInt16(this.myGridView.Rows[i].Cells[1].Text);
string userName = ((TextBox)this.myGridView.Rows[i].FindControl("txtUserName")).Text.ToString();
string password = ((TextBox)this.myGridView.Rows[i].FindControl("txtPassword")).Text.ToString();
string describe = ((TextBox)this.myGridView.Rows[i].FindControl("txtDescribe")).Text.ToString();
DateTime birthDay = ((Calendar)this.myGridView.Rows[i].FindControl("txtBirthDay")).SelectedDate;
string Sql;
Sql = "Update MyUser Set UserName='" + userName + "',Password='" + password + "',Describe='" + describe + "',BirthDay='" + birthDay + "' where id=" + id;
sqlHelper.ExecuteSql(Sql, CommandType.Text, null);
this.myGridView.EditIndex = -1;
this.bindData(true);
this.BtSave.Enabled = false;
}
}
/// <summary>
/// 用户删除事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void myGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int selectIndex = e.RowIndex;
//这里的 Cells[1] 对应的是编号列
int id = Convert.ToInt16(this.myGridView.Rows[selectIndex].Cells[1].Text);
string Sql;
Sql = "Delete from MyUser where ID=" + id;
sqlHelper.ExecuteSql(Sql,CommandType.Text,null);
//从内存中删除
DataRow[] dr = dtUser.Select("ID=" + id);
dtUser.Rows.Remove(dr[0]);
this.bindData(false);
}
/// <summary>
/// 用户编辑事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void myGridView_RowEditing(object sender, GridViewEditEventArgs e)
{
this.myGridView.EditIndex = e.NewEditIndex;
this.bindData(false);
}
protected void myGridView_DataBound(object sender, EventArgs e)
{
string userName = "";
foreach (GridViewRow r in this.myGridView.Rows)
{
//userName = ((Label)r.FindControl("showUserName")).Text;
((LinkButton)r.FindControl("txtDel")).Attributes.Add("onclick", "return checkDel(" + userName + ")");
}
}
}
3、数据访问类
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
public class SqlHelper
{
private const string ConnStr = "Data Source=127.0.0.1;Initial Catalog=HJ;User id=sa;Password=as;";
private SqlConnection Conn = null;
private SqlCommand Cmd = null;
private SqlDataAdapter Adp = null;
private DataSet ds = new DataSet();
/// <summary>
/// 返回 DataSet
/// </summary>
/// <param name="cmdText"></param>
/// <param name="cmdType"></param>
/// <param name="pars"></param>
/// <param name="tableName"></param>
public DataSet getDs(string cmdText, CommandType cmdType, SqlParameter[] pars, string tableName)
{
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = cmdText;
Cmd.CommandType = cmdType;
if (pars != null)
{
foreach (SqlParameter par in pars)
{
Cmd.Parameters.Add(par);
}
}
Adp = new SqlDataAdapter(Cmd);
try
{
Conn.Open();
if (tableName == null || tableName == string.Empty)
Adp.Fill(ds);
else
Adp.Fill(ds, tableName);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
Conn.Close();
}
return ds;
}
/// <summary>
/// 执行sql语句，返回受影响的行数
/// </summary>
/// <param name="cmdText"></param>
/// <param name="cmdType"></param>
/// <param name="pars"></param>
/// <returns></returns>
public int ExecuteSql(string cmdText, CommandType cmdType, SqlParameter[] pars)
{
int res = 0;
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = cmdText;
Cmd.CommandType = cmdType;
if (pars != null)
{
foreach (SqlParameter par in pars)
{
Cmd.Parameters.Add(par);
}
}
try
{
Conn.Open();
res = Cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
Conn.Close();
}
return res;
}
/// <summary>
/// 返回 DataReader 对象
/// </summary>
/// <param name="cmdText"></param>
/// <param name="cmdType"></param>
/// <param name="pars"></param>
/// <returns></returns>
public SqlDataReader getDr(string cmdText, CommandType cmdType, SqlParameter[] pars)
{
SqlDataReader dr = null;
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = cmdText;
Cmd.CommandType = cmdType;
if (pars != null)
{
foreach (SqlParameter par in pars)
{
Cmd.Parameters.Add(par);
}
}
try
{
Conn.Open();
dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
}
return dr;
}
public object getScalar(string cmdText, CommandType cmdType, SqlParameter[] pars)
{
object res = null;
Conn = new SqlConnection(ConnStr);
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = cmdText;
Cmd.CommandType = cmdType;
if (pars != null)
{
foreach (SqlParameter par in pars)
{
Cmd.Parameters.Add(par);
}
}
try
{
Conn.Open();
res = Cmd.ExecuteScalar();
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
Conn.Close();
}
return res;
}
}
4、数据库表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyUser]
GO
CREATE TABLE [dbo].[MyUser] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Describe] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[BirthDay] [datetime] NULL
) ON [PRIMARY]
GO