Add multiple SQL values with same parameterized query?

Question

I'm fairly new to SQL and trying to figure out the best way to add some predefined data. I figured out from searching around here that I should used a parameterized command to avoid a sql injection attack which isn't a huge concern in this case but I would like to avoid the possibility and learn to do it right... Anyway here is the code I have right now:

        using (SqlTransaction trans = connection.BeginTransaction())
        {
            foreach (IEnumerable<string> row in table.RowData)
            {
                using (SqlCommand sql = new SqlCommand("INSERT INTO " + table.Title
                    + " (" + string.Join(", ", table.Headers)
                    + ") VALUES (" + string.Join(", ", table.Headers.Select(x => "@" + x)) + ");", connection, trans))
                {

                    for (int i = 0; i < table.Headers.Count(); i++)
                    {
                        if (string.IsNullOrEmpty(row.ElementAt(i)))
                        { sql.Parameters.AddWithValue("@" + table.Headers.ElementAt(i), DBNull.Value); }
                        else
                        { sql.Parameters.AddWithValue("@" + table.Headers.ElementAt(i), row.ElementAt(i)); }
                    }
                    sql.ExecuteNonQuery();
                }
            }
            trans.Commit();
        }

This seems to work and all the data gets in there but it 'feels' inefficient to me. I'm wrapping it in a transaction so there is only one commit, but it's creating the parameters every time and just setting different values for each row.

Is there a way to make this use the same parameters but just set different values per row? Or is this the best way to do this and I should not worry about it?

Thanks in advance for any help you can give.

Objects in c# are cheap. Could it be more efficient? Possibly. But you aren't going to gain much of anything by reusing a parameter, and you might even introduce some subtle bugs by doing so. I advise against it. — user47589
– user47589, Commented Oct 28, 2016 at 18:16
With some more context we could help better, because we don't know whether this is supposed to be a reusable wrapper around the SQL APIs, a specific query with lots of insert statements and values, or what - the answer depends. — Yushatak
– Yushatak, Commented Oct 28, 2016 at 18:18
Sorry to have to tell you this, but this is open to injection attack -- you are concatenating variable strings (table.Title) into your query. — Hogan
– Hogan, Commented Oct 28, 2016 at 18:18
Also, I don't see any point in wrapping in a transaction if you have no code that does a rollback. Just more likely to have dead locks if you do it like this. — Hogan
– Hogan, Commented Oct 28, 2016 at 18:22
No, you can't use parameters for table and column names, only values. — user47589
– user47589, Commented Oct 28, 2016 at 18:28

Kittoes0124 · Accepted Answer · 2016-10-28 22:01:50Z

2

We can do what you want by parsing the headers into parameters in a pre-processing step. I have also removed the explicit transaction because every single insert already gets an implicit transaction by default (why pay the performance penalty of two transactions?).

using (var command = new SqlCommand()) {
    command.CommandText =
        "INSERT INTO " + table.Title + " ("
      + string.Join(", ", table.Headers)
      + ") VALUES ("
      + string.Join(", ", table.Headers.Select(x => "@" + x))
      + ");";
    command.Connection = connection;

    foreach (var header in table.Headers) {
        /*
             Add all parameters as strings. One could choose to infer the
             data types by inspecting the first N rows or by using some sort
             of specification to map the types from A to B.
         */
        command.Parameters.Add("@" + header, typeof(string));
    }

    foreach (var row in table.RowData) {
        for (var i = 0; i < table.Headers.Count(); i++) {
            if (!string.IsNullOrEmpty(row.ElementAt(i))) {
                command.Parameters["@" + table.Headers.ElementAt(i)].Value = row.ElementAt(i);
            }
            else {
                command.Parameters["@" + table.Headers.ElementAt(i)].Value = DBNull.Value;
            }
        }

        command.ExecuteNonQuery();
    }
}

edited Oct 28, 2016 at 22:01

answered Oct 28, 2016 at 18:41

Kittoes0124

5,1024 gold badges30 silver badges48 bronze badges

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

6 Comments

sfaust Over a year ago

Ok I get that, except that command.Parameters.Add() requires a second parameter of the data type... Not too hard to do but it's nice that AddWithValue seems to automatically recognize the data type...

Kittoes0124 Over a year ago

@sfaust It does, based on the incoming value. Since we're hoisting the logic up you'll have to determine the type ahead of time. One way is to infer the data type using the first n rows.

sfaust Over a year ago

Yes not too hard to do, just wanted to point it out mainly for anyone else that comes along with this question. I will modify a bit to get the data types but thanks, that's what I was looking for!

sfaust Over a year ago

Also, it should be command.Parameters[...].Value = ... You don't have value in there. Can I request you update the answer with that and the data type so it's not confusing to other newbies? :)

Kittoes0124 Over a year ago

@sfaust Of course. My apologies, I was writing this without testing it.

|

y.m · Accepted Answer · 2016-10-28 18:45:53Z

0

this is my example of insert that works for me

  private void insertWordCount(string songId, string wordId, int wordCount)
    {
        string query = "insert into songs_words_conn values(@wordId,@songId,@wordCount)";
        SqlCommand cmd = new SqlCommand(query, conn);

        cmd.Parameters.AddWithValue("@wordId", wordId);
        cmd.Parameters.AddWithValue("@songId", songId);
        cmd.Parameters.AddWithValue("@wordCount", wordCount);

        cmd.ExecuteNonQuery();
    }

answered Oct 28, 2016 at 18:45

y.m

491 silver badge6 bronze badges

1 Comment

sfaust Over a year ago

That does work and is essentially what I'm doing. However in order to add multiple values to the table it would have to loop which creates the parameters multiple times which is what I was trying to avoid...

jhilden · Accepted Answer · 2016-10-28 18:50:07Z

0

Yes, you can be much more effecient by reusing SqlParameter objects. Here is some pseudo code:

        const string sql = "INSERT INTO table1 (column1) VALUES (@p0)";
        using (var sqlCommand = new SqlCommand(sql, connection, transaction))
        {
            var param1 = sqlCommand.Parameters.Add("@p0", SqlDbType.Int);
            foreach (var row in table)
            {
                param1.Value = row["value"];
                sqlCommand.ExecuteNonQuery();
            }
        }

edited Oct 28, 2016 at 18:50

answered Oct 28, 2016 at 18:35

jhilden

12.5k5 gold badges56 silver badges77 bronze badges

3 Comments

Hogan Over a year ago

This is doing something different. This is only adding one column per row. How exactly is this an answer?

jhilden Over a year ago

This shows you HOW to reuse SQL Parameters in a foreach, which was the gist of the question. Using a little critical thinking will get the user to the complete answer.

sfaust Over a year ago

Not horrible at all, @jhilden is constructing the parameter outside the foreach loop where I'm constructing it inside the loop. The original code is creating new ones every time, this code is not. How is that horrible?

Collectives™ on Stack Overflow

Add multiple SQL values with same parameterized query?

3 Answers 3

6 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related