WebForm增删(批删)改查(多条件)
程序员文章站
2022-07-06 10:58:02
//Model层(两表)--biao:using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace MODER{ public class biao { public int ID { get; set; } public string NAME { get; set;...
//Model层(两表)
--biao:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MODER
{
public class biao
{
public int ID { get; set; }
public string NAME { get; set; }
public string QUYU { get; set; }
public int PIAOJIA { get; set; }
public int COID { get; set; }
public string MIAOSHU { get; set; }
public string ZT { get; set; }
}
}
--country
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MODER
{
public class country
{
public int CID { get; set; }
public string GUOJIA { get; set; }
}
}
//Dal访问层
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using MODER;
namespace DAL
{
public class guan
{
DBHelper db = new DBHelper();
public int Add(biao b) //景点表做添加方法
{
string s = string.Format("insert into biao values('{0}','{1}',{2},{3},'{4}','{5}')",b.NAME,b.QUYU,b.PIAOJIA,b.COID,b.MIAOSHU,b.ZT);
return db.ExecuteNonQuery(s);
}
public List<country> Xian() //给国家表做一个显示方法
{
string s = "select *from COUNTRY";
return db.GetToList<country>(s);
}
public List<biao> Show() //景点表的显示方法
{
string s = "select*from biao";
return db.GetToList<biao>(s);
}
public int Delete(int id) //景点表的删除方法
{
string s = string.Format("delete from biao where ID={0}",id);
return db.ExecuteNonQuery(s);
}
public int PDAL(string id) //景点表的批删方法
{
string s = string.Format("delete from biao where id in ("+id+")");
return db.ExecuteNonQuery(s);
}
public int UPdate(biao b) //景点表的修改方法
{
string s = string.Format("update biao set NAME='{0}',QUYU='{1}',PIAOJIA={2},COID={3},MIAOSHU='{4}',ZT='{5}' WHERE ID={6}",b.NAME,b.QUYU,b.PIAOJIA,b.COID,b.MIAOSHU,b.ZT,b.ID);
return db.ExecuteNonQuery(s);
}
public DataTable CHaid(int id) //根据你所修改的数据去查ID
{
string s = "select*from biao where id=" + id;
return db.GetTable(s);
}
//public List<biao> CHAxun(string NAME) 单条件查询,按照姓名进行查询
//{
// string s = string.Format("select*from biao where name like '%"+NAME+"%'");
// return db.GetToList<biao>(s);
//}
public List<biao> CHAzhao(string name, int COID) //多条件查询
{
string s = string.Format("select*from biao where 1=1");
if (name != "") //根据姓名去查询
{
s += "and name like '%" + name + "%'";
}
else if (COID != 0) //根据所在国家查询
{
s += "and coid=" + COID;
}
return db.GetToList<biao>(s);
}
}
}
//Dbhelper封装方法
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Net;
using System.Net.Sockets;
namespace DAL
{
public class DBHelper
{
private SqlConnection conn = null;
/// <summary>
/// 构造函数
/// </summary>
public DBHelper()
{
if (conn == null)
{
conn = new SqlConnection("Data Source=.;Initial Catalog=KUBLL;Integrated Security=True");
}
}
/// <summary>
/// 返回DataTable查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public DataTable GetTable(string sql, SqlParameter[] par = null)
{
try
{
SqlCommand com = new SqlCommand(sql, conn);
if (par != null)
{
com.Parameters.AddRange(par);
}
SqlDataAdapter ada = new SqlDataAdapter(com);
DataTable dt = new DataTable();
ada.Fill(dt);
ada.Dispose();
if (conn.State == ConnectionState.Open)
{
this.Close();
}
return dt;
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open)
{
this.Close();
}
throw;
}
}
/// <summary>
/// 返回List查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public List<T> GetToList<T>(string sql, SqlParameter[] par = null)
{
List<T> li = DataTableToList<T>(GetTable(sql));
return li;
}
/// <summary>
/// 返回查询结果首行首列
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, SqlParameter[] par = null)
{
try
{
this.Open();
SqlCommand com = new SqlCommand(sql, conn);
if (par != null)
{
com.Parameters.AddRange(par);
}
return com.ExecuteScalar();
}
catch (Exception ex)
{
this.Close();
throw;
}
}
/// <summary>
/// 返回执行结果受影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, SqlParameter[] par = null)
{
try
{
this.Open();
SqlCommand com = new SqlCommand(sql,conn);
if (par != null)
{
com.Parameters.AddRange(par);
}
return com.ExecuteNonQuery();
}
catch (Exception ex)
{
this.Close();
throw;
}
}
/// <summary>
/// Table转list集合
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private static List<T> DataTableToList<T>(DataTable dt)
{
//初始化值
List<T> result = new List<T>();
for (int i = 0; i < dt.Rows.Count; i++)
{
T _t = (T)Activator.CreateInstance(typeof(T));
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pro in propertys)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (pro.Name.Equals(dt.Columns[j].ColumnName))
{
if (dt.Rows[i][j] != DBNull.Value)
{
pro.SetValue(_t, dt.Rows[i][j], null);
}
else
{
pro.SetValue(_t, null, null);
}
break;
}
}
}
result.Add(_t);
}
return result;
}
/// <summary>
/// 打开数据库链接
/// </summary>
private void Open()
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
/// <summary>
/// 关闭数据库链接
/// </summary>
private void Close()
{
if (conn.State != ConnectionState.Closed)
{
conn.Dispose();
}
}
}
}
//Bll逻辑层
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using DAL;
using MODER;
namespace BLL
{
public class zhang
{
guan g = new guan();
public int Add(biao b) //景点表做添加方法
{
return g.Add(b);
}
public List<country> Xian() //给国家表做一个显示方法
{
return g.Xian();
}
public List<biao> Show() //景点表的显示方法
{
return g.Show();
}
public int Delete(int id) //景点表的删除方法
{
return g.Delete(id);
}
public int PDAL(string id) //景点表的批删方法
{
return g.PDAL(id);
}
public int UPdate(biao b) //景点表的修改方法
{
return g.UPdate(b);
}
public DataTable CHaid(int id) //根据你所修改的数据去查ID
{
return g.CHaid(id);
}
//public List<biao> CHAxun(string NAME) 单条件查询,按照姓名进行查询
//{
// return g.CHAxun(NAME);
//}
public List<biao> CHAzhao(string name, int COID) //多条件查询
{
return g.CHAzhao(name, COID);
}
}
}
//UI显示,视图(增)
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ADD.aspx.cs" Inherits="xinjian.ADD" %>
<!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>
<style type="text/css">
.auto-style1 {
width: 156px;
}
.auto-style2 {
width: 156px;
height: 23px;
}
.auto-style3 {
height: 23px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="width: 100%;">
<tr>
<td class="auto-style2">景点名称:</td>
<td class="auto-style3">
<asp:TextBox ID="name" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">所属区域:</td>
<td>
<asp:RadioButtonList ID="quyu" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Selected="True" Value="1">境内</asp:ListItem>
<asp:ListItem Value="2">*</asp:ListItem>
</asp:RadioButtonList>
</td>
</tr>
<tr>
<td class="auto-style1">票价:</td>
<td>
<asp:TextBox ID="piaojia" runat="server" TextMode="Number"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">所属国家:</td>
<td>
<asp:DropDownList ID="guojia" runat="server">
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="auto-style1">景点描述:</td>
<td>
<asp:TextBox ID="miaoshu" runat="server" TextMode="MultiLine"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">状态:</td>
<td>
<asp:RadioButtonList ID="zhuangtai" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Selected="True" Value="1">开放</asp:ListItem>
<asp:ListItem Value="2">关闭</asp:ListItem>
</asp:RadioButtonList>
</td>
</tr>
<tr>
<td class="auto-style1"> </td>
<td>
<asp:Button ID="Button1" runat="server" Text="保存" OnClick="Button1_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
//保存按钮点击事件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MODER;
using BLL;
namespace xinjian
{
public partial class ADD : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
zhang z = new zhang();
country c = new country();
if (!IsPostBack) //绑定下拉
{
guojia.DataSource = z.Xian();
guojia.DataTextField = "GUOJIA";
guojia.DataValueField = "CID";
guojia.DataBind();
ListItem li = new ListItem("请选择","0"); //附上默认值
guojia.Items.Insert(0,li);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
zhang z = new zhang(); //实例化BLL层
biao b = new biao(); //实例化对应的表
b.NAME = name.Text;
b.QUYU = quyu.SelectedItem.Value == "1" ? "境内" : "*";
b.PIAOJIA = int.Parse(piaojia.Text);
b.COID = int.Parse(guojia.SelectedItem.Value);
b.MIAOSHU = miaoshu.Text;
b.ZT = zhuangtai.SelectedItem.Value == "1" ? "开放" : "关闭"; //从前台获取值
int o = z.Add(b);
if (o > 0)
{
Response.Write("<script>alert('录入成功');location.href='/SHOW.aspx'</script>");
}
else
{
Response.Write("<script>alert('录入失败')</script>");
}
}
}
}
//显示,查询,单删,批删
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SHOW.aspx.cs" Inherits="xinjian.SHOW" %>
<!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>
<div style="margin-top:30px">
请输入要查找人的姓名:<asp:TextBox ID="NAME" runat="server" Width="148px"></asp:TextBox> 请输入要查找人的国籍:<asp:DropDownList ID="guojia" runat="server"></asp:DropDownList> <asp:Button ID="Button5" runat="server" Text="查询" OnClick="Button5_Click" /> <asp:Button ID="Button2" runat="server" Text="批量删除" OnClick="Button2_Click" /><asp:Button ID="Button3" runat="server" Text="添加" OnClick="Button3_Click" />
</div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" OnPageIndexChanging="GridView1_PageIndexChanging" PageSize="4" OnRowCommand="GridView1_RowCommand" Width="916px" DataKeyNames="id" Height="177px">
<Columns>
<asp:TemplateField HeaderText="批量处理">
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ID" HeaderText="景区编号" />
<asp:BoundField DataField="NAME" HeaderText="景区名称" />
<asp:BoundField DataField="QUYU" HeaderText="所属区域" />
<asp:BoundField DataField="PIAOJIA" HeaderText="票价" />
<asp:BoundField DataField="COID" HeaderText="所属国家" />
<asp:BoundField DataField="MIAOSHU" HeaderText="景区描述" />
<asp:BoundField DataField="ZT" HeaderText="状态" />
<asp:TemplateField HeaderText="操作">
<ItemTemplate>
<asp:Button ID="Button1" runat="server" Text="删除" CommandName="shanchu" CommandArgument='<%#Eval("ID")%>' OnClientClick="return confirm('您确定要删除吗?')"/>
<asp:Button ID="Button4" runat="server" Text="修改" CommandName="xiugai" CommandArgument='<%#Eval("ID")%>'/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
```csharp
//批删,多查
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MODER;
using BLL;
namespace xinjian
{
public partial class SHOW : System.Web.UI.Page
{
zhang z = new zhang();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
guojia.DataSource = z.Xian();
guojia.DataTextField = "GUOJIA"; //绑定下拉
guojia.DataValueField = "CID";
guojia.DataBind();
ListItem li = new ListItem("请选择国家", "0");
guojia.Items.Insert(0, li);
GridView1.DataSource = z.Show(); //获取所有的信息
GridView1.DataBind();
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex; //获取当前页
GridView1.DataSource = z.Show(); //根据当前页数获取当前信息
GridView1.DataBind();
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
string q = e.CommandName; //获取前台的删除按钮的命名
int id = Convert.ToInt32(e.CommandArgument); //获取命令对象
if (q == "shanchu") //判断Q的值
{
int k = z.Delete(id); //获取到相对应的值进行删除
if (k > 0)
{
Response.Write("<script>location.href='/SHOW.aspx'</script>");
}
else
{
Response.Write("<script>alert('删除失败')</script>");
}
}
else if(q=="xiugai")
{
Response.Redirect("/UPDATE.aspx?ID="+id);
}
}
protected void Button3_Click(object sender, EventArgs e) //添加按钮
{
Response.Write("<script>location.href='/ADD.aspx'</script>");
}
protected void Button2_Click(object sender, EventArgs e) //批量删除
{
string str = "";
//获取每一行的对象
foreach (GridViewRow item in GridView1.Rows)
{
//获取每一行的复选框对象
CheckBox ck = item.FindControl("CheckBox1") as CheckBox;
if (ck.Checked==true)
{
//获取每一行的值
str+=","+Convert.ToInt32(GridView1.DataKeys[item.RowIndex].Value);
}
}
if (str != "")
{
str = str.Substring(1); //截取每一个要删除吧对象
int i = z.PDAL(str);
if (i > 0)
{
Response.Write("<script>alert('删除成功');location.href='/SHOW.aspx'</script>");
}
else
{
Response.Write("<script>alert('删除失败')</script>");
return;
}
}
else
{
Response.Write("<script>alert('对不起请选择你要删除的编号')</script>");
return;
}
}
protected void Button5_Click(object sender, EventArgs e) //单条件查询
{
//string name = NAME.Text; 获取文本框的值
//GridView1.DataSource = z.CHAxun(name); 获取到要查询的条件
//GridView1.DataBind(); 绑定
string name = NAME.Text; //获取对应条件的值
int COID = int.Parse(guojia.SelectedItem.Value);
GridView1.DataSource = z.CHAzhao(name, COID); //获取要查询的条件
GridView1.DataBind(); //绑定
}
}
}
```csharp
```csharp
//修改
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UPDATE.aspx.cs" Inherits="xinjian.UPDATE" %>
<!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>
<table style="width: 100%;">
<tr>
<td class="auto-style2">景点名称:</td>
<td class="auto-style3">
<asp:TextBox ID="name" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">所属区域:</td>
<td>
<asp:RadioButtonList ID="quyu" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Selected="True" Value="1">境内</asp:ListItem>
<asp:ListItem Value="2">*</asp:ListItem>
</asp:RadioButtonList>
</td>
</tr>
<tr>
<td class="auto-style1">票价:</td>
<td>
<asp:TextBox ID="piaojia" runat="server" TextMode="Number"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">所属国家:</td>
<td>
<asp:DropDownList ID="guojia" runat="server">
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="auto-style1">景点描述:</td>
<td>
<asp:TextBox ID="miaoshu" runat="server" TextMode="MultiLine"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">状态:</td>
<td>
<asp:RadioButtonList ID="zhuangtai" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Selected="True" Value="1">开放</asp:ListItem>
<asp:ListItem Value="2">关闭</asp:ListItem>
</asp:RadioButtonList>
</td>
</tr>
<tr>
<td class="auto-style1"> </td>
<td>
<asp:Button ID="Button1" runat="server" Text="修改" OnClick="Button1_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
//修改按钮点击事件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MODER;
using BLL;
namespace xinjian
{
public partial class UPDATE : System.Web.UI.Page
{
zhang z = new zhang();
protected void Page_Load(object sender, EventArgs e)
{
country c = new country();
//开始反填
if (!IsPostBack) //第一次加载
{
guojia.DataSource = z.Xian();
guojia.DataTextField = "GUOJIA"; //绑定下拉
guojia.DataValueField = "CID";
guojia.DataBind();
ListItem li = new ListItem("请选择国家", "0");
guojia.Items.Insert(0, li);
int id = int.Parse(Request["id"]);
DataTable d = z.CHaid(id);
name.Text = d.Rows[0]["NAME"].ToString(); //反填当前内容
quyu.Text = d.Rows[0]["QUYU"].ToString();
piaojia.Text = d.Rows[0]["PIAOJIA"].ToString();
guojia.Text = d.Rows[0]["COID"].ToString();
miaoshu.Text = d.Rows[0]["MIAOSHU"].ToString();
zhuangtai.Text = d.Rows[0]["ZT"].ToString();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
zhang z = new zhang(); //实例化BLL层
biao b = new biao(); //实例化对应的表
b.NAME = name.Text;
b.QUYU = quyu.SelectedItem.Value == "1" ? "境内" : "*";
b.PIAOJIA = int.Parse(piaojia.Text);
b.COID = int.Parse(guojia.SelectedItem.Value);
b.MIAOSHU = miaoshu.Text;
b.ZT = zhuangtai.SelectedItem.Value == "1" ? "开放" : "关闭"; //从前台获取值
b.ID = int.Parse(Request["ID"]);
int o = z.UPdate(b);
if (o > 0)
{
Response.Write("<script>alert('修改成功');location.href='/SHOW.aspx'</script>");
}
else
{
Response.Write("<script>alert('修改失败')</script>");
return;
}
}
}
}
本文地址:https://blog.csdn.net/qq_45244974/article/details/107318577
上一篇: 荐 MySQL数据库之数据库优化