0

I'm trying to execute a valid command on MySQL on my C# code. I tried on SQL section in phpmyadmin and it works, but now, I must execute it on my C# program and I don't have any idea how to do it

C# suppose this @ refers to a parameter and it expects it, but it isn't, what I want is execute this command as it is. I tried to asign it as a parameter like ("@TRIGGER_CHECKS","@TRIGGER_CHECKS") but it doesn't work either.

This is the command, it works on MySQL:

SET @TRIGGER_CHECKS = TRUE;

I want to execute that command on C# and I don't know how to do it. Thanks.

Valentín Sánchez Boto
  • 111
  • 12
  • Can you write a stored procedure that does that command and call the procedure instead? – Crowcoder May 19 '19 at 20:55
  • I'm trying and it doesn't work in a procedure, only if i execute it isolated. – Valentín Sánchez Boto May 19 '19 at 21:02
  • You could try `string query = @"SET @TRIGGER_CHECKS = TRUE;"` see here the explainment about [@](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/tokens/verbatim) – Raymond Nijland May 19 '19 at 21:04
  • @RaymondNijland `@` is not a special character at the C# level. `@"@"` and `"@"` are identical. – GSerg May 19 '19 at 21:05
  • *"@ is not a special character at the C# level. @"@" and "@" are identical."* @GSerg mine comment was fore meant because ive read *"C# suppose this @ refers to a parameter and it expects it"* But you could be right i didn't do C# for a while now so iam a bit rusty. – Raymond Nijland May 19 '19 at 21:08
  • 1
    @RaymondNijland The `MySqlCommand` [understands](https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html) the `@TRIGGER_CHECKS` as a parameter named `TRIGGER_CHECKS`. – GSerg May 19 '19 at 21:10
  • Ok thanks for the link @GSerg then mine mind is blown why the MySQL devs would choose the `@` for a param then with makes it incompatible with MySQL user variable not really smart.. – Raymond Nijland May 19 '19 at 21:14
  • @RaymondNijland the MSSQL provider has the same issue. Major bummer. – Crowcoder May 19 '19 at 21:15

1 Answers1

3

By default, Connector/NET assumes that any variable prefixed with a @ is a parameter to the SQL statement.

To allow your statement to execute correctly, you need to add ;Allow User Variables=true to your connection string. Then the command will be passed to the server as-is.

Bradley Grainger
  • 27,458
  • 4
  • 91
  • 108