Post
Ask Question

Save Array Or List Of Records As XML Data To SQL Server In One Call

In this blog, we will discuss the way to submit the data of a model list to SQL Server to save all records of the list at once. We can do this by converting the model list to XML and then that XML can be submitted to SQL Server through Store Procedure. We can read the XML in SQL Server to use the data for SQL queries. I hope this will be helpful in the situations where we need to save multiple records of same entity or model by looping the list.
 
Requirement 
 
There might be a situation where you have a model named "Student" and you want to save a list of students. Generally, in Entity Framework, we have to iterate the list using for loop and have to save each record one by one. It takes a lot of time to save a large number of records. Even if we have any stored procedure to save single record and use that to save a list by looping, it takes more time. In this case too, the application has to make a connection call each time. 
 
Advantages of using XML post instead of looping
  • Single call to SQL server to save the data
  • Performance improvement as there is no need to make multiple calls to SQL
  • Adding/Removing any field in XML does not require many changes. If you add any property/field in the model list, there is no need to change input paramter of SQL Stored Procedure.
Example
 
Suppose, we have a model named "Student".
  1. public class Student    
  2. {    
  3.    public int StudentId { get; set; }    
  4.    public int RoleNumber { get; set; }    
  5.    public string Name { get; set; }    
  6. }    
 You can convert your model list into XML like this.
  1. List<Student> StudentList= new List<Student>();  
  2.             StudentList.Add(new Student{StudentId=101,RoleNumber=185,Name="Test1" });  
  3.             StudentList.Add(new Student{StudentId=102,RoleNumber=186,Name="Test2" });  
  4.             foreach(var a in StudentList)  
  5.             {  
  6.                 Console.WriteLine(a.Name);  
  7.             }  
  8.             string xml = null;  
  9.                 using (StringWriter sw = new StringWriter())  
  10.                 {  
  11.                     XmlSerializer xs = new XmlSerializer(typeof(List<Student>));  
  12.                       
  13.                     xs.Serialize(sw, StudentList);  
  14.                       
  15.                     try  
  16.                     {  
  17.                         xml = sw.ToString();  
  18.                           
  19.                     }  
  20.                     catch (Exception e)  
  21.                     {  
  22.                         throw e;  
  23.                     }  
  24.                 }  
You will have to pass this XML as a string parameter to SQL Server. Suppose the name of the parameter used to pass XML is @data, here is SQL Stored Procedure to save/update the records passed via XML.
  1.      
  2. CREATE Procedure SaveStudentData      
  3. (      
  4.    @data nvarchar(max)      
  5. )                      
  6. AS                      
  7. Begin   
  8. DECLARE @xmldata XML=CAST(@data AS XML);   
  9. BEGIN TRANSACTION;              
  10.   BEGIN TRY        
  11. Update S set RoleNumber=x.v.value('RoleNumber[1]','int'), Name= x.v.value('Name[1]','varchar(50)')      
  12. from Student S INNER JOIN @xmldata.nodes('ArrayOfStudent/Student') as x(v)      
  13. on CPD.StudentId=x.v.value('StudentId[1]','int')   
  14. END TRY              
  15. BEGIN CATCH              
  16. IF @@TRANCOUNT > 0                
  17.     ROLLBACK  TRANSACTION;                
  18.              
  19. END CATCH              
  20.  IF @@TRANCOUNT > 0                
  21.     COMMIT TRANSACTION;                
  22.                     
  23. END       
  24.     
 This way, you can insert/update/delete the bulk data according to the requirement in just one SQL call.