How to add SqlParameters in a loop?

Question

I want to remove manual escaping from my code. Instead of this I want to use SqlParameter object.

foreach (ThreadPost post in ThreadPosts)
{
     string newMessage = Clean(post.Message);
     string oldMessage = post.Message;

     // escape ' character for prevent errors in SQL script
     // I want to pass newMessage and oldMessage as an SqlParameters to prevent unexpected changes, but how it could be done based on the code bellow???
     newMessage = newMessage.Replace("'", "''");
     oldMessage = oldMessage.Replace("'", "''");

     cmdText += string.Format("INSERT INTO ThreadCleanup VALUES ({0}, {1}, '{2}', '{3}')",
          post.ID.ToString(),
          "NULL",
          oldMessage,
          newMessage);
     }
}
if (!string.IsNullOrEmpty(cmdText))
{
     using (SqlConnection con = new SqlConnection(CONNSTR))
     {
          con.Open();
          SqlTransaction tran = con.BeginTransaction(IsolationLevel.ReadUncommitted);
          try
          {
              using (SqlCommand cmd = new SqlCommand(cmdText, con, tran))
              {
                 cmd.ExecuteNonQuery(); // updated records
              }
              tran.Commit();
          }
          catch (SqlException ex)
          {
              tran.Rollback();
          }
          con.Close();
     }
}

I want to not doing Replace("'", "''"). Instead of this I want use SqlParameters for adding newMessage and oldMessage to command. — Vytalyi
– Vytalyi, Commented Dec 6, 2012 at 10:47
Yes, I understand what you want. I am asking what did you try. — Oded
– Oded, Commented Dec 6, 2012 at 10:48
I don't know from what to start, because I'm not sure that it could be done in a loop as in example... — Vytalyi
– Vytalyi, Commented Dec 6, 2012 at 10:51
Did you try without a loop first? Do that, then see what can be abstracted out to a loop. — Oded
– Oded, Commented Dec 6, 2012 at 10:53

Sergey Kalinichenko · Accepted Answer · 2012-12-06 11:05:45Z

You can do it roughly the same way that you do now - by formatting an insert in a loop, but instead of using one insert per the new/old pair, use one statement for all pairs. You should use column names explicitly to avoid relying on the column order in the table (a big no-no in production) and to avoid creating the second parameter in a loop, even though you always send a NULL to it.

var cmdText = new StringBuilder("INSERT INTO ThreadCleanup (id,oldMessage,newMessage) VALUES ");
var args = new List<Tuple<long,string,string>>();
foreach (ThreadPost post in ThreadPosts) {
     int cnt = args.Count();
     if (cnt != 0) {
         cmdText.Append(",");
     }
     cmdText.AppendFormat("(@id{0}, @old{0}, @new{0})", cnt);
     args.Add(new Tuple<long,string,string>(post.ID, post.Message, Clean(post.Message)));
}

At this point, you have a SQL string that looks like this:

INSERT INTO ThreadCleanup (id,oldMessage,newMessage) VALUES
(@id0, @old0, @new0), (@id1, @old1, @new1), (@id2, @old2, @new2), ...

You also have a list of Tuple<long,string,string> for each of the parameters, so you can do this:

if (args.Count != 0) {
    using (SqlConnection con = new SqlConnection(CONNSTR)) {
        con.Open();
        SqlTransaction tran = con.BeginTransaction(IsolationLevel.ReadUncommitted);
        try {
            using (SqlCommand cmd = new SqlCommand(cmdText, con, tran)) {
               for (var i = 0 ; i != args.Count ; i++) {
                   cmd.Parameters.AddWithValue("@id"+i, args[i].Item1);
                   cmd.Parameters.AddWithValue("@old"+i, args[i].Item2);
                   cmd.Parameters.AddWithValue("@new"+i, args[i].Item3);
               }
               cmd.ExecuteNonQuery(); // updated records
            }
            tran.Commit();
        } catch (SqlException ex) {
            tran.Rollback();
        }
        con.Close();
    }
}

The advantage of doing it in one go is that you make a single round-trip to your database no matter how many records you must insert. Other than that, the solution follows the same pattern as your current one, so the performance should be comparable.

skjcyber · Accepted Answer · 2012-12-06 11:03:26Z

1

Declare your Insert command outside foreach loop. Then add SQLParameters to the command.

String cmdInsert= "INSERT INTO ThreadCleanup VALUES(@value1,@value2,@value3,@value4)"
        cmdInsert.Parameters.AddWithValue("@value1", System.Data.SqlDbType.VarChar);
        cmdInsert.Parameters.AddWithValue("@value2", System.Data.SqlDbType.VarChar);
        cmdInsert.Parameters.AddWithValue("@value3", System.Data.SqlDbType.VarChar);
        cmdInsert.Parameters.AddWithValue("@value4", System.Data.SqlDbType.VarChar);

Then inside foreach loop you assign the values.

foreach (ThreadPost post in ThreadPosts)
{
 string newMessage = Clean(post.Message);
 string oldMessage = post.Message;

 // escape ' character for prevent errors in SQL script
 // I want to pass newMessage and oldMessage as an SqlParameters to prevent unexpected changes, but how it could be done based on the code bellow???
 newMessage = newMessage.Replace("'", "''");
 oldMessage = oldMessage.Replace("'", "''");

cmdInsert.Parameters["@value1"].Value = post.ID.ToString();
cmdInsert.Parameters["@value2"].Value = DBNull.Value;
cmdInsert.Parameters["@value3"].Value = oldMessage;
cmdInsert.Parameters["@value4"].Value = newMessage;

}

edited Dec 6, 2012 at 11:03

answered Dec 6, 2012 at 10:54

skjcyber

6,00712 gold badges44 silver badges60 bronze badges

2 Comments

Vytalyi Over a year ago

it looks good, and how do you think - will it slow if I'll have 10k records?

skjcyber Over a year ago

This is the way to add value to SQLParameters dynamically. As your values will get changed every time, you can not add those using AddWithValue() . I think taken time will be aprroximately same as the running time of your foreach loop.

shaho · Accepted Answer · 2014-08-14 17:42:02Z

1

type sqlcmd.Parameters.Clear(); before sqlcmd.Parameters.AddWithValue ("@sample",sample);

answered Aug 14, 2014 at 17:42

shaho

111 bronze badge

Collectives™ on Stack Overflow

How to add SqlParameters in a loop?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related