How to upload multiple records in C# and stored procedure without looping

Recently I come across a question regarding how to save multiple records in sql server using C# and without using any loop.

Let’s take a simple case of saving list of persons. Let’s have the following class as person-

public class Person {
        public string PFirstName { get; set; }
        public string PLastName { get; set; }
        public int PAge { get; set; }
    }

Then let’s create a simple data base table called Person as below-

CREATE TABLE [dbo].[Person](
	[FirstName] [varchar](100) NULL,
	[LastName] [varchar](100) NULL,
	[Age] [int] NULL
) ON [PRIMARY] 

Now we can solve this problem by passing XML data as input to a stored procedure and using SQL XML for parsing data and saving to database.

So, let’s first create a list of persons for this example like below-

 List personList =new List(){
                new Person(){ PFirstName="abc", PLastName= "smit", PAge=32},
                new Person(){ PFirstName="bcd", PLastName= "pal", PAge=32}
            };
 

Now let’s parse this list to XML for saving and put it into a string variable-

 string SProc = "dbo.save_person_bulk";
            string ConnectonString = @"Data Source=(local);Initial Catalog=sample;Integrated Security=True;";
            using (SqlConnection sqlConnection = new SqlConnection(ConnectonString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(SProc, sqlConnection))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@person_data", SqlDbType.VarChar)
                          {
                              Value = xmlPersonData
                          });
                    using (DataTable dataTable = new DataTable())
                    {
                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
                        {
                            sqlDataAdapter.SelectCommand = sqlCommand;
                            sqlDataAdapter.Fill(dataTable);
                        }
                    }
                }
            }

On doing the list parsing the resulting XML will look like this-

 CREATE PROCEDURE [dbo].[save_person_bulk]   
 @person_data xml  
AS  
BEGIN  
 SET NOCOUNT ON;  
 
 INSERT INTO [dbo].[person]  (FirstName, LastName, Age)    
           select  
           e.value('(PFirstName/text())[1]', 'varchar(100)')   
           ,e.value('(PLastName/text())[1]', 'varchar(100)')   
           ,e.value('(PAge/text())[1]', 'int')   
      FROM  
            @person_data.nodes('//ArrayOfPerson/Person') pdata(e)  
END

Looking at the generated XML and the stored procedure the code is self explanatory.