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.

Answer 1

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.