ADO.NET之数据刺进、修正、删去、查询操作51CTO博客 - 乐橙lc8

ADO.NET之数据刺进、修正、删去、查询操作51CTO博客

2019年04月24日11时43分26秒 | 作者: 怀山 | 标签: 查询,数据,删去 | 浏览: 1184

布景:VisualStudio2005;              对数据库进行查询、刺进、修正、删去操作;              运用GridView控件显现数据; 完成:
  • 数据库规划
create database db_user
go
use db_user
go
create table tb_user
( pID varchar(10) primary key,
  pName varchar(20) not null,
  pSex varchar(2) not null
)
go
insert into tb_user values(1,admin,男)
go
select * from tb_user
go

  • 界面
  • 代码
html: <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %> <!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>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="工 号:"></asp:Label>
        <asp:TextBox ID="txtPID" runat="server"></asp:TextBox>
        <asp:CustomValidator ID="CustomValidator1" runat="server" ControlToValidate="txtPID"
            ErrorMessage="用户现已存在!" OnServerValidate="CustomValidator1_ServerValidate"></asp:CustomValidator>
        <asp:CheckBox ID="chkPID" runat="server" Style="z-index: 100; left: 353px; position: absolute;
            top: 15px" />
        <br />
        <asp:Label ID="Label2" runat="server" Text="姓 名:"></asp:Label>
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtName"
            ErrorMessage="*"></asp:RequiredFieldValidator>
        &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
        &nbsp;
        <asp:CheckBox ID="chkPName" runat="server" Style="z-index: 101; left: 353px; position: absolute;
            top: 39px" />
        <br />
        <asp:Label ID="Label3" runat="server" Text="性 别:"></asp:Label>
        <asp:RadioButton ID="rbtMale" runat="server" GroupName="sex" Text="男" Width="43px" Checked="True" />
        <asp:RadioButton ID="rbtFemale" runat="server" GroupName="sex" Text="女" />
        &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
        &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
        &nbsp;
        <asp:CheckBox ID="chkPSex" runat="server" Style="z-index: 103; left: 353px; position: absolute;
            top: 63px" />
        <br />
        <asp:Button ID="btnAdd" runat="server" OnClick="btnAdd_Click" Text="添 加" />
        <asp:Button ID="btnDelete" runat="server" Text="删 除" CausesValidation="False" OnClick="btnDelete_Click" />
        <asp:Button ID="btnEdit" runat="server" Text="修 改" CausesValidation="False" OnClick="btnEdit_Click" />&nbsp;
        <asp:Button ID="btnSelect" runat="server" Text="查 询" CausesValidation="False" OnClick="btnSelect_Click" />
        &nbsp;&nbsp;
        <br />
        <asp:Label ID="lblMessage" runat="server" Width="240px"></asp:Label><br />
        <asp:GridView ID="GridView1" runat="server" Height="137px" Width="240px" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" >
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:BoundField DataField="pID" HeaderText="工 号" />
                <asp:BoundField DataField="pName" HeaderText="姓 名" />
                <asp:BoundField DataField="pSex" HeaderText="性 别" />
            </Columns>
            <RowStyle BackColor="#EFF3FB" Font-Size="Small" HorizontalAlign="Center" />
            <EditRowStyle BackColor="#2461BF" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" Font-Size="Small" ForeColor="White"
                HorizontalAlign="Center" />
            <AlternatingRowStyle BackColor="White" HorizontalAlign="Center" />
        </asp:GridView>
   
    </div>
    </form>
</body>
</html>

后台: person.cs:   public class person
{
    //界说person目标的三个特点字段
    public string pID;
    public string pName;
    public string pSex;
    public person()
 {
  //
  // TODO: 在此处增加结构函数逻辑
  //
 }
}


personOperate.cs: using System;
using System.Data;
using System.Configuration;
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;
using System.Data.SqlClient;
/// <summary>
/// operate 的摘要阐明,一切操作类
/// </summary>
public class personOperate
{  
 public personOperate()
 {
  //
  // TODO: 在此处增加结构函数逻辑
  //
 }
    public static SqlConnection createCon()
    {
        return new SqlConnection("server=localhost\\sqlexpress;database=db_user;uid=sa;pwd=hello");
    }


    //依据职工工号 pID查询数据库是否现已存在同名记载
    public static bool  findPerson( string pID)
    {
        SqlConnection con = personOperate.createCon();
        con.Open();
        SqlCommand cmd = new SqlCommand("select count(*) from tb_user where pID=" + pID + "", con);
        //用count获取:回来查询所回来的成果会集的榜首行榜首列;假如大于0表明存在记载;反之则不存在
        int count = Convert.ToInt32(cmd.ExecuteScalar());
        if (count > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    //显现一切职工信息
    public static DataTable selectAll()
    {
        SqlConnection con = personOperate.createCon();
        //SqlDataApapter表明用于填充DataSet和更新SQL Server数据库的一组数据指令和一个数据库衔接
        SqlDataAdapter sda = new SqlDataAdapter();
        //(获取或)设置一个T_SQL句子或存储进程,用于在数据源中挑选记载
        sda.SelectCommand = new SqlCommand("select * from tb_user", con);
        //DataSet表明数据在内存中的缓存;初始化DataSet类的新实例
        DataSet ds = new DataSet();
        //运用sda.Fill()办法填充ds,并创立一个名为“user"的DataTable目标,将数据存放在表中
        sda.Fill(ds, "user");
        //(获取或)设置在DataSet中的表的调集
        //获取数据会集的数据表
        return ds.Tables["user"];
    }

   //增加职工数据,增加时查看职工工号字段pID是否现已存在
    public static bool addPerson(person p)
    {
        try
        {
            SqlConnection con = personOperate.createCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into tb_user values(@pID,@pName,@pSex)", con);
            SqlParameter para = new SqlParameter("@pID", SqlDbType.VarChar, 10);
            para.Value = p.pID;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@pName", SqlDbType.VarChar, 20);
            para.Value = p.pName;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@pSex", SqlDbType.VarChar, 2);
            para.Value = p.pSex;
            cmd.Parameters.Add(para);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }

    //依据职工工号pID删去职工数据
    public static bool delPerson(string pID)
    {
        try
        {
            SqlConnection con = personOperate.createCon();
            con.Open();
            //表明要对SQL Server数据库履行一个T_SQL句子或存储进程,在此运用参数
            SqlCommand cmd = new SqlCommand("delete from tb_user where pID=" + @pID + "", con);
            //表明SqlCommand的参数(参数称号,数据类型,巨细)
            SqlParameter para = new SqlParameter("@pID", SqlDbType.VarChar, 10);
            //(获取或)设置参数的值
            para.Value = pID;
            //将拟定的SqlParameter目标增加到SqlParameterCollection中
            cmd.Parameters.Add(para);
            //对衔接履行T_SQL句子并回来受影响的行数
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }
    }


    //依据职工工号pID修正职工数据
    public static bool editPerson(person p)
    {
        try
        {
            SqlConnection con = personOperate.createCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("update tb_user set pName="+p.pName+ ",pSex=" + p.pSex + " where pID=" +p.pID+"", con);
           
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception eedit)
        {
            return false;
        }
    }

    //依据条件含糊查询用户信息
    public static DataTable selectPerson(string sql)
    {
       
        SqlConnection con = personOperate.createCon();
        con.Open();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = new SqlCommand(sql, con);
        DataSet ds = new DataSet();
        sda.Fill(ds, "user");
        return ds.Tables["user"];
    }
}

Default.aspx: using System;
using System.Data;
using System.Configuration;
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;
using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //初始化页面时,读取悉数用户信息
            this.fillGV();
        }
    }

    protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
    {
       //获取需自界说验证控件验证的值
        string pID = args.Value;
        //调用personOperate.findPerson()函数
        if(personOperate.findPerson(pID))
        {
            args.IsValid=false;
        }
        else
        {
            args.IsValid=true;
        }
    }

    //将GridView1数据源设置为DataTable表数据
    private void  fillGV()
    { 
        this.GridView1.DataSource=personOperate.selectAll();
        this.GridView1.DataBind();
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
       //假如页面经过验证,则引发增加数据的按钮事情
        if (this.IsValid)
        { 
            //实例化person目标
            person p = new person();
            p.pID = this.txtPID.Text;
            p.pName = this.txtName.Text;
            if (this.rbtMale.Checked)
            {
                p.pSex = "男";
            }
            else
            {
                p.pSex = "女";
            }
            //假如用户不存在则调用personOperate.delPerson()办法进行用户信息增加
            if (personOperate.addPerson(p))
            {
                this.lblMessage.Text = "刺进成功!";
                this.fillGV();             }
            else
            {
                this.lblMessage.Text = "刺进失利!";
            }
        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
          //获取用户的工号字段值pID,并调用personOperate.delPerson()办法删去指定用户信息
            string pID = this.txtPID.Text;
            if (personOperate.delPerson(pID))
            {
                this.lblMessage.Text = "删去成功!";
                this.fillGV();
            }
            else
            {
                this.lblMessage.Text = "删去失利!";
            }
     
    }

    //依据职工工号修正职工信息
    protected void btnEdit_Click(object sender, EventArgs e)
    {
      
            person p = new person();
            p.pID = this.txtPID.Text;
            p.pName = this.txtName.Text;
            if (this.rbtMale.Checked)
            {
                p.pSex = "男";
            }
            else
            {
                p.pSex = "女";
            }
            if (personOperate.editPerson(p))
            {
                this.lblMessage.Text = "修正成功!";
                this.fillGV();             }
            else
            {
                this.lblMessage.Text = "修正失利!";
            }
      
    }

    protected void btnSelect_Click(object sender, EventArgs e)
    {
        //初始设置查询字符串,运用"where 1=1"
        string sqlQuery = "select * from tb_user where 1=1 ";
        if (this.chkPID.Checked)
        {
            sqlQuery = sqlQuery + " and"+" pID like  %"+ this.txtPID.Text +"%";
           
        }
        if (this.chkPName.Checked)
        {
            sqlQuery = sqlQuery + " and" + " pName like %" + this.txtName.Text + "%";         }
        if (this.chkPSex.Checked && this.rbtMale.Checked)
        {
            sqlQuery += " and" + " pSex=" + "男" + "";
        }
        if (this.chkPSex.Checked && this.rbtFemale.Checked)
        {
            sqlQuery += " and" + " pSex=" + "女" + "";
        }
        //SqlConnection con = personOperate.createCon();
        //con.Open();
        //SqlDataAdapter sda = new SqlDataAdapter();
        //sda.SelectCommand = new SqlCommand(sqlQuery, con);
        //DataSet ds = new DataSet();
        //sda.Fill(ds, "user");
        //this.GridView1.DataSource = ds.Tables["user"];
        //this.GridView1.DataBind();
        this.GridView1.DataSource = personOperate.selectPerson(sqlQuery);
        this.GridView1.DataBind();     }
  
}

  • 运转
1、初次加载页面,显现一切职工信息   2、增加用户信息,提示“刺进成功” 3、将新增加记载的职工性别有“男”更改为“女”   4、删去职工号为12的用户记载 5、查询工号中包括有“1”的职工信息 6、查询职工名字中包括有“阳”的职工记载   7、查询性别为男的一切职工信息       8、准确查询    
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表乐橙lc8立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2

    游戏数据库布置脚本51CTO博客

    脚本,游戏,数据库
  • 3
  • 4

    SHUTDOWN51CTO博客

    商场,方法,或许
  • 5
  • 6
  • 7
  • 8

    mongodb 根本操作ITeye

    删去,数组,字段
  • 9
  • 10