donet学习笔记7(数据库的增删改查．dataview的运用)

１．数据库前台设计：非常简单，建立一个表即可

create   table  person
(
  pID  varchar ( 10 )  primary   key ,
  personName  varchar ( 20 )  not   null ,
  personSex  varchar ( 2 ) ,
)

２．前台设计：
有两个textbox负责输入编号，姓名，性别用radiobutton做．然后每一个拦位都有一个checkbox的选择框选择是否作为查询条件
四个Button控制增，删，改，查
一个datagrid用于显示数据
具体ＡＳＰ代码如下：

<% @ 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 > Untitled Page </ title >
</ head >
< body >
     < form id = " form1 "  runat = " server " >
     < div >
         < asp:RadioButton ID = " RadioButtonFemale "  runat = " server "  GroupName = " sex "  Style = " z-index: 100;
            left: 233px; position: absolute; top: 143px "  Text= " 女 "  />
         & nbsp; & nbsp;
         < asp:Button ID = " ButtonUpdate "  runat = " server "  Style = " z-index: 101; left: 97px; position: absolute;
            top: 197px "  Text= " 修改 "  OnClick= " ButtonUpdate_Click "  />
     </ div >
         < asp:DataGrid ID = " DataGridShow "  runat = " server "  AutoGenerateColumns = " True "  
            Style = " z-index: 102; left: 84px; position: absolute;
            top: 251px "  Height= " 174px "  Width= " 313px " ></asp:DataGrid>
         & nbsp; & nbsp;
         < asp:Label ID = " Label1 "  runat = " server "  Height = " 20px "  Style = " z-index: 103; left: 78px;
            position: absolute; top: 83px "  Text= " 编号: "  Width= " 75px " ></asp:Label>
         < asp:TextBox ID = " TextBoxNum "  runat = " server "  Style = " z-index: 104; left: 167px; position: absolute;
            top: 83px " ></asp:TextBox>
         < asp:Label ID = " Label2 "  runat = " server "  Style = " z-index: 105; left: 82px; position: absolute;
            top: 112px "  Text= " 姓名： " ></asp:Label>
         < asp:TextBox ID = " TextBoxName "  runat = " server "  Style = " z-index: 106; left: 168px; position: absolute;
            top: 114px " ></asp:TextBox>
         < asp:Label ID = " Label3 "  runat = " server "  Style = " z-index: 107; left: 82px; position: absolute;
            top: 141px "  Text= " 性别 " ></asp:Label>
         < asp:RadioButton ID = " RadioButtonMale "  runat = " server "  Checked = " True "  GroupName = " sex "
            Style = " z-index: 108; left: 173px; position: absolute; top: 142px "  Text = " 男 "   />
         < asp:Button ID = " ButtonInsert "  runat = " server "  OnClick = " ButtonInsert_Click "  Style = " z-index: 109;
            left: 95px; position: absolute; top: 171px "  Text= " 添加 "  />
         < asp:CustomValidator ID = " CustomValidator1 "  runat = " server "  ControlToValidate = " TextBoxNum "
            Display = " Dynamic "  ErrorMessage = " 该用户已经存在 "  OnServerValidate = " CustomValidator1_ServerValidate "
            Style = " z-index: 110; left: 309px; position: absolute; top: 84px " ></ asp:CustomValidator >
         < asp:RequiredFieldValidator ID = " RequiredFieldValidator1 "  runat = " server "  ControlToValidate = " TextBoxName "
            ErrorMessage = " * "  Style = " z-index: 111; left: 313px; position: absolute; top: 117px " ></ asp:RequiredFieldValidator >
         < asp:RequiredFieldValidator ID = " RequiredFieldValidator2 "  runat = " server "  ControlToValidate = " TextBoxNum "
            ErrorMessage = " * "  Style = " z-index: 112; left: 431px; position: absolute; top: 82px " ></ asp:RequiredFieldValidator >
         < asp:Button ID = " ButtonDelete "  runat = " server "   Style = " z-index: 113;
            left: 270px; position: absolute; top: 173px "  Text= " 删除 "  OnClick= " ButtonDelete_Click "  />
         < asp:Button ID = " ButtonSelect "  runat = " server "  OnClick = " ButtonSelect_Click "
            Style = " z-index: 114; left: 271px; position: absolute; top: 195px "  Text = " 查询 "   />
         < asp:CheckBox ID = " CheckBoxPID "  runat = " server "  Style = " z-index: 115; left: 454px; position: absolute;
            top: 83px "  />
         < asp:CheckBox ID = " CheckBoxName "  runat = " server "  Style = " z-index: 116; left: 440px;
            position: absolute; top: 110px "  />
         < asp:CheckBox ID = " CheckBoxSex "  runat = " server "  Style = " z-index: 118; left: 447px; position: absolute;
            top: 140px "  />
     </ form >
</ body >
</ html >

3.后台代码：
后台分为ＡＳＰ的前台页面自身ＣＳ文件(Default.asp.cs)，实体类文件（person.cs），和实体类操作(personOperator.cs)文件，代码如下：
person.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;

public   class  person
{
    public string pID;
    public string pName;
    public  string pSex;
    public person()
    {
    }
}

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;
using  System.Data.SqlClient;


public   class  personOperate
{
    public personOperate()
    {
    }
    //创建连接
    public static SqlConnection createConnection()
    {
        return new SqlConnection("server=.;database=adoNetTest;uid=sorry0208;pwd=sorry0208;");
    }
    //查询是否有该用户存在
    public static bool findPerson(string pID)
    {
        SqlConnection conn = personOperate.createConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand("select count(*) from person where pID = '" + pID + "'",conn);
        int count = Convert.ToInt32(cmd.ExecuteScalar());
        if (count > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    //选择所有用户,返回一个数据表集合
    public static DataTable selectAllPerson()
    {
        SqlConnection conn = personOperate.createConnection();
        //用适配器操作的时候不需要打开connection，如果发现没有打开它会自动打开
        //conn.Open();
        //数据库适配器．
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = new SqlCommand("select * from person",conn);
        //创建数据集
        DataSet ds = new DataSet();
        //填充：第二个参数为表名
        sda.Fill(ds, "person");
        //取出了所有数据库中的person表的所有字段
        return ds.Tables["person"];
    }
    //修改表
    public static bool updateOperator(person p)
    {
        try
        {
            SqlConnection conn = personOperate.createConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand("update  person set personName = '" + p.pName + "', personSex = '" + p.pSex + "' where pID = " + p.pID, conn);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch(Exception e)
        {
            
            return false;
        }
    }
    //删除表
    public static bool deleteOperator(String pID)
    {
        try
        {
            SqlConnection conn = personOperate.createConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand("delete  person where pID =  "+pID, conn);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            return false;
        }

    }
    //插入表
    public static bool insertOperator(person p)
    {
        try
        {
            SqlConnection conn = personOperate.createConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand("insert into person values(@pID,@pName,@pSex)", conn);
            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;
        }
    }
}

Default.asp.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;
public  partial  class  _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    //服务器端验证,查询输入的用户是否已经存在
    protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
    {
        string pID = args.Value;
        if (personOperate.findPerson(pID))
        {
            args.IsValid = false;
        }
        else
        {
            args.IsValid = true;
        }

    }
    //对DataGrid做绑定.绑定到一张表上边去
    private void fillDg()
    {
        DataGridShow.DataSource = personOperate.selectAllPerson();
        DataGridShow.DataBind();
    }
    //insert按纽出发新增事件
    protected void ButtonInsert_Click(object sender, EventArgs e)
    {
        if (this.IsValid)
        {
            person p = new person();
            p.pID = TextBoxNum.Text;
            p.pName = TextBoxName.Text;
            if (RadioButtonMale.Checked)
            {
                p.pSex = "男";
            }
            else
            {
                p.pSex = "女";
            }
            if (personOperate.insertOperator(p))
            {
                Response.Write("插入成功");
                this.fillDg();
            }
            else
            {
                Response.Write("插入失败");
            }

        }
    }
    //update按纽出发修改事件
    protected void ButtonUpdate_Click(object sender, EventArgs e)
    {
        //如果验证空间的值为假，就表示用户存在，就可以修改
        if (!this.CustomValidator1.IsValid)
        {
            person p = new person();
            p.pID = TextBoxNum.Text;
            p.pName = TextBoxName.Text;
            if (RadioButtonMale.Checked)
            {
                p.pSex = "男";
            }
            else
            {
                p.pSex = "女";
            }
            if (personOperate.updateOperator(p))
            {
                Response.Write("修改成功");
                this.fillDg();
            }
            else
            {
                Response.Write("修改失败");
            }

        }
    }
    //delete按纽出发删除事件
    protected void ButtonDelete_Click(object sender, EventArgs e)
    {
        string id = TextBoxNum.Text;
        if (personOperate.deleteOperator(id))
        {
            Response.Write("删除成功");
            this.fillDg();
        }
        else
        {
            Response.Write("删除失败");
        }
    }
    //select按纽触发查询事件
    protected void ButtonSelect_Click(object sender, EventArgs e)
    {
        string condition="";
        if (this.CheckBoxPID.Checked)
        {
                condition = "pID = " + TextBoxNum.Text;

        }
        else
        {
            condition = "pID like '%'";
        }
        if (CheckBoxName.Checked)
        {
            condition += " and personName like '%"+TextBoxName.Text+"%'";
        }
        if (CheckBoxSex.Checked)
        {
            if (RadioButtonMale.Checked)
            {
                condition += "and personSex = '男'";
            }
            else
            {
                condition += "and personSex = '女'";
            }
        }
        //DataView是作为一个视图查询,过滤表生成视图
        //DataView必须基于datatable建立,而personOperate.selectAllPerson()的返回值就是一个datatable
        DataView dv = new DataView(personOperate.selectAllPerson());
        //设置过滤器,设置过滤条件
        dv.RowFilter = condition;
        // 设置排序条件
        dv.Sort = "pID Desc";
        DataGridShow.DataSource = dv;
       DataGridShow.DataBind();
    }
}