Parameter passing Vs Table Valued Parameters Vs XML to SQL 2008 from .Net Application
- by Harryboy
As We are working on a asp .net project there three ways one can update data into database when there are multiple rows updation / insertion required
Let's assume we need to update employee education detail (which could be 1,3,5 or 10 records)
Method to Update Data
Pass value as parameter (Traditional approach), If 10 records are there then 10 round trip required
Pass data as xml and write logic inside your stored procedure to get that data from xml and update the table (only single roundtrip required)
Use Table valued parameters (only single roundtrip required)
Note : Data is available as List, so i need to convert it to xml or any other format if i need to pass.
There are no. of places in entire application we need to update data in bulk (or multiple records)
I just need your suggestions that
Which method will be faster (please mention if there are some other overheads)
Manageability or testability concern with any approach
Any other bottleneck or issue with any of the approach (Serialization /Deserialization concern or limit on size of the data passing)
Any other method you suggest for same operations
Thanks
