OUT parameter test case

from the Artful MySQL Tips List


using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace TestSP {
  class TestSP {
    private MySqlConnection con = null;   
    
    [STAThread]
    static void Main(string[] args) {
      bool error = false;
      int param = 0;
      try {
        param = Int32.Parse(args[0]);
      }
      catch (Exception e) {
        Console.WriteLine("You must pass an integer value " + "as parameter!");
        error = true;
      }
      if(! error ) {
        new TestSP(param);
      }
    }

    public TestSP(int param) {
      // set up the connection
      if (setConnection()) {
        MySqlCommand cmd = createCommandObject(param);
        // first get the output parameter
        int outputParameter = getOutputParameter(cmd);
        if (outputParameter != -1) {
          // set up the SQL command
          cmd = createCommandObject(param);
          // call the Stored Procedure setting the
          // input parameter
          callSP(cmd);
          // output the output parameter
          Console.WriteLine(
            "The value of the output parameter is " + outputParameter
          );
        }
        // close the connection
        closeConnection();
      }
    }

    private bool setConnection() {
      // set up connection
      string constring = "Data Source=localhost;" + 
                         "User Id=root;" + "Password=pass;" + "Database=test";
      con = new MySqlConnection(constring);
      try {
        con.Open();
      } 
      catch (MySqlException e) {
        Console.WriteLine("Could not establish connection!");
        return false;
      }
      return true;
    }

    private MySqlCommand createCommandObject(int param) {
      // create command object and set values
      MySqlCommand cmd = new MySqlCommand();
      cmd.Connection = con;
      cmd.CommandText = "testProc";
      cmd.CommandType = CommandType.StoredProcedure;
      // set input parameter
      cmd.Parameters.Add("?_val", param);
      cmd.Parameters["?_val"].Direction = ParameterDirection.Input;
      // set output parameter
      cmd.Parameters.Add("?square", MySqlDbType.Int32);
      cmd.Parameters["?square"].Direction = ParameterDirection.Output;
      return cmd;
    }

    private bool callSP(MySqlCommand cmd) {
      try {
        bool moreResults = true;
        // execute Stored Procedure
        MySqlDataReader reader = cmd.ExecuteReader();
        // repeat while as long as there are more results
        while (moreResults) {
          // output result
          while (reader.Read()) {
            Console.WriteLine(reader.GetString(0));
          }
          // ask if there are more results
          moreResults = reader.NextResult();
        }
      }
      catch (MySqlException e) {
        return false;
      }
      return true;
    }

    private int getOutputParameter(MySqlCommand cmd) {
      try {
        // execute Stored Procedure to get output parameter
        cmd.ExecuteNonQuery();
        // store output parameter
        object outputParameter = cmd.Parameters[1].Value;
        return (int)outputParameter;
      }
      catch (MySqlException e) {
        return -1;
      }
    }

    private void closeConnection() {
      try       {
        con.Close();
      } catch (MySqlException ignored) {}
    }
  }
}



Return to the Artful MySQL Tips page