ASP.NET 增 @ 删 @ 改 @ 查

自己学习asp.net时总结的一些数据库连接最基本的方法。

 

/// <summary> ­

/// Page.Load 加载时 使用!IsPostBack 判断是否回显 ­

/// </summary> ­

  if (!IsPostBack)// !IsPostBack 获取一个值，该值指示该页是否正为响应客户端回发而加载，或者它是否正被首次加载和访问 ­

­

//==================================连接数据库  增删改查================================== ­

­

/// <summary> ­

/// 在 web.config中配置数据库字符串 ­

/// </summary> ­

  <appSettings> ­

   <add key="ConnectionString" value="Data Source=ASUS/SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True"/> ­

  </appSettings> ­

­

­

/// <summary> ­

/// 获取 Web.Config 配置文件中与数据库连接的字符串 ­

/// </summary> ­

  public SqlConnection GetConnection() ­

  { ­

   string sqlconString = ConfigurationManager.AppSettings["ConnectionString"].ToString(); ­

   SqlConnection con = new SqlConnection(sqlconString); ­

   return con; ­

  } ­

­

­

/// <summary> ­

/// 编写 填充方法 ­

/// </summary> ­

  private void fillDataGridView() ­

  { ­

   //将方法赋值给con  等同于SqlConnection con = new SqlConnection(sqlconString); ­

   SqlConnection con = GetConnection(); ­

   con.Open(); ­

   //拼SQL语句 ­

   string sql = "select * from yuanGong"; ­

   //创建 Command ­

   SqlCommand com = new SqlCommand(sql, con); ­

   //创建 DataAdapter ­

   SqlDataAdapter dataAdapter = new SqlDataAdapter(com); ­

   //创建 DataSet ­

   DataSet dataSet = new DataSet(); ­

   //填充 DataSet ­

   dataAdapter.Fill(dataSet); ­

   //填充 GridView 控件 ­

   GridView1.DataSource = dataSet; ­

   //将数据绑定到 GridView 控件 ­

   GridView1.DataBind(); ­

   //释放资源 ­

   dataAdapter.Dispose(); ­

   dataSet.Dispose(); ­

   con.Close(); ­

  } ­

­

­

/// <summary> ­

/// 按照条件查询数据 ­

/// </summary> ­

  //如果文本框不为空(进行查询) 如果文本框为空(填充所有数据) ­

        if(this.TextBox1.Text!="") ­

        { ­

            //创建 Connection 对象 ­

            SqlConnection con = GetConnection(); ­

            con.Open(); ­

            //拼SQL ­

            string sql = "select * from yuanGong where name=@name"; ­

            //创建 Command 对象 ­

            SqlCommand com = new SqlCommand(sql,con); ­

            // ­

            com.Parameters.Add("@name", SqlDbType.VarChar, 20).Value = this.TextBox1.Text.Trim(); ­

            //创建 DataAdapter 对象 ­

            SqlDataAdapter dataAdpater = new SqlDataAdapter(com); ­

            //创建 DataSet 对象 ­

            DataSet dataSet = new DataSet(); ­

            //填充 dataSet ­

            dataAdpater.Fill(dataSet); ­

            //判断是否查到数据 ­

            if (dataSet.Tables[0].Rows.Count > 0) ­

            { ­

                //填充 GridView ­

                GridView1.DataSource = dataSet; ­

                //绑定数据到 GridView ­

                GridView1.DataBind(); ­

            } ­

            else ­

            { ­

                Response.Write("<script>alert('报告冯磊老大，没有相关记录！')</script>"); ­

            } ­

            //释放资源占用 ­

            dataAdpater.Dispose(); ­

            dataSet.Dispose(); ­

            con.Close(); ­

        }else{ ­

            this.fillDataGridView();//调用自定义方法 填充所有数据 ­

        } ­

­

­

/// <summary>添加数据 ­

/// 添加数据 ­

/// </summary> ­

  if(TextBox1.Text.Trim()!="") ­

   { ­

    SqlConnection con = GetConnection(); ­

    string sql = "insert into yuanGong (Name) values ( '"+this.TextBox1.Text.Trim()+" ')";//特别声明：‘this.TextBox1.Text.Trim()’ 应放在单引号中 ­

    SqlCommand com = new SqlCommand(sql,con); ­

    con.Open(); ­

    com.ExecuteNonQuery(); ­

    con.Close(); ­

    this.fillDataGridView(); ­

   }else{ ­

    this.fillDataGridView(); ­

   } ­

­

­

/// <summary>修改数据 ­

/// 编辑 ­

/// </summary> ­

/// <param name="sender"></param> ­

/// <param name="e"></param> ­

  protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) ­

  { ­

   // ­

   GridView1.EditIndex = e.NewEditIndex; ­

   //重新加载 GridView ­

   this.fillDataGridView(); ­

  } ­

/// <summary>更新 按键 GridView RowUpdating 方法 ­

/// 更新 ­

/// </summary> ­

/// <param name="sender"></param> ­

/// <param name="e"></param> ­

  protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) ­

  { ­

   //拿到值 ­

   int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString()); ­

   string name = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text.ToString(); ­

   string age = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString(); ­

   string gongZi = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[5].Controls[0])).Text.ToString(); ­

   string jiGuan = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[7].Controls[0])).Text.ToString(); ­

   //拼SQL ­

   string sql = string.Format("update yuanGong set Name='{0}',Age='{1}',gongZi='{2}',jiGuan='{3}' where ID='{4}'", name,age,gongZi,jiGuan,id); ­

   //创建 connection 对象 ­

   SqlConnection con = GetConnection(); ­

   //打开数据库连接 ­

   con.Open(); ­

   //创建 command 对象 ­

   SqlCommand com = new SqlCommand(sql,con); ­

   //执行SQL ­

   com.ExecuteNonQuery(); ­

   //释放资源 ­

   com.Dispose(); ­

   con.Close(); ­

   // ­

   GridView1.EditIndex = -1; ­

   //重新加载 GridView ­

   this.fillDataGridView(); ­

  } ­

/// <summary>取消按键 GridView 的 RowCancelingEdit 方法 ­

/// 取消 ­

/// </summary> ­

/// <param name="sender"></param> ­

/// <param name="e"></param> ­

  protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) ­

  { ­

   GridView1.EditIndex = -1; ­

   //重新加载 GridView ­

   this.fillDataGridView(); ­

  } ­

­

­

/// <summary>删除数据 ­

/// 删除数据 ­

/// </summary> ­

/// <param name="sender"></param> ­

/// <param name="e"></param> ­

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e) ­

    { ­

        //拿到 ID ­

        int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString()); ­

        //拼SQL ­

        string sqlStr = "delete from yuanGong where ID=" + id; ­

        SqlConnection myConn = GetConnection(); ­

        myConn.Open(); ­

        SqlCommand myCmd = new SqlCommand(sqlStr, myConn); ­

        myCmd.ExecuteNonQuery(); ­

        myCmd.Dispose(); ­

        myConn.Close(); ­

        GridView1.EditIndex = -1; ­

        this.fillDataGridView(); ­

    } ­

/// <summary>弹出窗口询问是否删除 ­

    /// 弹出窗口询问是否删除 ­

/// </summary> ­

/// <param name="sender"></param> ­

/// <param name="e"></param> ­

    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('冯磊老大你确定要删除我吗?')"); ­

        } ­

    } ­