c# change parameter for sql in loop

Question

I want to reuse a parameterized query in a loop.

(This query is a simple example, I don't think I could make the loop inside sql and just return the needed rows)

Instead of

private String sql = "SELECT v FROM t WHERE VAL_1 = @param_1";
for (int n=1;n<10;n++)
{
    MySqlCommand m = new MySqlCommand(sql);
    m.Parameters.AddWithValue("@param_1", n);
    res = Convert.ToInt32(m.ExecuteScalar());
    ( ... )
}

I'd like to move the setup of the query outside the loop; something like

private String sql = "SELECT v FROM t WHERE VAL_1 = @param_1";
MySqlCommand m = new MySqlCommand(sql);
m.Parameters.Add("@param_1");  // does not exist
for (int n=1;n<10;n++)
{
    m.Parameters.Set("@param_1", n); // does not exist
    res = Convert.ToInt32(m.ExecuteScalar());
    ( ... )
}

So the server does not have to parse the same sql for each ilteration in loop.

Is that possible?

Have you want to assign parameter values inside the loop? Note that the loop itself may override previous assignments for corresponding parameter, ensure every assignment executed before the loop going to next iteration. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented May 31, 2017 at 12:00
do you mean you want to pass a different parameter at every step in the for loop? — jamiedanq
– jamiedanq, Commented May 31, 2017 at 12:02

Dirk · Accepted Answer · 2017-05-31 12:03:46Z

7

You can add a parameter with

m.Parameters.Add("@param_1", MySqlDbType.Int32);

and later in the loop assign a value with

m.Parameters["@param_1"].Value = n;

answered May 31, 2017 at 12:03

Dirk

11k2 gold badges38 silver badges50 bronze badges

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

Comments

itay · Accepted Answer · 2017-05-31 12:03:52Z

2

If you just need to run query for list of parms without do diffrent things on each result, You can create a string with a loop like that:

String  where_str= VAL_1 = @param_1" OR VAL_1 = @param_2" OR VAL_1 = @param_3"...

String sql = "SELECT v FROM t WHERE " + where_str;

and then exec the query it will give the same result.

If you need to saparate results so you can make it with prepaerd statement. Also, I recommend you to read about stored procedure it may be the best soultion for you in some cases.

example for prepaerd statement: (more info in the link)

private static void SqlCommandPrepareEx(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlCommand command = new SqlCommand(null, connection);

        // Create and prepare an SQL statement.
        command.CommandText =
            "INSERT INTO Region (RegionID, RegionDescription) " +
            "VALUES (@id, @desc)";
        SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
        SqlParameter descParam = 
            new SqlParameter("@desc", SqlDbType.Text, 100);
        idParam.Value = 20;
        descParam.Value = "First Region";
        command.Parameters.Add(idParam);
        command.Parameters.Add(descParam);

        // Call Prepare after setting the Commandtext and Parameters.
        command.Prepare();
        command.ExecuteNonQuery();

        // Change parameter values and call ExecuteNonQuery.
        command.Parameters[0].Value = 21;
        command.Parameters[1].Value = "Second Region";
        command.ExecuteNonQuery();
    }
}

edited May 31, 2017 at 12:03

answered May 31, 2017 at 11:58

itay

3574 silver badges17 bronze badges

3 Comments

Leif Neland Over a year ago

I specified I could not do one query and loop over the result, the query was a simple example.

itay Over a year ago

Did you try to do something like the example of the prepared statement i wrote?

Leif Neland Over a year ago

Yeah; I didn't notice the change of parameters, because I saw no loop. Sorry; I can't un-downvote your answer.

Marcel Gangwisch · Accepted Answer · 2017-05-31 12:06:13Z

1

Yes, this should be possible! Have a look for SQL Prepared Statements!

You can just use:

cmd = new MySqlCommand("SELECT * FROM yourTable WHERE condition=@val1", MySqlConn.conn);

In the loop add the parameters and prepare the command

cmd.Parameters.AddWithValue("@val1", value);
cmd.Prepare();

after the loop execute your query with

cmd.ExecuteNonQuery();

edited May 31, 2017 at 12:06

answered May 31, 2017 at 11:56

Marcel Gangwisch

9,0464 gold badges27 silver badges32 bronze badges

2 Comments

Leif Neland Over a year ago

I specified I could not do one query and loop over the result, the query was a simple example.

sujith karivelil Over a year ago

@LeifNeland: I strongly believe that you can't do like this doesn't be a reason for a down vote

LoztInSpace · Accepted Answer · 2017-05-31 12:08:53Z

0

Yep, you can do all of those things but unless that's just an example you'd want to use IN with all the values or a join to a bulk loaded temp table if there are a large number of them. The reason is that each round trip to the DB has a significant overhead that you can reduce from n to 1 with either of those techniques.

answered May 31, 2017 at 12:08

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

4 Comments

Leif Neland Over a year ago

I specified I could not do one query and loop over the result, the query was a simple example.

LoztInSpace Over a year ago

Sure you can. If you can write it as a loop you can write it as a set. Maybe you should ask the question you want answered rather than make assumptions and then down vote people trying to help.

Leif Neland Over a year ago

Sorry. I dumbed down the example too much, to get the answer on the real problem instead of the example. Instead of using a series of queries to the database, I should just fetch the entire database to an array and search that instead. Yeah, right.

LoztInSpace Over a year ago

What?! You clearly don't understand how databases work or how to use them to the full potential. Never mind.

Collectives™ on Stack Overflow

c# change parameter for sql in loop

4 Answers 4

Comments

3 Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related