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为mydsmyda.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('确定要删除这天数据吗 ？')");}}
