使用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,).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[].Controls[])).Text.ToString();

        string cPwd = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[].Controls[])).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 = -;

        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[].Controls[]).Attributes.Add("onclick", "return confirm('确定要删除这天数据吗 ？')");

        }

    }