Moq + Unit Testing - System.Reflection.Targetparametercountexception: Parameter Count Mismatch

C# parameterized queries for Oracle - serious & dangerous bug!

This is not a bug but explicitly mentioned in Oracle ODP.Net documentation. In a OracleCommand class the parameters are bound by position as default. If you want to bind by name then set the property cmd.BindByName = true; explicitly.

Reference to Oracle documentation.
http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/OracleCommandClass.htm#i997666

Changing the where clause to use Oracle Command Parameter

You are making multiple mistakes in your code. I'm writing code for you, but remaining you have to fix.

        string jrs = "";
        string dateofBirth = "";
        string connectionString = ConfigurationManager.ConnectionStrings["TGSDataConnection"].ConnectionString;
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            string query = "SELECT * from LIMS_SAMPLE_RESULTS_VW where JRS_NO =:jrs and DOB=:dateofBirth";
            OracleCommand command = new OracleCommand(query, connection);
            command.Parameters.Add(new OracleParameter("jrs", jrs));
            command.Parameters.Add(new OracleParameter("dateofBirth", dateofBirth));
            command.CommandType = CommandType.Text;
            connection.Open();
            OracleDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    string value = reader["ColumName"].ToString();
                }
            }
            finally
            {
                reader.Close();
            }
        }

Do not write query in code, Write stored procedure and then call it by code.
You have to use ExecuteReader to get the result from SELECT query.
Replace ColumName with your column name in table.
Do not use @ with arguments, use: before them.
Check your connection string whether is it correct or not.
You can run your query separately in Oracle DB just to test whether your query is giving the required results or not.
Check the DataType of jrs and dateOfBirth, In my example I have taken as string.
Close Reader in finally block.
My personal opinion, do not use SELECT *, always use column names. because it will give you all columns, may be you require only 2 or 3.

ORA-01722: invalid number on INSERT in C# - Oracle 11g

An ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. So, check the parameters for correct data types.

And also see:

C# parameterized queries for Oracle - serious & dangerous bug!

and

Why am I getting an ORA-01722 (invalid number)?

Update statement working in SQL Developer but not in C#

Alright, i found out why this wasn't working, it wasn't because of the colons (you can add colons in parameters without it being a problem):

command.Parameters.Add(":months", OracleDbType.Int32).Value = months;

The problem was that i was adding the parameters in a different order than I was using them.

So if in your SQL statement you are adding parameters in a specific order, you should follow that order when you add your OracleCommand.Parameters, like so:

using (OracleConnection con = new OracleConnection(connectionString))
    {
        con.Open();
        OracleCommand command = con.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "UPDATE people " +
                          "SET months = :months " +
                          "WHERE number = :number";
        command.Parameters.Add(":months", OracleDbType.Int32).Value = months;
        command.Parameters.Add(":number", OracleDbType.Int32).Value = number;

        command.ExecuteNonQuery();
    }

Using Dapper with Oracle

IMO, the correct approach here is not to (as per the accepted answer) use the database specific parameter prefix (so @ for sql-server, : for oracle) - but rather: use no prefix at all. So ultimately this is:

il.Emit(OpCodes.Ldstr, prop.Name);

(etc)

In particular, a static property would be bad as it would limit you to one vendor per AppDomain.

Dapper has been updated with this change. It also now dynamically detects BindByName and sets it accordingly (all without needing a reference to OracleCommand).

Why am I getting an ORA-01722 (invalid number)?

I have given answer credit already, but I think it's worth mentioning here exactly what the root of my problems was, in case anyone else finds this item while looking for an answer to their own problem.

The problem is that the C# implementation of parameterized queries for Oracle contains a serious and potentially dangerous bug - a real "pit in the public domain":

It doesn't matter what you name your parameters; they have to be added in the order in which they appear in the query.

See more here.

How to add records to an Oracle table with foreign keys?

First check which table the TIMESUSER.SYS_C007051 constraint refers to.
As you have a field named USER_ID and the constraint is TIMESUSER.SYS_C007051, the problem could be that you don't have a user named test

Or maybe I am totally wrong because I don't remember if TIMESUSER here is a schema name or a table name. Without knowing which relationship is on error it's hard to guess.

EDIT
I can't guess the true origin of the error but I am quite sure it's due to the NCHAR type usage. I don't think it's a good practice to use this datatype as PK/FK furthermore if the data content does not exactly match the field type.

Here how I would do things to avoid this issue (and many others that you might meet later) :

1. In the database, add some ID fields (numeric) to use as PK/FK and some sequences to increase their value (not needed if >= Oracle12c as there is an auto-increment datatype) :

-- Projects
CREATE TABLE TASK_PROJECT (
    ID INT NOT NULL,
    PROJECT_CODE NCHAR(9) NOT NULL,
    PROJECT_NAME NVARCHAR(255),
    CONSTRAINT PK_TASK_PROJECT PRIMARY KEY (ID) CLUSTERED
);
-- Unique index to keep sure that PROJECT_CODE remains unique in the table
CREATE UNIQUE INDEX UX_TASK_PROJECT_CODE  ON TASK_PROJECT(PROJECT_CODE);
-- Sequence to use to increment TASK_PROJECT.ID
CREATE SEQUENCE SEQ_TASK_PROJECT (
  START WITH 1
  INCREMENT BY 1
);

-- Tasks
CREATE TABLE TASK_TASK (
    ID INT NOT NULL,
    TASK_CODE NCHAR(9) NOT NULL,
    TASK_NAME NVARCHAR(255),
    CONSTRAINT PK_TASK_TASK PRIMARY KEY (ID) CLUSTERED
);
-- Unique index to keep sure that TASK_CODE remains unique in the table
CREATE UNIQUE INDEX UX_TASK_TASK_CODE  ON TASK_TASK(TASK_CODE);
-- Sequence to use to increment TASK_TASK.ID
CREATE SEQUENCE SEQ_TASK_TASK (
  START WITH 1
  INCREMENT BY 1
);

-- Users
CREATE TABLE TASK_USER (
    ID INT NOT NULL,
    USER_LOGIN VARCHAR2(50),
    CONSTRAINT PK_TASK_USER PRIMARY KEY (ID) CLUSTERED
);
-- Unique index to keep sure that USER_LOGIN remains unique in the table
CREATE UNIQUE INDEX UX_TASK_USER_LOGIN ON TASK_USER(USER_LOGIN);
-- Sequence to use to increment TASK_USER.ID
CREATE SEQUENCE SEQ_TASK_USER (
  START WITH 1
  INCREMENT BY 1
);

-- Task info
CREATE TABLE TASK_INFO (
    TASK_INFO_ID INT NOT NULL,
    USER_ID INT NOT NULL,
    PROJECT_ID INT NOT NULL,
    TASK_ID INT NOT NULL,
    ATTENDED_DATE NCHAR(10),
    START_TIME NCHAR(5),
    END_TIME NCHAR(5),
    TASK_HOURS INT,
    TASK_DESCRIPTION VARCHAR2(50),
    CONSTRAINT PK_TASK_INFO PRIMARY KEY (TASK_INFO_ID) CLUSTERED,
    CONSTRAINT FK_TASK_INFO_TASK_PROJECT FOREIGN KEY (PROJECT_ID) REFERENCES TASK_PROJECT(ID),
    CONSTRAINT FK_TASK_INFO_TASK_TASK FOREIGN KEY (TASK_ID) REFERENCES TASK_TASK(ID),
    CONSTRAINT FK_TASK_INFO_TASK_USER FOREIGN KEY (USER_ID) REFERENCES TASK_USER(ID)
);
-- Sequence to use to increment TASK_INFO.ID
CREATE SEQUENCE SEQ_TASK_INFO (
  START WITH 1
  INCREMENT BY 1
);

2. On C# application side :

        // Sample of how I fill the Project Code combobox
        private void fillProjectComboBox()
        {
            DataTable dtProject = new DataTable();

            // For the moment, you don't seem to use the field PROJECT_NAME, so no need to retrieve it
            //string sql = "SELECT PROJECT_ID, PROJECT_CODE, PROJECT_NAME FROM TASK_PROJECT";
            // I added an ORDER BY PROJECT_CODE to ensure the order in the combo box
            string sql = "SELECT PROJECT_ID, PROJECT_CODE FROM TASK_PROJECT ORDER BY PROJECT_CODE";
            var cmd = new OracleCommand(sql, conn);
            using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd))
            {
                oracleDataAdapter.Fill(dtProject);
            }
            regProjectBox.SelectedValuePath = "PROJECT_ID";
            regProjectBox.DisplayMemberPath = "PROJECT_CODE";
            regProjectBox.ItemsSource = dtProject.DefaultView;
        }

        private void RegisterNewButton_Click(object sender, RoutedEventArgs e)
        {
            // Here we use TASK_ID and PROJECT_ID instead of TASK_CODE and PROJECT_CODE
            string sql = "INSERT INTO TASK_INFO(TASK_INFO_ID, USER_ID, ATTENDED_DATE, START_TIME, END_TIME, TASK_HOURS, PROJECT_ID, TASK_ID, TASK_DESCRIPTION)" +
                " VALUES(SEQ_TASK_INFO.NEXTVAL, :USER_ID, :ATTENDED_DATE, :START_TIME, :END_TIME, :TASK_HOURS, :PROJECT_ID, :TASK_ID, :TASK_DESCRIPTION)";
            var cmd = new OracleCommand(sql, conn);
            // Here I changed the defintion of USER_ID from login to a numeric identifier  of this login
            cmd.Parameters.Add("USER_ID", OracleDbType.Int16).Value = 1; // Identifier related to the user "test"
            cmd.Parameters.Add("ATTENDED_DATE", OracleDbType.NChar, 10).Value = regDateTextBox.Text;
            cmd.Parameters.Add("START_TIME", OracleDbType.NChar, 5).Value = regStartTimeTextBox.Text;
            cmd.Parameters.Add("END_TIME", OracleDbType.NChar, 5).Value = regEndTimeTextBox.Text;
            // WRONG !!!- TryParse returns a boolean that indicates if the parsing was successful or not
            //cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16, 3).Value = int.TryParse(regTaskTimeTextBox.Text, out int result);
            int taskHours;
            if (int.TryParse(regTaskTimeTextBox.Text, out taskHours))
            {
                cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16).Value = taskHours;
            } else {
                // Handle task hours if no numeric value
                cmd.Parameters.Add("TASK_HOURS", OracleDbType.Int16).Value = 0;
            }
            cmd.Parameters.Add("TASK_DESCRIPTION", OracleDbType.Varchar2, 50).Value = regTaskDescTextBox.Text;
            // Change Project code to project id
            // Explict cast as int to be sure you assign a numeric value
            cmd.Parameters.Add("PROJECT_ID", OracleDbType.Int16).Value = (int)regProjectBox.SelectedValue;
            // Change Task code to task id
            // Explict cast as int to be sure you assign a numeric value
            cmd.Parameters.Add("TASK_ID", OracleDbType.Int16).Value = (int)regTaskBox.SelectedValue;
        }

Binding query parameters by name with ODP.NET

I think you can create your own provider that uses the defaults you want to use. You could create that provider easily by inheriting all the classes from odp.net, just adjust some properties like BindByName.

The DbProviderfactory will create your classes instead of the normal odp.net classes.

---

Related Topics