Changing the where clause to use Oracle Command Parameter

Question

I am currently creating the web API to accept one input parameter and using them in the particular field in the where clause. Below is the code for the service

      public HttpResponseMessage Getdetails( string JRS_NO,string DOB)
    {

        List<OracleParameter> prms = new List<OracleParameter>();
        List<string> selectionStrings = new List<string>();
        var jrs ="";
        var dateofBirth="";
        string connStr = ConfigurationManager.ConnectionStrings["TGSDataConnection"].ConnectionString;
        using (OracleConnection dbconn = new OracleConnection(connStr))
        {
            DataSet userDataset = new DataSet();
            var strQuery = "SELECT * from LIMS_SAMPLE_RESULTS_VW where JRS_NO =:jrs and DOB=:dateofBirth";
            jrs = JRS_NO;
            dateofBirth = DOB;
            prms.Add(jrs);
            prms.Add(dateofBirth);

Instead of giving them directly in the Query how can I use the OracleParameter here.I have created the prms for the command parameter but not sure how to proceed with that.

Possible duplicate of [C# parameterized queries for Oracle - serious & dangerous bug!](http://stackoverflow.com/questions/3876856/c-sharp-parameterized-queries-for-oracle-serious-dangerous-bug) — शेखर, Nov 16 '16 at 13:07
Here is your answer. [Using Parameters](http://stackoverflow.com/a/11048965/6527049) — Vivek Nuna, Nov 16 '16 at 13:09
@viveknuna I tried and have edited my question with the code. But I am getting error in prms.Add(). I have not used the command parameters before — trx, Nov 16 '16 at 13:26
string sql = "select department_name from departments where department_id = " + ":department_id"; OracleCommand cmd = new OracleCommand(sql, conn); cmd.CommandType = CommandType.Text; OracleParameter p_department_id = new OracleParameter(); p_department_id.OracleDbType = OracleDbType.Decimal; p_department_id.Value = departmentID.Text; cmd.Parameters.Add(p_department_id); OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); Follow this sample code — Vivek Nuna, Nov 16 '16 at 13:34

score 2 · Accepted Answer · answered Nov 16 '16 at 13:50

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.

Changing the where clause to use Oracle Command Parameter

1 Answers1