How to use command.Parameters.AddWithValue with value?

Question

My code is as below:

if (!string.IsNullOrEmpty(Mpdue.TheObjectPropertyNameNs))
{

    string sql = @String.Format(" SELECT * FROM doTable WHERE dOCode IN ('" + Mpdue.TheObjectPropertyNameNs + "'); ");

    using (OdbcConnection myConnectionString =
        new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString))
    {
        using (OdbcCommand command =
            new OdbcCommand(sql, myConnectionString))
        {
            try
            {
                command.Connection.Open();

                using (OdbcDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Response.Write(reader["dOCode"].ToString() + "<br />");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("operation failed!", ex);
            }
            finally
            {
                command.Connection.Close();
            }
        }
    }
 }

Output:

D410
D420
D430
D440

Now I tried use command.Parameters.AddWithValue in MySql query.

I have been trying and cannot find error, the output when use command.Parameters.AddWithValue is empty, why ?

if (!string.IsNullOrEmpty(Mpdue.TheObjectPropertyNameNs))
{

    string sql = @String.Format(" SELECT * FROM doTable WHERE dOCode IN (?); ");

    using (OdbcConnection myConnectionString =
        new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString))
    {
        using (OdbcCommand command =
            new OdbcCommand(sql, myConnectionString))
        {
            try
            {
                command.Connection.Open();
                command.Parameters.AddWithValue("param1", Mpdue.TheObjectPropertyNameNs);

                using (OdbcDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Response.Write(reader["dOCode"].ToString() + "<br />");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("operation failed!", ex);
            }
            finally
            {
                command.Connection.Close();
            }
        }
    }
}

Edit #2

var parameters = new string[Mpdue.TheObjectPropertyNameNs.Length];
var paramValue = string.Join(", ", parameters);


string sql = string.Format("SELECT * FROM doTable WHERE dOCode IN ({0})", string.Join(", ", parameters.Select(x => "?")));

int index = 0;
foreach (var param in parameters)
{
   command.Parameters.AddWithValue("param{index}", param);
   index++;
}

Edit #1

My new code is as below:

if (!string.IsNullOrEmpty(Mpdue.TheObjectPropertyNameNs))
{
   var parameters = new string[Mpdue.TheObjectPropertyNameNs.Length]; 

   string sql = @String.Format(" SELECT * FROM doTable WHERE dOCode IN ({0})", string.Join(", ", parameters));

        using (OdbcConnection myConnectionString =
            new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString))
        {
            using (OdbcCommand command =
                new OdbcCommand(sql, myConnectionString))
            {
                try
                {
                    command.Connection.Open();

                    for (int i = 0; i < Mpdue.TheObjectPropertyNameNs.TrimStart('\'').TrimEnd('\'').Length; i++)
                    {
                       parameters[i] = string.Format("param1{0}", i);
                       command.Parameters.AddWithValue(parameters[i], Mpdue.TheObjectPropertyNameNs.TrimStart('\'').TrimEnd('\'')[i]);
                    }                    

                    using (OdbcDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Response.Write(reader["dOCode"].ToString() + "<br />");
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw new ApplicationException("operation failed!", ex);
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }
     }

Error :

ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.24-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , , , , , , , , , , , , , , , , , , , , , , , , ) at line 1

[Don't use AddWithValue.](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Zohar Peled, Feb 21 '19 at 12:28
The problem might be that you insert a list of values in the first approach like `IN ('A', 'B')` whereas when using `AddWithValue` you insert all values into just one parameter which ends in `IN ('A. B')`. Maybe this question helps: https://stackoverflow.com/q/13116042/642579 — Markus, Feb 21 '19 at 12:31
The fact that you are doing this in a Master Page only means you are doing it in the wrong place and has nothing to do with your problem — Camilo Terevinto, Feb 21 '19 at 12:32
I don't know if the ODBC objects will work the same as the SQL ones, but this may be helpful: https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand — David, Feb 21 '19 at 12:33
Consider using Dapper instead. https://dapper-tutorial.net/parameter-list — mjwills, Feb 21 '19 at 12:49
@David Thank you for help, please see the **Edit #1** in my first question. — Chevy Mark Sunderland, Feb 21 '19 at 13:29
@Markus Thank you for help, please see the **Edit #1** in my first question. — Chevy Mark Sunderland, Feb 21 '19 at 13:31
@ZoharPeled That article is about SqlClient / SQL Server. There's really no reason to avoid `AddWithValue` for MySQL; it doesn't have the `varchar` vs `nvarchar` issue that he talks about and everything gets converted to a string on the wire anyway. (Source: I wrote a MySQL driver: https://github.com/mysql-net/MySqlConnector) — Bradley Grainger, Feb 22 '19 at 00:10
@BradleyGrainger I don't work with MySql (did some work with it in the past, though) so I'll take your word for it. Still, I think it's better to use `add` even only for readability. — Zohar Peled, Feb 22 '19 at 06:56

score 1 · Answer 1 · answered Feb 21 '19 at 13:55

1

In order to solve this, you can change your query to the following string:

SELECT * FROM doTable WHERE FIND_IN_SET(dOCode, ?)

Then, you need to make sure that your parameter is set in the form D410, D420, D430, D440. So you could build it as in your first approach like

var paramValue = string.Join(", ", parameters);

and add it later on

command.Parameters.AddWithValue("param1", paramValue);

Please note that using FIND_IN_SET might have a negative impact on performance as it will scan the whole table regardless of index. An alternative would be to insert n parameters into the query and then add each parameter individually, e.g.

string sql = string.Format("SELECT * FROM doTable WHERE dOCode IN ({0})", string.Join(", ", parameters.Select(x => "?")));
// ...
int index = 0;
foreach(var param in parameters)
{
  command.Parameters.AddWithValue($"param{index}", param);
  index++;
}

This is a viable way if the number of parameters that is to be expected is not too high.

answered Feb 21 '19 at 13:55

Markus

20,838
4
31
55

Thank you, but new error now ERROR [HY001] [MySQL][ODBC 5.1 Driver][mysqld-5.5.24-log]Memory allocation error – Chevy Mark Sunderland Feb 21 '19 at 14:13
The parameters are **'D410','D430','D440','D420'** – Chevy Mark Sunderland Feb 22 '19 at 07:26
My new code in **Edit #2** in my first question, thank you- – Chevy Mark Sunderland Feb 22 '19 at 08:07
@ChevyMarkSunderland maybe you can check the following: do you have a `$` in `.AddWithValue($"param{index}", param);`? `param` does not need quotation marks once it is used in a parameter. Does `param` have any quotation marks? – Markus Feb 22 '19 at 08:54
Not quotation marks in `param` ... The code posted on reply from @Hamamelis working in my case ... – Chevy Mark Sunderland Feb 22 '19 at 10:29

Hamamelis · Answer 2 · 2019-02-22T10:23:12.297

Try this in c#. I hope I was helpful.

    if (!string.IsNullOrEmpty(Mpdue.TheObjectPropertyNameNs))
    {
        var paramValue = string.Join(", ", Mpdue.TheObjectPropertyNameNs);

        string sql = string.Format("SELECT * FROM doTable WHERE dOCode IN ({0})", paramValue.ToString());

        ...

        foreach (var param in paramValue)
        {
           command.Parameters.AddWithValue("param1", param.ToString());
        }

        ...
   }

How to use command.Parameters.AddWithValue with value?

2 Answers2