Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can it possible to perform multiple Update with XML format query Expand / Collapse
Author
Message
Posted Wednesday, October 13, 2010 3:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 15, 2010 7:59 AM
Points: 1, Visits: 4
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
Post #1003446
Posted Friday, October 15, 2010 12:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 04, 2011 6:16 AM
Points: 15, Visits: 48
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.
Post #1004927
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse