title: C# best way to call MySQL Stored Procedures, Functions
body: <p>Hello I wrote my DAL calling Stored Procedures, but I still don't know if I should use ExecuteScalar, NonQuery or Reader for some procedures.</p>
  
  <p>For example I wrote this function that I want to call</p>
  
  <pre><code>CREATE FUNCTION `retornarIdArea`(cod longtext) RETURNS int(11)
  BEGIN
  DECLARE id int;
      select AreaId into id FROM area where Codigo = cod;
      return id;
  END
  </code></pre>
  
  <p>this procedure that should return a dataset</p>
  
  <pre><code>CREATE PROCEDURE `consultar_n_asunto`(in asun longtext, in est int)
  BEGIN
      select * from notas where Asunto LIKE CONCAT('%',CONCAT(asun,'%')) AND Estado = est;
  END$$
  </code></pre>
  
  <p>And last a procedure that inserts some data, and I validate that with a select using output Parameter.</p>
  
  <pre><code>CREATE PROCEDURE `registrar_dest`(in nomb longtext,
  in dir longtext, in inst int, in mail longtext, in tel longtext,
  in act int, out res tinyint(1))
  BEGIN
      -- verificar que no exista el destinatario
      select count(*) into res from destinatario WHERE Nombre = nomb AND 
      Direccion = dir AND Email = mail AND Telefono = tel AND Activo = act;
  
      IF res = 0 THEN
          INSERT INTO destinatario (Nombre, Direccion, InstitucionId, Email, Telefono, Activo)
          VALUES (nomb, dir, inst, mail, tel, act);
          select count(*) into res from destinatario WHERE Nombre = nomb AND 
          Direccion = dir AND Email = mail AND Telefono = tel AND Activo = act;
      ELSE 
          set res = -1;
      END IF;
  END$$
  </code></pre>
  
  <p>Now I wrote in C# this to return values from FUNCTIONS</p>
  
  <blockquote>
  <pre><code>    public object ejecutarFuncion()
      {
          using (MySqlConnection conn = new MySqlConnection(stringDeConexion))
          {
              using (MySqlCommand cmd = new MySqlCommand(procedimiento, conn))
              {
                  cmd.CommandType = System.Data.CommandType.StoredProcedure;
                  //Se abre la conexión
                  conn.Open();
                  //existen parámetros los recorremos y agregamos.
                  foreach (KeyValuePair&lt;string, object&gt; pars in parametros)
                  {
                      cmd.Parameters.Add(new MySqlParameter(pars.Key, pars.Value));
                  }
                  //Se crea la variable de retorno
                  cmd.Parameters.Add(new MySqlParameter(nombreOut, tipoParOut));
                  cmd.Parameters[nombreOut].Direction = System.Data.ParameterDirection.ReturnValue;
                  cmd.ExecuteNonQuery();
                  // Cerramos conexión
                  conn.Close();
                  return cmd.Parameters[nombreOut].Value;
              }
          }
      }
  </code></pre>
    
    <p>If you see I do a ExecuteNonQuery() here, but should I used Execute Scalar? or just use the return value from parms? </p>
  </blockquote>
  
  <p>Then I wrote this method to execute Procedures with output values (some of them do update, insert) operations, I use the output value to check if operation was done correctly.</p>
  
  <pre><code>public object ejecutarProcedimientoConOutput()
  {
      using (MySqlConnection conn = new MySqlConnection(stringDeConexion))
      {
          using (MySqlCommand cmd = new MySqlCommand(procedimiento, conn))
          {
              cmd.CommandType = System.Data.CommandType.StoredProcedure;
              //Se abre la conexión
              conn.Open();
              //verificamos si se mando la lista de parámetros
              if (parametros.Count &gt; 0)
              {
                  //existen parámetros los recorremos y agregamos.
                  foreach (KeyValuePair&lt;string, object&gt; pars in parametros)
                  {
                      cmd.Parameters.Add(new MySqlParameter(pars.Key, pars.Value));
                      cmd.Parameters[pars.Key].Direction = System.Data.ParameterDirection.Input;
                  }
              }
              cmd.Parameters.Add(new MySqlParameter(nombreOut, tipoParOut));
              cmd.Parameters[nombreOut].Direction = System.Data.ParameterDirection.Output;
              cmd.ExecuteNonQuery();
              conn.Close();
              return cmd.Parameters[nombreOut].Value;
          }
      }
  }
  </code></pre>
  
  <p>Again if I'm doing an update or insert operation, but I use output parameter to check if it was done, you can check the insert stored procedure should I use ExecuteNonQuery or ExecuteScalar?</p>
  
  <p>And for procedures that returns datasets is the same as the method I wrote above, but without the output parameter, I'm using executeReader since I only do a select and not using output parameter.</p>
  
  <p>I just want to know if I'm using the correct execute commands for those procedures.</p>