Asp.Net页面
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewAdd.aspx.cs" Inherits="GridViewAdd" Debug="true" %>
<!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>无标题页</title>
<script language="javascript">
function checkDel(str)
{
if(!confirm("确认删除 "+str+" 吗？"))
return(false);
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID=myGridView runat=server AutoGenerateColumns=False
AllowPaging=True PageSize=15 AllowSorting=True
HorizontalAlign=Center AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" Width="772px"
DataKeyNames="ID" CellPadding="4" ForeColor="#333333" GridLines="None" OnRowCancelingEdit="myGridView_RowCancelingEdit" OnRowUpdating="myGridView_RowUpdating" OnRowDeleting="myGridView_RowDeleting" OnRowEditing="myGridView_RowEditing" OnDataBound="myGridView_DataBound">
<PagerStyle HorizontalAlign=Center ForeColor=White BackColor="#284775" />
<Columns>
<asp:BoundField HeaderText="编号" DataField=ID ReadOnly=True />
<asp:TemplateField HeaderText="用户名">
<ItemTemplate>
<asp:Label ID=showUserName runat=server Text='<%# Eval("UserName") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=txtUserName runat=server Text='<%# Eval("UserName") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="密码">
<ItemTemplate>
<asp:Label ID=showPassword runat=server Text='<%# Eval("Password") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=txtPassword runat=server Text='<%# Eval("Password") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="描述">
<ItemTemplate>
<asp:Label ID=showDescribe runat=server Text='<%# Eval("Describe") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=txtDescribe runat=server Text='<%# Eval("Describe") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="生日">
<ItemTemplate>
<asp:Label ID=showBirthDay runat=server Text='<%# ((DateTime)Eval("BirthDay")).ToString("yyyy年MM月dd") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Calendar ID="txtBirthDay" runat=server SelectedDate='<%# Eval("BirthDay") %>'></asp:Calendar>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="txtDel" runat="server" CausesValidation="False" CommandArgument='<%# Eval("ID") %>'
OnClick="txtDel_Click" Text="Del"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<PagerSettings FirstPageText="第一页" LastPageText="最末页"
NextPageText="下一页" PreviousPageText="上一页" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<center><asp:Button ID="BtAdd" runat="server" Text="新增" OnClick="BtAdd_Click" />
<asp:Button ID="BtSave" runat="server" Text="保存" OnClick="BtSave_Click"/></center></div>
</form>
</body>
</html>
*********************
代码
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 + ")");
}
}
}
***********************
数据访问引用到的类
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;
}
}
************************************
数据库表
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