Monday, August 4, 2014

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
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))

                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;
On doing the list parsing the resulting XML will look like this- Looking at the generated XML and the stored procedure the code is self explanatory.

No comments:

Post a Comment