-5

I have created a list in c#, now I need to insert the list into SQL Server 2008. Is this possible? please explain with a simple example.

Praveen
  • 55,303
  • 33
  • 133
  • 164
  • 6
    You should look into [table valued parameters](http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters). – Martin Smith Sep 14 '12 at 14:13
  • Smith, how the attributes in list can be assigned as parameter? – Praveen Sep 14 '12 at 14:21
  • The following link helps you to provide a solution for your problem. [http://stackoverflow.com/questions/10757818/c-sharp-insert-into-sql-table-with-list-as-parameter](http://stackoverflow.com/questions/10757818/c-sharp-insert-into-sql-table-with-list-as-parameter) –  Sep 14 '12 at 14:27

1 Answers1

6

Here's a simple example:

List<String> list = new List<String>() { "A", "B", "C" };
using (var con = new SqlConnection(connectionString))
{
    con.Open();
    using (var cmd = new SqlCommand("INSERT INTO TABLE(Column)VALUES(@Column)", con))
    {
        cmd.Parameters.Add("@Column", SqlDbType.VarChar);
        foreach (var value in list)
        {
            cmd.Parameters["@Column"].Value = value;
            int rowsAffected = cmd.ExecuteNonQuery();
        }
    }
}

This just loops through all items in the list and executes one insert-command after the other with ExecuteNonQuery.

Edit: If you want to know the most efficient ways to insert arrays(or lists) into sql-server, you should definitely read this: http://www.sommarskog.se/arrays-in-sql-2008.html

If you have a specific question later, you can come back and show what you've tried.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Well, that's not really an efficient way to insert a list, that's just a way to loop through a list and insert one at a time. A TVP would be a much more attractive option. :-) – Aaron Bertrand Sep 14 '12 at 14:29
  • @AaronBertrand: I wanted to show a simple example since i've assumed that OP just wants to insert his 1-dimensional list(one field). There was no requirement for the most efficient way ;) If he wants to know how to handle lists(arrays) most efficiently he should read this: http://www.sommarskog.se/arrays-in-sql-2008.html – Tim Schmelter Sep 14 '12 at 14:37
  • My database has 30 columns, but what I have in list is only 20 columns and it grows dynamically based upon user. Now, How to do the insert? – Praveen Sep 14 '12 at 14:38
  • @user1671639 you need to start with a tutorial (a search can yield these pretty easily), then come back here with problems when you've written some code and can't quite get it to work. This isn't a "write my code for me" site. – Aaron Bertrand Sep 14 '12 at 14:39
  • I missed to pick this answer sorry for being too late :( Thank you :) – Praveen May 08 '14 at 07:06
  • Note that in order for this to work you might have to move the definition of cmd inside the foreach loop as otherwise c# might complain that the value of @column has already been set. – Thijser Aug 14 '15 at 08:52
  • @Thijser: that's not quite correct, since i'm only setting the `Value` property and not adding the parameter multiple times C# won't complain. It is perfectly valid to overwrite a `Value` of a parameter in a loop. This was tested and i have tested it again to verify that it works as expected. – Tim Schmelter Aug 14 '15 at 09:10
  • Ah yes I see now I was using addwithvalue that won't work. – Thijser Aug 14 '15 at 09:37