CODEWITHSUNDEEP
asp.netlistcollections
Pawan Pillai
Pawan Pillai

Reputation: 2065

ASP.NET - How to save a List Collection to DB using Stored Procedure?

I have a List Collection:

 List<Person> people = new List<Person>();

    Person p1 = new Person();

    p1.FirstName = "Nikos";
    p1.LastName = "Kantzelis";
    p1.Email = "[email protected]";
    p1.Height = 1.78m;
    p1.Weight = 88.5m;


    Person p2 = new Person();

    p2.FirstName = "James";
    p2.LastName = "Rowling";
    p2.Email = "[email protected]";
    p2.Height = 1.98m;
    p2.Weight = 98.25m;

    Person p3 = new Person();

    p3.FirstName = "George";
    p3.LastName = "Graham";
    p3.Email = "[email protected]";
    p3.Height = 1.88m;
    p3.Weight = 81.5m;

    people.Add(p1);
    people.Add(p2);
    people.Add(p3);

Now I want to save the values inside this List Collection to some of my tables in DB using a stored procedure.

Please advise.

Regards, Pawan

Upvotes: 0

Views: 1247

Answers (2)

slolife
slolife

Reputation: 19870

If your DB is SQL 2005 (or is it 2008) and above, you could serialize the list of objects to XML, then pass the whole XML blob to the SP in a parameter then use MSSQL's XML query capabilities to parse the XML and insert the data into the table.

Sort of a INSERT INTO Person SELECT * FROM XML

That would allow a single SP call and it could be wrapped in a transaction within the SP (rather than crossing process boundaries).

Upvotes: 0

James Johnson
James Johnson

Reputation: 46067

I'm not sure if you're looking for something fancier, but you can do something like this:

foreach (Person person in people)
{
    //example code
    //save each person in the list to the database
    SavePerson(person.Name, person.Age, ...); 
}

If you'd rather, you can also do it with LINQ like this:

//example code
//save each person in the list to the database
people.ForEach(person => SavePerson(person.Name, person.Age, ...));

Upvotes: 2

Related Questions