How to insert a list of string into a table of SQL Server database

Question

I have a table of 3 columns along with a primary key:

create table tempTable
(
    id int primary key identity,
    name nvarchar(50),
    gender nvarchar(50),
    city nvarchar(50)
)

And there is a list of string like this:

List<String> list = new List<String>() { "name", "male", "city" };

I want this list into the tempTable. How can I do that?

Your question is both unclear and shows no effort. Is the temp table really a temp table? Are you using raw ADO.Net? What have you tried so far? — David L
– David L, Commented Aug 14, 2016 at 19:26
there are a lot of ways to do it, what have you tired? here is a link that shows you 1 way to insert the data into a table stackoverflow.com/questions/14001040/… with that and a for each loop on your list you can accomplish it. There are enve more ways too. — Matt
– Matt, Commented Aug 14, 2016 at 19:27
the name of the table is tempTable I am using raw ado.net here is my code so far. But I know, it is not making any sense. List<String> list = new List<String>() { "A", "B", "C" }; string qry = "INSERT INTO tempTable VALUES(@Column)"; using (var cmd = new SqlCommand(qry, connection)) { cmd.Parameters.Add("@Column", SqlDbType.VarChar); foreach (var value in list) { cmd.Parameters["@Column"].Value = value; cmd.ExecuteNonQuery(); } } — Safayat Zisan
– Safayat Zisan, Commented Aug 14, 2016 at 19:28
@SafayatZisan - instead of adding it as a comment edit your question and add it there — Gilad Green
– Gilad Green, Commented Aug 14, 2016 at 19:30

Sleepy Panda · Accepted Answer · 2016-08-14 19:51:56Z

1

You should do something like this:

string query="INSERT INTO tempTable(name, gender, city) VALUES (@name, @gender, @city)";
using (var connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand())
    {
        command.Connection = connection;
        command.CommandString = query;
        command.CommandType = CommandType.Text;
        command.Parameters.AddWithValue("@name", list[0]);
        command.Parameters.AddWithValue("@gender", list[1]);
        command.Parameters.AddWithValue("@city", list[2]);
        try
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            // your code...
        }
    }
}

answered Aug 14, 2016 at 19:51

Sleepy Panda

4584 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...

Sleepy Panda Over a year ago

Wow! I never had issues with AddWithValue(). But I think that's very interesting link. Thank you :)

Collectives™ on Stack Overflow

How to insert a list of string into a table of SQL Server database

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related