Oraclecommand SQL Parameters Binding

OracleCommand SQL Parameters Binding

Remove single quotes around @username, and with respect to oracle use : with parameter name instead of @, like:

OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM sup_sys.user_profile
                           WHERE domain_user_name = :userName", db);
oraCommand.Parameters.Add(new OracleParameter("userName", domainUser));

Source: Using Parameters

C# Oracle command Parameters add. How can I add part of the text itself into the command text?

For this I'd just run a different query:

string sql = "";
if (int.TryParse(ss.StoreNumber, out int n) == true)
{
  sql = "SELECT * FROM SALES_STATUS WHERE store_no = :SerchStore";
}
else
{
  sql = "SELECT * FROM SALES_STATUS;"
}    
//Creating cmd using sql and conn
OracleCommand cmd = new OracleCommand(sql, conn);

//Create Parameters to add value
if (int.TryParse(ss.StoreNumber, out int n) == true)
{
  cmd.Parameters.Add("SerchStore", int.Parse(ss.StoreNumber));
}

If it was in a stored procedure, you might do something like "OR :SerchStore is NULL", but since you're sending the query from C# code, just send the query you want to send.

How to pass the data to OracleCommand Class c#

Try : instead of @

string query = "select s.store_code,count(i.invc_sid) as count from invoice_v i Left join store_v s ON i.sbs_no = s.sbs_no and i.store_no = s.store_no Where(i.created_date between to_date(:fromDate,'MM//DD//YY') and to_date(:toDate,'MM//DD//YY'))  and i.proc_status not in ('131072','65536','147456', '81920') and i.invc_type = 0 AND i.sbs_no = 6  GROUP BY  s.store_code";

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.

Assign a literal to a bind variable (C# output parameter) in an oracle block

The problem is probably that you're using OracleDbType instead of DbType. I usually use OracleDbType, but in this case you're selecting a number into some variable (select -1 into ...) and its returning a Decimal type instead of an Int. The simple example below works for me:

string connStr = "User Id=myuser;Password=mypass;Data Source=myinstance;";
                using (OracleConnection con = new OracleConnection(connStr))
                {
                    string s = @"BEGIN
                    select 1 into :TESTID from dual where 1=0;
                    EXCEPTION
                    when no_data_found then
                    select -1 into :TESTID from dual; 
                    END;";

                    using (OracleCommand cmd = new OracleCommand(s, con))
                    {
                        con.Open();
                        Console.WriteLine("Running statement");

                        OracleParameter prm = new OracleParameter();
                        cmd.Parameters.Clear();
                        cmd.BindByName = true;

                        //prm = new OracleParameter("TESTID", OracleDbType.Int32, ParameterDirection.InputOutput); prm.Value = 0; cmd.Parameters.Add(prm);
                        prm = new OracleParameter();
                        prm.ParameterName = "TESTID";
                        prm.DbType = DbType.Int32; // don't use OracleDbType, will return decimal in some cases, not int
                        prm.Direction = ParameterDirection.InputOutput;
                        prm.Value = 0;
                        cmd.Parameters.Add(prm);

                        cmd.ExecuteNonQuery();

                        // may have cast/conversion issues here if using OracleDbType
                        int id = Convert.ToInt32(cmd.Parameters["TESTID"].Value);

                        Console.WriteLine("ID is: " + id);

                        con.Close();
                    }
                }

Parameters.AddWithValue vs Parameters.Add?

The first error is caused because you pass your connstr (string) instead of your conn (OracleConnection). Change this:

using (OracleCommand cmd =new OracleCommand(cmdstr,connstr))

To this

using (OracleCommand cmd =new OracleCommand(cmdstr,conn))

As for the second one, the OracleParameter does not seem to need :. Example use:

OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM sup_sys.user_profile
                           WHERE domain_user_name = :userName", db);
oraCommand.Parameters.Add(new OracleParameter("userName", domainUser));

It seems like there is a difference between Oracle.DataAccess.Client and System.Data.OracleClient. OracleParameterCollection.AddWithValue seems to exist in System.Data.OracleClient. But you use Oracle.DataAccess.Client.

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