Using SQL Keywords in C# MySqlCommand.CommandText

Question

I'm moving my stored procedures to C# code. In the stored procedures I have something like

Begin
SET @Id = null;
IF ?Status = 0 THEN SET ? ?DateToday = UTC_TIMESTAMP(); END IF;
SELECT * FROM TABLE
END

In the C# Code I just copy pasted everything inside Begin and END. Like so,

MySqlCommand command = new MySqlCommand();
command.CommandText = @"SET @Id = null;
IF ?Status = 0 THEN SET ? ?DateToday = UTC_TIMESTAMP(); END IF;
SELECT * FROM TABLE"

I have an error in the If statement saying incorrect syntax. The stored procedure is working but not when I place it in C# code.

Why not use only command.CommandText = "SELECT * FROM TABLE" — Damith
– Damith, Commented Nov 14, 2016 at 6:57
@Damith its just an example, the Set and If statement are causing errors. — Cosytyle
– Cosytyle, Commented Nov 14, 2016 at 7:02

Tolga Evcimen · Accepted Answer · 2016-11-15 08:42:43Z

1

That's not the logic of Stored Procedures. As its name implies stored procedures are functions that are stored in the database itself. And you can invoke them by calling their names.

Hence you cannot just put the stored procedure function code in your C# code, I mean you shouldn't.

Update: If you only want to use T-SQL statements (if-else blocks and so) check out this link.

edited Nov 15, 2016 at 8:42

answered Nov 14, 2016 at 7:01

Tolga Evcimen

7,39011 gold badges63 silver badges97 bronze badges

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

1 Comment

Cosytyle Over a year ago

I understand, but what is the counterpart of Set and the If statement in C# CommandText?btw thanks for you reply :)

Rahul Tripathi · Accepted Answer · 2016-11-14 06:58:39Z

0

If you have created a stored procedure then you can call it using a stored procedure syntax like this;

using (SqlConnection con = new SqlConnection(someconnectionstring)) {
    using (SqlCommand cmd = new SqlCommand("myStoredProc", con)) {
      cmd.CommandType = CommandType.StoredProcedure;
      //If you have parameters in your SP, add like this else ignore below line
      cmd.Parameters.Add("@myparam", SqlDbType.VarChar).Value = somevalue;

      con.Open();
      cmd.ExecuteNonQuery();
    }
  }

answered Nov 14, 2016 at 6:58

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

1 Comment

Rahul Tripathi Over a year ago

@Cosytyle;- Any specific reason to do that. As it is not a good coding practice?

Collectives™ on Stack Overflow

Using SQL Keywords in C# MySqlCommand.CommandText

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related