How to retrieve data from multiple tables from an Oracle database, create a datatable and then store this data in a class object in C#?

Question

I am trying to retrieve data from multiple tables from an Oracle database using C#. I use while (reader.Read()) to get all the records from a table. However, this time I want to get only one specific record and store it in an object of class called production. I have the following code:

public DataTable GetProduction(string serialNr)
        {
            DataTable dataTable = new DataTable();

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                string queryString = "SELECT E.SERIENR, R.PRODUCT_ID, R.LIJNSOORT, R.RATIO, R.AANTAL_PLAKKEN, R.EXTRA_INFO_ALFANUMERIEK_1, H.LAMELHOOGTE, R.LAMELAANTAL " +
                    "FROM RO_EXEMPLAAR E JOIN " +
                    "RO_ROOSTER R ON E.PRODUCT_ID = R.PRODUCT_ID JOIN " +
                    "RO_LAMELHOOGTE H ON H.LIJNSOORT = R.LIJNSOORT AND H.RATIO = R.RATIO " +
                    "WHERE E.SERIENR=" + serialNr;
                OracleCommand command = new OracleCommand(queryString);
                try
                {
                    command.Connection = connection;
                    connection.Open();
                    OracleDataAdapter da = new OracleDataAdapter(command);
                    da.Fill(dataTable);
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            return dataTable;
        }

When I execute this by giving a serial number, it is giving me the error saying that the identifier is invalid. I checked the sql statement and it seems all correct to me. How can I solve this issue? I also want to create a production object using the data stored in this DataTable. How is this possible? Thanks a lot in advance!

Answer 1

I used the Oracle SQL developer query tool and I realized that it does not allow using + in the middle of the query. That is why I removed the + signs from my queryString.

Since my aim was to be able to pass any serial number into query I used command.Parameters.AddWithValue("serialNr", serialNr);

Here is the entire code:

public DataTable GetProduction(string serialNr)
        {
            Production temp = null;
            DataTable dataTable = new DataTable();

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                string queryString = "SELECT E.SERIENR, R.PRODUCT_ID, R.LIJNSOORT, R.RATIO, R.AANTAL_PLAKKEN, R.EXTRA_INFO_ALFANUMERIEK_1, H.LAMELHOOGTE, R.LAMELAANTAL FROM RO_EXEMPLAAR E JOIN RO_ROOSTER R ON E.PRODUCT_ID = R.PRODUCT_ID JOIN RO_LAMELHOOGTE H ON H.LIJNSOORT = R.LIJNSOORT AND H.RATIO = R.RATIO WHERE E.SERIENR=:serialNr";
                OracleCommand command = new OracleCommand(queryString);
                try
                {
                    command.Connection = connection;
                    connection.Open();
                    command.Parameters.AddWithValue("serialNr", serialNr);
                    OracleDataAdapter da = new OracleDataAdapter(command);
                    da.Fill(dataTable);
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
            return dataTable;
        }

To access the stored information from the dataTable:

string serialNo = dt.Rows[0]["SERIENR"].ToString();
int productID = Convert.ToInt32(dt.Rows[0]["PRODUCT_ID"]); 

I hope that this helps you too!