CODEWITHSUNDEEP
c#mysqlparameter-passing
Leif Neland
Leif Neland

Reputation: 1514

c# change parameter for sql in loop

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?

Upvotes: 0

Views: 3688

Answers (4)

LoztInSpace
LoztInSpace

Reputation: 5697

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.

Upvotes: 0

Marcel Gangwisch
Marcel Gangwisch

Reputation: 9026

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();

Upvotes: 1

itay
itay

Reputation: 357

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();
    }
}

Upvotes: 2

Dirk
Dirk

Reputation: 10958

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;

Upvotes: 7

Related Questions