C# MySQL Parameters : ? or @

Question

I'm kind of confused with the MySQL parameters.

Both of the following parts of my code work fine. The first one uses parameters with @ :

const string query = "UPDATE `items` SET `name` = @name, `price` = @price WHERE `id` = @id";
try
{
    using (MySqlCommand cmd = new MySqlCommand(query, Database.MyConnection))
    {
        cmd.Parameters.AddWithValue("name", name);
        cmd.Parameters.AddWithValue("price", price);
        cmd.Parameters.AddWithValue("id", id);
        cmd.ExecuteNonQuery();
    }
}

Second uses parameters with ? :

const string query = "UPDATE `items` SET `name` = ?name, `price` = ?price WHERE `id` = ?id";
try
{
    using (MySqlCommand cmd = new MySqlCommand(query, Database.MyConnection))
    {
        cmd.Parameters.AddWithValue("name", name);
        cmd.Parameters.AddWithValue("price", price);
        cmd.Parameters.AddWithValue("id", id);
        cmd.ExecuteNonQuery();
    }
}

These answers say both @ or ? work fine. Even cmd.Parameters.AddWithValue("@name", name); seems to work (note the @ in the name).

Why all of them work fine with MySQL ?
Is there a difference between them ?
Which one is the proper way to use with MySQL ?

Thanks for any help I'll get.

score 6 · Accepted Answer · answered Jan 18 '14 at 16:08

6

From the documentation:

Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible with MySQL user variables, so the provider now uses the '?' symbol to locate parameters in SQL. To support older code, you can set 'old syntax=yes' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL.

answered Jan 18 '14 at 16:08

CodeCaster

147,647
23
218
272

I think this answers all questions from OP. `+1` of course and nice find. – Soner Gönül Jan 18 '14 at 16:11
1

Thanks ! This definitively answers my questions. +1 Nice catch. – Gabriel L. Jan 18 '14 at 19:33

dpotts · Answer 2 · 2015-05-26T15:48:54.233

It appears this has changed again

From the updated documentation Connector Options

Old Syntax, OldSyntax, Use Old Syntax, UseOldSyntax - This option was deprecated in Connector/Net 5.2.2. All code should now be written using the '@' symbol as the parameter marker.

Also from Creating a Connector .Net

Using the '@' symbol for parameters is now the preferred approach, although the old pattern of using '?' is still supported. To avoid conflicts when using the '@' symbol in combination with user variables, see the Allow User Variables connection string option in Chapter 7, Connector/Net Connection String Options Reference. The Old Syntax connection string option has now been deprecated.

score 0 · Answer 3 · answered Jul 28 '17 at 05:42

@ and ? are both symbol to mark parameters in MySQL, you can even used it both like...

command.CommandText = @"UPDATE sellinginventorytable SET cqty=cqty+@aqty,`date`=CURRENT_TIMESTAMP(),`user`=@user WHERE cid=?cid;
                        INSERT INTO internaltranstable SET `type`='INV.ADJ.', `cid`=?cid, `cqty`=@aqty, `date`=CURRENT_TIMESTAMP(), `notes`='STORE INVENTORY ADJUSTMENT', `area`='COUNTER', `sender`=@user, `receiver`=@user;";
command.Parameters.AddWithValue("?cid", row.Cells["barcode"].Value);
command.Parameters.AddWithValue("@aqty", row.Cells["aqty"].Value);
command.Parameters.AddWithValue("@user", Properties.Settings.Default.user.ToUpper());
command.ExecuteNonQuery();
command.Parameters.Clear();

C# MySQL Parameters : ? or @

3 Answers3

Linked