ASP.Net 新手学习笔记一 数据库操作(SQL Server)

[Web.config]

<configuration>
  <appSettings>

    <add key="ConnectionString" value="uid=sa;password=;database=tourism;server=(local)"/>

  </appSettings>
  ............
</configuration>


[PAGD]

  using System.Data.SqlClient;
  using System.Configuration;

（Select 查询数据）

private void BindGrid()
  {
   String dsn = ConfigurationSettings.AppSettings["ConnectionString"];    
   SqlConnection Myconnection = new SqlConnection(dsn);

   String SSql ="select I_UserID,C_User,C_Password from T_User";
   DataSet ds  = new DataSet();
   SqlDataAdapter mydatacommand = new SqlDataAdapter (SSql,Myconnection);

   mydatacommand.Fill(ds,"T_User"); 

   grdCourse.DataSource = ds.Tables["T_User"].DefaultView;

   grdCourse.DataBind();

  }

  private void InitializeComponent()
  {   
   this.btnuser.Click += new System.EventHandler(this.btnuser_Click);
   this.grdCourse.PageIndexChanged += new System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.grdCourse_PageIndexChanged); 
   this.Load += new System.EventHandler(this.Page_Load);

  }

  private void grdCourse_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
  {
   BindGrid();
   grdCourse.CurrentPageIndex = e.NewPageIndex;
   grdCourse.DataBind();
  }

（ADD 增加数据）
  private void BtnAdd_Click(object sender, System.EventArgs e)
  {
   String dsn = ConfigurationSettings.AppSettings["ConnectionString"];
      
   SqlConnection Myconnection = new SqlConnection(dsn);                        
           
   SqlCommand  MyCommand;                            
   
   string InsertCmd = "insert into T_User(c_user,c_password) values(@user,@password)";

   MyCommand = new SqlCommand(InsertCmd,Myconnection);

   MyCommand.Parameters.Add(new SqlParameter("@user", SqlDbType.NVarChar, 50));                    
   MyCommand.Parameters["@user"].Value = TxtUser.Text;      
   MyCommand.Parameters.Add(new SqlParameter("@password", SqlDbType.NVarChar, 50));
   MyCommand.Parameters["@password"].Value = TxtPassword.Text;                 
   MyCommand.Connection.Open();    
   try
   {

    MyCommand.ExecuteNonQuery();          

    lblMessage.Text ="增加成功！";

    Response.Redirect("User.aspx");

   }

   catch

   {
    lblMessage.Visible = true;
    lblMessage.Text="增加失败！";

   }

   finally

   {
    MyCommand.Connection.Close();
   }
  }

（EDIT 修改数据）
  private void BtnEdit_Click(object sender, System.EventArgs e)
  {
   String dsn = ConfigurationSettings.AppSettings["ConnectionString"];     
   SqlConnection Myconnection = new SqlConnection(dsn);                                    
   SqlCommand  MyCommand;                                
   string UpdateCmd = "UPDATE T_User SET c_user=@user,c_password=@password where I_UserID="+Request["id"];

   MyCommand = new SqlCommand(UpdateCmd,Myconnection);

   MyCommand.Parameters.Add(new SqlParameter("@user", SqlDbType.NVarChar, 50));                    
   MyCommand.Parameters["@user"].Value = TxtUser.Text;            
   MyCommand.Parameters.Add(new SqlParameter("@password", SqlDbType.NVarChar, 50));
   MyCommand.Parameters["@password"].Value = TxtPassword.Text;                     
   MyCommand.Connection.Open();      
   try
   {

    MyCommand.ExecuteNonQuery();

    lblMessage.Text ="修改成功！";

    Response.Redirect("User.aspx");

   }

   catch

   {
    lblMessage.Text="修改失败！";

   }

   finally

   {
    MyCommand.Connection.Close();
   }
  }

（DEL 删除数据）

  private void BtnDel_Click(object sender, System.EventArgs e)
  {
   String dsn = ConfigurationSettings.AppSettings["ConnectionString"];     
   SqlConnection Myconnection = new SqlConnection(dsn);                                    
   SqlCommand  MyCommand;                               
   string DeleteCmd = "delete from T_User where I_UserID="+Request["id"];

   MyCommand = new SqlCommand(DeleteCmd,Myconnection);              
   MyCommand.Connection.Open();     
   try
   {

    MyCommand.ExecuteNonQuery();

    lblMessage.Text ="删除成功！";

    Response.Redirect("User.aspx");

   }

   catch

   {
    lblMessage.Text="删除失败！";

   }

   finally

   {
    MyCommand.Connection.Close();
   }  
  }