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.
3
-
6You should look into table valued parameters.Martin Smith– Martin Smith2012-09-14 14:13:20 +00:00Commented Sep 14, 2012 at 14:13
-
Smith, how the attributes in list can be assigned as parameter?Praveen– Praveen2012-09-14 14:21:24 +00:00Commented Sep 14, 2012 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-parameteruser1537319– user15373192012-09-14 14:27:11 +00:00Commented Sep 14, 2012 at 14:27
Add a comment
|
1 Answer
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.
8 Comments
Aaron Bertrand
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. :-)
Tim Schmelter
@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: sommarskog.se/arrays-in-sql-2008.html
Praveen
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?
Aaron Bertrand
@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.
Praveen
I missed to pick this answer sorry for being too late :( Thank you :)
|