ADO.NET无连接模式的详细介绍(2)

/// <summary>
      /// 根据ID更新姓名和年龄
      /// </summary>
      /// <param>姓名</param>
      /// <param>年龄</param>
      /// <param>学生ID</param>
      /// <returns>返回更新后的DataSet</returns>
      public DataSet UpdateStudent(Student stu,int id)
      {
          DataSet ds = GetUserInfor();
          string str = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
          string sql = "UPDATE student SET name=@name,age=@age WHERE id=@id";
          var conn = new SqlConnection(str);
          var cmd = new SqlCommand(sql, conn);
          var sda = new SqlDataAdapter();
          SqlParameter param1 = new SqlParameter()
          {
              ParameterName="@name",SourceColumn="name"
          };
          SqlParameter param2 = new SqlParameter()
          {
              ParameterName = "@age",
              SourceColumn = "age",
              SqlDbType=SqlDbType.Int
          };
          SqlParameter param3 = new SqlParameter()
          {
              ParameterName = "@id",
              SourceColumn = "id"
          };
          SqlParameter[] param = new SqlParameter[] {param1,param2,param3 };
          cmd.Parameters.AddRange(param);
          sda.UpdateCommand = cmd;
          DataTable dt = ds.Tables["student"];
          foreach (DataRow dr in dt.Rows)
          {
              int oldID=Convert.ToInt32(dr["id"]);
              if (oldID == id)
              {
                  dr["name"] = stu.name;
                  dr["age"] = stu.age;
              }
          }
          sda.Update(ds,"student");
          return ds;
      }

删除数据

复制代码 代码如下:

/// <summary>
      /// 根据ID删除一个学生
      /// </summary>
      /// <param>返回更新后的DataSet</param>
      public DataSet DeleteStudent(int id)
      {
          DataSet ds = GetUserInfor();
          string str = "Data Source=.;Initial Catalog=Student;Integrated Security=True";
          string sql = "DELETE FROM student WHERE id=@id";
          var conn = new SqlConnection(str);
          var cmd = new SqlCommand(sql, conn);
          var sda = new SqlDataAdapter();
          SqlParameter param = new SqlParameter()
          {
              ParameterName="@id",SourceColumn="id",SqlDbType=SqlDbType.Int
          };
          cmd.Parameters.Add(param);
          sda.DeleteCommand = cmd;
          DataTable dt=ds.Tables["student"];
          foreach (DataRow dr in dt.Rows)
          {
              int oldId = Convert.ToInt32(dr["id"]);
              if (oldId == id)
                  dr.Delete();
          }
          sda.Update(ds,"student");
          return ds;
      }