SqlDataAdapter 更新数据库

  // 示例
  private void btnUpdate_Click(object sender, System.EventArgs e)
  {
   SqlConnection nwindConn = new SqlConnection( "server=(local);uid=sa;pwd=111;database=Northwind" );

   SqlDataAdapter catDA = new SqlDataAdapter("SELECT * FROM Categories", nwindConn);      

   catDA.UpdateCommand = new SqlCommand("UPDATE Categories SET CategoryName = @CategoryName " +
    "WHERE CategoryID = @CategoryID" , nwindConn);

   catDA.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");

   SqlParameter workParm = catDA.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int);
   workParm.SourceColumn = "CategoryID";
   workParm.SourceVersion = DataRowVersion.Original;

   DataSet catDS = new DataSet();
   catDA.Fill(catDS, "Categories");  

   DataRow cRow = catDS.Tables["Categories"].Rows[0];
   cRow["CategoryName"] = "NewName";

   catDA.Update(catDS,"Categories");
   Bind();
  }

  private void btnAdd_Click(object sender, System.EventArgs e)
  {
   SqlConnection nwindConn = new SqlConnection( "server=(local);uid=sa;pwd=111;database=Northwind" );

   SqlDataAdapter catDA = new SqlDataAdapter("SELECT * FROM Categories", nwindConn);      

   catDA.InsertCommand = new SqlCommand("Insert into Categories(CategoryName,Description) values"
    +" (@CategoryName,@Description)", nwindConn);

   catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");
   catDA.InsertCommand.Parameters.Add("@Description", SqlDbType.NText, 16, "Description");

   DataSet catDS = new DataSet();
   catDA.Fill(catDS, "Categories");  

   DataRow dr = catDS.Tables["Categories"].NewRow();
   dr["CategoryName"] = "Added New Name";
   dr["Description"] = "my Description";
   catDS.Tables["Categories"].Rows.Add(dr);

   catDA.Update(catDS,"Categories");
   Bind();
  }       

 //程序中应用
//更新最终得分
        public void UpdateFinalData()
        {
            DataSet ds=new DataSet();
            StringBuilder strSql=new StringBuilder();

            SqlDataAdapter adapter;

            strSql.Append(@"SELECT V.jobgrade,E.hrms_userfield_84,A.em_id,
                                         E.hrms_userfield_40,U.hrms_userfield_70, P.avgx,
                                         case when result-lasttimeresult>1 then lasttimeresult+12
                                            when lasttimeresult-result>2 then lasttimeresult-23
                                             else result end  AS TmpfinalData,U.hrms_userfield_71 AS UfinalData
                            FROM hr_ms_evaluateaddinfo A
                                inner join
                                    v_report_workinfo V                                        
                                    on A.em_id=V.em_id 
                                inner join
                                    hm_employeesaddinfo E                                         
                                    on e.em_id=A.em_id
                                inner join 
                                    hrms_usertable_13 U
                                    on U.em_id=e.em_id 
                                inner join
                                    hr_ms_formulaPara P                                             
                                    on p.joblevel=A.joblevel and A.dep_group=P.deptype                                                                                   
                                WHERE u.hrms_userfield_67='"+ParaBase.CurrentDate+ "'");

            adapter = new SqlDataAdapter(strSql.ToString(),DbUtils.GetConnection());

            adapter.UpdateCommand = new SqlCommand("UPDATE hrms_usertable_13 SET hrms_userfield_71=@finalData WHERE em_id=@em_id", DbUtils.GetConnection());

            adapter.UpdateCommand.Parameters.Add("@finalData", SqlDbType.Decimal, 6, "TmpfinalData");

            SqlParameter workParm = adapter.UpdateCommand.Parameters.Add("@em_id", SqlDbType.UniqueIdentifier);
            workParm.SourceColumn = "em_id";

            workParm.SourceVersion = DataRowVersion.Original;       //原始数据没有改变时可以更新，否则报错

            adapter.Fill(ds, "evaluateaddinfo");

            int i=0;
            DataRow cRow;

            //把查询临时字段值 更新到数据库的字段中去
            //DataRow cRow1 只起到一个循环作用
            //也可以用for 循环 代替foreach ds.Tables[0].rows.count
            foreach(DataRow cRow1 in ds.Tables["evaluateaddinfo"].Rows)
            {
                cRow = ds.Tables["evaluateaddinfo"].Rows[i];
                cRow["TmpfinalResult"] = ds.Tables["evaluateaddinfo"].Rows[i].ItemArray[6];   //给 updatecommand传递实参
                i++;
            }

            adapter.Update(ds, "evaluateaddinfo");

 

        }