ASP.NET Web——GridView
完整增删改查示例(全篇幅包含sql脚本)大二结业考试必备技能
环境说明
系统要求：win7/10/11
开发语言：C#
开发工具：Visual Studio 2012/2017/2019/2022，本示例使用的是Visual Studio 2017
项目创建：ASP.NET Web应用程序(.NET Framework)
数据库：SQLServer 2012/2014/2017/2019，本示例使用的是SQLServer 2014
数据库工具：Navicat
功能演示
ASP.NET Web增删改查演示(ASP.NET Web——GridView完整增删改查示例(全篇幅包含sql脚本)大二结业考试必备技能)
数据库脚本
建表语句
CREATE TABLE [dbo].[users] ( [id] varchar(32) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT (replace(newid(),'-','')) , [createDate] datetime NOT NULL DEFAULT (getdate()) , [userName] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL , [age] int NOT NULL , [introduce] varchar(200) COLLATE Chinese_PRC_CI_AS NOT NULL , CONSTRAINT [PK__users__3213E83F0E2177B8] PRIMARY KEY ([id]) ) ON [PRIMARY] GO
信息插入
INSERT INTO [dbo].[users] ([id], [createDate], [userName], [age], [introduce]) VALUES (N'1a19c0945dfc44e98a715ffccdb1cc54', N'2223-08-08 18:18:22.000', N'superGirl777', N'17', N'超级女孩'); GO INSERT INTO [dbo].[users] ([id], [createDate], [userName], [age], [introduce]) VALUES (N'1EBB75FB1DD64B678413894A4B736484', N'2222-08-08 18:18:22.000', N'貂蝉', N'16', N'吕布爱妻'); GO INSERT INTO [dbo].[users] ([id], [createDate], [userName], [age], [introduce]) VALUES (N'7979e6d162c44ccbaf47fd3b0172ecf3', N'2222-01-01 01:01:01.000', N'周瑜', N'32', N'大都督'); GO INSERT INTO [dbo].[users] ([id], [createDate], [userName], [age], [introduce]) VALUES (N'9BFE04E8999F415D9224CCFCEEF40927', N'2222-08-08 18:18:22.000', N'赵子龙', N'27', N'子龙浑身都是胆'); GO
创建ASP.NET Web项目
选择左侧菜单栏中的【Web】项目，右侧会弹出对应的ASP.NET Web应用程序(.NET Framework)
选择创建【Web窗体】
创建三层关系
创建类库并完成三层关系
三层关系
引入方式
注意层级引入顺序
完成DAL层DBHelper
注意换成自己的数据库连接
using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; namespace DAL { public class DBHelper { //数据库连接地址 private static string url = "Data Source=.;Initial Catalog=test;Integrated Security=True"; public static DataTable Query(string sql) { SqlConnection conn = new SqlConnection(url);//创建链接 SqlDataAdapter sdap = new SqlDataAdapter(sql,conn);//闭合式查询 DataSet ds = new DataSet();//结果集 sdap.Fill(ds);//将闭合式查询的结果放置到结果集中 return ds.Tables[0];//返回结果集中的第一项 } public static bool NoQuery(string sql) { SqlConnection conn = new SqlConnection(url);//创建链接 conn.Open();//打开数据库连接 SqlCommand cmd = new SqlCommand(sql,conn);//声明操作 int rows=cmd.ExecuteNonQuery();//执行操作 conn.Close();//关闭数据库连接 return rows > 0;//判断是否操作成功 } } }
完成DAL层UsersDAL.cs
using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DAL { public class UsersDAL { /// <summary> /// 查询所有 /// </summary> /// <returns></returns> public static DataTable GetAll() { string sql = "select * from users"; return DBHelper.Query(sql); } /// <summary> /// 模糊查询 /// </summary> /// <param name="userName"></param> /// <returns></returns> public static DataTable GetSelectByName(string userName) { string sql = "select * from users where userName like '%" + userName + "%'"; return DBHelper.Query(sql); } /// <summary> /// 添加操作DAL /// </summary> /// <param name="userName"></param> /// <param name="age"></param> /// <param name="introduce"></param> /// <returns></returns> public static bool AddInfo(string userName, int age, string introduce) { string id = Guid.NewGuid().ToString("N"); string createDate = "2023-1-4 15:24:15"; string sql = string.Format("insert into users values('{0}','{1}','{2}',{3},'{4}')", id, createDate, userName, age, introduce); return DBHelper.NoQuery(sql); } /// <summary> /// 删除语句 /// </summary> /// <param name="id"></param> /// <returns></returns> public static bool DeleteById(string id) { string sql = "delete from users where id='" + id + "'"; return DBHelper.NoQuery(sql); } /// <summary> /// 根据id进行精准查询 /// </summary> /// <param name="id"></param> /// <returns></returns> public static DataTable GetSelectById(string id) { string sql = "select * from users where id='"+id+"'"; return DBHelper.Query(sql); } /// <summary> /// 修改 /// </summary> /// <param name="id"></param> /// <param name="age"></param> /// <param name="introduce"></param> /// <returns></returns> public static bool UpdateById(string id,string age,string introduce) { string sql = string.Format("update users set age={0},introduce='{1}' where id='{2}'", age,introduce,id); return DBHelper.NoQuery(sql); } } }
完成BLL层UsersBLL.cs
using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; namespace BLL { public class UsersBLL { public static DataTable GetAll() { return DAL.UsersDAL.GetAll(); } public static DataTable GetSelectByName(string userName) { return DAL.UsersDAL.GetSelectByName(userName); } public static bool AddInfo(string userName, int age, string introduce) { return DAL.UsersDAL.AddInfo(userName, age, introduce); } public static bool DeleteById(string id) { return DAL.UsersDAL.DeleteById(id); } public static DataTable GetSelectById(string id) { return DAL.UsersDAL.GetSelectById(id); } public static bool UpdateById(string id, string age, string introduce) { return DAL.UsersDAL.UpdateById(id,age,introduce); } } }
完成视图层Index.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Index.aspx.cs" Inherits="Demo8.Index" %> <!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> <link href="Content/bootstrap.css" rel="stylesheet" /> </head> <body> <form id="form1" runat="server"> <div> <p> <asp:TextBox runat="server" ID="selectKey"></asp:TextBox> <asp:Button runat="server" Text="查询" OnClick="Unnamed_Click"/> </p> <a href="AddInfo.aspx" class="btn btn-primary">添加数据</a> <asp:GridView CssClass="table table-bordered table-hover" runat="server" ID="showList" AutoGenerateColumns="false" OnRowCommand="showList_RowCommand"> <Columns> <asp:BoundField DataField="id" HeaderText="编号"/> <asp:BoundField DataField="createDate" HeaderText="创建时间"/> <asp:BoundField DataField="userName" HeaderText="昵称"/> <asp:BoundField DataField="age" HeaderText="年龄"/> <asp:BoundField DataField="introduce" HeaderText="简介"/> <asp:TemplateField> <ItemTemplate> <asp:LinkButton runat="server" CommandName="UpdateById" CommandArgument='<%# Eval("id") %>' CssClass="btn btn-primary">修改</asp:LinkButton> <asp:LinkButton runat="server" CommandName="DeleteById" CommandArgument='<%# Eval("id") %>' OnClientClick="return confirm('是否删除此行?')" CssClass="btn btn-primary">删除</asp:LinkButton> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> </div> </form> </body> </html>
完成后台Index.aspx.cs
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace Demo8 { public partial class Index : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //绑定数据 this.showList.DataSource = BLL.UsersBLL.GetAll(); //显示数据 this.showList.DataBind(); } } protected void Unnamed_Click(object sender, EventArgs e) { string selectKey = this.selectKey.Text; this.showList.DataSource = BLL.UsersBLL.GetSelectByName(selectKey); //显示数据 this.showList.DataBind(); } protected void showList_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "DeleteById") { BLL.UsersBLL.DeleteById(e.CommandArgument.ToString()); Response.Redirect("Index.aspx"); } else if (e.CommandName == "UpdateById") { Response.Redirect("UpdateById.aspx?id="+e.CommandArgument.ToString()); } } } }
完成视图层AddInfo.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AddInfo.aspx.cs" Inherits="Demo8.AddInfo" %> <!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> <p> <asp:TextBox runat="server" ID="userName" placeholder="请输入昵称"></asp:TextBox> </p> <p> <asp:TextBox runat="server" ID="age" placeholder="请输入年龄"></asp:TextBox> </p> <p> <asp:TextBox runat="server" ID="introduce" placeholder="请输入简介"></asp:TextBox> </p> <p> <asp:Button runat="server" Text="添加" OnClick="Unnamed_Click"/> </p> </div> </form> </body> </html>
完成后台AddInfo.aspx.cs
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace Demo8 { public partial class AddInfo : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Unnamed_Click(object sender, EventArgs e) { string userName=this.userName.Text; int age=int.Parse(this.age.Text); string introduce = this.introduce.Text; if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(this.age.Text) || string.IsNullOrEmpty(introduce)) { Response.Write("<script>alert('参数不允许有空存在!');</script>"); return; } bool isf=BLL.UsersBLL.AddInfo(userName,age,introduce); if (isf) { Response.Redirect("Index.aspx"); } else { Response.Write("<script>alert('添加失败!');</script>"); } } } }
完成视图层UpdateById.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UpdateById.aspx.cs" Inherits="Demo8.UpdateById" %> <!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> <p> <asp:TextBox runat="server" ID="id" ReadOnly="true"></asp:TextBox> </p> <p> <asp:TextBox runat="server" ID="age"></asp:TextBox> </p> <p> <asp:TextBox runat="server" ID="introduce"></asp:TextBox> </p> <p> <asp:Button runat="server" Text="提交修改" OnClick="Unnamed_Click"/> </p> </div> </form> </body> </html>
完成后台UpdateById.aspx.cs
using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace Demo8 { public partial class UpdateById : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { string id = Request.QueryString["id"]; DataTable dt = BLL.UsersBLL.GetSelectById(id); this.id.Text = dt.Rows[0]["id"].ToString(); this.age.Text = dt.Rows[0]["age"].ToString(); this.introduce.Text = dt.Rows[0]["introduce"].ToString(); } } protected void Unnamed_Click(object sender, EventArgs e) { string id=this.id.Text; string age = this.age.Text; string introduce = this.introduce.Text; if ( string.IsNullOrEmpty(id)|| string.IsNullOrEmpty(age)|| string.IsNullOrEmpty(introduce) ) { Response.Write("<script>alert('参数不允许为空!');<script>"); return; } BLL.UsersBLL.UpdateById(id,age,introduce); Response.Redirect("Index.aspx"); } } }
最终执行效果：
