Calling an Oracle insert stored procedure from C#

Question

Stored procedure:

CREATE OR REPLACE PROCEDURE insertMovie(
   p_m_id IN MOVIE.M_ID%TYPE,
   p_movie_name IN MOVIE.MOVIE_NAME%TYPE,
   p_year IN MOVIE.YEAR%TYPE,
   p_category IN MOVIE.CATEGORY%TYPE)
IS
BEGIN
  INSERT INTO MOVIE ("M_ID", "MOVIE_NAME", "YEAR", "CATEGORY") 
  VALUES (p_m_id, p_movie_name,p_year, p_category);
END;
/

This is the procedure I have created to insert data into the movie table. Now I am trying to call it from by C#. I have tried the following code but it's not working.

A little help in this regard will be much appreciated. Thank you

    private void button1_Click(object sender, EventArgs e)
    {
        dbConnection conObj = new dbConnection();
        OracleConnection theConn = conObj.connFunc();

        String p_m_id, p_movie_name, p_year, p_category;
        p_m_id = movie_id.Text;
        p_movie_name = movie_name.Text;
        p_year = year.Text;
        p_category = category.Text;

        OracleParameter parChoiceIn = new OracleParameter();
        OracleParameter parDataIn = new OracleParameter();
        OracleParameter parOut = new OracleParameter();

        parChoiceIn.ParameterName = "p_m_id";
        parChoiceIn.OracleType = OracleType.Number;
        parChoiceIn.Size = 32;
        parChoiceIn.Direction = System.Data.ParameterDirection.Input;
        parChoiceIn.Value = p_m_id;

        parDataIn.ParameterName = "p_movie_name";
        parDataIn.OracleType = OracleType.VarChar;
        parDataIn.Size = 32;
        parDataIn.Direction = System.Data.ParameterDirection.Input;
        parDataIn.Value = p_movie_name;

        parDataIn.ParameterName = "p_year";
        parDataIn.OracleType = OracleType.Number;
        parDataIn.Size = 32;
        parDataIn.Direction = System.Data.ParameterDirection.Input;
        parDataIn.Value = p_year;

        parDataIn.ParameterName = "p_category";
        parDataIn.OracleType = OracleType.VarChar;
        parDataIn.Size = 32;
        parDataIn.Direction = System.Data.ParameterDirection.Input;
        parDataIn.Value = p_category;

        OracleCommand cmd = theConn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "insertMovie";
        cmd.Parameters.Add(parChoiceIn);
        cmd.Parameters.Add(parDataIn);
        cmd.Parameters.Add(parOut);

        theConn.Open();

        OracleDataAdapter adapter = new OracleDataAdapter(cmd);
        DataSet myset = new DataSet("theResCur");
        adapter.Fill(myset);
        theGrid.DataSource = myset;
        theGrid.DataSource = myset.Tables[0];

        theConn.Close();
    }
}

Answer 1

You are creating a parOut parameter but never initializing it.

Also, you are redefining the parDataIn parameter three times, doing so, your OracleCommand only receives the last defined parameter p_category.

I do not understand what is your intent in the last part of your code. If you want to simply call the stored procedure, maybe you should try this:

private void button1_Click(object sender, EventArgs e)
{
    dbConnection conObj = new dbConnection();
    OracleConnection theConn = conObj.connFunc();

    String p_m_id, p_movie_name, p_year, p_category;
    p_m_id = movie_id.Text;
    p_movie_name = movie_name.Text;
    p_year = year.Text;
    p_category = category.Text;

    theConn.Open();

    OracleCommand cmd = theConn.CreateCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "insertMovie";
    cmd.Connection = theConn;

    OracleParameter parChoiceIn = new OracleParameter();
    OracleParameter parDataIn = new OracleParameter();

    parChoiceIn.ParameterName = "p_m_id";
    parChoiceIn.OracleType = OracleType.Number;
    parChoiceIn.Size = 32;
    parChoiceIn.Direction = System.Data.ParameterDirection.Input;
    parChoiceIn.Value = p_m_id;
    cmd.Parameters.Add(parChoiceIn);

    parDataIn.ParameterName = "p_movie_name";
    parDataIn.OracleType = OracleType.VarChar;
    parDataIn.Size = 32;
    parDataIn.Direction = System.Data.ParameterDirection.Input;
    parDataIn.Value = p_movie_name;
    cmd.Parameters.Add(parDataIn);

    parDataIn = new OracleParameter();
    parDataIn.ParameterName = "p_year";
    parDataIn.OracleType = OracleType.Number;
    parDataIn.Size = 32;
    parDataIn.Direction = System.Data.ParameterDirection.Input;
    parDataIn.Value = p_year;
    cmd.Parameters.Add(parDataIn);

    parDataIn = new OracleParameter();
    parDataIn.ParameterName = "p_category";
    parDataIn.OracleType = OracleType.VarChar;
    parDataIn.Size = 32;
    parDataIn.Direction = System.Data.ParameterDirection.Input;
    parDataIn.Value = p_category;
    cmd.Parameters.Add(parDataIn);

    cmd.ExecuteNonQuery();

    theConn.Close();
}