Ado.net批量添加和更新数据简单示例

 

代码

SqlConnection conn  =   new  SqlConnection( " server=xk\\xk;uid=sa;pwd=112233;database=test " );

    SqlDataAdapter da  =   new  SqlDataAdapter();

    SqlCommand cmd;

     protected   void  Page_Load( object  sender, EventArgs e)
    {
         if  ( ! IsPostBack)
        {
            Get_Insert_Update_Info();
        }
    }

     private   void  Get_Insert_Update_Info()
    {
        cmd  =   new  SqlCommand( " select * from student " , conn);
        DataSet ds  =   new  DataSet();
        da.SelectCommand  =  cmd;
        da.Fill(ds,  " Table " );
         for  ( int  i  =   0 ; i  <  ds.Tables[ " Table " ].Rows.Count; i ++ )
        {
            Response.Write(ds.Tables[ " Table " ].Rows[i][ " name " ]  +   " <br/> " );
        }
        ViewState[ " ds " ]  =  ds;
    }

     // 批量更新数据
     protected   void  Button1_Click( object  sender, EventArgs e)
    {
        DataSet ds  =  ViewState[ " ds " ]  as  DataSet;
         for  ( int  i  =   0 ; i  <  ds.Tables[ " Table " ].Rows.Count; i ++ )
        {
            ds.Tables[ " Table " ].Rows[i].BeginEdit();
            ds.Tables[ " Table " ].Rows[i][ " name " ]  =   " ***************** " ;
            ds.Tables[ " Table " ].Rows[i].EndEdit();
        }

        String UpdateSQL  =   " Update student set name=@name where Id=@id " ;
        cmd  =   new  SqlCommand(UpdateSQL, conn);
        cmd.Parameters.Add( " @name " , SqlDbType.VarChar,  50 ,  " name " );
        cmd.Parameters.Add( " @id " , SqlDbType.Int,  4 ,  " id " );
        da.UpdateCommand  =  cmd;
        da.Update(ds,  " Table " );
        ds.AcceptChanges();
    }

     // 批量插入数据
     protected   void  Button2_Click( object  sender, EventArgs e)
    {
        DataTable dt  =   new  DataTable();
        DataRow dr;
        dt.Columns.Add( new  DataColumn( " name " ));
         for  ( int  i  =   0 ; i  <   3 ; i ++ )
        {
            dr  =  dt.NewRow();
            dr[ 0 ]  =   " name "   +  i.ToString();
            dt.Rows.Add(dr);
        }

        System.Text.StringBuilder sb  =   new  System.Text.StringBuilder( "" );
        sb.Append( " INSERT student(name) VALUES( " );
        sb.Append( " @name) " );
        da.InsertCommand  =   new  SqlCommand();
        da.InsertCommand.CommandText  =  sb.ToString();
        da.InsertCommand.Connection  =  conn;
        SqlParameter sp  =   new  SqlParameter( " @name " , SqlDbType.VarChar,  50 );
        sp.SourceVersion  =  DataRowVersion.Current;
        sp.SourceColumn  =   " name " ;  //  or sp.SourceColumn = Dt.Columns[0].ColumnName;
        da.InsertCommand.Parameters.Add(sp);
        da.Update(dt);
    }