ASP.NET数据库操作

// 公有变量（连接数据库）
  protected SqlConnection con = new SqlConnection();
  protected SqlCommand cmd = new SqlCommand();
  protected SqlDataAdapter datap = new SqlDataAdapter();
  protected DataSet ds = new DataSet();
  protected DataTable dt = new DataTable();

// 查询语句
  private void Button1_Click(object sender, System.EventArgs e)
  {
   con.ConnectionString = "server = (local); integrated security = SSPI; database = ClassSystem;";

   cmd.Connection = con;
   cmd.CommandText = "SELECT * FROM teacher;";

   datap.SelectCommand = cmd;

   ds.Clear();
   datap.Fill(ds,"teacher");

   DataGrid1.DataSource = ds.Tables["teacher"];
   DataGrid1.DataBind();
  }

// 模糊查询
cmd.CommandText += " WHERE CustomerName LIKE '%" + txtSearchCustomerName.Text.Trim() + "%'";

// 插入语句
                // 录入数据库
                con.Open();
                cmd.CommandText = "INSERT INTO chl_manager (username,password) VALUES('" + txtUserName.Text + "', '" + strPW_MD5 + "')";
                cmd.ExecuteNonQuery();
                con.Close();
                Response.Write("<script>alert('添加成功!')</script>");
                Response.Redirect("manager.aspx?action=look");

 

// 更新数据
    protected void GVShowManager_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        // 给输入的密码加密
        string strPW_MD5 = FormsAuthentication.HashPasswordForStoringInConfigFile(((TextBox)(GVShowManager.Rows[e.RowIndex].Cells[1].Controls[0])).Text.ToString().Trim(), "md5").ToLower();

        // 修改数据
        cmd.CommandText = "UPDATE chl_manager SET ";
        cmd.CommandText += "username = '" + ((TextBox)(GVShowManager.Rows[e.RowIndex].Cells[0].Controls[0])).Text.ToString().Trim() + "', ";
        cmd.CommandText += "password = '" + strPW_MD5 + "'";
        cmd.CommandText += "WHERE id = '" + GVShowManager.DataKeys[e.RowIndex].Value.ToString() + "'";
        datap.SelectCommand = cmd;
        DataTable dtTmp = new DataTable();
        datap.Fill(dtTmp);
        GVShowManager.EditIndex = -1;
        ShowManagerInfo();

        Response.Write("<script>alert('管理员信息更新成功！')</script>");
    }

// 删除数据
    protected void GVShowManager_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        cmd.CommandText = "SELECT id FROM chl_manager";
        datap.SelectCommand = cmd;
        DataTable dtTmp = new DataTable();
        datap.Fill(dtTmp);

        if (dtTmp.Rows.Count > 1)
        {
            con.Open();
            cmd.CommandText = "DELETE FROM chl_manager WHERE id = '" + GVShowManager.DataKeys[e.RowIndex].Value.ToString() + "'";
            cmd.ExecuteNonQuery();
            con.Close();
            ShowManagerInfo();
        }
        else
        {
            Response.Write("<script>alert('删除失败，该系统至少要有一个管理员！')</script>");
        }
    }