当前位置: 移动技术网 > IT编程>脚本编程>Python > WebForm增删(批删)改查(多条件)

WebForm增删(批删)改查(多条件)

2020年07月14日  | 移动技术网IT编程  | 我要评论
//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">&nbsp;</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>&nbsp;请输入要查找人的国籍:<asp:DropDownList ID="guojia" runat="server"></asp:DropDownList>&nbsp;<asp:Button ID="Button5" runat="server" Text="查询" OnClick="Button5_Click" />&nbsp; <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">&nbsp;</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

如对本文有疑问, 点击进行留言回复!!

相关文章:

验证码:
移动技术网