Can it possible to perform multiple Update with XML format query

• Nitesh katare

  SSC Enthusiast

  Points: 123

  More actions

  October 13, 2010 at 3:49 am

  #230338

  I got an Idea how to Send multiple rows to the Database from an Application

  and from the below code we can even insert it to the table as u showed in the Article

  like,

  consider a table Emp

  INSERT INTO Emp (Name, Salary)

  SELECT Name, Salary FROM OPENXML (@idoc, '/ROWS/ROW', 1)

  WITH (Name varchar(50), Salary int)

  WHERE Salary >= 1500

  ORDER BY Name

  My Doubt is about how to Update multiple rows in Emp table using the below xml query

  <ROWS>

  <ROW>

  <ID>1</ID>

  <Name>Tom</Name>

  <Salary>21000</Salary>

  </ROW>

  <ROW>

  <ID>2</ID>

  <Name>Nitesh</Name>

  <Salary>18000</Salary>

  </ROW>

  <ROW>

  <ID>3</ID>

  <Name>Mat</Name>

  <Salary>15000</Salary>

  </ROW>

  </ROWS>

  Please help me out for updating the table with above xml Query

  Thank and Regards

  Nitesh katare

• crackbridge

  SSC Enthusiast

  Points: 185

  More actions

  October 15, 2010 at 12:49 am

  #1237319

  Actually this is an interesting topic, but I would advice using SqlBulkCopy instead, which provide amazing performance, with just a few lines of code:

  public virtual bool SqlBulkCopy(DataSet ds, string TableName) // provide ability to update any Table in the DS, assume TableName is idem for SQL and App Level

  bool result = true;

  using (SqlBulkCopy BulkCopy = new SqlBulkCopy(MyConnectionString))

  {

  BulkCopy.DestinationTableName = TableName;

  try

  {

  BulkCopy.WriteToServer(ds.Tables[TableName]); // Write from the source to the destination.

  }

  catch (Exception e)

  {

  // Provide feedback of error issues, to be recovered using something like "DataSetErrorsShower.Show()"

  ds.Tables[TableName].Rows[0].RowError = ExceptionHandler.WrapSqlException(e);

  result = false;

  }

  if (result == true)

  ds.Tables[TableName].AcceptChanges();

  return result

  }

  Hope it helps, keep on comments.

  Kind Regards,

  Louis.