使用command对象操作数据库

1.Command对象查询数据库

 protected void Button1_Click(object sender, EventArgs e)
    {
        //读取web.config节点配置
        string strcon = ConfigurationManager.ConnectionStrings["testjm"].ConnectionString;
        //实例化sqlConnection对象
        SqlConnection con = new SqlConnection(strcon);
            //数据库建立连接打开
            con.Open();
           string strsql = "select * from userinfo where name=@name";//查询语句
           SqlCommand mycmd = new SqlCommand(strsql, con);
           mycmd.Parameters.Add("@name", SqlDbType.VarChar,20).Value = TextBox1.Text.Trim();
           SqlDataAdapter myda = new SqlDataAdapter(mycmd);//实例化SqlDataAdapter，把strsql查询语句通过con传递给数据库
           DataSet myds = new DataSet();//实例化DataSet为myds
            myda.Fill(myds, "userinfo");//填充数据集
            GridView1.DataSource = myds;//界面上显示返回的数据集，指定数据源
            GridView1.DataBind();//绑定数据库



            myda.Dispose();
            myds.Dispose();

            con.Close();//关闭连接
    }

2.Command对象添加数据

/// <summary>
    /// 封装查询userinfo表信息
    /// </summary>
    protected void bind()
    {

        SqlConnection con = getcon();
        //数据库建立连接打开
        con.Open();
        string strsql = "select * from userinfo";//查询语句
        SqlDataAdapter myda = new SqlDataAdapter(strsql,con);
        DataSet myds = new DataSet();
        myda.Fill(myds);
        GridView1.DataSource = myds;//界面上显示返回的数据集，指定数据源
        GridView1.DataKeyNames = new string[] { "id" };
        GridView1.DataBind();//绑定数据库
        myda.Dispose();
        myds.Dispose();
        con.Close();

    }

    /// <summary>
    /// 封装数据库连接
    /// </summary>
    /// <returns></returns>
    protected SqlConnection getcon()
    {
        //读取web.config节点配置
        string strcon = ConfigurationManager.ConnectionStrings["testjm"].ConnectionString;
        //实例化sqlConnection对象
        SqlConnection con1 = new SqlConnection(strcon);
        return con1;
    }

/// <summary>
    /// 添加数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btSumbit_Click(object sender, EventArgs e)
    {
        SqlConnection con = getcon();
        //数据库建立连接打开
        con.Open();
        string strinsert = "insert into userinfo(id,name,password,age) values(" + this.tbid.Text.Trim() + ",'" + this.tbname.Text.Trim() + "','" + this.tbpwd.Text.Trim() + "'," + this.tbage.Text.Trim() + ")";
        SqlCommand mycmd = new SqlCommand(strinsert, con);
        mycmd.ExecuteNonQuery();
        mycmd.Dispose();
        con.Close();//关闭连接
        this.bind();
    }
    /// <summary>
    /// 添加数据中的重置
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btReset_Click(object sender, EventArgs e)
    {
        tbid.Text = "";
        tbname.Text = "";
        tbpwd.Text = "";
        tbage.Text = "";
    }

 3.Command对象修改数据

 /// <summary>
    /// 单击编辑按钮，会触发RowEditing事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        this.bind();
    }
    /// <summary>
    /// 更新数据，RowUpdating更新前的事件，RowUpdated更新后的事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int cid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
        string cName = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text.ToString();
        string cPwd = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString();
        string strupdate = "update userinfo set name='" + cName + "',password='"+ cPwd +"'where id=" + cid;
        SqlConnection con = getcon();
        con.Open();
        SqlCommand mycmd = new SqlCommand(strupdate, con);
        mycmd.ExecuteNonQuery();
        mycmd.Dispose();
        con.Close();//关闭连接
        this.bind();
    }
    /// <summary>
    /// 单击更新中的取消按钮，触发RowCancelingEdit事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        this.bind();
    }

 4.Command对象删除数据

 /// <summary>
    /// 删除数据
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int cid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
        string strdelete = "delete from userinfo where id=" + cid;
        SqlConnection con = getcon();
        con.Open();
        SqlCommand mycmd = new SqlCommand(strdelete,con);
        mycmd.ExecuteNonQuery();
        mycmd.Dispose();
        con.Close();
        this.bind();
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            ((LinkButton)e.Row.Cells[0].Controls[0]).Attributes.Add("onclick", "return confirm('确定要删除这天数据吗 ？')");
        }
    }