利用ADO.NET简单实现数据库的增删改查

上一篇介绍了ADO.NET里面的主要内容，这次就刚好利用前面的知识来实现对数据库的查询，添加，修改，删除的操作
1.实现查询功能
根据表的主键编号查询

protected void btnsearch_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=DESKTOP-D16EG4G;Initial Catalog=HISDB;Integrated Security=True";
        SqlDataAdapter da = new SqlDataAdapter();
        string sqr="select * from Doctor where dID='"+txtkey.Text+"'";
        da.SelectCommand = new SqlCommand(sqr, conn);
        DataSet ds = new DataSet();//新建一个空的DataSet对象
        da.Fill(ds);//用获得的数据源去填满DataSet对象
        GridView1.DataSource = ds;
        GridView1.DataBind();
        conn.Close();//关闭连接
    }

2.实现添加记录功能

 protected void btnAdd_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=DESKTOP-D16EG4G;Initial Catalog=HISDB;Integrated Security=True";
        SqlDataAdapter da = new SqlDataAdapter();
        string sqr = "select * from Doctor";
        da.SelectCommand = new SqlCommand(sqr, conn);
        SqlCommandBuilder scb = new SqlCommandBuilder(da);//为DataAdapter自动生成更新命令
        DataSet ds = new DataSet();
        da.Fill(ds);
        DataRow NewRow = ds.Tables[0].NewRow();//向DataSet中的第一个表对象中添加一个新行
        NewRow["dID"] = txtid.Text;//添加各个字段
        NewRow["dName"] = txtname.Text;
        NewRow["Title"] = txtname.Text;
        NewRow["Deparmebt"] = txtdeparment.Text;
        NewRow["Assistant"] = txtassistant.Text;
        NewRow["sex"] = txtsex.Text;
        ds.Tables[0].Rows.Add(NewRow);//将新行添加到数据集中
        da.Update(ds);//向数据库提交更新的数据
        conn.Close();
        Response.Write("<script language=javascript>alert('新记录添加成功！');</script>");
        GridView1.DataSource = SqlDataSource1;//刷新页面的数据
        GridView1.DataBind();
    }

3.修改记录

protected void btnEdit_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=DESKTOP-D16EG4G;Initial Catalog=HISDB;Integrated Security=True";
        SqlDataAdapter da = new SqlDataAdapter();
        string sqr = "select * from Doctor where dID='" + txtkey.Text + "'";
        da.SelectCommand = new SqlCommand(sqr, conn);
        SqlCommandBuilder scb = new SqlCommandBuilder(da);
        DataSet ds = new DataSet();
        da.Fill(ds);
        DataRow MyRow = ds.Tables[0].Rows[0];//从DataSet中得到要修改的记录
        MyRow[1] = txtname.Text;//为第二个字段赋值，第一个字段不需要赋值
        MyRow[2] = txttitle.Text;
        MyRow[3] = txtdeparment.Text;
        MyRow[4] = txtassistant.Text;
        MyRow[5] = txtsex.Text;
        da.Update(ds);
        conn.Close();
        GridView1.DataSource = SqlDataSource1;
        GridView1.DataBind();
        
        Response.Write("<script language=javascript>alert('记录修改成功');</script>");
        

4.删除记录

protected void btnDel_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=DESKTOP-D16EG4G;Initial Catalog=HISDB;Integrated Security=True";
        SqlDataAdapter da = new SqlDataAdapter();
        string sqr = "select * from Doctor where dID='" + txtkey.Text + "'";
        da.SelectCommand = new SqlCommand(sqr, conn);
        SqlCommandBuilder scb = new SqlCommandBuilder(da);
        DataSet ds = new DataSet();
        da.Fill(ds);
        DataRow Delrow = ds.Tables[0].Rows[0];//得到要删除的行
        Delrow.Delete();//调用DataRow对象的Delete()方法，从数据表中删除行
        da.Update(ds);
        conn.Close();
        Response.Write("<script language=javascript>alert('记录删除成功');</script>");
        GridView1.DataSource = SqlDataSource1;
        GridView1.DataBind();
    }