CODEWITHSUNDEEP
c#mysqlsyntax
Cosytyle
Cosytyle

Reputation: 57

Using SQL Keywords in C# MySqlCommand.CommandText

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.

Upvotes: 1

Views: 573

Answers (2)

Tolga Evcimen
Tolga Evcimen

Reputation: 7352

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.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

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

Upvotes: 0

Related Questions